자수성가한 부자

1. result_cache 힌트는?

 - 쿼리 결과를 caching 하라는 힌트이다.

 - 조회 용도의 select 되는 건이 많지 않고, 값이 변하지 않는 데이터(예를 들면 과거 매출 데이터) 등을 집계 / 분석하는 쿼리에 result_cache 힌트를 사용하면 유용하다.

 - caching 할 크기가 너무 커서 result_cache_max_size, result_cache_max_result 파라미터 값을 넘어가면 캐싱 기능을 사용할 수 없다.

- Data dictionary, Temporary Table에 대한 쿼리 또는 시퀀스에 대한 curval, nextval에 대한 쿼리는 캐시되지 않는다.

관련 파라미터 

SQL> show parameter result_cache

NAME      TYPE  VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag       big integer 3000
client_result_cache_size      big integer 0
result_cache_max_result       integer  5
result_cache_max_size      big integer 11904K
result_cache_mode      string  MANUAL
result_cache_remote_expiration      integer  0

result_cache_max_result : 단일 result_set 캐시안에 캐시될 최대 비율(%)이며, 이를 초과할 경우 캐싱 기능을 사용할 수 없는 invalid 상태가 된다.

result_cache_mode : default 값이 manual이며, result_cache 힌트를 명시해야 결과값 캐싱이 된다.

result_cache_max_size : 캐싱되는 최대 바이트 사이즈 이다.

result_cache_remote_expiration : 원격오브젝트에 대한 쿼리 결과로 캐싱되어 유효한 시간

2. result_cache 힌트를 사용한 SQL 수행 결과

SQL> select /*+ result_cache */ count(*)
from test_objs a, test_segs b
where a.object_name <> b.segment_name;  2    3  

  COUNT(*)
----------
 411839796

Elapsed: 00:00:32.20
SQL> /

  COUNT(*)
----------
 411839796

Elapsed: 00:00:00.01

참고사이트 : 

https://blog.naver.com/dbmsexpert/221185913762

리눅스를 모니터링 할 때 가장 유용하게 사용되는 명령어인 Top에 대해서 알아보겠습니다.

top을 통해 살펴보면 프로세스 정보들

top

- 시스템의 상태를 전반적으로 가장 빠르게 파악 가능함(CPU, Memory, Process)

- 옵션 없이 입력하면 interval 간격(기본 3초)으로 화면을 갱신하여 정보를 보여줌

- top 실행 전 옵션

   순간의 정보를 확인하려면 -b 옵션 추가 (batch 모드)

   -n : top 실행 주기 설정(반복 횟수)

top 실행 후 명령어

 - shift + p : CPU 사용률 내림차순

 - shift + m : 메모리 사용률 내림차순

 - shift + t : 프로세스가 돌아가고 있는 시간 순

 - k : kill, k 입력 후 PID 번호 작성, singal은 9

 - f : sort file 선택화면 -> q 누르면 RES 순으로 정렬

 - a : 메모리 사용량에 따라 정렬

 - b : Batch 모드로 작동

 - 1 : CPU Core 별로 사용량 보여줌

ps와 top의 차이점

- ps는 ps한 시점에 proc에서 검색한 cpu 사용량

 - top은 proc에서 일정 주기로 합산해 cpu 사용율 출력

- top -b -n 1 수행 시에 출력되는 화면

- 36days, 22:26 : 서버가 구동된지 36일 22시간 26분이 되었음

- load average : 현재 시스템이 얼마나 일을 하는지를 나타냄, 3개의 숫자는 1분, 5분, 15분 간의 평균 실행 / 대기 중인 프로세스의 수, CPU 코어수 보다 적으면 문제 없음.

- Tasks : 프로세스의 갯수

- KiB Mem, Swap : 각 메모리의 사용량

- PR : 실행 우선 순위

- VIRT, RES, SHR : 메모리 사용량 => 메모리 누수 check 가능함.

- S : 프로세스 상태(작업중, I/O 대기, 유휴 상태 등)

VIRT, RES, SHR

메모리 사용량 값에 대한 설명

- 현재 프로세스가 사용하고 있는 메모리

- VIRT

  - 프로세스가 사용하고 있는 virtual memory의 전체 총량

  - 프로세스에 할당된 가상 메모리 전체

  - SWAP + RES

- RES

  - 현재 프로세스가 사용하고 있는 물리 메모리의 양

  - 실제로 메모리에 올려서 사용하고 있는 물리 메모리

  - 실제로 메모리를 쓰고 있는 RES가 핵심!!

- SHR

  - 다른 프로세스와 공유하고 있는 shared memory의 양

  - 예시로 라이브러리를 들 수 있음. 대부분의 리눅스 프로세스는 glibc라는 라이브러리를 참고하기에 이런 라이브러리를 공유 메모리에 올려서 사용

Memory Commit

 - 프로세스가 커널에게 필요한 메모리를 요청하면 커널은 프로세스에 메모리 영역을 주고 실제로 할당은 하지 않지만 해당 영역을 프로세스에게 주었다는 것을 저장해둠.

 -이런 과정을 Memory Commit이라 부름

 - 왜 커널은 프로세스의 메모리 요청에 따라 즉시 할당하지 않고 Memory Commit 과 같은 기술을 사용해 요청을 지연시킬까?

   - fork()와 같은 새로운 프로세스를 만들기 위한 콜을 처리해야 하기 때문

   - fork() 시스템 콜을 사용하면 커널은 실행중인 프로세스와 똑같은 프로세스를 하나 더 만들고, exec() 시스템 콜을 통해 다른 프로세스로 변함. 이 때 확보한 메모리가 쓸모 없어질 수 있음

   - COW(Copy-On-Write) 기법을 통해 복사된 메모리 영역에 실제 쓰기 작업이 발생한 후 실질적인 메모리 할당을 진행

프로세스 상태

- SHR 옆에 있는 S 항목으로 볼 수 있음

  : D : Uninterruptiable sleep, 디스크 혹은 네트워크 I/O를 대기

  : R : 실행 중(CPU 자원을 소모)

  : S : Sleeping 상태, 요청한 리소스를 즉시 사용 가능

  : T : Traced or Stopped, 보통의 시스템에서 자주 볼 수 없는 상태

  : Z : zombie, 부모 프로세스가 죽은 자식 프로세스

참고사이트 : https://zzsza.github.io/development/2018/07/18/linux-top/

gather_plan_statistics의 힌트는 무엇인가?

Oracle DBMS 10g 부터 gather_plan_statistics 힌트를 이용하면 SQL trace를 수행하지 않고도 쿼리의 Plan 단계별로 row수, block의 개수, 사용되는 힌트 등의 SQL 통계정보를 확인할 수가 있게 되었다.

이에 쿼리 성능을 확인, 비교할 수 있기 때문에 SQL 튜닝 시에 아주 빈번하게 사용되는 유용한 힌트절이다.

사용방법)

1. 테스트 수행하는 Oracle DB의 버전은 19c 이다.

2. sample SQL의 실행

   SQL에 gather_plan_statistics(/*+ gather_plan_statistics */ )를 부여한다.

3. 수행한 SQL의 sql_id를 확인한다. jgh_01을 comment로 넣었으므로 이 값으로 like 검색하면 확인할 수 있다.

    조회한 sql_id는 985k8bddta87t 임을 확인할 수 있다.

4. 3에서 확인한 sql_id로 dbms_xplan.display_cursor로 SQL의 통계를 확인할 수 있다.

set lines 200

set pages 10000

위 옵션은 SQL*PLUS에서 한줄의 표현할 수 있는 라인의 갯수와 페이지의 크기를 지정할 수 있다.

dbms_xplan.display_cursor 수행시 아래와 같은 결과를 얻을 수 있다.

각 항목의 설명은 캡쳐된 화면 아래에서 확인할 수 있다.

Id, Operation, Name

이 부분은 SQL plan의 정보이다. 객체에 대한 Access 순서(driving)와 Access 방법(index scan, table scan, join의 순서)을 나타낸다. 참고로 Access 순서를 변경할 수 있는 힌트절은 ordered, leading 등이 있고 Access 방법을 변경할 수 있는 힌트절은 use_nl, use_hash, use_merge, index, full 등이 있다.

Starts

오퍼레이션을 수행한 횟수를 의미한다. Starts * E-Rows의 값이 A-Rows 값과 비슷하다면 통계정보의 예측 Row 수와 실제 실행 결과에 따른 실제 Row 수가 유사함을 알 수 있다. 만약 값에 큰 차이가 있다면 통계정보가 실제의 정보를 제대로 반영하지 못했다고 생각할 수 있다. 이로인해 오라클 DBMS의 옵티마이져가 잘못된 실행 계획을 수립할 수도 있으므로 이 때는 SQL의 튜닝이 필요하다.

E-Rows (Estimated Rows)

