Re: extended statistics and non-existent combined values

Useful details thanks!

Sent from my iPhone

> On 2 Aug 2018, at 10:54, Stefan Koehler <contact_at_soocs.de> 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

>>> Single Tab Card adjusted from 1307.000000 to 1.000000 due to adaptive dynamic sampling

>> Dominic Brooks <dombrooks_at_hotmail.com> 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_at_gmail.com> wrote: 
>>> 
>>> A slightly lighter sledgehammer would be using /*+ DYNAMIC_SAMPLING(table,11) */.