Re: extended statistics and non-existent combined values
Date: Thu, 2 Aug 2018 11:54:23 +0200 (CEST)
Message-ID: <870919613.112986.1533203663803_at_ox.hosteurope.de>
Hey Dominic,
qksdsExeStmt(): newSoftTimeLimit is 1
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(): 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
...
Of course ADS code is run for the index as well but works the same way there - so omitted this snippet.
Best Regards
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
...
-------------------8<-------------------------
Stefan Koehler
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) */.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 02 2018 - 11:54:23 CEST