/*
gather_plan_statistics hint를 사용한 SQL Trace+Tkprof 수행 동일 효과 보기
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))
(Oracle 10g ~)
*/
-- /*+ gather_plan_statistics */ Hint 이용한 방법
SELECT /*+ gather_plan_statistics */
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 ;
-- plan
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats last -rows +alias +outline +predicate'));
-- 결과
SQL_ID 1sq5s3pjphk1q, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ 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
Plan hash value: 2855117487
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 |00:00:01.18 | 13797 | 2315 | | | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 2 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 4 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 6 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 8 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 10 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 11 | SORT ORDER BY | | 1 | 1 | 50 |00:00:01.18 | 13797 | 2315 | 6144 | 6144 | 6144 (0)|
|* 12 | FILTER | | 1 | | 50 |00:00:01.83 | 13797 | 2315 | | | |
| 13 | NESTED LOOPS OUTER | | 1 | 1 | 50 |00:00:01.81 | 12214 | 2304 | | | |
| 14 | NESTED LOOPS OUTER | | 1 | 1 | 50 |00:00:01.81 | 12160 | 2279 | | | |
| 15 | NESTED LOOPS OUTER | | 1 | 1 | 50 |00:00:01.79 | 12056 | 2226 | | | |
| 16 | NESTED LOOPS OUTER | | 1 | 1 | 50 |00:00:01.79 | 11924 | 2218 | | | |
| 17 | NESTED LOOPS OUTER | | 1 | 1 | 50 |00:00:01.79 | 11924 | 2218 | | | |
| 18 | ......
| 71 | FIXED TABLE FULL | X$KZSRO | 0 | 2 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 72 | INDEX RANGE SCAN | I_OBJAUTH2 | 0 | 2 | 0 |00:00:00.01 | 0 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJ#"=:B1)
4 - access("O"."OBJ#"=:B1)
6 - access("O"."OBJ#"=:B1)
8 - access("O"."OBJ#"=:B1)
10 - access("O"."OBJ#"=:B1)
12 - filter(((INTERNAL_FUNCTION("O"."TYPE#") OR ("O"."TYPE#"=2 AND IS NULL)) AND ("O"."SPARE3"=USERENV('SCHEMAID') OR IS NOT NULL OR IS NOT NULL) AND
(BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"<>88 AND IS NULL) OR ( IS NOT NULL AND
((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))) AND (BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0
OR ("O"."TYPE#"<>88 AND IS NULL) OR ( IS NOT NULL AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))) AND ("U"."NAME"=SYS_CONTEXT('USERENV','CURRENT_USER') OR
ORA_CHECK_SYS_PRIVILEGE("U"."USER#",2)=1 OR IS NOT NULL)))
25 - access("C"."NAME"="C"."NAME") ....
'ORACLE > 튜닝' 카테고리의 다른 글
| 실행 중 Session Transaction 확인 (0) | 2020.11.23 |
|---|---|
| [TRACE 11g] monitor hint 를 사용한 Plan 보기(v$sql_monitor, v$sql_plan_monitor) (0) | 2020.06.24 |
| SQL Trace file의 내용을 SQL 문장으로 분석 (0) | 2019.11.12 |
| 오라클 업그레이드 SQL 튜닝 대상 추출 (0) | 2019.11.12 |
| merging과 unnesting에 대해서 ... (0) | 2019.06.18 |