Useful details thanks!
Sent from my iPhone
On 2 Aug 2018, at 10:54, Stefan Koehler <contact@xxxxxxxx> wrote:
Hey Dominic,
no, it works but (new) ADS code has some other possible side effect -
especially with 12.2 and SPD / directive cache.
-------------------8<-------------------------
qksdsExeStmt(): qksdsExeStmt(): enter
qksdsExeStmt(): do compute: sampSize = 100
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( "T20") */ 1 AS C1 FROM "T20
" "T20" WHERE ("T20"."C2"='N') AND ("T20"."C3"='N')) innerQuery
qksdsExeStmt():
qksdsExeStmt(): newSoftTimeLimit is 1
qksdsExeStmt(): timeInt = 1 timeLimit = 0 elapTime = 0
=====================
PARSING IN CURSOR #139742383085112 len=280 dep=1 uid=106 oct=3 lid=106
tim=356438955 hv=2939816853 ad='85f43500' sqlid='117mkrurmn2wp'
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( "T20") */ 1 AS C1 FROM "T20
" "T20" WHERE ("T20"."C2"='N') AND ("T20"."C3"='N')) innerQuery
END OF STMT
PARSE
#139742383085112:c=1000,e=1227,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=1856612004,tim=356438954
EXEC
#139742383085112:c=0,e=832,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1856612004,tim=356439871
FETCH
#139742383085112:c=0,e=1801,p=5,cr=2,cu=0,mis=0,r=1,dep=1,og=1,plh=1856612004,tim=356441704
CLOSE #139742383085112:c=0,e=9,dep=1,type=0,tim=356441772
**************************************************************
Iteration 1
Exec count: 1
CR gets: 2
CU gets: 0
Disk Reads: 5
Disk Writes: 0
IO Read Requests: 2
IO Write Requests: 0
Bytes Read: 40960
Bytes Written: 0
Bytes Exchanged with Storage: 40960
Bytes Exchanged with Disk: 40960
Bytes Simulated Read: 0
Bytes Simulated Returned: 0
Elapsed Time: 4155 (us)
CPU Time: 1000 (us)
User I/O Time: 1456 (us)
qksdsDumpEStats(): Sampling Input
IO Size: 8
Sample Size: 100.000000
Post S. Size: 100.000000
qksdsExeStmt(): qksdsExeStmt: exit
qksdsExecute(): Dumping unscaled result
qksdsDumpResult(): DS Results: #exps=1, smp obj=T20
qksdsDumpResult(): T.CARD = qksdsDumpResult(): (mid=0.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=T20
qksdsDumpResult(): T.CARD = qksdsDumpResult(): (mid=0.0, low=0.0,
hig=0.0)qksdsDumpResult():
qksdsDumpResult(): end dumping results
...
...
qksdsDumpStats():
**************************************************************
DS Service Statistics
qksdsDumpStats(): Executions: 1
Retries: 0
Timeouts: 0
ParseFails: 0
ExecFails: 0
qksdsDumpStats():
qksdsExecute(): qksdsExecute(): exit
Rounded: 1 Computed: 1.000000 Non Adjusted: 1307.000000Single Tab Card adjusted from 1307.000000 to 1.000000 due to adaptive
dynamic sampling
...
...
-------------------8<-------------------------
Of course ADS code is run for the index as well but works the same way there
- so omitted this snippet.
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website:
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.soocs.de&data=02%7C01%7C%7C32423b98747743d993ea08d5f85dee78%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636688004672573057&sdata=ZVrLuWSb40XEWMpidctDyJ2cZiGOkGeX%2FJd%2FX2vTSjw%3D&reserved=0
Twitter: @OracleSK
Dominic Brooks <dombrooks@xxxxxxxxxxx> hat am 2. August 2018 um 10:41
geschrieben:
Wouldn’t the optimiser then discard dynamic sampling results because there
was no matching data in the sample?
On 1 Aug 2018, at 21:14, Mladen Gogala < gogala.mladen@xxxxxxxxx> wrote:
A slightly lighter sledgehammer would be using /*+
DYNAMIC_SAMPLING(table,11) */.
