오라클 실행계획 보는 법, trace 남기는 법

trace 하기

ALTER SESSION SET "_oracle_script" = true;

 

ALTER SESSION SET tracefile_identifier='jinwook';

 

ALTER SESSION SET sql_trace=TRUE; -- 시작

ALTER SESSION SET sql_trace=FALSE; -- 종료

 

-- 생성된 경로 확인

SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS;
SELECT name,VALUE FROM v$diag_info;

 

TKPROF 생성된파일명 변환할파일명

--<SQL2>
SELECT ALL      PLAYER_NM FROM TB_PLAYER

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          4          0           5

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 9  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         5          5          5  TABLE ACCESS FULL TB_PLAYER (cr=3 pr=0 pw=0 time=24 us starts=1 cost=2 size=135 card=5)

 

방금 실행한 쿼리 분석

SELECT SUBSTR(SQL_TEXT, 1, 30) SQL_TEXT, SQL_ID , CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE '%(A.JOB_NM, B.DEPT_NM)%' AND SQL_TEXT NOT LIKE '%V$SQL%'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('조회한 SQL_ID', 0, 'ALLSTATS LAST'));SELECT SUBSTR(SQL_TEXT, 1, 30) SQL_TEXT,
       SQL_ID
     , CHILD_NUMBER
  FROM V$SQL
 WHERE SQL_TEXT LIKE '%(A.JOB_NM, B.DEPT_NM)%'
   AND SQL_TEXT NOT LIKE '%V$SQL%';
   
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('조회한 SQL_ID', 0, 'ALLSTATS LAST'));

SQL_ID  aw206bs0p4us1, child number 0
-------------------------------------
SELECT JOB_NM
      , B.DEPT_NM
      , AVG(A.CUR_SAL) AS CUR_SAL
   
FROM TB_EMP A
      , TB_DEPT B
 WHERE A.DEPT_CD = B.DEPT_CD
 
GROUP BY ROLLUP (A.JOB_NM, B.DEPT_NM) --JOB+DEPT_NM별, JOB별, 전체
 
Plan hash value: 2749281032
 
-------------------------------------------------------------------------------
| Id  | Operation            | Name       | E-Rows |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |        |       |       |          |
|   1 |  SORT GROUP BY ROLLUP|            |     10 |  2048 |  2048 | 2048  (0)|
|*  2 |   HASH JOIN          |            |     10 |  1265K|  1265K|  791K (0)|
|   3 |    TABLE ACCESS FULL | TB_DEPT    |      5 |       |       |          |
|   4 |    TABLE ACCESS FULL | TB_EMP     |     10 |       |       |          |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."DEPT_CD"="B"."DEPT_CD")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level