Re: extended statistics and non-existent combined values

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Thu, 2 Aug 2018 10:26:43 +0000
Message-ID: <VI1P190MB0221E184B22607783804B1D4A12C0_at_VI1P190MB0221.EURP190.PROD.OUTLOOK.COM>


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

> Rounded: 1 Computed: 1.000000 Non Adjusted: 1307.000000
> ...
> ...
> -------------------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&amp;data=02%7C01%7C%7C32423b98747743d993ea08d5f85dee78%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636688004672573057&amp;sdata=ZVrLuWSb40XEWMpidctDyJ2cZiGOkGeX%2FJd%2FX2vTSjw%3D&amp;reserved=0
> Twitter: _at_OracleSK
>
>> 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) */.
†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Thu Aug 02 2018 - 12:26:43 CEST

Original text of this message