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.
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.