-- 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
...