통계정보에 근거한 예측 Row 수를 의미한다. 통계정보를 갱신할수록 값이 매번 다를 수 있으며, 대부분의 DB 운영에서는 통계정보를 수시로 갱신하지 않으므로 해당 값에 큰 의미를 둘 필요는 없다. 하지만 E-Rows 값과 A-Rows 값이 현격하게 차이가 있다면 오라클이 잘못된 실행계획을 세울 수도 있음을 인지해야 하며 통계정보 생성을 검토해 보아야 한다.

A-Rows (Actual Rows)

쿼리 실행 결과에 따른 실제 수행 시간을 의미한다. 하지만 실행 시점의 여러 상황에 늘 가변적이고 또한 메모리에 올라온 Block의 수에 따라서 수행 시간이 달라지므로 해당 값에  큰 의미를 둘 필요는 없다.

Buffer (Logical Reads)

논리적인 Block의 수를 의미한다. 해당 값은 오라클 옵티마이저가 일한 총량을 의미하므로, 튜닝을 진행할 때 대부분의 튜너들이 가장 중요하게 생각하는 요소 중 하나이다.

Reads (Physical Reads)

물리적인 Get Block 수를 의미한다. 동일한 쿼리일 경우에는 값이 0인 것을 보면 알 수 있듯이 메모리에서 읽어 온 Block은 제외된다. 해당 값에 큰 의미를 둘 필요는 없다.

위의 항목들 중 튜닝 시에 가장 중요하게 생각되고 활용되는 값은 Buffers와 A-Rows이다. Buffers 값을 통해서 Get Block의 총량을 알 수 있고, A-Rows를 통해 실행 계획 단계별로 실제 Row 수를 알 수 있기 때문이다.

참고 사이트 : 

https://jmkjb.blogspot.com/2015/06/gatherplanstatistics.html

oratop - Oracle Database Monitoring Tool

oratop이란?

오라클이 제공하는 Oracle Call Interface(OCI) API를 이용한, 텍스트 기반 데이터베이스 모니터링 툴입니다. 현재 데이터베이스 활동 상태와 성능을 모니터링하고, 경합과 병목지점을 식별할 수 있습니다. 오라클 데이터베이스 11gR2 버전부터 지원하며, 유닉스 top 유틸리티와 유사한 인터페이스를 가지고 있습니다.

바이너리 파일 위치

oracle$ cd $ORACLE_HOME/suptools

oracle$ ls
oratop tfa

주요 특징

 - 프로세스, SQL 모니터링
 - 실시간 Wait Event
 - Active Data Guard 지원
 - 멀티태넌트 CDB 지원

수행방법

./oratop -h

 => 수행방법 및 도움말.

local 데이터베이스를 모니터링 할 경우

$ ./oratop -i 10 / as sysdba

remote 데이터베이스를 모니터링 할 경우

$ ./oratop -i 10 username/password@tns_alias

$ ./oratop -i 10 system/manager@tns_alias

예)
./oratop -i 10 -f -m / as sysdba

10초 간격으로 Module/Action 정보를 포함해서 상세 포맷으로 데이터베이스 정보를 출력하도록 oratop을 실행해보겠습니다.
참고로, 데이터베이스 statistics_level 레벨이 "BASIC"으로 설정되어 있으면, oratop이 모니터링 정보를 제대로 가져올 수 없다.

기본 모드로 보인다.(standard format)

f 입력시 자세한 모드로 볼 수 있다.(detailed format)

각 항목의 의미

Section 1 - Global Database information

   Version        : Oracle major version
   role           : database role 
   db name        : db_unique_name
   time           : time as of the most recent stats (hh24:mi:ss)
   up             : database uptime (UTC)
   ins            : total number of instance(s)
   sn             : total user sessions (non-predefined)
   us             : number of distinct users (non-predefined)
   sga            : system global area (SGA)
   fra            : flashback recovery area %used
   er             : diag active problem count (checked once at start)
   %db            : %Active Database (work)

Section 2 - Top 5 Instance(s) Activity ordered by Database Wait Time Ratio Desc

   ID             : Instance Id.
   CPU            : CPU Count
   %CPU           : Host CPU Utilization (%busy)
   %DCP           : Database cpu usage as %CPU
   LOAD           : Current OS Load
   AAS            : Average Active Sessions (dbtime (s/s))
   ASC            : Active Sessions on CPU
   ASI            : Active Sessions waiting on User I/O
   ASW            : Active Sessions Waiting on other events
   IDL            : Idle User Sessions (non-predefined)
   ASP            : Active Parallel Sessions (F/G)
   LAT            : Average Synchronous Single-Block Read Latency (Threshold: 20ms)
   MBPS           : I/O Megabytes per Second (throughput R/W)
   IOPS           : I/O Requests per second (R/W)
   R/S            : Physical Read Total IO Requests Per Sec
   W/S            : Physical Write Total IO Requests Per Sec
   LIO            : Logical Reads Per Sec
   GCPS           : GC (CR+Current) Block Received Per Second
   %FR            : Shared Pool Free %
   PGA            : Total PGA Allocated
   TEMP           : Temp Space Used
   UTPS           : User Transaction Per Sec
   UCPS           : User Calls Per Sec
   RT/X           : Response Time Per Txn
   DCTR           : Database CPU Time Ratio
   DWTR           : Database Wait Time Ratio

