Drop Database
Explain plan/SQL Trace file 보는법
한알두알
2007. 10. 30. 18:18
|
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을 사용한다. |