[TRACE 11g] monitor hint 를 사용한 Plan 보기(v$sql_monitor, v$sql_plan_monitor)

/*
monitor hint 를 사용한 Plan 보기(v$sql_monitor, v$sql_plan_monitor)
Oracle은 실행 시간이 5초 이상(_sqlmon_threshold 히든 파라미터로 조정)인 모든 쿼리의 실행 이력 정보를 v$sql_monitor와 v$sql_plan_monitor 뷰에 남긴다. 
그리고 dbms_sqltune.report_sql_monitor 함수를 이용해서 정보를 조회할 수 있도록 해준다. 
또는 MONITOR 힌트를 사용해서 강제로 추적하게 할 수 있다
(Oracle 11g ~)
 */ 
 
-- /*+ monitor */ hint 이용한 방법
SELECT /*+ monitor */ 
     A.OWNER
    , A.TABLE_NAME
    , A.COLUMN_ID
    , A.COLUMN_NAME
    , B.COMMENTS
    , A.DATA_TYPE
    , A.DATA_LENGTH
    , A.NULLABLE
FROM   ALL_TAB_COLUMNS A
     , ALL_COL_COMMENTS B
WHERE  A.TABLE_NAME = B.TABLE_NAME
      AND  A.COLUMN_NAME = B.COLUMN_NAME
      AND A.OWNER in ('SPSDC','SPMKT','SPSAC','SPMBR','CMSTORE')      /*owner name*/
--      AND A.TABLE_NAME = 'EMPLOY' /*table name*/
      AND  A.COLUMN_NAME like 'INSD_DEVICE_ID%'
--      AND  B.COMMENTS like '%위치%'
ORDER BY A.TABLE_NAME, A.COLUMN_ID ;


-- sql_id 조회
select sql_id from v$sql where sql_text like 'SELECT /*+ monitor */ %';  


-- 결과 
select dbms_sqltune.report_sql_monitor(sql_id=>'6c29v4vunxz1t') from dual;

spool sqlmon_active.html

select dbms_sqltune.report_sql_monitor(sql_id=>'&sqlid',type=>'active', report_level=>'ALL') from dual;

spool off 

 

 SQL Monitoring Report

SQL Text

SELECT /*+ monitor */ A.OWNER , A.TABLE_NAME , A.COLUMN_ID , A.COLUMN_NAME , B.COMMENTS , A.DATA_TYPE , A.DATA_LENGTH , A.NULLABLE FROM ALL_TAB_COLUMNS A , ALL_COL_COMMENTS B WHERE A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME AND A.OWNER in ('SPSDC','SPMKT','SPSAC','SPMBR','CMSTORE') /*owner name*/ -- AND A.TABLE_NAME = 'EMPLOY' /*table name*/ AND A.COLUMN_NAME like 'INSD_DEVICE_ID%' -- AND B.COMMENTS like '%위치%' ORDER BY A.TABLE_NAME, A.COLUMN_ID

Global Information

 Status              :  DONE (ALL ROWS)      
 Instance ID         :  1                    
 Session             :  SPADMIN (4359:34279) 
 SQL ID              :  6c29v4vunxz1t       

 SQL Execution ID    :  16777216             
 Execution Started   :  06/24/2020 15:47:16  
 First Refresh Time  :  06/24/2020 15:47:16  
 Last Refresh Time   :  06/24/2020 15:47:17  

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    0.73 |    0.21 |     0.52 |     1 |  13790 | 2231 |  17MB |
================================================================

SQL Plan Monitoring Details (Plan Hash Value=2855117487)
==============================================================================================================================================================================================
| Id |                      Operation                       |       Name        |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Mem  | Activity | Activity Detail |
|    |                                                      |                   | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | (Max) |   (%)    |   (# samples)   |
==============================================================================================================================================================================================
|  0 | SELECT STATEMENT                                     |                   |         |      |           |        |       |          |      |       |     . |          |                 |
|  1 |   TABLE ACCESS BY INDEX ROWID BATCHED                | OBJ$              |       1 |    1 |           |        |       |          |      |       |     . |          |                 |
|  2 |    INDEX RANGE SCAN                                  | I_OBJ1            |       1 |    1 |           |        |       |          |      |       |     . |          |                 |
|  3 |    TABLE ACCESS BY INDEX ROWID BATCHED               | OBJ$              |       1 |    1 |           |        |       |          |      |       |     . |          |                 |
|  4 |     INDEX RANGE SCAN                                 | I_OBJ1            |       1 |    1 |           |        |       |          |      |       |     . |          |                 |
|  5 |     TABLE ACCESS BY INDEX ROWID BATCHED              | OBJ$              |       1 |    1 |           |        |       |          |      |       |     . |          |                 |
|  6 |      INDEX RANGE SCAN                                | I_OBJ1            |       1 |    1 |           |        |       |          |      |       |     . |          |                 |
|  7 |      TABLE ACCESS BY INDEX ROWID BATCHED             | OBJ$              |       1 |    1 |           |        |       |          |      |       |     . |          |                 |
|  8 |       INDEX RANGE SCAN                               | I_OBJ1            |       1 |    1 |           |        |       |          |      |       |     . |          |                 |
|  9 |       TABLE ACCESS BY INDEX ROWID BATCHED            | OBJ$              |       1 |    1 |           |        |       |          |      |       |     . |          |                 |
| 10 |        INDEX RANGE SCAN                              | I_OBJ1            |       1 |    1 |           |        |       |          |      |       |     . |          |                 |
| 11 |   SORT ORDER BY                                      |                   |       1 |  653 |         1 |     +1 |     1 |       50 |      |       |  6144 |          |                 |
| 12 |    FILTER                                            |                   |         |      |         1 |     +1 |     1 |       50 |      |       |     . |          |                 |
| 13 |     NESTED LOOPS O

................