Use gather_plan_statistics hint to understand optimizer’s estimations and much more ..
Posted by FatDBA on January 10, 2022
Lately someone told me that he ran the gather_plan_statistics hint with his SQL, but he is not getting the detailed execution plan, I mean all extra stats that you see i.e. starts, estimated time, starts, buffers, actual and estimated number of rows were not there and he was getting the regular/simple execution plan.
But I found he was trying it in a wrong way! The /*+ gather_plan_statistics */ hint does not save data into PLAN_TABLE, but it stores execution statistics in V$SQL_PLAN performance view. To display these data you can use (dbms_xplan.display_cursor (format=>’ALLSTATS LAST’)), but this not always work, because you must execute the second command immediately after the SQL query. The better method is to query V$SQL or V$SQLAREA or any useful view to obtain SQL_ID of the query, and then use DISPLAY_CURSOR function, for example in this way …
SQL>
SQL> SELECT /*+ gather_plan_statistics */ * FROM (SELECT A.MANDNA_KKAJ_DI,A.NAME,ROWNUM IDX FROM DIXDROI.INF_KRA_PRIMAR A,DIXDROI.INF_KRA_PRIMAR_B B,DIXDROI.INF_KRA_PRIMAR_RANGE C WHERE A.TYPE='MOBILE'
AND A.STATUS='Available' AND A.ASSIGNED_CSN_ID IS NULL AND B.OWNING_OBJECT_ID=A.MANDNA_KKAJ_DI AND
B.BINDING_NAME='com.paratapata.killer.mfs.oare.mainframe.locking.ADAKingstonToKillerRangeStst' AND C.MANDNA_KKAJ_DI=B.TARGET_OBJECT_ID
AND C.STATUS = NVL('Active', C.STATUS) AND C.CATEGORY = 'Range'
AND 'Internal' = NVL(C.NUMBER_USAGE,'External') AND (A.lock_id IS NULL OR A.LOCK_DATE <= (sysdate - NVL(A.LOCK_PERIOD,(SELECT mark_raar_value
FROM DIXDROI.PAM_KRIAIS_MAINFATRAARA_AHAH WHERE mark_raar_name = 'DEFAULT_LOCK_PERIOD' )) / 60 / 24))
ORDER BY TO_NUMBER(A.NAME) ) RESULTS WHERE RESULTS.IDX BETWEEN 1 AND 15;
MANDNA_KKAJ_DI NAME IDX
-------------- -------------------------------------------------- ----------
91891302 0444915115 12
91891309 0444915122 13
91891310 0444915123 14
91891314 0444915127 15
91891723 0444915536 2
91891724 0444915537 3
91891726 0444915539 4
91891730 0444915543 5
91891739 0444915552 6
91891748 0444915561 7
91891766 0444915579 8
91891768 0444915581 9
91891807 0444915620 10
91891854 0444915667 11
17116808 04466962472 1
15 rows selected.
SQL> select sql_id, plan_hash_value, executions, sql_text from gv$sqlarea where sql_fulltext like '%gather_plan_statistics%';
SQL_ID PLAN_HASH_VALUE EXECUTIONS SQL_TEXT
------------- --------------- ---------- ------------------------------------------------------------
0m329sngnhv1p 2185860753 1 SELECT /*+ gather_plan_statistics */ * FROM (SELECT A.OBJECT
_INST_ID,A.NAME,ROWNUM IDX FROM DIXDROI.INF_KRA_PRIMAR A,INS
TALL.INF_KRA_PRIMAR_B B,DIXDROI.INF_KRA_PRIMAR_RANGE C WHERE
A.TYPE=:"SYS_B_00" AND A.STATUS=:"SYS_B_01" AND A.ASSIGNED_
CSN_ID IS NULL AND B.OWNING_OBJECT_ID=A.MANDNA_KKAJ_DI AND B
.BINDING_NAME=:"SYS_B_02" AND C.MANDNA_KKAJ_DI=B.TARGET_OBJE
CT_ID AND C.STATUS = NVL(:"SYS_B_03", C.STATUS) AND C.CATEGO
RY = :"SYS_B_04" AND :"SYS_B_05" = NVL(C.NUMBER_USAGE,:"SYS_
B_06") AND (A.lock_id IS NULL OR A.LOCK_DATE <= (sysdate - N
VL(A.LOCK_PERIOD,(SELECT mark_raar_value FROM DIXDROI.EAI_HY
BRIS_CONFIGURATION_DATA WHERE mark_raar_name = :"SYS_B_07" )
) / :"SYS_B_08" / :"SYS_B_09")) ORDER BY TO_NUMBER(A.NAME) )
RESULTS WHERE RESULTS.IDX BETWEEN :"SYS_B_10" AND :"SYS_B_1
1"
The above query returns SQL_ID=0m329sngnhv1p and CHILD_NUMBER=0(child number is just a cursor number). Use these values to query the collected plan. This will show you statistics such as the actual number of rows processed (A-Rows), rather than just the estimates (E-Rows). A-Rows is the total number of rows produced by all starts of that operation. But E-Rows is an estimate of the number of rows produced by a single start of an operation. If you want to read about E-Rows and A-Rows columns, please read a brilliant post by Jonathan Lewis.
Okay, the plan also includes a column called Starts, which tells you how many times each step was executed. A-Rows, E-Rows and Starts are all incredibly useful if you want to understand a plan.
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('0m329sngnhv1p', 0, 'ALLSTATS'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0m329sngnhv1p, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM (SELECT
A.MANDNA_KKAJ_DI,A.NAME,ROWNUM IDX FROM DIXDROI.INF_KRA_PRIMAR
A,DIXDROI.INF_KRA_PRIMAR_B B,DIXDROI.INF_KRA_PRIMAR_RANGE C WHERE
A.TYPE=:"SYS_B_00" AND A.STATUS=:"SYS_B_01" AND A.ASSIGNED_CSN_ID IS
NULL AND B.OWNING_OBJECT_ID=A.MANDNA_KKAJ_DI AND
B.BINDING_NAME=:"SYS_B_02" AND C.MANDNA_KKAJ_DI=B.TARGET_OBJECT_ID AND
C.STATUS = NVL(:"SYS_B_03", C.STATUS) AND C.CATEGORY = :"SYS_B_04" AND
:"SYS_B_05" = NVL(C.NUMBER_USAGE,:"SYS_B_06") AND (A.lock_id IS NULL OR
A.LOCK_DATE <= (sysdate - NVL(A.LOCK_PERIOD,(SELECT mark_raar_value
FROM DIXDROI.PAM_KRIAIS_MAINFATRAARA_AHAH WHERE mark_raar_name =
:"SYS_B_07" )) / :"SYS_B_08" / :"SYS_B_09")) ORDER BY TO_NUMBER(A.NAME)
) RESULTS WHERE RESULTS.IDX BETWEEN :"SYS_B_10" AND :"SYS_B_11"
Plan hash value: 2185860753
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M|
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 |00:00:00.62 | 353K| | | |
|* 1 | FILTER | | 1 | | 15 |00:00:00.62 | 353K| | | |
|* 2 | VIEW | | 1 | 5530 | 15 |00:00:00.62 | 353K| | | |
| 3 | SORT ORDER BY | | 1 | 5530 | 7873 |00:00:00.62 | 353K| 549K| 457K| 1/0/0|
| 4 | COUNT | | 1 | | 7873 |00:00:00.46 | 353K| | | |
| 5 | NESTED LOOPS | | 1 | 5530 | 7873 |00:00:00.46 | 353K| | | |
| 6 | NESTED LOOPS | | 1 | 5530 | 266K|00:00:00.44 | 240K| | | |
| 7 | NESTED LOOPS | | 1 | 5530 | 266K|00:00:00.19 | 46039 | | | |
|* 8 | TABLE ACCESS FULL | INF_KRA_PRIMAR_RANGE | 1 | 4392 | 90 |00:00:00.01 | 878 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| INF_KRA_PRIMAR_B | 90 | 1 | 266K|00:00:00.17 | 45161 | | | |
|* 10 | INDEX RANGE SCAN | EAI_NUMBER_REL_1 | 90 | 1 | 266K|00:00:00.04 | 3871 | | | |
|* 11 | INDEX UNIQUE SCAN | PK_INF_KRA_PRIMAR | 266K| 1 | 266K|00:00:00.20 | 194K| | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | INF_KRA_PRIMAR | 266K| 1 | 7873 |00:00:00.20 | 113K| | | |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED | PAM_KRIAIS_MAINFATRAARA_AHAH | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 14 | INDEX RANGE SCAN | IDXGETNUMBERPD1 | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:SYS_B_11>=:SYS_B_10)
2 - filter(("RESULTS"."IDX">=:SYS_B_10 AND "RESULTS"."IDX"<=:SYS_B_11))
8 - filter((NVL("C"."CATEGORY",'Range')=:SYS_B_04 AND NVL("C"."NUMBER_USAGE",:SYS_B_06)=:SYS_B_05 AND "C"."STATUS"=NVL(:SYS_B_03,"C"."STATUS")))
10 - access("C"."MANDNA_KKAJ_DI"="B"."TARGET_OBJECT_ID" AND "B"."BINDING_NAME"=:SYS_B_02)
11 - access("B"."OWNING_OBJECT_ID"="A"."MANDNA_KKAJ_DI")
12 - filter(("A"."TYPE"=:SYS_B_00 AND "A"."STATUS"=:SYS_B_01 AND "A"."ASSIGNED_CSN_ID" IS NULL AND ("A"."LOCK_ID" IS NULL OR
"A"."LOCK_DATE"<=SYSDATE@!-NVL("A"."LOCK_PERIOD",)/:SYS_B_08/:SYS_B_09)))
14 - access("mark_raar_NAME"=:SYS_B_07)
49 rows selected.
There are multiple other ways how you can add or remove more details to your execution plan, lets take a look on few of those methods.
-- To get additional COST and BYTES column into plan
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'0m329sngnhv1p',format=>'ALLSTATS LAST +cost +bytes'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0m329sngnhv1p, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM (SELECT
A.MANDNA_KKAJ_DI,A.NAME,ROWNUM IDX FROM DIXDROI.INF_KRA_PRIMAR
A,DIXDROI.INF_KRA_PRIMAR_B B,DIXDROI.INF_KRA_PRIMAR_RANGE C WHERE
A.TYPE=:"SYS_B_00" AND A.STATUS=:"SYS_B_01" AND A.ASSIGNED_CSN_ID IS
NULL AND B.OWNING_OBJECT_ID=A.MANDNA_KKAJ_DI AND
B.BINDING_NAME=:"SYS_B_02" AND C.MANDNA_KKAJ_DI=B.TARGET_OBJECT_ID AND
C.STATUS = NVL(:"SYS_B_03", C.STATUS) AND C.CATEGORY = :"SYS_B_04" AND
:"SYS_B_05" = NVL(C.NUMBER_USAGE,:"SYS_B_06") AND (A.lock_id IS NULL OR
A.LOCK_DATE <= (sysdate - NVL(A.LOCK_PERIOD,(SELECT mark_raar_value
FROM DIXDROI.PAM_KRIAIS_MAINFATRAARA_AHAH WHERE mark_raar_name =
:"SYS_B_07" )) / :"SYS_B_08" / :"SYS_B_09")) ORDER BY TO_NUMBER(A.NAME)
) RESULTS WHERE RESULTS.IDX BETWEEN :"SYS_B_10" AND :"SYS_B_11"
Plan hash value: 2185860753
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 3753 (100)| 15 |00:00:00.62 | 353K| | | |
|* 1 | FILTER | | 1 | | | | | 15 |00:00:00.62 | 353K| | | |
|* 2 | VIEW | | 1 | 5530 | 286K| | 3753 (1)| 15 |00:00:00.62 | 353K| | | |
| 3 | SORT ORDER BY | | 1 | 5530 | 918K| 1064K| 3753 (1)| 7873 |00:00:00.62 | 353K| 549K| 457K| 487K (0)|
| 4 | COUNT | | 1 | | | | | 7873 |00:00:00.46 | 353K| | | |
| 5 | NESTED LOOPS | | 1 | 5530 | 918K| | 3544 (1)| 7873 |00:00:00.46 | 353K| | | |
| 6 | NESTED LOOPS | | 1 | 5530 | 918K| | 3544 (1)| 266K|00:00:00.44 | 240K| | | |
| 7 | NESTED LOOPS | | 1 | 5530 | 615K| | 2437 (1)| 266K|00:00:00.19 | 46039 | | | |
|* 8 | TABLE ACCESS FULL | INF_KRA_PRIMAR_RANGE | 1 | 4392 | 92232 | | 240 (1)| 90 |00:00:00.01 | 878 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| INF_KRA_PRIMAR_B | 90 | 1 | 93 | | 1 (0)| 266K|00:00:00.17 | 45161 | | | |
|* 10 | INDEX RANGE SCAN | EAI_NUMBER_REL_1 | 90 | 1 | | | 1 (0)| 266K|00:00:00.04 | 3871 | | | |
|* 11 | INDEX UNIQUE SCAN | PK_INF_KRA_PRIMAR | 266K| 1 | | | 1 (0)| 266K|00:00:00.20 | 194K| | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | INF_KRA_PRIMAR | 266K| 1 | 56 | | 1 (0)| 7873 |00:00:00.20 | 113K| | | |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED | PAM_KRIAIS_MAINFATRAARA_AHAH | 1 | 1 | 28 | | 1 (0)| 1 |00:00:00.01 | 2 | | | |
|* 14 | INDEX RANGE SCAN | IDXGETNUMBERPD1 | 1 | 1 | | | 1 (0)| 1 |00:00:00.01 | 1 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:SYS_B_11>=:SYS_B_10)
2 - filter(("RESULTS"."IDX">=:SYS_B_10 AND "RESULTS"."IDX"<=:SYS_B_11))
8 - filter((NVL("C"."CATEGORY",'Range')=:SYS_B_04 AND NVL("C"."NUMBER_USAGE",:SYS_B_06)=:SYS_B_05 AND "C"."STATUS"=NVL(:SYS_B_03,"C"."STATUS")))
10 - access("C"."MANDNA_KKAJ_DI"="B"."TARGET_OBJECT_ID" AND "B"."BINDING_NAME"=:SYS_B_02)
11 - access("B"."OWNING_OBJECT_ID"="A"."MANDNA_KKAJ_DI")
12 - filter(("A"."TYPE"=:SYS_B_00 AND "A"."STATUS"=:SYS_B_01 AND "A"."ASSIGNED_CSN_ID" IS NULL AND ("A"."LOCK_ID" IS NULL OR
"A"."LOCK_DATE"<=SYSDATE@!-NVL("A"."LOCK_PERIOD",)/:SYS_B_08/:SYS_B_09)))
14 - access("mark_raar_NAME"=:SYS_B_07)
49 rows selected.
-- To get OUTLINE data into your execution plan
SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'0m329sngnhv1p', format=>'ALLSTATS LAST +outline'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0m329sngnhv1p, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM (SELECT
A.MANDNA_KKAJ_DI,A.NAME,ROWNUM IDX FROM DIXDROI.INF_KRA_PRIMAR
A,DIXDROI.INF_KRA_PRIMAR_B B,DIXDROI.INF_KRA_PRIMAR_RANGE C WHERE
A.TYPE=:"SYS_B_00" AND A.STATUS=:"SYS_B_01" AND A.ASSIGNED_CSN_ID IS
NULL AND B.OWNING_OBJECT_ID=A.MANDNA_KKAJ_DI AND
B.BINDING_NAME=:"SYS_B_02" AND C.MANDNA_KKAJ_DI=B.TARGET_OBJECT_ID AND
C.STATUS = NVL(:"SYS_B_03", C.STATUS) AND C.CATEGORY = :"SYS_B_04" AND
:"SYS_B_05" = NVL(C.NUMBER_USAGE,:"SYS_B_06") AND (A.lock_id IS NULL OR
A.LOCK_DATE <= (sysdate - NVL(A.LOCK_PERIOD,(SELECT mark_raar_value
FROM DIXDROI.PAM_KRIAIS_MAINFATRAARA_AHAH WHERE mark_raar_name =
:"SYS_B_07" )) / :"SYS_B_08" / :"SYS_B_09")) ORDER BY TO_NUMBER(A.NAME)
) RESULTS WHERE RESULTS.IDX BETWEEN :"SYS_B_10" AND :"SYS_B_11"
Plan hash value: 2185860753
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 |00:00:00.62 | 353K| | | |
|* 1 | FILTER | | 1 | | 15 |00:00:00.62 | 353K| | | |
|* 2 | VIEW | | 1 | 5530 | 15 |00:00:00.62 | 353K| | | |
| 3 | SORT ORDER BY | | 1 | 5530 | 7873 |00:00:00.62 | 353K| 549K| 457K| 487K (0)|
| 4 | COUNT | | 1 | | 7873 |00:00:00.46 | 353K| | | |
| 5 | NESTED LOOPS | | 1 | 5530 | 7873 |00:00:00.46 | 353K| | | |
| 6 | NESTED LOOPS | | 1 | 5530 | 266K|00:00:00.44 | 240K| | | |
| 7 | NESTED LOOPS | | 1 | 5530 | 266K|00:00:00.19 | 46039 | | | |
|* 8 | TABLE ACCESS FULL | INF_KRA_PRIMAR_RANGE | 1 | 4392 | 90 |00:00:00.01 | 878 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| INF_KRA_PRIMAR_B | 90 | 1 | 266K|00:00:00.17 | 45161 | | | |
|* 10 | INDEX RANGE SCAN | EAI_NUMBER_REL_1 | 90 | 1 | 266K|00:00:00.04 | 3871 | | | |
|* 11 | INDEX UNIQUE SCAN | PK_INF_KRA_PRIMAR | 266K| 1 | 266K|00:00:00.20 | 194K| | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | INF_KRA_PRIMAR | 266K| 1 | 7873 |00:00:00.20 | 113K| | | |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED | PAM_KRIAIS_MAINFATRAARA_AHAH | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 14 | INDEX RANGE SCAN | IDXGETNUMBERPD1 | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('optimizer_index_cost_adj' 10)
FIRST_ROWS(1)
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "RESULTS"@"SEL$1")
FULL(@"SEL$2" "C"@"SEL$2")
INDEX_RS_ASC(@"SEL$2" "B"@"SEL$2" ("INF_KRA_PRIMAR_B"."TARGET_OBJECT_ID" "INF_KRA_PRIMAR_B"."BINDING_NAME"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2" "B"@"SEL$2")
INDEX(@"SEL$2" "A"@"SEL$2" ("INF_KRA_PRIMAR"."MANDNA_KKAJ_DI"))
LEADING(@"SEL$2" "C"@"SEL$2" "B"@"SEL$2" "A"@"SEL$2")
USE_NL(@"SEL$2" "B"@"SEL$2")
USE_NL(@"SEL$2" "A"@"SEL$2")
NLJ_BATCHING(@"SEL$2" "A"@"SEL$2")
PUSH_SUBQ(@"SEL$3")
INDEX_RS_ASC(@"SEL$3" "PAM_KRIAIS_MAINFATRAARA_AHAH"@"SEL$3" ("PAM_KRIAIS_MAINFATRAARA_AHAH"."mark_raar_NAME"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3" "PAM_KRIAIS_MAINFATRAARA_AHAH"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:SYS_B_11>=:SYS_B_10)
2 - filter(("RESULTS"."IDX">=:SYS_B_10 AND "RESULTS"."IDX"<=:SYS_B_11))
8 - filter((NVL("C"."CATEGORY",'Range')=:SYS_B_04 AND NVL("C"."NUMBER_USAGE",:SYS_B_06)=:SYS_B_05 AND "C"."STATUS"=NVL(:SYS_B_03,"C"."STATUS")))
10 - access("C"."MANDNA_KKAJ_DI"="B"."TARGET_OBJECT_ID" AND "B"."BINDING_NAME"=:SYS_B_02)
11 - access("B"."OWNING_OBJECT_ID"="A"."MANDNA_KKAJ_DI")
12 - filter(("A"."TYPE"=:SYS_B_00 AND "A"."STATUS"=:SYS_B_01 AND "A"."ASSIGNED_CSN_ID" IS NULL AND ("A"."LOCK_ID" IS NULL OR
"A"."LOCK_DATE"<=SYSDATE@!-NVL("A"."LOCK_PERIOD",)/:SYS_B_08/:SYS_B_09)))
14 - access("mark_raar_NAME"=:SYS_B_07)
77 rows selected.
SQL>
-- Lets try a more sophisticated one to get all sorts of information available
SQL>
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '0m329sngnhv1p', FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE +PROJECTION +REMOTE +NOTE'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0m329sngnhv1p, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM (SELECT
A.MANDNA_KKAJ_DI,A.NAME,ROWNUM IDX FROM DIXDROI.INF_KRA_PRIMAR
A,DIXDROI.INF_KRA_PRIMAR_B B,DIXDROI.INF_KRA_PRIMAR_RANGE C WHERE
A.TYPE=:"SYS_B_00" AND A.STATUS=:"SYS_B_01" AND A.ASSIGNED_CSN_ID IS
NULL AND B.OWNING_OBJECT_ID=A.MANDNA_KKAJ_DI AND
B.BINDING_NAME=:"SYS_B_02" AND C.MANDNA_KKAJ_DI=B.TARGET_OBJECT_ID AND
C.STATUS = NVL(:"SYS_B_03", C.STATUS) AND C.CATEGORY = :"SYS_B_04" AND
:"SYS_B_05" = NVL(C.NUMBER_USAGE,:"SYS_B_06") AND (A.lock_id IS NULL OR
A.LOCK_DATE <= (sysdate - NVL(A.LOCK_PERIOD,(SELECT mark_raar_value
FROM DIXDROI.PAM_KRIAIS_MAINFATRAARA_AHAH WHERE mark_raar_name =
:"SYS_B_07" )) / :"SYS_B_08" / :"SYS_B_09")) ORDER BY TO_NUMBER(A.NAME)
) RESULTS WHERE RESULTS.IDX BETWEEN :"SYS_B_10" AND :"SYS_B_11"
Plan hash value: 2185860753
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 3753 (100)| | 15 |00:00:00.62 | 353K| | | |
|* 1 | FILTER | | 1 | | | | | | 15 |00:00:00.62 | 353K| | | |
|* 2 | VIEW | | 1 | 5530 | 286K| | 3753 (1)| 00:00:01 | 15 |00:00:00.62 | 353K| | | |
| 3 | SORT ORDER BY | | 1 | 5530 | 918K| 1064K| 3753 (1)| 00:00:01 | 7873 |00:00:00.62 | 353K| 549K| 457K| 1/0/0|
| 4 | COUNT | | 1 | | | | | | 7873 |00:00:00.46 | 353K| | | |
| 5 | NESTED LOOPS | | 1 | 5530 | 918K| | 3544 (1)| 00:00:01 | 7873 |00:00:00.46 | 353K| | | |
| 6 | NESTED LOOPS | | 1 | 5530 | 918K| | 3544 (1)| 00:00:01 | 266K|00:00:00.44 | 240K| | | |
| 7 | NESTED LOOPS | | 1 | 5530 | 615K| | 2437 (1)| 00:00:01 | 266K|00:00:00.19 | 46039| | | |
|* 8 | TABLE ACCESS FULL | INF_KRA_PRIMAR_RANGE | 1 | 4392 | 92232 | | 240 (1)| 00:00:01 | 90 |00:00:00.01 | 878| | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| INF_KRA_PRIMAR_B | 90 | 1 | 93 | | 1 (0)| 00:00:01 | 266K|00:00:00.17 | 45161| | | |
|* 10 | INDEX RANGE SCAN | EAI_NUMBER_REL_1 | 90 | 1 | | | 1 (0)| 00:00:01 | 266K|00:00:00.04 | 3871| | | |
|* 11 | INDEX UNIQUE SCAN | PK_INF_KRA_PRIMAR | 266K| 1 | | | 1 (0)| 00:00:01 | 266K|00:00:00.20 | 194K| | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | INF_KRA_PRIMAR | 266K| 1 | 56 | | 1 (0)| 00:00:01 | 7873 |00:00:00.20 | 113K| | | |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED | PAM_KRIAIS_MAINFATRAARA_AHAH | 1 | 1 | 28 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2| | | |
|* 14 | INDEX RANGE SCAN | IDXGETNUMBERPD1 | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1| | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / RESULTS@SEL$1
3 - SEL$2
8 - SEL$2 / C@SEL$2
9 - SEL$2 / B@SEL$2
10 - SEL$2 / B@SEL$2
11 - SEL$2 / A@SEL$2
12 - SEL$2 / A@SEL$2
13 - SEL$3 / PAM_KRIAIS_MAINFATRAARA_AHAH@SEL$3
14 - SEL$3 / PAM_KRIAIS_MAINFATRAARA_AHAH@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('optimizer_index_cost_adj' 10)
FIRST_ROWS(1)
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "RESULTS"@"SEL$1")
FULL(@"SEL$2" "C"@"SEL$2")
INDEX_RS_ASC(@"SEL$2" "B"@"SEL$2" ("INF_KRA_PRIMAR_B"."TARGET_OBJECT_ID" "INF_KRA_PRIMAR_B"."BINDING_NAME"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2" "B"@"SEL$2")
INDEX(@"SEL$2" "A"@"SEL$2" ("INF_KRA_PRIMAR"."MANDNA_KKAJ_DI"))
LEADING(@"SEL$2" "C"@"SEL$2" "B"@"SEL$2" "A"@"SEL$2")
USE_NL(@"SEL$2" "B"@"SEL$2")
USE_NL(@"SEL$2" "A"@"SEL$2")
NLJ_BATCHING(@"SEL$2" "A"@"SEL$2")
PUSH_SUBQ(@"SEL$3")
INDEX_RS_ASC(@"SEL$3" "PAM_KRIAIS_MAINFATRAARA_AHAH"@"SEL$3" ("PAM_KRIAIS_MAINFATRAARA_AHAH"."mark_raar_NAME"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3" "PAM_KRIAIS_MAINFATRAARA_AHAH"@"SEL$3")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (VARCHAR2(30), CSID=873): 'MOBILE'
2 - :2 (VARCHAR2(30), CSID=873): 'Available'
3 - (VARCHAR2(30), CSID=873): 'com.paratapata.killer.mfs.oare.mainframe.locking.ADAKingstonToKillerRangeStst'
4 - (VARCHAR2(30), CSID=873): 'Active'
5 - (VARCHAR2(30), CSID=873): 'Range'
6 - (VARCHAR2(30), CSID=873): 'Internal'
7 - (VARCHAR2(30), CSID=873): 'External'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:SYS_B_11>=:SYS_B_10)
2 - filter(("RESULTS"."IDX">=:SYS_B_10 AND "RESULTS"."IDX"<=:SYS_B_11))
8 - filter((NVL("C"."CATEGORY",'Range')=:SYS_B_04 AND NVL("C"."NUMBER_USAGE",:SYS_B_06)=:SYS_B_05 AND "C"."STATUS"=NVL(:SYS_B_03,"C"."STATUS")))
10 - access("C"."MANDNA_KKAJ_DI"="B"."TARGET_OBJECT_ID" AND "B"."BINDING_NAME"=:SYS_B_02)
11 - access("B"."OWNING_OBJECT_ID"="A"."MANDNA_KKAJ_DI")
12 - filter(("A"."TYPE"=:SYS_B_00 AND "A"."STATUS"=:SYS_B_01 AND "A"."ASSIGNED_CSN_ID" IS NULL AND ("A"."LOCK_ID" IS NULL OR
"A"."LOCK_DATE"<=SYSDATE@!-NVL("A"."LOCK_PERIOD",)/:SYS_B_08/:SYS_B_09)))
14 - access("mark_raar_NAME"=:SYS_B_07)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "RESULTS"."MANDNA_KKAJ_DI"[NUMBER,22], "RESULTS"."NAME"[VARCHAR2,50], "RESULTS"."IDX"[NUMBER,22]
2 - "RESULTS"."MANDNA_KKAJ_DI"[NUMBER,22], "RESULTS"."NAME"[VARCHAR2,50], "RESULTS"."IDX"[NUMBER,22]
3 - (#keys=1) TO_NUMBER("NAME")[22], "A"."MANDNA_KKAJ_DI"[NUMBER,22], "A"."NAME"[VARCHAR2,50], ROWNUM[22]
4 - "A"."MANDNA_KKAJ_DI"[NUMBER,22], "NAME"[VARCHAR2,50], ROWNUM[8]
5 - "A"."MANDNA_KKAJ_DI"[NUMBER,22], "NAME"[VARCHAR2,50]
6 - "A".ROWID[ROWID,10], "A"."MANDNA_KKAJ_DI"[NUMBER,22]
7 - "B"."OWNING_OBJECT_ID"[NUMBER,22]
8 - "C"."MANDNA_KKAJ_DI"[NUMBER,22]
9 - "B"."OWNING_OBJECT_ID"[NUMBER,22]
10 - "B".ROWID[ROWID,10]
11 - "A".ROWID[ROWID,10], "A"."MANDNA_KKAJ_DI"[NUMBER,22]
12 - "NAME"[VARCHAR2,50]
13 - "mark_raar_VALUE"[NUMBER,22]
14 - "PAM_KRIAIS_MAINFATRAARA_AHAH".ROWID[ROWID,10]
120 rows selected.
SQL>
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 
What are those strange columns in my execution plan OMem 1Mem O/1/M and Used-Mem ? « Tales From A Lazy Fat DBA said
[…] « Use gather_plan_statistics hint to understand optimizer’s estimations and much more .. […]