Re: In 12.1.0.2 ADS due to SPD will be always at level 11

From: Stefan Koehler <contact_at_soocs.de>
Date: Fri, 4 Dec 2015 21:48:10 +0100 (CET)
Message-ID: <939567924.1068790.1449262090775.JavaMail.open-xchange_at_app03.ox.hosteurope.de>

Hi Yasser,

> But I am not able to interpret the tracing result to prove that it does dynamic sampling at level 11 due to SPD.

Just check the following section and compare it with the table 13-1 from the official documentation (http://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#TGSQL453): …
SAMPLE BLOCK(51.5796, 8)
…

---

Iteration 1

Exec count:         3
CR gets:            805
qksdsDumpEStats(): Sampling Input
IO Size:      8

Sample Size: 51.579626
Post S. Size: 100.000000

---

Not quite sure how large your table CUSTOMERS is, but your target was 51% block samples (= 805 CR gets in sum). If you compare this with level 2 - only 64 blocks (or less in my experience as the doc is wrong here) are sampled. If you also enable an extended SQL trace, you can dig into each iteration (exec count = 3) in detail and check how many blocks were sampled by each.

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK  

> Yasser Khan <yasser8_at_gmail.com> hat am 4. Dezember 2015 um 16:40 geschrieben:
>
> 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
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 04 2015 - 21:48:10 CET