Explain plan/SQL Trace file 보는법

Explain plan/SQL Trace file 보는법

1. Explain Plan 보는법      SQL문장을 사용시 중요한 부분중의 하나가 SQL TUNING 이다. SQL문장을 사용시 많이 고민하고 생각하지만 의도 대로 안되는 경우가 많이 있습니다. 이를 확인하는 방법중의 하나가 EXPLAIN 을 사용하는 것입니다.     사용하는 방법도 중요하지만 분석하는 방법이 더욱 중요함을 강조 하고 싶습니다.   사용법    SQL> EXPLAIN PLAN SET STATEMENT_ID='TEXT' FOR       2 SELECT * FROM TAB WHERE TNAME = 'KKK' ;   2번째 라인은 분석하고자 하는 SQL 문장을 쓰시면 됩니다.   확인 법    SQL> SELECT LPAD(' ',2*(LEVEL-1))||OPERATION OPERATION, OPTIONS,OBJECT_NAME         POSITION         FROM PLAN_TABLE         START WITH ID = 0 AND STATEMENT_ID = 'TEXT'         CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'TEXT' ;    위의 SQL 문장의 결과는 2번째 LINE 의 SQL 문장의 ACCESS PATH를 보여 줍니다.    다음은 예를 하나 들겠습니다. plan 의 결과가 다음과 같을 때            OPERATION                 OPTION          OBJNM        POS    ------------------------- --------------- ------------ --- 1   SELECT STATEMENT 2     NESTED LOOPS                                           1 3       NESTED LOOPS                                         1 4         NESTED LOOPS                                       1 5           TABLE ACCESS      BY ROWID        PQASC01T       1 6             INDEX           RANGE SCAN      PQASC01T_PK    1 7           TABLE ACCESS      BY ROWID        PQDLI03T       2 8             INDEX           RANGE SCAN      PQDLI03T_X01   1 9         INDEX               RANGE SCAN      PQDLI06T_PK    2 10      TABLE ACCESS          BY ROWID        PQASC01T       2 11        INDEX               RANGE SCAN      PQASC01T_PK    1   위의 결과는 PQASC01T와 PQDLI03T 라는 두개의 TABLE이 INDEX 를 사용하므로 아무 이상이 없을 겁니다.     그러나 위의 TABLE 중 PQASC01T 의 건수가 적고 상대적으로 PQDLI03T 의 건수가 많을 경우는 아래와 위가 바뀌어야 하지 않을 까요 특히 PQASC01T 가 건수가 적은 TABLE 일 경우는 FULL TABLE SCAN 하는 것도 하나의 방법이지요.     5번 LINE 의 NESTED LOOPS 는 작은 TABLE(PQASC01T) 을 찾고 그리고 큰 TABLE(PQDLI03T) 을 찾는 것이므로 시간이 많이 걸리겠지요.     위의 SQL 문장은 PQASC01T에 있는 INDEX 를 사용하지 않거나 PQDLI03T 의 INDEX를 먼저 사용하는 것이겠지요.     INDEX 를 사용하지 않는 방법은 여러가지가 있습니다.    해당 COLUMN 의 값을 변경하는 경우도 있고 위치를 바꾸는 경우도 있습니다.    ( 인덱스 컬럼의 변형(Suppressing)을 통해서 가능      - 대용량데이터베이스솔루션(이화식 저) 1. 인덱스의 활용 참조 ) 2. SQL_TRACE   1. SQL Trace란?       ● Trace 파일은 시스템을 튜닝하는데 필요한 아주 유효한 정보를 제공한다.     ● 시스템 전체에 대해서 SQL_TRACE를 수행시키면 전체적인 수행성능은        20%~30% 정도 감소한다.     ● SQL문의 실행통계를 Session별로 모아서 Trace 파일을 만든다.        ▶ SQL Parsing, Execute, Fetch를 수행한 횟수        ▶ CPU Time, Elapsed Time(총 경과시간)        ▶ Disk(물리적), Memory(논리적) 읽기를 수행한 횟수        ▶ 추출된 Row의 수        ▶ 라이브러리 캐쉬 miss 수     ● SQL_TRACE에 의해서 생성되는 Trace 파일의 확장자는 .trc이다.        ▶TKPROF 유틸리티로 읽을 수 있는 파일을 생성해야 한다.   2. SQL_TRACE를 수행하는 방법          ▶ SQL*Plus에서는 다음과 같이 세션을 변경시켜야 한다.              SQL>alter session set sql_trace=true;        ▶ 이렇게 설정해 두면 실행되는 질의문에 대해서 트레이스 파일이 생성된           다.        ▶ 생성되는 위치는 init.ora에서 정의한 user_dump_dest 디렉토리에 *.trc           형태로 생성된다.   3. 트레이스 파일 보기          ▶ 생성된 트레이스 파일은(*.trc) 바로 볼 수 없다. 그래서 tkprof라는 유           틸리티를 사용하여 생성된 트레이스 파일을 분석이 가능한 형식으로 전           환하여 준다. 이미 생성된 트레이스 파일이나 트레이스 파일을 생성하고           있는 중에도 tkprof를 수행시킬 수 있다.             트레이스 파일은 SQL문에 대한 실행계획뿐만 아니라 실행시간, 다양한           옵션을 이용하여 분석하기 쉬운 형태 등의 정보를 보여준다.        ▶ tkprof는 다음과 같이 실행시킬 수 있다.             Usage: tkprof tracefile outputfile [explain=user/passwd]             tracefile : 생성된 트레이스 파일명             outputfile : tkprof가 출력하는 텍스트 파일명                          (디폴트로 확장자가 .prf임)             explain=user/passwd : 해당 트레이스 파일이 수행된 세션의 사용자                                   및 패스워드               예) tkprof ccdb_ora_1124.trc 1124.txt explain=scott/tiger 4. 트레이스 파일의 분석          ▶ tkprof는 정형화된 리스트(출력파일)를 생성한다.        ▶ 생성된 파일에는 다음과 같은 내용들을 포함하고 있다. call     count     cpu    elapsed      disk       query    current       rows ------- ------  -------- ---------- ---------- ---------- ----------  -------- Parse     1       0.01      0.01        0           0          0           0 Execute   1       0.00      0.00        0           0          0           0 Fetch     1       0.00      0.02        2           3          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  -------- total     3       0.01      0.03        2           3          0           1          ■ parse         ·SQL문이 파싱되는 단계에 대한 통계이다. 새로 파싱을 했거나, 공유 풀           에서 찾아 온 것도 포함된다.         ·단, PL/SQL 내에서 반복 수행(Loop)된 SQL인 경우에는 한번만 파싱된다.        ■ execute         · SQL문의 실행 단계에 대한 통계이다. UPDATE, INSERT, DELETE 문들은 여            기에 수행한 결과가 나타난다.         · 전체범위 방식으로 처리된 결과가 여러 건인 경우는 주로 여기에 많은            값이 나타나며 fetch에는 아주 적은 값이 나타난다.        ■ fetch         · SQL문이 실해되면서 페치된 통계이다.         · 부분범위 방식으로 처리된 SELECT문들이나 전체범위 처리를 한 후 한 건            을 추출하는 경우(AGGREGATE, 전체집계, Count 등)는 주로 여기에 많은            값들이 나타나고 execute에는 아주 적은 값이 나타난다.        ■ count         · SQL문이 파싱된 횟수, 실행된 횟수, 페치가 수행된 횟수이다.        ■ cpu         · pares, execute, fetch가 실제로 사용한 CPU 시간이다.(1/100초 단위)        ■ elapsed         · 작업의 시작에서 종료시까지 실제 소요된 총 시간이다.        ■ disk         · 디스크에서 읽혀진 데이타 블록의 수        ■ query         · 메모리 내에서 변경되지 않은 블록을 읽거나 다른 세션에 의해 변경 되            었으나 아직 Commit되지 않아 복사해 둔 스냅샷 블록을 읽은 블록의 수            이다.         · SELECT문에서는 거의가 여기에 해당하며 UPDATE, DELETE, INSERT 시에            는 소량만 발생한다.        ■ current         · 현 세션에서 작업한 내용을 Commit하지 않아 오로지 자신에게만 유효한            블록(Dirty Block)을 액섹스한 블록 수이다.         · 주로 UPDATE, INSERT, DELETE 작업시 많이 발생한다. SELECT 문에서는            거의 없으나 아주 적은 양인 경우가 대부분이다.        ■ rows         · SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수이다.         · 서브쿼리에 의해서 추출된 로우는 제외된다.         · 만약 SUM, AVG, MAX, MIN, COUNT 등의 그룹함수를 사용한 경우라면 큰            의미가 없다.  5. 분석결과의 예       ●  트레이스의 중간부분에 'Misses im library cache during parse:1'이             라는 문장이 있다. 이것은 공유 SQL 영역에서 파싱된 결과를 찾지 못             하여 실제 파싱작업을 하게 되었다는 것을 의미한다.     ●  최종적으로 추출된 로우의 수는 적으나 많은 CPU 시간이 소요되었다면             이것은 분명히 적절한 액세스 경로로 수행되지 않았음을 의미한다.     ●  CPU 시간과 ELAPSED 시간의 차이는 적을수록 좋다. 만약 CPU시간에 비             해 ELAPSED 시간이 훨씬 많다면, 그 원인은 다음 중 하나일 가능성이             높다.        ▶ 주변의 다른 세션에서 많은 부하를 발생시켜 시스템 전체에 부하가 많이           걸려있는 경우        ▶ 어플리케이션의 문제이거나 다량의 데이타 처리에 따른 I/O 병목현상이           발생한 경우     ● disk, query, current의 숫자는 적을수록 좋다. 이 숫자들이 커다는 것은        메모리 공유 영역의 적중률(Hit Ratio)이 낮다는 것을 의미한다.     ● 다음은 아주 빠른 응답이 요구되는 온라인 프로세싱 시스템의 경우에서만        적용되는 규칙들이다.        ▶ 모든 Execute 'CPU'가 1초보다 적어야 한다.        ▶ Parse 'CPU' 시간이 Parse당 0.01초보다 적어야 한다.        ▶ 작은 테이블(200로우 이하)에서만 전체 테이블 스캔이 일어나게 한다.        ▶ sysdate만 찾아오거나, 오직 연산만 하거나, 'SELECT ... INTO ...'로           값을 복사하는 경우를 위해서 DUAL 테이블들을 불필요하게 사용하는 것           은 모두 없앤다.        ▶ 동시에 작업되는 SQL들은 가능한 PL/SQL을 사용한다.