Oracle - SQL TRACE

[ Oracle - SQL TRACE ]

 

☞ SQL TRACE   1. 정의 : SQL TRACE의 기능을 사용하면 개개의 SQL 문 실행시 실행과정의 및 결과등의 통계 정보를           트레이스.파일에 출력한다. 이 결과를 모니터링하여 SQL튜닝을 실시해야 할 곳을 발견할           수 있다. 2. 단점 : SQL TRACE를 하게 되면 속도가 느려 지므로 필요시에만  상태를 True로 하고 사용하지 않을           경에는 False로 한다.    ☞ SQL TRACE 시작 /종료 OPTION   ■ 현재Session 만을 변경    SQL> alter session set timed_statistics=true;  --(CPU 시간을 알고 싶을 경우에 실행)   SQL> alter session set sql_trace = true;   SQL> select ... (SQL문 처리의 실행)   SQL> alter session set sql_trace = false;   SQL> exit ■ 임의Session을 변경     SQL> alter session set timed_statistics=true;      SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, TRUE);      SQL> select ... (처리의 실행)     SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, FALSE); ■ 인스턴스 전체를 변경   init.ora ->   timed_statistics=true   sql_trace_true   user_dump_dest ...   RESTART INSTANCE   여기서 초기화 파라미터 USER_DUMP_DEST(udump)에 트레이스·파일이 작성 된다.   해당 트레이스·파일은 작성된 타임 스탬프로 확인한다.   이 SQL 트레이스·파일은 SQL문 실행시에 순서대로 정보가 써지므로 이 파일을 보고 SQL문을   분석하는 것은 곤란하다. 다음의 2.에서 소개하는 TKPROF라고 하는 유틸리티를 사용해    SQL 트레이스·파일을 정형하는 것으로 튜닝을 위한 분석을 실시하기 쉽게 한다. 2. TKPROF의 실행 방법      - TKPROF로 SQL 트레이스를 포맷 해 보기 쉽게 한다 상기로 취득한 SQL 트레이스에 대해 TKPROF 커멘드를 실행하면 SQL 트레이스가  알기 쉽게 포맷 됩니다. % tkprof <상기로 작성된 트레이스·파일> <임의의 파일명>   (예： % tkprof oracle_ora_8138.trc tkprof.txt) ※ TKPROF 커멘드에는 많은 옵션이 있습니다.자세한 것은 Oracle8 or Oracle8i 「퍼포먼스를 위한 설계 및 튜닝」또는 Oracle9i  「데이타베이스·퍼포먼스·가이드 및 레퍼런스」의 "SQL 트레이스 기능과 TKPROF의 사용 방법" - 순서 3:TKPROF에 의한 트레이스·파일의 포맷을 참조. 3. 통계 정보를 해석한다    - TKPROF 실행 후의 트레이스·파일의 대략적인 견해(처리에 걸린 시간)  ☞ SQL TRACE시 취득 정보   ◆ Parse    - SQL문이 파싱되는 단계에 대한 통계    - 새로 파싱을 했거나, 공유 풀에서 찾아 온 것도 포함됨.    - 단, PL/SQL 내에서 반복 수행(Loop)된 SQL이나 PL*SQL에서 보존 커서(Hold cursor)를 지정한      경우에는 한 번만 파싱됨. ◆ Execute    - SQL문의 실행단계에 대한 통계임.    - UPDATE, INSERT, DELETE 문들은 여기에 수행한 결과가 나타나게 됨.    - 전체범위 방식으로 처리된 결과가 여러 건인 경우는 주로 여기에 많은 값이 나타나며      Fetch에는 아주 적은 값이 나타남. ◆ Fetch    - SQL문이 실행되면서 Fetch된 통계임.     - 부분범위 방식으로 처리된 SELECT 문들이나 전체범위 처리를 한 후 한 건을 추출하는 경우        (AGGREGATE, 전체집계, COUNT 등)는 주로 여리게 많은 값들이 나타나고 EXECUTE에는 아주      적은 값이 나타남. ◆ COUNT    - SQL문이 파싱된 횟수, 실행된 횟수, FETCH가 수행된 횟수 ◆ CPU    - PARSE, EXECUTE, FETCH가 실제로 사용한 CPU 시간(1/100초 단위) ◆ ELAPSED    - 작업의 시작에서 종료시까지 실제 소요된 총 시간 ◆ DISK    - 디스크에서 읽혀진 데이타 블록의 수 ◆ QUERY    - 메모리 내에서 변경되지 않은 블록을 읽거나 다른 세션에 의해 변경되었으나 아직 Commit되지      않아 복사해 둔 스냅샷 블록을 읽은 블록의 수    - SELECT 문에서는 거의가 여기에 해당하며 UPDATE, DELETE, INSERT시에는 소량만 발생됨. ◆ CURRENT    - 현 세션에서 작업한 내용을 Commit하지 않아 오로지 자신에게만 유효한 블록(Dirty Block)을      액세스한 블록 수    - 주로 UPDATE, INSERT, DELETE 작업시 많이 발생    - SELECT문에서는 거의 없으나 아주 적은 양인 경우가 대부분임. ◆ Logical I/O : QUERY + CURRENT ◆ ROWS    - SQL문을 수행한 결과에 의해 최종적으로 액세스된 ROW의 수     - 서브쿼리에 의해서 추출된 ROW는 제외됨.    - 만약 SUM, AVG, MIN, COUNT 등의 그룹함수를 사용한 경우라면 큰 의미가 없음. ▣ 재귀적 콜(RECURSIVE CALL)이란：    유저가 발행한 SQL문을 실행하기 위해서 Oracle는 추가의 문장을 발행해야만 하는 경우가 있다.    이러한 문장을 재귀적 콜 또는 재귀적 SQL문 이라고 한다. 예를 들어 충분한 공간이 없는 테이블에    행을 삽입하려고 하면, Oracle는 재귀적 콜을 실행해 동적으로 공간을 할당한다. SQL 트레이스    기능이 사용 가능으로 되어 있을 때 재귀적 콜이 발생하면 TKPROF는 재귀적 콜의 원인이 된    문장에 대해 재귀적 SQL문의 통계를 표시한다. ▣ 재귀적 콜의 분별법：    TKPROF로 출력되고 있는 SQL문이 재귀적 SQL문인가 그렇지 않은가는 각 SQL문의 최후의 「    recursive depth」정보로 판단한다. 이 값이 1이상의 경우, 그 SQL문은 재귀적 SQL문으로서의    통계 정보가 된다. ▣ 「recursive depth」(이하, DEPTH)은, SQL 문이 발행된 재귀 레벨을 나타낸다. 예를 들어 값 0 은     유저가 그 문장을 발행한 것을 나타내 보인다. 값 1은 Oracle이 값0의 문장(유저 발행의 문장)을     처리하는 재귀적 콜로서 그 문장을 생성했다란 것을 나타낸다.값n 는 Oracle 가 그 문장을 값 n-1     의 문장을 처리하는 재귀적 콜로서 생성한 것을 나타낸다.     주의：내부표에 대한 처리만이 재귀적 SQL문이 되는 것은 아니다.     PL/SQL 블록으로       스토어드·프로그램(패키지, 프로시저, 팬쿠션등)을 부르는 처리를 실시했을 경우       스토어드·프로그램 안에서 실행되는 SQL문은 SELECT나 UPDATE여도 재귀적 SQL문                  으로 간주해져 재귀적 SQL문의 통계 정보로서 출력됩니다. 전체의 통계 정보에 대해： 전체의 처리에 걸린 통계 정보는 트레이스·파일의 마지막에 출력되고 있다       OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS       OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS 란 통계 정보로 확인한다. OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS = 비재귀적 SQL문의 통계 정보의 합계 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS = 재귀적 SQL문의 통계 정보의 합계     처리 전체에 걸린 시간은 이 양자를 합계한 시간이 된다.     (※ SQL 트레이스를 취득할 때에 초기화 파라미터 timed_statistics를 TRUE로 하고 있지 않은         경우 에는 처리 시간은 항상 0.00이 된다.

☞ ORANGE TOOL에서 SQL TRACE하기   [단, Oracle이  windows에 설치 되었을 경우의 설명] 1. ORANGE 로 Oracle접속 2. 메뉴 : Tools -> Plan Tool 선택 3. 메뉴 : Tools -> Trace Method -> Local File             (참고, Plan Tool창을 띄워야  Trace 메뉴가 추가 됨) 4. POP_UP창에서 : Trace  file 위치를 [ \oracle\admin\sid\udump ] 로하고                             Trace Name Format은 [ sid_ora_%d.trc ] 로 한다.    5. 다시 Plan Tool 창으로 온다. 6. Trace를 하고 싶은 SQL 문을 KEY IN 한다. 7. ORANGE의 왼쪽에 상단에 TRACE  Icon       을 클릭 한다. 8. Plan Tool 창  하단 TRACE Tab page에서 Trace 내용이 출력 된다. ☞  ORANGE TOOL에서 SQL TRACE 결과 보기   1. SQL문을 KEY IN 후 처음 TRACE를 할 경우     (아래 결과는          - Oracle이  Local PC인  windows XP에 설치 되었다.        - TABLE FULLSCAN 이다        - Record 갯수가  334개 결과가 나오는 SQL문장의 TRACE결과이다           2. SQL문을 KEY IN 후 두 번째 부터의 TRACE결과