?12c database ????Adaptive Execution Plans ????????
- by Liu Maclean(???)
12c R1 ????SQL??????- Adaptive Execution Plans ????????,???????optimizer ??????(runtime)???????????????, ?????????????????????
SQL???????? ????????????, ?????????????????????????????????????????????????????????????adaptive plan ????????????????????????????????????,?????subplan????????????????????
??????, ???????? ???????????????,?????????, ?????? ???????????????”???”????, ???????????????????buffer ??????? ????????????,?????,???????????????????
???optimizer ?????????????????????????,?????????????????????????????????????????plan???? ??12C?????????????, ???????????????????,?????? ????????????
????????????2???:
Dynamic Plans????: ???????????????????????;??????,???optimizer??????????subplans??????????????, ???????????????????,??????????????
Reoptimization????: ?Dynamic Plans????,Reoptimization??????????????????????Reoptimization??,?????????????????????????,??reoptimization?????
OPTIMIZER_ADAPTIVE_REPORTING_ONLY ???? report-only????????????????TRUE,?????????report-only????,???????????????,???????????????
Dynamic Plans
??????????????,????????????????????????, ?????????????,???????????,????????????????????????????????????????? ?????????????final plan??????????????default plan, ??final plan?default plan???????,?????????????
subplan ???????????????,????????????????????????
??????,???????statistics collector ?buffer???????????statistics collector?????????????????,???????????????????????????? ?????????????????????????????????????????,??????????,?????????????? ???????????,???????buffer???? ???????????????,?????????????????????????????,??????buffer,??????final plan?
????????,???????????????????????,?????????????????
?V$SQL??????IS_RESOLVED_DYNAMIC_PLAN??????????final plan???default plan? ??????dynamic plan ???????SQL PLAN directives??????
declare
cursor PLAN_DIRECTIVE_IDS is select directive_id from DBA_SQL_PLAN_DIRECTIVES;
begin
for z in PLAN_DIRECTIVE_IDS loop
DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE(z.directive_id);
end loop;
end;
/
explain plan for select /*MALCEAN*/ product_name from oe.order_items o, oe.product_information p
where o.unit_price=15 and quantity>1 and p.product_id=o.product_id;
select * from table(dbms_xplan.display());
Plan hash value: 1255158658
www.askmaclean.com
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 128 | 7 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4 | 128 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | ORDER_ITEMS | 4 | 48 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 1 | 20 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("O"."UNIT_PRICE"=15 AND "QUANTITY">1)
4 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
alter session set events '10053 trace name context forever,level 1';
OR
alter session set events 'trace[SQL_Plan_Directive] disk highest';
select /*MALCEAN*/ product_name from oe.order_items o, oe.product_information p
where o.unit_price=15 and quantity>1 and p.product_id=o.product_id;
---------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 7 | |
| 1 | HASH JOIN | | 4 | 128 | 7 | 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 4 | 128 | 7 | 00:00:01 |
| 4 | STATISTICS COLLECTOR | | | | | |
| 5 | TABLE ACCESS FULL | ORDER_ITEMS | 4 | 48 | 3 | 00:00:01 |
| 6 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK| 1 | | 0 | |
| 7 | TABLE ACCESS BY INDEX ROWID | PRODUCT_INFORMATION | 1 | 20 | 1 | 00:00:01 |
| 8 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | 20 | 1 | 00:00:01 |
---------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
5 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT /*+ OPT_ESTIMATE (@"SEL$1" JOIN ("P"@"SEL$1" "O"@"SEL$1") ROWS=13.000000 ) OPT_ESTIMATE (@"SEL$1" TABLE "O"@"SEL$1" ROWS=13.000000 ) */ "P"."PRODUCT_NAME" "PRODUCT_NAME" FROM "OE"."ORDER_ITEMS" "O","OE"."PRODUCT_INFORMATION" "P" WHERE "O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1 AND "P"."PRODUCT_ID"="O"."PRODUCT_ID"
Objects referenced in the statement
PRODUCT_INFORMATION[P] 92194, type = 1
ORDER_ITEMS[O] 92197, type = 1
Objects in the hash table
Hash table Object 92197, type = 1, ownerid = 6573730143572393221:
No Dynamic Sampling Directives for the object
Hash table Object 92194, type = 1, ownerid = 17822962561575639002:
No Dynamic Sampling Directives for the object
Return code in qosdInitDirCtx: ENBLD
===================================
SPD: END context at statement level
===================================
=======================================
SPD: BEGIN context at query block level
=======================================
Query Block SEL$1 (#0)
Return code in qosdSetupDirCtx4QB: NOCTX
=====================================
SPD: END context at query block level
=====================================
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0, objid = 92197, objtyp = 1, vecsize = 6, colvec = [4, 5, ], fid = 2896834833840853267
SPD: Inserted felem, fid=2896834833840853267, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = YES, keep = YES
SPD: qosdCreateFindingSingTab retCode = CREATED, fid = 2896834833840853267
SPD: qosdCreateDirCmp retCode = CREATED, fid = 2896834833840853267
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = JOIN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SKIP_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = JOIN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0, objid = 92197, objtyp = 1, vecsize = 6, colvec = [4, 5, ], fid = 2896834833840853267
SPD: Modified felem, fid=2896834833840853267, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = YES, keep = YES
SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0, objid = 92194, objtyp = 1, vecsize = 2, colvec = [1, ], fid = 5618517328604016300
SPD: Modified felem, fid=5618517328604016300, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0, objid = 92194, objtyp = 1, vecsize = 2, colvec = [1, ], fid = 1142802697078608149
SPD: Modified felem, fid=1142802697078608149, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
SPD: Generating finding id: type = 1, reason = 2, objcnt = 2, obItr = 0, objid = 92194, objtyp = 1, vecsize = 0, obItr = 1, objid = 92197, objtyp = 1, vecsize = 0, fid = 1437680122701058051
SPD: Modified felem, fid=1437680122701058051, ftype = 1, freason = 2, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
select * from table(dbms_xplan.display_cursor(format=>'report')) ;
????report????adaptive plan
Adaptive plan:
-------------
This cursor has an adaptive plan, but adaptive plans are enabled for
reporting mode only. The plan that would be executed if adaptive plans
were enabled is displayed below.
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | HASH JOIN | | 4 | 128 | 7 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| ORDER_ITEMS | 4 | 48 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| PRODUCT_INFORMATION | 1 | 20 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
SQL> select SQL_ID,IS_RESOLVED_DYNAMIC_PLAN,sql_text from v$SQL WHERE SQL_TEXT like '%MALCEAN%' and sql_text not like '%like%';
SQL_ID IS
-------------------------- --
SQL_TEXT
--------------------------------------------------------------------------------
6ydj1bn1bng17 Y
select /*MALCEAN*/ product_name from oe.order_items o, oe.product_information p
where o.unit_price=15 and quantity>1 and p.product_id=o.product_id
???? explain plan for ????default plan, ??????optimizer???final plan,??V$SQL.IS_RESOLVED_DYNAMIC_PLAN???Y,?????????????
DBA_SQL_PLAN_DIRECTIVES?????????????SQL PLAN DIRECTIVES, ???12c? ???MMON?????DML ???column usage??????????,????SMON??? MMON????SGA??PLAN DIRECTIVES???
?????DBMS_SPD.flush_sql_plan_directive????
select directive_id,type,reason from DBA_SQL_PLAN_DIRECTIVES
/
DIRECTIVE_ID TYPE REASON
----------------------------------- -------------------------------- -----------------------------
10321283028317893030 DYNAMIC_SAMPLING JOIN CARDINALITY MISESTIMATE
4757086536465754886 DYNAMIC_SAMPLING JOIN CARDINALITY MISESTIMATE
16085268038103121260 DYNAMIC_SAMPLING JOIN CARDINALITY MISESTIMATE
SQL> set pages 9999
SQL> set lines 300
SQL> col state format a5
SQL> col subobject_name format a11
SQL> col col_name format a11
SQL> col object_name format a13
SQL> select d.directive_id, o.object_type, o.object_name, o.subobject_name col_name, d.type, d.state, d.reason
2 from dba_sql_plan_directives d, dba_sql_plan_dir_objects o
3 where d.DIRECTIVE_ID=o.DIRECTIVE_ID
4 and o.object_name in ('ORDER_ITEMS')
5 order by d.directive_id;
DIRECTIVE_ID OBJECT_TYPE OBJECT_NAME COL_NAME TYPE STATE REASON
------------ ------------ ------------- ----------- -------------------------------- ----- -------------------------------------
---
1.8156E+19 COLUMN ORDER_ITEMS UNIT_PRICE DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY MISESTIMATE
1.8156E+19 TABLE ORDER_ITEMS DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY MISESTIMATE
1.8156E+19 COLUMN ORDER_ITEMS QUANTITY DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY MISESTIMATE
DBA_SQL_PLAN_DIRECTIVES????? _BASE_OPT_DIRECTIVE ? _BASE_OPT_FINDING
SELECT d.dir_own#,
d.dir_id,
d.f_id,
decode(type, 1, 'DYNAMIC_SAMPLING', 'UNKNOWN'),
decode(state,
1,
'NEW',
2,
'MISSING_STATS',
3,
'HAS_STATS',
4,
'CANDIDATE',
5,
'PERMANENT',
6,
'DISABLED',
'UNKNOWN'),
decode(bitand(flags, 1), 1, 'YES', 'NO'),
cast(d.created as timestamp),
cast(d.last_modified as timestamp),
-- Please see QOSD_DAYS_TO_UPDATE and QOSD_PLUS_SECONDS for more details
-- about 6.5
cast(d.last_used as timestamp) - NUMTODSINTERVAL(6.5, 'day')
FROM sys.opt_directive$ d
??dbms_spd??? SQL PLAN DIRECTIVES, SQL PLAN DIRECTIVES???retention ???53?:
Package: DBMS_SPD
This package provides subprograms for managing Sql Plan
Directives(SPD). SPD are objects generated automatically by Oracle
server. For example, if server detects that the single table cardinality
estimated by optimizer is off from the actual number of rows returned
when accessing the table, it will automatically create a directive to
do dynamic sampling for the table. When any Sql statement referencing
the table is compiled, optimizer will perform dynamic sampling for the
table to get more accurate estimate.
Notes:
DBMSL_SPD is a invoker-rights package. The invoker requires ADMINISTER
SQL MANAGEMENT OBJECT privilege for executing most of the subprograms of
this package. Also the subprograms commit the current transaction (if any),
perform the operation and commit it again.
DBA view dba_sql_plan_directives shows all the directives created in
the system and the view dba_sql_plan_dir_objects displays the objects that
are included in the directives.
-- Default value for SPD_RETENTION_WEEKS
SPD_RETENTION_WEEKS_DEFAULT CONSTANT varchar2(4) := '53';
| STATE : NEW : Newly created directive.
| : MISSING_STATS : The directive objects do not
| have relevant stats.
| : HAS_STATS : The objects have stats.
| : PERMANENT : A permanent directive. Server
| evaluated effectiveness and these
| directives are useful.
|
| AUTO_DROP : YES : Directive will be dropped
| automatically if not
| used for SPD_RETENTION_WEEKS.
| This is the default behavior.
| NO : Directive will not be dropped
| automatically.
Procedure: flush_sql_plan_directive
This procedure allows manually flushing the Sql Plan directives that
are automatically recorded in SGA memory while executing sql
statements. The information recorded in SGA are periodically flushed
by oracle background processes. This procedure just provides a way to
flush the information manually.
????”_optimizer_dynamic_plans”(enable dynamic plans)????????,???TRUE??DYNAMIC PLAN? ???FALSE????????????
????,Dynamic Plan????????????Nested Loop?Hash Join???case ,????????Nested loop???????????HASH JOIN,?HASH JOIN?????????????????
????????subplan?????,???? pass?? ?join method???,?????STATISTICS COLLECTOR???cardinality?,???????HASH JOIN?????Nested Loop,????????????subplan?????access path;
???????Sales??????????????????,????HASH JOIN,??SUBPLAN??customers?????????;?????Nested Loop,???????cust_id?????Range Scan+Access by Rowid?
Cardinality feedback
Cardinality feedback????????11.2????,????????re-optimization???; ???????????,Cardinality feedback?????????????????????????? ???????????????????,?????????????????,??????????Cardinality feedback????????????? ?????????????????????????
??????????????Cardinality feedback ??: ????????,???????????,??????????,????????????????selectivity ???
????????????:
??????,?????????????????????????????????,??????????????????? ????????????????????????????????????????,?????????????????????????? ?????????,???????????????,??????????
??????????Cardinality ????,??????join Cardinality ????????? Cardinality feedback???????cursor?,?Cursor???aged out?????
SELECT /*+ gather_plan_statistics */ product_name FROM order_items
o, product_information p WHERE o.unit_price = 15 AND quantity
> 1 AND p.product_id = o.product_id
Plan hash value: 1553478007
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 |00:00:00.01 | 24 | 20 | | | |
|* 1 | HASH JOIN | | 1 | 4 | 13 |00:00:00.01 | 24 | 20 | 2061K| 2061K| 429K (0)|
|* 2 | TABLE ACCESS FULL| ORDER_ITEMS | 1 | 4 | 13 |00:00:00.01 | 7 | 6 | | | |
| 3 | TABLE ACCESS FULL| PRODUCT_INFORMATION | 1 | 1 | 288 |00:00:00.01 | 17 | 14 | | | |
----------------------------------------------------------------------------------------------------------------------------------------
SELECT /*+ gather_plan_statistics */ product_name FROM order_items
o, product_information p WHERE o.unit_price = 15 AND quantity
> 1 AND p.product_id = o.product_id
Plan hash value: 1553478007
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 |00:00:00.01 | 24 | | | |
|* 1 | HASH JOIN | | 1 | 13 | 13 |00:00:00.01 | 24 | 2061K| 2061K| 413K (0)|
|* 2 | TABLE ACCESS FULL| ORDER_ITEMS | 1 | 13 | 13 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| PRODUCT_INFORMATION | 1 | 288 | 288 |00:00:00.01 | 17 | | | |
-------------------------------------------------------------------------------------------------------------------------------
Note
-----
- statistics feedback used for this statement
SQL> select count(*) from v$SQL where SQL_ID='cz0hg2zkvd10y';
COUNT(*)
----------
2
SQL>select sql_ID,USE_FEEDBACK_STATS FROM V$SQL_SHARED_CURSOR where USE_FEEDBACK_STATS ='Y';
SQL_ID U
------------- -
cz0hg2zkvd10y Y
????????Cardinality feedback????,???????????????????????????,????????????order_items???????? ????2??????plan hash value??(??????????),?????2????child cursor??????gather_plan_statistics???actual : A-ROWS estimate :E-ROWS?????????
Automatic Re-optimization
???dynamic plan, Re-optimization??????????????? ? ??????????????? ???????????????????????????????? ???????????,??????????????, ???????????????????? ??????????? Re-optimization??, ?????????????????????
Re-optimization????dynamic plan?????????? dynamic plan????????????????????, ???????????????????? ????,??????????join order ??????????????,?????????????join order????? ??????,????????Re-optimization, ??Re-optimization ???????????????????
?Oracle database 12c?,join statistics?????????????????????,??????????????????????Re-optimization???????????adaptive cursor sharing????? ????????????????,????????????
????? ???????statistics collectors ????????????????????Re-optimization??????2?????????????,????????????????
??????????????Re-optimization?????,??????????????????????
???v$SQL??????IS_REOPTIMIZABLE?????????????????????Re-optimization,??????????Re-optimization???,?????Re-optimization ,???????reporting?????
IS_REOPTIMIZABLE
VARCHAR2(1)
This columns shows whether the next execution matching this child cursor will trigger a reoptimization. The values are:
Y: If the next execution will trigger a reoptimization
R: If the child cursor contains reoptimization information, but will not trigger reoptimization because the cursor was compiled in reporting mode
N: If the child cursor has no reoptimization information
??1:
select plan_table_output from table (dbms_xplan.display_cursor('gwf99gfnm0t7g',NULL,'ALLSTATS LAST'));
SQL_ID gwf99gfnm0t7g, child number 0
-------------------------------------
SELECT /*+ SFTEST gather_plan_statistics */ o.order_id, v.product_name
FROM orders o, ( SELECT order_id, product_name FROM order_items o,
product_information p WHERE p.product_id = o.product_id AND
list_price < 50 AND min_price < 40 ) v WHERE o.order_id = v.order_id
Plan hash value: 1906736282
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 269 |00:00:00.02 | 1336 | 18 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 269 |00:00:00.02 | 1336 | 18 | | | |
| 2 | MERGE JOIN CARTESIAN| | 1 | 4 | 9135 |00:00:00.02 | 34 | 15 | | | |
|* 3 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | 1 | 87 |00:00:00.01 | 33 | 14 | | | |
| 4 | BUFFER SORT | | 87 | 105 | 9135 |00:00:00.01 | 1 | 1 | 4096 | 4096 | 4096 (0)|
| 5 | INDEX FULL SCAN | ORDER_PK | 1 | 105 | 105 |00:00:00.01 | 1 | 1 | | | |
|* 6 | INDEX UNIQUE SCAN | ORDER_ITEMS_UK | 9135 | 1 | 269 |00:00:00.01 | 1302 | 3 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID")
SQL_ID gwf99gfnm0t7g, child number 1
-------------------------------------
SELECT /*+ SFTEST gather_plan_statistics */ o.order_id, v.product_name
FROM orders o, ( SELECT order_id, product_name FROM order_items o,
product_information p WHERE p.product_id = o.product_id AND
list_price < 50 AND min_price < 40 ) v WHERE o.order_id = v.order_id
Plan hash value: 35479787
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 269 |00:00:00.01 | 63 | 3 | | | |
| 1 | NESTED LOOPS | | 1 | 269 | 269 |00:00:00.01 | 63 | 3 | | | |
|* 2 | HASH JOIN | | 1 | 313 | 269 |00:00:00.01 | 42 | 3 | 1321K| 1321K| 1234K (0)|
|* 3 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | 87 | 87 |00:00:00.01 | 16 | 0 | | | |
| 4 | INDEX FAST FULL SCAN| ORDER_ITEMS_UK | 1 | 665 | 665 |00:00:00.01 | 26 | 3 | | | |
|* 5 | INDEX UNIQUE SCAN | ORDER_PK | 269 | 1 | 269 |00:00:00.01 | 21 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
5 - access("O"."ORDER_ID"="ORDER_ID")
Note
-----
- statistics feedback used for this statement
SQL> select IS_REOPTIMIZABLE,child_number FROM V$SQL A where A.SQL_ID='gwf99gfnm0t7g';
IS CHILD_NUMBER
-- ------------
Y 0
N 1
1* select child_number,other_xml From v$SQL_PLAN where SQL_ID='gwf99gfnm0t7g' and other_xml is not nul
SQL> /
CHILD_NUMBER OTHER_XML
------------ --------------------------------------------------------------------------------
1 <other_xml><info type="cardinality_feedback">yes</info><info type="db_version">1
2.1.0.1</info><info type="parse_schema"><![CDATA["OE"]]></info><info type="plan_
hash">35479787</info><info type="plan_hash_2">3382491761</info><outline_data><hi
nt><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATUR
ES_ENABLE('12.1.0.1')]]></hint><hint><![CDATA[DB_VERSION('12.1.0.1')]]></hint><h
int><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$F5BB74E1")]]></
hint><hint><![CDATA[MERGE(@"SEL$2")]]></hint><hint><![CDATA[OUTLINE(@"SEL$1")]]>
</hint><hint><![CDATA[OUTLINE(@"SEL$2")]]></hint><hint><![CDATA[FULL(@"SEL$F5BB7
4E1" "P"@"SEL$2")]]></hint><hint><![CDATA[INDEX_FFS(@"SEL$F5BB74E1" "O"@"SEL$2"
("ORDER_ITEMS"."ORDER_ID" "ORDER_ITEMS"."PRODUCT_ID"))]]></hint><hint><![CDATA[I
NDEX(@"SEL$F5BB74E1" "O"@"SEL$1" ("ORDERS"."ORDER_ID"))]]></hint><hint><![CDATA[
LEADING(@"SEL$F5BB74E1" "P"@"SEL$2" "O"@"SEL$2" "O"@"SEL$1")]]></hint><hint><![C
DATA[USE_HASH(@"SEL$F5BB74E1" "O"@"SEL$2")]]></hint><hint><![CDATA[USE_NL(@"SEL$
F5BB74E1" "O"@"SEL$1")]]></hint></outline_data></other_xml>
0 <other_xml><info type="db_version">12.1.0.1</info><info type="parse_schema"><![C
DATA["OE"]]></info><info type="plan_hash">1906736282</info><info type="plan_hash
_2">2579473118</info><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]>
</hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('12.1.0.1')]]></hint><hint><![CD
ATA[DB_VERSION('12.1.0.1')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CD
ATA[OUTLINE_LEAF(@"SEL$F5BB74E1")]]></hint><hint><![CDATA[MERGE(@"SEL$2")]]></hi
nt><hint><![CDATA[OUTLINE(@"SEL$1")]]></hint><hint><![CDATA[OUTLINE(@"SEL$2")]]>
</hint><hint><![CDATA[FULL(@"SEL$F5BB74E1" "P"@"SEL$2")]]></hint><hint><![CDATA[
INDEX(@"SEL$F5BB74E1" "O"@"SEL$1" ("ORDERS"."ORDER_ID"))]]></hint><hint><![CDATA
[INDEX(@"SEL$F5BB74E1" "O"@"SEL$2" ("ORDER_ITEMS"."ORDER_ID" "ORDER_ITEMS"."PROD
UCT_ID"))]]></hint><hint><![CDATA[LEADING(@"SEL$F5BB74E1" "P"@"SEL$2" "O"@"SEL$1
" "O"@"SEL$2")]]></hint><hint><![CDATA[USE_MERGE_CARTESIAN(@"SEL$F5BB74E1" "O"@"
SEL$1")]]></hint><hint><![CDATA[USE_NL(@"SEL$F5BB74E1" "O"@"SEL$2")]]></hint></o
utline_data></other_xml>
??2:
SELECT /*+gather_plan_statistics*/ *
FROM customers
WHERE cust_state_province='CA'
AND country_id='US';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID b74nw722wjvy3, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ * from customers where
CUST_STATE_PROVINCE='CA' and country_id='US'
Plan hash value: 1683234692
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 29 |00:00:00.01 | 17 | 14 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 1 | 8 | 29 |00:00:00.01 | 17 | 14 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"='US'))
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE
FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT /*+gather_plan_statistics*/%';
SQL_ID CHILD_NUMBER SQL_TEXT I
------------- ------------ ----------- -
b74nw722wjvy3 0 select /*+g Y
ather_plan_
statistics*
/ * from cu
stomers whe
re CUST_STA
TE_PROVINCE
='CA' and c
ountry_id='
US'
EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER, o.OBJECT_NAME,
o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND o.OWNER IN ('SH')
ORDER BY 1,2,3,4,5;
DIR_ID OWNER OBJECT_NAME COL_NAME OBJECT TYPE STATE REASON
----------------------- ----- ------------- ----------- ------ ---------------- ----- ------------------------
1484026771529551585 SH CUSTOMERS COUNTRY_ID COLUMN DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY
MISESTIMATE
1484026771529551585 SH CUSTOMERS CUST_STATE_ COLUMN DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY
PROVINCE MISESTIMATE
1484026771529551585 SH CUSTOMERS TABLE DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY
MISESTIMATE
SELECT /*+gather_plan_statistics*/ *
FROM customers
WHERE cust_state_province='CA'
AND country_id='US';
ELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID b74nw722wjvy3, child number 1
-------------------------------------
select /*+gather_plan_statistics*/ * from customers where
CUST_STATE_PROVINCE='CA' and country_id='US'
Plan hash value: 1683234692
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 29 |00:00:00.01 | 17 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 1 | 29 | 29 |00:00:00.01 | 17 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"='US'))
Note
-----
- cardinality feedback used for this statement
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE
FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT /*+gather_plan_statistics*/%';
SQL_ID CHILD_NUMBER SQL_TEXT I
------------- ------------ ----------- -
b74nw722wjvy3 0 select /*+g Y
ather_plan_
statistics*
/ * from cu
stomers whe
re CUST_STA
TE_PROVINCE
='CA' and c
ountry_id='
US'
b74nw722wjvy3 1 select /*+g N
ather_plan_
statistics*
/ * from cu
stomers whe
re CUST_STA
TE_PROVINCE
='CA' and c
ountry_id='
US'
SELECT /*+gather_plan_statistics*/ CUST_EMAIL
FROM CUSTOMERS
WHERE CUST_STATE_PROVINCE='MA'
AND COUNTRY_ID='US';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3tk6hj3nkcs2u, child number 0
-------------------------------------
Select /*+gather_plan_statistics*/ cust_email From customers Where
cust_state_province='MA' And country_id='US'
Plan hash value: 1683234692
-------------------------------------------------------------------------------
|Id | Operation | Name | Starts|E-Rows|A-Rows| A-Time |Buffers|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01| 16 |
|*1 | TABLE ACCESS FULL| CUSTOMERS | 1 | 2| 2 |00:00:00.01| 16 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CUST_STATE_PROVINCE"='MA' AND "COUNTRY_ID"='US'))
Note
-----
- dynamic sampling used for this statement (level=2)
- 1 Sql Plan Directive used for this statement
EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER, o.OBJECT_NAME,
o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND o.OWNER IN ('SH')
ORDER BY 1,2,3,4,5;
DIR_ID OW OBJECT_NA COL_NAME OBJECT TYPE STATE REASON
------------------- -- --------- ---------- ------- --------------- ------------- ------------------------
1484026771529551585 SH CUSTOMERS COUNTRY_ID COLUMN DYNAMIC_SAMPLING MISSING_STATS SINGLE TABLE CARDINALITY
MISESTIMATE
1484026771529551585 SH CUSTOMERS CUST_STATE_ COLUMN DYNAMIC_SAMPLING MISSING_STATS SINGLE TABLE CARDINALITY
PROVINCE MISESTIMATE
1484026771529551585 SH CUSTOMERS TABLE DYNAMIC_SAMPLING MISSING_STATS SINGLE TABLE CARDINALITY
MISESTIMATE
