오라클 쿼리 튜닝 방법(기초)

오라클 쿼리 튜닝방법에 대해 간단히 알아봅니다. 실제 다양한 튜닝 경험이 필요하나 간단한 부하쿼리 확인, 전체쿼리 확인, 바인드 변수 확인, 플랜 보기, 인덱스 생성등의 방법으로 알아보겠습니다.

 

부하 쿼리 확인

부하쿼리를 확인합니다. last_load_time에 날짜를 지정해서 좀더 범위를 좁혀서 확인할 수 있습니다.

SELECT ROWNUM cnt ,
       t2.*
FROM   (
        SELECT t1.parsing_schema_name
              ,t1.module
              ,t1.sql_id
              ,t1.hash_value
              ,t1.substr_sqltext
              ,t1.executions
              ,t1.buffer_gets                   -- 캐시/버퍼에서 읽은 블록수(8KB)
              ,t1.disk_reads                     -- 디스크에서 읽은 블록 수(8KB)
              ,t1.rows_processed
              ,t1.cpu_time
              ,t1.elapsed_time
              ,ROUND( t1.cpu_time /t1.cpu_time_total*100 , 1 ) ratio_cpu
              ,ROUND( t1.elapsed_time /elapsed_time_total * 100 , 1 ) ratio_elapsed_time
              ,t1.last_load_time
              ,t1.avg_buffer_gets_KB
              ,t1.avg_disk_reads_KB
              ,t1.avg_elapsed_time_sec
        FROM   (
                SELECT parsing_schema_name
                       ,MODULE
                       ,sql_id
                       ,hash_value
                       ,SUBSTR( sql_text , 1 , 100 ) substr_sqltext
                       ,executions
                       ,buffer_gets                 
                       ,disk_reads
                       ,rows_processed
                       ,cpu_time
                       ,elapsed_time                                                  --      Elapsed time (in microseconds)    
                       ,SUM( cpu_time ) over( ) cpu_time_total
                       ,SUM( elapsed_time ) over( ) elapsed_time_total
                       ,last_load_time
                       , ROUND( NVL( buffer_gets / DECODE(executions, 0, NULL, executions)*8, 0),1) avg_buffer_gets_KB
                       , ROUND( NVL( disk_reads / DECODE(executions, 0, NULL, executions)*8, 0),1) avg_disk_reads_KB
                       , ROUND( NVL( elapsed_time / DECODE(executions, 0, NULL, executions)/1000000, 0),3) avg_elapsed_time_sec
                FROM   v$sqlarea s
               ) t1
        WHERE  t1.executions > 0
        AND    t1.parsing_schema_name NOT IN ( 'SYS' , 'SYSTEM' , 'SYSMAN', 'RDSADMIN' )
        AND    t1.last_load_time > '23/10/30'  -- 날짜타입
        ORDER  BY ratio_cpu DESC
       ) t2
WHERE  ROWNUM <= 100;

 

위와 같이 수행하면 부하쿼리 순서대로 볼수있습니다 .CPU부하 중심으로 볼수 있으나, 평균 CPU, 평균 수행시간등 다양한 관점에서 볼수 있습니다.

결과에서 쿼리의 SQL_ID로 전체쿼리를 볼수 있습니다.

 

쿼리 전체 확인

-- SQL_ID 로 쿼리 조회
SELECT SQL_ID, PIECE, SQL_TEXT
  FROM V$SQLTEXT
WHERE SQL_ID = 'cvny6qgxmuft3'
ORDER BY PIECE;

 

바인드 변수가 있는 쿼리는 별도로 확인이 어렵기 때문에 바인드변수에 어떤 값이 들어갔었는지 아래 쿼리로 확인이 가능합니다.

Bind SQL의 Bind값 확인

-- Bind SQL의 Bind값 확인
select * from v$sql_bind_capture where sql_id='grbnzjd7g5w6v'

쿼리 플랜 확인

explain plan for
쿼리

select * from table(dbms_xplan.display);

 

위 쿼리의 결과로 플랜을 확인할 수 있습니다.

플랜을 보고 테이블을 전체 스캔하는 부분을 중점으로 확인해 보시기 바랍니다. 너무나 다양한 케이스가 있기 때문에 여기서는 간단히 다루겠습니다.

인덱스 생성

플랜을 보고 where조건에 해당하는 부분에 인덱스가 없다면 아래와 같이 인덱스를 생성해 주시기 바랍니다.

create index ix_table_001 on table(id);

 

쿼리 플랜 다시 확인하고, 실행해서 실제 쿼리의 수행시간이 변경되었는지 확인합니다.

이상 간단하게 부하쿼리를 수집하고, 프랜을 확인하고, 인덱스를 생성하는 방법에 대해 알아봤습니다.