Section 3 - Top 5 Timed Events ordered by wait time desc (Cluster-wide, non-idle)

  EVENT           : wait event name
        (RT)      : Real-Time mode
  TOTAL WAITS     : total waits
  TIME(s)         : total wait time in seconds)
  AVG_MS          : average wait time in milliseconds
  PCT             : percent of wait time (all events)
  WAIT_CLASS      : name of the wait class

Section 4 - Non-Idle processes ordered by event wait time desc.

   ID             : inst_id
   SID            : session identifier
   SPID           : oraserver process os id
   MODULE         : Name of the currently executing module
   ACTION         : Name of the currently executing action
   SRV            : SERVER (dedicated, shared, etc.)
   SERVICE        : db service_name
   PGA            : pga_used_mem
   SQL_ID/BLOCKER : sql_id or the final blocker's (inst:sid)
   OPN            : operation name, e.g. select
   E/T            : session elapsed time (active/inactive)
   STA            : ACTive|INActive|KILled|CAChed|SNIped
   STE            : process state, e.g. on CPU or user I/O or WAIting
   WAIT_CLASS     : wait_class for the named event
   EVENT/*LATCH   : session wait event name. Auto toggle with *latch name
   W/T            : event wait time (Threshold: 1s)

t를 누르면 테이블 스페이스 정보를 얻을 수 있다.

r을 누르면 Wait Event 정보를 볼 수 있다.

batch mode

x 키보드 눌렀을 경우

sql에 대한 정보를 볼 수 있는 화면이 나옴.

SQL의 실행계획을 볼 수 있다.

p 키보드를 눌렀을 경우 (session/process 모드)

i 키보드를 눌렀을 경우 갱신되는 주기를 입력할 수 있다. (초단위)

h 키보드를 눌렀을 경우 help를 볼 수 있다.

참고문서

oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)
https://feellikeghandi.tistory.com/12

인덱스 정보

일별지수업종별거래및시세_FK : 지수구분코드 + 지수업종코드 + 거래일자

일별지수업종별거래및시세_X01 : 거래일자

튜닝전 쿼리

튜닝 포인트

지수구분코드 + 지수업종코드 + 거래일자 컬럼에 해당되는 인덱스가 있으나

지수구분코드||지수업종코드 이렇게 인덱스에 포함된 컬럼이 가공되어 있어

INDEX RANGE SCAN이 되지 못하고 있다.

INDEX RANGE SCAN이 될 수 있도록 인덱스에 포함된 컬럼을 가공하지 않도록 아래와 같이 조건을 변경한다.

변경 전

AND 지수구분코드 || 지수업종코드 in ('1001','2003')

변경 후 

AND (지수구분코드, 지수업종코드) in (('1','001'),('2','003'))

튜닝 후 전체 쿼리

출처 : 

오라클 성능 고도화 원리와 해법Ⅱ p.31

4. NULL 관련 함수

COALESCE(컬럼1, 컬럼2, ..., 컬럼n) 

: 처음으로 null이 아닌 컬럼을 만나면 그 컬럼 값을 리턴한다.

NULLIF(컬럼1, 컬럼2) 

: 컬럼1과 컬럼2가 같으면 null을 반환하고, 컬럼1과 컬럼2가 다르면 컬럼1을 리턴한다.

NVL(컬럼1, 컬럼2)

 : 컬럼1이 null이면 컬럼2를 반환하고 , null이 아니면 컬럼1을 반환한다.

공통사항

문법

RANK() OVER (

    [PARTITION BY <value expression1>] [,....]

    ORDER BY <value expression2> [collate clause] [ASC/DESC]

      [NULLS FIRST|NULLS LAST] [,...]

)

각 절의 설명  

- OVER

  쿼리 result set을 이용해 동작하는 함수라는 구분이다.

- PARTITION BY 

  result set을 value expression에 지정된 값에 근거하여 분할하는 역할 수행

- ORDER BY 

  각 PARTITION 내에서 DATA가 어떤 값을 기준으로 정렬될 것인가를 지정.

- NULLS FIRST | NULLS LAST

   NULL이 포함된 ROW가 순서상 제일 앞에 위치할 것인지 제일 뒤에 위치할 것인지를 지정.

두 함수의 차이점 

RANK() : 

등수를 리턴하는 함수로 같은 등수가 있을 때는 그 등수를 제외하고 등수가 부여됨.
          예) 1,2,2,4,5,5,7 ..

DENSE_RANK() :

등수를 리턴하는 함수로 같은 등수가 있더라도 그 등수를 빼지 않고 등수를 부여함.
         예) 1,2,2,3,4,4,5 ..

SELECT coalesce (컬럼1, 컬럼2, ..., 컬럼n)

FROM 테이블명;

coalesce 함수는 처음으로 null이 아닌 값을 만나면 그 컬럼 값을 리턴한다.

컬럼1 이 null 이 아니면 컬럼 1을 리턴,

컬럼1 이 null이고 컬럼 2가 null이 아니면 컬럼 2를 리턴,

컬럼1부터 컬럼N-1까지가 값이 null이면 컬럼N을 리턴한다.

예제)

SELECT coalesce('A','B','C') as 'COALESCE_TESE' from dual 

UNION ALL

SELECT COALESCE(NULL, 'B','C') FROM dual

UNION ALL

SELECT COALESCE(NULL,NULL,NULL) FROM dual;

COALESCE_TEST

--------------------------

A

B

null

우선 OUTER JOIN이란,

INNER JOIN이 JOIN 조건에 부합하는 행만 JOIN이 발생하는 거라면,

OUTER JOIN은 조건에 부합하지 않는 행까지도 포함시켜 결합하는 것을 의미한다.

LEFT OUTER JOIN이란?

OUTER JOIN의 일종이므로 JOIN 조건에 부합하지 않는 행까지 반환시키되,

왼쪽에 있는 테이블의 모든 행을 포함시키는 것을 의미한다.

예를 들어 아래의 쿼리를 보면

두개의 테이블을 JOIN 하고 있다,

usertable 과 buytable이다.

left outer join은 왼쪽 테이블의 행은 조건에 부합하지 않더라도 모두 출력되어야 한다는 의미이다.

즉, from 첫번째 테이블 left outer join 두번재 테이블이라면, 첫번째 테이블의 것은 모두 출력 되어야 한다.

Inner join 시 inner 키워드를 생략 가능했던 것처럼

left outer join 역시 left join 만으로 작성해도 무방하다.

위 예제의 결과는 아래와 같다. (모든 usertable의 행이 출력되었다.)

서브쿼리 unnesting 

중첩된 서브쿼리를 풀어냄(서브쿼리 nounnesting : 그대로 둠) 

SELECT * FROM EMP A

WHERE EXISTS (

SELECT 'X' FROM DEPT

WHERE DEPTNO = A.DEPTNO

)

   AND SAL >

(SELECT AVG(SAL) FROM EMP B

 WHERE EXISTS (

SELECT 'X' FROM SALGRADE

WHERE B.SAL BETWEEN LOSAL AND HISAL

    AND GRADE = 4)

);

-- 메인 쿼리에서 읽히는 레코드마다 서브쿼리를 반복 수행하며 필터링

-- 옵티마이저는 Unnesting을 하는 것이 최적일지 아닐지를 고민하고 선택

서브쿼리 unnesting의 이점

서브쿼리를 메인 쿼리와 같은 레벨로 풀어내면 다양한 access 경로와 JOIN 메쏘드를 평가할 수 있음.

옵티마이저는 많은 join 테크닉을 가지고 있으므로, JOIN 형태로 변환했을 때 더 나은 실행계획을 찾을 가능성이 높아짐.

힌트 

/*+ unnest */ : 서브쿼리를 unnesting 함으로써 JOIN 방식으로 최적화하도록 유도

/*+ no_unnest */ : 서브쿼리를 둔 상태에서 필터 방식으로 최적화하도록 유도

서브쿼리 Unnesting 기본 예시

 unnest 힌트를 주게 되면 아래의 두 쿼리와 같은 형태로 서브 쿼리가 풀리면서 조인 형태로 되게 되고,

이에 따라 조인 방식의 변경 등을 통해 다양한 튜닝 기법을 구사할 수 있음.

참고사이트 

https://jungmina.com/755

https://everyday-deeplearning.tistory.com/entry/ORACLE-SQL-%ED%8A%9C%EB%8B%9D-%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC-%ED%8A%9C%EB%8B%9D%EC%A1%B0%EC%9D%B8-%ED%9E%8C%ED%8A%B8-nomerge-nounnest