-- Demo case: Research of SPD dynamic sampling result caching -- Date: 28-11-2017 -- References: https://blogs.oracle.com/optimizer/how-are-dynamic-sampling-results-managed-for-sql-plan-directives -- Demo run on Oracle version: 12.2.0.1 drop user cfbt cascade; create user cfbt identified by cfbt default tablespace users; grant dba to cfbt; grant sysdba to cfbt; connect cfbt/cfbt@T122DB create table cfb_basic (a number, b number); insert into cfb_basic select 0, 0 from dual CONNECT BY LEVEL <= 600000; insert into cfb_basic select 1, 1 from dual CONNECT BY LEVEL <= 100000; insert into cfb_basic select 2, 2 from dual CONNECT BY LEVEL <= 100000; commit; create index cfb_basic_a on cfb_basic(a); create index cfb_basic_b on cfb_basic(b); exec dbms_stats.gather_table_stats(USER,'CFB_BASIC', method_opt => 'FOR ALL COLUMNS SIZE 1'); alter system flush shared_pool; select /*+ gather_plan_statistics */ count(*) from cfb_basic where a = 0 and b = 0; select /*+ gather_plan_statistics */ count(*) from cfb_basic where a = 0 and b = 0; exec dbms_spd.flush_sql_plan_directive(); select a.notes, b.notes, b.type from dba_sql_plan_dir_objects a, dba_sql_plan_directives b where a.directive_id = b.directive_id and a.object_name = 'CFB_BASIC'; NOTES NOTES TYPE ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ----------------------- DYNAMIC_SAMPLING NEW NO {EC(CFBT.CFB_BASIC)[A, B]} DYNAMIC_SAMPLING NEW NO {EC(CFBT.CFB_BASIC)[A, B]} DYNAMIC_SAMPLING YES NEW YES NO NO {EC(CFBT.CFB_BASIC)[A, B]} NO alter system flush shared_pool; alter session set optimizer_dynamic_sampling=11; select /*+ gather_plan_statistics */ count(*) from cfb_basic where a = 0 and b = 0; exec dbms_spd.flush_sql_plan_directive(); select a.notes, b.notes, b.type from dba_sql_plan_dir_objects a, dba_sql_plan_directives b where a.directive_id = b.directive_id and a.object_name = 'CFB_BASIC'; NOTES NOTES TYPE ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ----------------------- DYNAMIC_SAMPLING NEW NO {EC(CFBT.CFB_BASIC)[A, B]} DYNAMIC_SAMPLING NEW NO {EC(CFBT.CFB_BASIC)[A, B]} DYNAMIC_SAMPLING YES NEW YES NO NO {EC(CFBT.CFB_BASIC)[A, B]} NO DYNAMIC_SAMPLING_RESULT NO NEW NO NO NO {(CFBT.CFB_BASIC, num_rows=800000) - (SQL_ID:3xccsbzqpj385, T.CARD=640399[-2 - NO DYNAMIC_SAMPLING_RESULT NO NEW NO NO NO {(CFBT.CFB_BASIC, num_rows=800000) - (SQL_ID:1nh2rhabu09q6, Stopped, I.ACCESS NO DYNAMIC_SAMPLING_RESULT NO NEW NO NO NO {(CFBT.CFB_BASIC, num_rows=800000) - (SQL_ID:3ajk1adfqu8xw, Stopped, I.ACCESS NO alter system flush shared_pool; alter session set optimizer_dynamic_sampling=11; alter session set events '10053 trace name context forever, level 1'; alter session set events 'trace[RDBMS.SQL_DS] disk=high'; select /*+ gather_plan_statistics */ count(*) from cfb_basic where a = 0 and b = 0; ... Access path analysis for CFB_BASIC *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for CFB_BASIC[CFB_BASIC] SPD: Directive valid: dirid = 17442183143052500323, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(80096)[1, 2]} SPD: Return code in qosdDSDirSetup: EXISTS, estType = TABLE *** 2017-11-28T10:08:29.948665+01:00 ** Performing dynamic sampling initial checks. ** ** Not using old style dynamic sampling since ADS is enabled. Column (#1): A(NUMBER) AvgLen: 3 NDV: 3 Nulls: 0 Density: 0.333333 Min: 0.000000 Max: 2.000000 Estimated selectivity: 0.333333 , col: #1 Column (#2): B(NUMBER) AvgLen: 3 NDV: 3 Nulls: 0 Density: 0.333333 Min: 0.000000 Max: 2.000000 Estimated selectivity: 0.333333 , col: #2 kkecdn: Single Table Predicate:"CFB_BASIC"."A"=0 Estimated selectivity: 0.333333 , col: #1 dsExecute kkecdn: Single Table Predicate:"CFB_BASIC"."B"=0 Estimated selectivity: 0.333333 , col: #2 Table: CFB_BASIC Alias: CFB_BASIC Card: Original: 800000.000000qksdsSInitCtx(): qksdsSInitCtx(): timeLimit(ms) = 2500 qksdsFindSampleTable(): qksdsFindSampleTable(): enter qksdsFindSampleTable(): qksdsFindSampleTable (CFB_BASIC): canSamp=Yes cdn=0.000000 maxCard=0.000000 qksdsFindSampleTable(): qksdsFindSampleTable(exit): tab=CFB_BASIC qksdsCheckPreds(): qksdsCheckPreds(exit): total count=2 usable count=2 qksdsExecute(): qksdsExecute(): enter SPD: qosdGetFObjKeyFromStmt: sqlText = SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "CFB_BASIC") */ 1 AS C1 FROM "CFB_BASIC" "CFB_BASIC" WHERE ("CFB_BASIC"."A"=0) AND ("CFB_BASIC"."B"=0)) innerQuery (objid = 4517559439000636677) SPD: Generating finding id: type = 2, reason = 7, objcnt = 2, obItr = 0, objid = 4517559439000636677, objtyp = 4, vecsize = 0, obItr = 1, objid = 80096, objtyp = 1, vecsize = 0, fid = 7003751258125903944 qksdsExecute(): Used results from directive cache (status = SUCCESS) qksdsDumpResult(): DS Results: #exps=1, smp obj=CFB_BASIC qksdsDumpResult(): T.CARD = qksdsDumpResult(): (mid=640399.0, low=640399.0, hig=640399.0)qksdsDumpResult(): qksdsDumpResult(): end dumping results qksdsDumpStats(): ************************************************************** DS Service Statistics qksdsDumpStats(): Executions: 0 Retries: 0 Timeouts: 0 ParseFails: 0 ExecFails: 0 qksdsDumpStats(): qksdsExecute(): qksdsExecute(): exit >> Single Tab Card adjusted from 88888.888889 to 640399.000000 due to adaptive dynamic sampling ... exec dbms_stats.gather_table_stats(USER,'CFB_BASIC', method_opt => 'FOR ALL COLUMNS SIZE 1'); exec dbms_spd.flush_sql_plan_directive(); alter system flush shared_pool; alter session set optimizer_dynamic_sampling=11; alter session set events '10053 trace name context forever, level 1'; alter session set events 'trace[RDBMS.SQL_DS] disk=high'; select /*+ gather_plan_statistics */ count(*) from cfb_basic where a = 0 and b = 0; ... Access path analysis for CFB_BASIC *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for CFB_BASIC[CFB_BASIC] SPD: Directive valid: dirid = 17442183143052500323, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(80096)[1, 2]} SPD: Return code in qosdDSDirSetup: EXISTS, estType = TABLE *** 2017-11-28T10:15:04.562893+01:00 ** Performing dynamic sampling initial checks. ** ** Not using old style dynamic sampling since ADS is enabled. Column (#1): A(NUMBER) AvgLen: 3 NDV: 3 Nulls: 0 Density: 0.333333 Min: 0.000000 Max: 2.000000 Estimated selectivity: 0.333333 , col: #1 Column (#2): B(NUMBER) AvgLen: 3 NDV: 3 Nulls: 0 Density: 0.333333 Min: 0.000000 Max: 2.000000 Estimated selectivity: 0.333333 , col: #2 kkecdn: Single Table Predicate:"CFB_BASIC"."A"=0 Estimated selectivity: 0.333333 , col: #1 kkecdn: Single Table Predicate:"CFB_BASIC"."B"=0 Estimated selectivity: 0.333333 , col: #2 Table: CFB_BASIC Alias: CFB_BASIC Card: Original: 800000.000000qksdsSInitCtx(): qksdsSInitCtx(): timeLimit(ms) = 250 qksdsFindSampleTable(): qksdsFindSampleTable(): enter qksdsFindSampleTable(): qksdsFindSampleTable (CFB_BASIC): canSamp=Yes cdn=0.000000 maxCard=0.000000 qksdsFindSampleTable(): qksdsFindSampleTable(exit): tab=CFB_BASIC qksdsCheckPreds(): qksdsCheckPreds(exit): total count=2 usable count=2 qksdsExecute(): qksdsExecute(): enter SPD: qosdGetFObjKeyFromStmt: sqlText = SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "CFB_BASIC") */ 1 AS C1 FROM "CFB_BASIC" "CFB_BASIC" WHERE ("CFB_BASIC"."A"=0) AND ("CFB_BASIC"."B"=0)) innerQuery (objid = 4517559439000636677) SPD: Generating finding id: type = 2, reason = 7, objcnt = 2, obItr = 0, objid = 4517559439000636677, objtyp = 4, vecsize = 0, obItr = 1, objid = 80096, objtyp = 1, vecsize = 0, fid = 7003751258125903944 SPD: Dynamic sampling result directive (6337388821259708697) is stale (table = 80096) qksdsExeStmt(): qksdsExeStmt(): enter qksdsExeStmt(): ************************************************************ DS Query Text: SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "CFB_BASIC") */ 1 AS C1 FROM "CFB_BASIC" SAMPLE BLOCK(31.9489, 8) SEED(1) "CFB_BASIC" WHERE ("CFB_BASIC"."A"=0) AND ("CFB_BASIC"."B"=0)) innerQuery qksdsExeStmt(): qksdsExeStmt(): newSoftTimeLimit is 1 qksdsExeStmt(): timeInt = 1 timeLimit = 0 elapTime = 0 ************************************************************** Iteration 1 Exec count: 2 CR gets: 456 CU gets: 0 Disk Reads: 0 Disk Writes: 0 IO Read Requests: 0 IO Write Requests: 0 Bytes Read: 0 Bytes Written: 0 Bytes Exchanged with Storage: 0 Bytes Exchanged with Disk: 0 Bytes Simulated Read: 0 Bytes Simulated Returned: 0 Elapsed Time: 14 (ms) CPU Time: 15 (ms) User I/O Time: 0 (us) qksdsDumpEStats(): Sampling Input IO Size: 8 Sample Size: 31.948882 Post S. Size: 100.000000 qksdsExeStmt(): qksdsExeStmt: exit qksdsExecute(): Dumping unscaled result qksdsDumpResult(): DS Results: #exps=1, smp obj=CFB_BASIC qksdsDumpResult(): T.CARD = qksdsDumpResult(): (mid=221760.0, low=0.0, hig=0.0)qksdsDumpResult(): qksdsDumpResult(): end dumping results qksdsExeStmt(): qksdsExeStmt(): enter qksdsExeStmt(): ************************************************************ DS Query Text: SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "CFB_BASIC") */ 1 AS C1 FROM "CFB_BASIC" SAMPLE BLOCK(31.9489, 8) SEED(2) "CFB_BASIC" WHERE ("CFB_BASIC"."A"=0) AND ("CFB_BASIC"."B"=0)) innerQuery qksdsExeStmt(): qksdsExeStmt(): newSoftTimeLimit is 1 qksdsExeStmt(): timeInt = 1 timeLimit = 0 elapTime = 14 ************************************************************** Iteration 2 Exec count: 1 CR gets: 415 CU gets: 0 Disk Reads: 0 Disk Writes: 0 IO Read Requests: 0 IO Write Requests: 0 Bytes Read: 0 Bytes Written: 0 Bytes Exchanged with Storage: 0 Bytes Exchanged with Disk: 0 Bytes Simulated Read: 0 Bytes Simulated Returned: 0 Elapsed Time: 9748 (us) CPU Time: 10 (ms) User I/O Time: 0 (us) qksdsDumpEStats(): Sampling Input IO Size: 8 Sample Size: 31.948882 Post S. Size: 100.000000 qksdsExeStmt(): qksdsExeStmt: exit qksdsExecute(): Dumping unscaled result qksdsDumpResult(): DS Results: #exps=1, smp obj=CFB_BASIC qksdsDumpResult(): T.CARD = qksdsDumpResult(): (mid=409200.0, low=0.0, hig=0.0)qksdsDumpResult(): qksdsDumpResult(): end dumping results qksdsScaleResult(): Dumping scaled result (status = SUCCESS) qksdsDumpResult(): DS Results: #exps=1, smp obj=CFB_BASIC qksdsDumpResult(): T.CARD = qksdsDumpResult(): (mid=640398.0, low=640398.0, hig=640398.0)qksdsDumpResult(): qksdsDumpResult(): end dumping results SPD: qosdGetFObjKeyFromStmt: sqlText = SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "CFB_BASIC") */ 1 AS C1 FROM "CFB_BASIC" "CFB_BASIC" WHERE ("CFB_BASIC"."A"=0) AND ("CFB_BASIC"."B"=0)) innerQuery (objid = 4517559439000636677) SPD: Generating finding id: type = 2, reason = 7, objcnt = 2, obItr = 0, objid = 4517559439000636677, objtyp = 4, vecsize = 0, obItr = 1, objid = 80096, objtyp = 1, vecsize = 0, fid = 7003751258125903944 SPD: qosdDirPropUpdate dirid = 6337388821259708697, retCode = UPDATED SPD: qosdCreateDir4DSResult retCode = UPDATED, fid = 0 ************************************************************** Final Exec count: 3 CR gets: 871 CU gets: 0 Disk Reads: 0 Disk Writes: 0 IO Read Requests: 0 IO Write Requests: 0 Bytes Read: 0 Bytes Written: 0 Bytes Exchanged with Storage: 0 Bytes Exchanged with Disk: 0 Bytes Simulated Read: 0 Bytes Simulated Returned: 0 Elapsed Time: 24 (ms) CPU Time: 25 (ms) User I/O Time: 0 (us) qksdsDumpEStats(): Sampling Input IO Size: 8 Sample Size: 31.948882 Post S. Size: 100.000000 qksdsDumpStats(): ************************************************************** DS Service Statistics qksdsDumpStats(): Executions: 2 Retries: 1 Timeouts: 0 ParseFails: 0 ExecFails: 0 qksdsDumpStats(): qksdsExecute(): qksdsExecute(): exit >> Single Tab Card adjusted from 88888.888889 to 640398.000000 due to adaptive dynamic sampling ... alter system flush shared_pool; alter session set optimizer_dynamic_sampling=11; alter session set events '10053 trace name context forever, level 1'; alter session set events 'trace[RDBMS.SQL_DS] disk=high'; select /*+ gather_plan_statistics */ count(*) from cfb_basic where a = 0 and b = 0; ... Access path analysis for CFB_BASIC *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for CFB_BASIC[CFB_BASIC] SPD: Directive valid: dirid = 17442183143052500323, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(80096)[1, 2]} SPD: Return code in qosdDSDirSetup: EXISTS, estType = TABLE *** 2017-11-28T10:18:35.357233+01:00 ** Performing dynamic sampling initial checks. ** ** Not using old style dynamic sampling since ADS is enabled. Column (#1): A(NUMBER) AvgLen: 3 NDV: 3 Nulls: 0 Density: 0.333333 Min: 0.000000 Max: 2.000000 Estimated selectivity: 0.333333 , col: #1 Column (#2): B(NUMBER) AvgLen: 3 NDV: 3 Nulls: 0 Density: 0.333333 Min: 0.000000 Max: 2.000000 Estimated selectivity: 0.333333 , col: #2 kkecdn: Single Table Predicate:"CFB_BASIC"."A"=0 Estimated selectivity: 0.333333 , col: #1 kkecdn: Single Table Predicate:"CFB_BASIC"."B"=0 Estimated selectivity: 0.333333 , col: #2 Table: CFB_BASIC Alias: CFB_BASIC Card: Original: 800000.000000qksdsSInitCtx(): qksdsSInitCtx(): timeLimit(ms) = 250 qksdsFindSampleTable(): qksdsFindSampleTable(): enter qksdsFindSampleTable(): qksdsFindSampleTable (CFB_BASIC): canSamp=Yes cdn=0.000000 maxCard=0.000000 qksdsFindSampleTable(): qksdsFindSampleTable(exit): tab=CFB_BASIC qksdsCheckPreds(): qksdsCheckPreds(exit): total count=2 usable count=2 qksdsExecute(): qksdsExecute(): enter SPD: qosdGetFObjKeyFromStmt: sqlText = SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "CFB_BASIC") */ 1 AS C1 FROM "CFB_BASIC" "CFB_BASIC" WHERE ("CFB_BASIC"."A"=0) AND ("CFB_BASIC"."B"=0)) innerQuery (objid = 4517559439000636677) SPD: Generating finding id: type = 2, reason = 7, objcnt = 2, obItr = 0, objid = 4517559439000636677, objtyp = 4, vecsize = 0, obItr = 1, objid = 80096, objtyp = 1, vecsize = 0, fid = 7003751258125903944 qksdsExecute(): Used results from directive cache (status = SUCCESS) qksdsDumpResult(): DS Results: #exps=1, smp obj=CFB_BASIC qksdsDumpResult(): T.CARD = qksdsDumpResult(): (mid=640399.0, low=640399.0, hig=640399.0)qksdsDumpResult(): qksdsDumpResult(): end dumping results qksdsDumpStats(): ************************************************************** DS Service Statistics qksdsDumpStats(): Executions: 0 Retries: 0 Timeouts: 0 ParseFails: 0 ExecFails: 0 qksdsDumpStats(): qksdsExecute(): qksdsExecute(): exit >> Single Tab Card adjusted from 88888.888889 to 640399.000000 due to adaptive dynamic sampling ...