SQL Trace와 TKPROF 유틸리티

☞ SQL Trace ◈ SQL Trace     - SQL Trace는 실행되는 SQL문의 실행통계를 세션별로 모아서 트레이스 파일을 만듭니다.   - SQL Trace는 세션과 인스턴스 레벨에서 SQL문장들을 분석 할 수 있습니다.   - SQL Trace에 의해 생성된 파일의 확장자는 .TRC 입니다.   - .TRC파일은 직접 읽을수가 없으므로 반드시 TKPROF 유틸리티를 실행 시켜야 합니다.   - init.ora파일에서 SQL Trace를 지정하여 인스턴스 레벨로 Trace를 수행시키면     전체적인 수행능력이 20~30% 정도 감소합니다.   ◈ SQL Trace에서 제공하는 정보   - parse, execute, fetch count      : 오라클의 SQL 처리 작업에서 parse,execute,fetch 작업들이 처리된 횟수   - 수행된 CPU 프로세스 시간과 경과(Elapsed)된 질의 시간들      : SQL문을 실행하는데 소비된 CPU시간과 실질적인 경과시간   - 물리적(Disk)/논리적(Memory) 읽기를 수행한 횟수      : 질이의 parse, execute, fetch 부분들에 대해 디스크에 있는 데이터파일들로부터        읽은 데이터 블록들의 전체 개수   - 처리된 로우수 : 결과 set을 생성하기 위해 오라클에 의해 처리된 행의 전체 개수   - 라이브러리 캐쉬 miss : 분석된 문장이 사용되기 위해  라이브러리 캐쉬 안으로 로드되어야 하는 횟수     ◈ SQL Trace와 관련된 파라미터   - TIMED_STATISTICS : RDBMS가 추가적인 CPU시간, 실행시간등을 모을수 있게 합니다.      → 이 시간통계는 SQL악성 여부를 판단하는 중요한 요소가 됩니다.      → ALTER SESSION SET TIMED_STATISTICS=TRUE 또는 init.ora파일에 설정     - SQL_TRACE : SQL Trace의 수행여부      .ALTER SESSION SET sql_trace=TRUE 또는 init.ora파일에 설정         - USER_DUMP_DEST : Trace파일이 생성되는 디렉토리를 지정 합니다.       - MAX_DUMP_FILE_SIZE : 트레이스파일의 최대 크기(단위: OS블럭수)   ◈ SQL Trace 실행 방법   1. ALTER SESSION SET SQL_TRACE=TRUE;   2 .EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(TRUE)   3. EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(session_id, serial_id, TRUE)   4. Init parameter 설정: SQL_TRACE = TRUE -- SQL_TRACE수행 SQL> ALTER SESSION SET SQL_TRACE = TRUE; 명령문이 처리되었습니다.     ☞ TKPROF ◈ TKPROF(trace 파일 출력)   - TKPROF는 SQL Trace를 통해 생성된 트레이스파일을 분석이 가능한 형식으로 전환하여      출력 합니다.     Syntax    TKPROF tracefile  outputfile  [SORT=number] [PRINT = number]                 [EXPLAIN=username/password]   - tracefile : SQL Trace 의해 생성된 통계정보를 가진 파일명   - outputfile : TKPROF가 읽기가능한 텍스트 파일로 생성할 파일명   - sort=option : 지정된 OPION(EXECPU,FCHDSK,PRSCPU)에 ASCENDING 순으로                       SQL 문을 정렬합니다.        ex) SORT=EXECPU -> 가장나쁜 Execute CPU값을 가진 통계값을 먼저 출력합니다.   - print : 지정된 수의 SQL문에 대해서만 TRACE 결과를 PRINT 합니다.   - explain : SQL문의 EXECUTION PLAN(실행계획) 을 수립하고 저장합니다.   ◈ TKPROF 실행   --SQL TRACE 파일 위치의 파악 SVRMGR> SHOW PARAMETER USER_DUMP_DEST; NAME             TYPE     VALUE ---------------- -------- ----------------------------- user_dump_dest   string   C:\oracle\admin\oracle\udump   -- TKPROF실행 C:\>TKPROF C:\Oracle\admin\oracle\udump\oracle_ora_1584.trc storm.txt         EXPLAIN=storm/storm      - 생성된 storm.txt파일의 내용   - 실행된 SQL문과 분석정보, 실행계획등이 생성되어 있습니다. ********************************************************************************   SELECT a.day, SUM(a.counter), ROUND(SUM(a.counter)/b.tot, 2)*200 rate, b.tot FROM storm_menu_counter a,         (SELECT max(aa.counter) tot           FROM             (SELECT SUM(counter) counter               FROM storm_menu_counter               WHERE year = 2001                    AND month= 7               GROUP BY day)aa)b  WHERE a.year = 2001       AND a.month = 7 GROUP BY day , b.tot ORDER BY day   call     count    cpu  elapsed  disk  query  current   rows ------- ------  ----- -------- ----- ------ --------  ----- Parse        1    0.01     0.04     1      1        0      0 Execute     1    0.00     0.00     0      0        0      0 Fetch        3    0.00     0.01    55    116       0     23 ------- ------  ----- -------- ----- ------ --------  ----- total          5    0.01     0.06    56    117        0     23   Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 65  (STORM)   Rows     Execution Plan -------  ---------------------------------------------------       0  SELECT STATEMENT   GOAL: CHOOSE       0   SORT (GROUP BY)       0    NESTED LOOPS       0     VIEW       0      SORT (AGGREGATE)       0       VIEW       0        SORT (GROUP BY)       0         TABLE ACCESS (FULL) OF ’STORM_MENU_COUNTER’       0     TABLE ACCESS (FULL) OF ’STORM_MENU_COUNTER’        ********************************************************************************    ☞ TKPROF 결과값 로우/컬럼 설  명 Parse  SQL문이 파싱되는 단계에 대한 통계.  새로 파싱을 했거나 Shared SQL Pool에서 찾아 온 것도 같이 포함 됩니다. Execute  SQL문의 실행 단계에 대한 통계.  Update, Insert, Delete문장들은 여기에 수행한 결과만 나옵니다. Fetch  SQL문이 실행되면서 페치된 통계. count  SQL문이 파싱/실행/페치가 수행된 횟수 cpu  parse, execute, fetch가 실제로 사용한 CPU시간(1/100초 단위) elapsed  작업의 시작에서 종료시까지 실제 소요된 시간  disk  디스크에서 읽혀진 데이터 블럭의 수  query  메모리내에서 변경되지 않은 블럭을 읽거나 다른 세션에 의해 변경되었으나  아직 커밋되지 않아 복사해 둔 스냅샷 블럭을 읽은 블럭 수  SELECT문에서는 거의가 여기에 해당하며 Update,Insert,Delete작업시에는  소량만 발생 합니다.  current  현 세선에서 작업한 내용을 커밋하지 않아 오로지 자신에게만 유효한  블럭(Dirty Block)을 액세스한 블럭 수  주로 Update, Insert, Delete작업시 많이 발생 합니다.  rows  SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수  (서브쿼리에서 추출된 로우는 제외됩니다.) 대용량 데이터베이스 솔루션 1 참고

================================================     * 데이터베이스 정보공유 커뮤니티 oracleclub.com     * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)   ================================================