More on Dynamic Sampling Level 11 (AUTO) in Oracle 12c

January 29, 2014

In a previous post I showed an example of how the new AUTO dynamic sampling level (11) consumed significant resources for a very simple SQL statement.

Here, I’ll try to find your why.
First surprise!
10053 trace does not capture information about dynamic sampling (DS) level 11. It works quite fine for other levels (0-10) of dynamic sampling though.
Here is how a dynamic sampling section of 10053 file for levels 0 to 10 looks like:

————————————————————————————

SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TAB3[TAB3]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE

*** 2014-01-29 11:41:02.678
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 3).

*** 2014-01-29 11:41:02.678
** Generated dynamic sampling query:
query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), COUNT(DISTINCT C3) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("TAB3") FULL("TAB3") NO_PARALLEL_INDEX("TAB3") */ 1 AS C1, CASE WHEN DECODE("TAB3"."ID",0,1)=567 THEN 1 ELSE 0 END AS C2, DECODE("TAB3"."ID",0,1) AS C3 FROM "JORDAN"."TAB3" SAMPLE BLOCK (0.829542 , 1) SEED (1) "TAB3") SAMPLESUB

*** 2014-01-29 11:41:03.597
** Executed dynamic sampling query:
level : 3
sample pct. : 0.829542
actual sample size : 9783
filtered sample card. : 0
orig. card. : 999999
block cnt. table stat. : 3737
block cnt. for sampling: 3737
max. sample block cnt. : 32
sample block cnt. : 31
unique cnt. C3 : 0
min. sel. est. : 0.01000000
** Using single table dynamic sel. est. : 0.00007085
Table: TAB3 Alias: TAB3
Card: Original: 999999.000000 Rounded: 71 
                             Computed: 70.85 Non Adjusted: 70.85

————————————————————————————

The section includes the query issues by the dynamic sampling along with lots of valuable information.

The same section for a query with dynamic sampling level 11 looks strikingly different:
————————————————————————————

SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TAB3[TAB3]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
Table: TAB3 Alias: TAB3
Card: Original: 999999.000000 >> Single Tab Card adjusted from:9999.990000 to:1.000000
Rounded: 1 Computed: 1.00 Non Adjusted: 9999.99

————————————————————————————
Even though we can see that the cardinality was adjusted from 9999.99 to 1 as a result of the dynamic sampling, there are no details about the DS queries that did the actual sampling. I was not able to see DS summary information either.

Since 10053 trace file did not give me the information I needed, I decided to look elsewhere – in the V$ tables.
After I flushed the shared pool and ran the query from the previous post, I issued the following SQL to get the DS SQL related to the statement:

select * from v$sql where sql_text like '%TAB3%'

Second surprise! The query returned a few records related to DS.
——————————————————————–

SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sampling(0) 
...
FROM "TAB3" SAMPLE BLOCK(21.4075, 8) SEED(1) "TAB3" WHERE ...


SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sampling(0) 
...
FROM "TAB3" SAMPLE BLOCK(42.8151, 8) SEED(2) "TAB3" WHERE ...

SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sampling(0) 
...
FROM "TAB3" SAMPLE BLOCK(85.6302, 8) SEED(3) "TAB3" WHERE ....

———————————————————————

They are similar with the exception of SAMPLE BLOCK and SEED arguments. It appears that the optimizer tried to get sampling data using small sample block argument, but it failed. Them it tried again with larger sample size, but failed again. Finally, in the third attempt, with 85% sampling, it succeeded.

Generally speaking, the new policy to retry sampling until the desired result is achieved is good. The algorithm needs to be tweaked a bit to account for situations like the one I described in a previous post though.

Advertisements