Re: OPTIMIZER_DYNAMIC_SAMPLING Level 11
- From: Stefan Koehler <contact@xxxxxxxx>
- To: mtnpreiss@xxxxxx
- Date: Fri, 12 Dec 2014 13:59:18 +0100 (CET)
Hi Martin,
just found a few minutes today to test the previously mentioned SQL_DS
component on Oracle 12.1.0.2.
alter session set optimizer_dynamic_sampling=11;
alter session set events 'trace[RDBMS.SQL_DS] disk=high';
select * from DYNTEST where COUNTRY = 'DE' and WERKS = '1200';
…
qksdsExecute(): qksdsExecute(): enter
qksdsInitSample(): do compute: nbSamp=2.5 totNbSamp=2.5 minIOs=100
maxIOs=18446744073709551615 nbBlocks=20 blockSize=8192 ioSize=8 tbName=DYNTEST
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 result_cache(snapshot=3600) */
SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "DYNTEST") */ 1
AS C1 FROM "DYNTEST" "DYNTEST" WHERE ("DYNTEST"."WERKS"='1200') AND
("DYNTEST"."COUNTRY"='DE')) innerQuery
qksdsExeStmt():
qksdsExeStmt(): timeInt = 2 timeLimit = 2 elapTime = 0
**************************************************************
Iteration 1
Exec count: 1
CR gets: 23
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: 2145 (us)
CPU Time: 2144 (us)
User I/O Time: 0 (us)
qksdsDumpEStats(): Sampling Input
IO Size: 8
Sample Size: 100.000000
Post S. Size: 100.000000
qksdsExeStmt(): qksdsExeStmt: exit
…
So at least you can get some more information about the dynamic sampling
behavior for level 11, if you enable a trace on the SQL Dynamic Sampling
Services (qksds) component. Combine that content with the CBO trace information
and you can get at least nearly same dynamic sampling information as
with a lower levels (dumped in the CBO trace as well with lower levels , e.g.
http://tinyurl.com/mgh9eox).
I am looking forward to the paper of Chris.
Best Regards
Stefan Koehler
Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
> Martin Preiss <mtnpreiss@xxxxxx> hat am 12. Dezember 2014 um 11:09
> geschrieben:
>
>
> Hi Chris,
>
> thank you for the additions. I saw some result cache answers for the
> DS_SVC queries - but did not expect the mentioned "statistics
> repository" to be just a cache layer.
>
> Good to hear that you are writing a paper covering the topic: I am sure
> it will answer lots of questions.
>
> Regards
>
> Martin
--
//www.freelists.org/webpage/oracle-l
Other related posts:
