I read at many places that in version 12.1.0.2 Adaptive Dynamic Sampling
done due to Sql Plan Directive will be always at level 11, but
dbms_xplan.display_cursor doesn't refelct this truth.
So I thought of proving it by tracing 10053 along with RDBMS.SQL_DS
tracing. But I am not able to interpret the tracing result to prove that it
does dynamic sampling at level 11 due to SPD.
Below is the snippet of trace and according to dbms_xplan.display_cursor it
says - (dynamic statistics used: dynamic sampling (level=2)). Could anyone
please help me in here.
=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "SH"."CUSTOMERS" "CUSTOMERS" WHERE
"CUSTOMERS"."CUST_STATE_PROVINCE"=:B1 AND "CUSTOMERS"."COUNTRY_ID"=:B2
Objects referenced in the statement
CUSTOMERS[CUSTOMERS] 93246, type = 1
Objects in the hash table
Hash table Object 93246, type = 1, ownerid = 12409857813664911764:
Dynamic Sampling Directives at location 1:
dirid = 17753408575423626103, state = 2, flags = 1, loc = 1
{EC(93246)[11, 13]}
Return code in qosdInitDirCtx: ENBLD
===================================
SPD: END context at statement level
===================================
kkoadsInitCtx(enter): ctxP=0x8d1ab730
check parallelism for statement[<unnamed>]
kkfdPaPrm.1:curInst:2, curpxEnabled=1, curCPUCount=1
kkfdPaPrm.2:sessInst:2, sesspxEnabled=1, sesCPUCount=1
kkfdtParallel: parallel is possible (no statement type restrictions)
kkfdPaForcePrm: dop:1 ()
use dictionary DOP(1) on table
kkfdPaPrm:- The table : 93246
kkfdPaPrm:DOP = 1 (computed from hint/dictionary/autodop)
kkfdiPaPrm: dop:1 serial(?) flags: 1
kkoadsTimeLimitFromSrc(Enter) exeStSrc=CC
kkoadsTimeLimitFromSrc(Exit) timeLimit=0
kkoadsTimeLimitFromSrc(Enter) exeStSrc=AWR
kkoadsTimeLimitFromSrc(Exit) timeLimit=0
kkoadsTimeLimit: source=Voodoo timeLimit=10
kkoadsMaxTabCount: source=auto maxTabCnt=2
kkoadsSetTimeSlice: #candTabs=1 slice=2500
*** 2015-12-03 06:49:51.372
qksdsSInitCtx(): qksdsSInitCtx(): timeLimit(ms) = 1
qksdsExecute(): qksdsExecute(): enter
qksdsEvaBlock(): qksdsEvaBlock: SUCCESS - blocks(CUSTOMERS)=1551
qksdsExecute(): qksdsExecute(): exit
qksdsSInitCtx(): qksdsSInitCtx(): timeLimit(ms) = 250
qksdsCheckPreds(): qksdsCheckPreds(exit): total count=2 usable count=2
qksdsExecute(): qksdsExecute(): enter
qksdsExeStmt(): qksdsExeStmt(): enter
qksdsExeStmt(): ************************************************************
DS Query Text:
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
optimizer_features_enable(default) no_parallel result_cache(snapshot=3600)
*/ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS(
"CUSTOMERS") */ 1 AS C1 FROM "SH"."CUSTOMERS" SAMPLE BLOCK(51.5796, 8)
SEED(1) "CUSTOMERS" WHERE ("CUSTOMERS"."CUST_STATE_PROVINCE"=:B1) AND
("CUSTOMERS"."COUNTRY_ID"=:B2)) innerQuery
qksdsExeStmt():
qksdsExeStmt(): timeInt = 1 timeLimit = 0 elapTime = 0
**************************************************************
Iteration 1
Exec count: 3
CR gets: 805
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: 10 (ms)
CPU Time: 6999 (us)
User I/O Time: 0 (us)
qksdsDumpEStats(): Sampling Input
IO Size: 8
Sample Size: 51.579626
Post S. Size: 100.000000
qksdsExeStmt(): qksdsExeStmt: exit
**************************************************************
Final
Exec count: 3
CR gets: 805
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: 10 (ms)
CPU Time: 6999 (us)
User I/O Time: 0 (us)
qksdsDumpEStats(): Sampling Input
IO Size: 8
Sample Size: 51.579626
Post S. Size: 100.000000
qksdsDumpStats():
**************************************************************
DS Service Statistics
qksdsDumpStats(): Executions: 1
Retries: 0
Timeouts: 0
ParseFails: 0
ExecFails: 0
qksdsDumpStats(): qksdsDumpResult(): DS Results: #exps=1, smp obj=CUSTOMERS
qksdsDumpResult(): T.CARD = qksdsDumpResult(): (mid=36.8, low=36.8,
hig=36.8)qksdsDumpResult():
qksdsDumpResult(): end dumping resultsqksdsExecute(): qksdsExecute(): exit
Thanks,
Yasser
