1. /*+ gather_plan_statistics ckj_as_01 */ 사용하여 쿼리
SELECT /*+ gather_plan_statistics ckj_as_01 */ S8.ITEM_VALUE
FROM SOD_8 S8 INNER JOIN (SELECT DISTINCT PROD_CD AS ITEM_VALUE
FROM SOD_24 S24 INNER JOIN SOD_44 S44 ON S24.PROD_CD = S44.ITEM_VALUE
AND S44.STR_CD IN (:1,
'981') INNER JOIN (SELECT SUBSTR(S13.ITEM_VALUE, 1, 8) AS ITEM_VALUE
FROM SOD_13 S13
WHERE S13.CUST_ID = :2 ) A ON S44.CATEGORY_ID LIKE A.ITEM_VALUE||'%'
WHERE ROWNUM <= 20 ) A ON S8.ITEM_VALUE = A.ITEM_VALUE
2. SQL_ID 확인
SELECT SQL_ID,SQL_FULLTEXT
FROM V$SQL
WHERE SQL_FULLTEXT LIKE '%ckj_as_01%'
AND SQL_FULLTEXT NOT LIKE '%$%';
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
1gtk80xx65vah SELECT /*+ gather_plan_statistics ckj_as_01 */ S8.ITEM_VALUE
FROM SOD_8 S8 INNER JOIN (SELECT DISTINCT PROD_CD AS ITEM_VALUE
FROM SOD_24 S24 INNER
3. 실행 쿼리 플랜 확인
select * from table(dbms_xplan.display_cursor('1gtk80xx65vah',null,'ALLSTATS LAST LAST'));
SQL> select * from table(dbms_xplan.display_cursor('1gtk80xx65vah',null,'ALLSTATS LAST LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1gtk80xx65vah, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics ckj_as_01 */ S8.ITEM_VALUE
FROM
SOD_8 S8 INNER JOIN (SELECT DISTINCT PROD_CD AS ITEM_VALUE
FROM SOD_24 S24 INNER JOIN SOD_44 S44 ON S24.PROD_CD =
S44.ITEM_VALUE
AND S44.STR_CD IN (:1,
'981') INNER JOIN (SELECT SUBSTR(S13.ITEM_VALUE, 1, 8) AS ITEM_VALUE
FROM SOD_13 S13
WHERE S13.CUST_ID =
:2 ) A ON S44.CATEGORY_ID LIKE A.ITEM_VALUE||'%'
WHERE ROWNUM
<= 20 ) A ON S8.ITEM_VALUE = A.ITEM_VALUE
Plan hash value: 2246381816
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 74 | | | |
| 1 | NESTED LOOPS | | 1 | 20 | 4 |00:00:00.01 | 74 | | | |
| 2 | VIEW | | 1 | 20 | 6 |00:00:00.01 | 66 | | | |
| 3 | HASH UNIQUE | | 1 | 20 | 6 |00:00:00.01 | 66 | 1558K| 1558K| 749K (0)|
|* 4 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 66 | | | |
|* 5 | HASH JOIN | | 1 | 1078 | 20 |00:00:00.01 | 66 | 1557K| 1557K| 1632K (0)|
| 6 | INDEX FAST FULL SCAN| PK_SOD_24 | 1 | 1415 | 1379 |00:00:00.01 | 17 | | | |
| 7 | NESTED LOOPS | | 1 | 80878 | 5237 |00:00:00.01 | 49 | | | |
|* 8 | INDEX RANGE SCAN | PK_SOD_13 | 1 | 7 | 3 |00:00:00.01 | 3 | | | |
| 9 | INLIST ITERATOR | | 3 | | 5237 |00:00:00.01 | 46 | | | |
|* 10 | INDEX RANGE SCAN | IDX_STD_SOD_44_1 | 5 | 11385 | 5237 |00:00:00.01 | 46 | | | |
|* 11 | INDEX UNIQUE SCAN | PK_SOD_8 | 6 | 1 | 4 |00:00:00.01 | 8 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(ROWNUM<=20)
5 - access("S24"."PROD_CD"="S44"."ITEM_VALUE")
8 - access("S13"."CUST_ID"=:2)
10 - access((("S44"."STR_CD"=:1 OR "S44"."STR_CD"='981')) AND "S44"."CATEGORY_ID" LIKE
SUBSTR("S13"."ITEM_VALUE",1,8)||'%')
filter("S44"."CATEGORY_ID" LIKE SUBSTR("S13"."ITEM_VALUE",1,8)||'%')
11 - access("S8"."ITEM_VALUE"="A"."ITEM_VALUE")
41 rows selected.
[Tip!] 2~3단계 한번에 실행하기
SELECT *
FROM TABLE(
SELECT dbms_xplan.display_cursor(SQL_ID,null,'ALLSTATS LAST LAST')
FROM V$SQL WHERE SQL_FULLTEXT LIKE '%ckj_to_01%' AND SQL_FULLTEXT NOT LIKE '%$%');