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.


What is the Deal with AVG_LEAF_BLOCKS_PER_KEY (DBA_INDEXES) !?!

December 17, 2013

AVG_LEAF_BLOCKS_PER_KEY column in DBA_INDEXES should show the average number of leaf blocks in which each distinct value in the index appears. Common sense and Christian’s Antognini’s book Troubleshooting Oracle Performance (pg.136) suggest that the value of AVG_LEAF_BLOCKS_PER_KEY should by derived from the values of LEAF_BLOCKS and DISTINCT_KEYS .

Well, that does not appear to be the case in Oracle 11g!

This query can help you find the indexes that do not conform to the rule:

select 
       * 
from 
       dba_indexes
where 
       DISTINCT_KEYS > 0
and 
       AVG_LEAF_BLOCKS_PER_KEY  > 3*LEAF_BLOCKS/DISTINCT_KEYS
and 
       LEAF_BLOCKS > DISTINCT_KEYS

The discrepancy is certainly not a rounding error. I have an index where the value of the AVG_LEAF_BLOCKS_PER_KEY is 150 times larger than LEAF_BLOCKS/DISTINCT_KEYS.

The problem seems to be happens only when stats are gathered (DBMS_STATS) with AUTO sampling. Computing stats ( estimate_percent = NULL) resolves the issue.

Ironically, DBMS_STATS AUTO sampling delivers good estimates for LEAF_BLOCKS and DISTINCT_KEYS. If only Oracle computed AVG_LEAF_BLOCKS_PER_KEY by using LEAF_BLOCKS and DISTINCT_KEYS, everything would have been great…


Dynamic sampling in Oracle 12c – it goes up to 11. Really!

December 13, 2013

For the first time in many years, Oracle introduced a new option for dynamic sampling, and a major one for that – http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#PFGRF95292  .

The new dynamic sampling level 11 is the all-encompassing “automatic” switch, which leaves all decisions to Oracle.
The big question now is when would the new auto dynamic sampling kick-in? Maria Colgan (http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf)  indicates that the auto option would fire more frequently that the default.
To begin filling in the blanks, I started with a really simple test – retrieval by primary key.

Here are the create scripts:

create table tab3 as
with v1 as 
     (select rownum n from dual connect by level <= 10000)
select
     rownum id , 
     dbms_random.string('u',dbms_random.value*29 + 1) str
from
     v1, v1
where
     rownum < 1000000;

alter table tab3 add primary key (id)

execute dbms_stats.gather_table_stats('JORDAN','TAB3')

Now let see when happens when set the optimizer_dynamic_sampling =11 and issue the search by primary key:

SQL> alter session set optimizer_dynamic_sampling =11 ;

Session altered.

SQL> set autotrace on
SQL> select * from tab3 where id = 123 ;

…output truncated…

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
468 bytes sent via SQL*Net to client
532 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

We see that that Oracle decided to do dynamic sampling. There is no guesswork in those types of queries. The cardinality of the statement is one, so dynamic sampling was not necessary.
But that’s not the worst part. The statement required 14 consistent gets and 15 recursive calls for a simple scan. Without dynamic sampling, the same statement requires 4 consistent gets and 1 recursive call.

SQL> select * from tab3 where id = 345 ;

…output truncated…

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
468 bytes sent via SQL*Net to client
532 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

The idea behind automatic dynamic sampling is excellent. Starting dynamic sampling only when the Oracle CBO cannot get reliable cardinalities would be a giant step towards query self-tuning.

The implementation of the feature in Oracle version 12.1.0.1, however, needs some more work. Hopefully, the logic would get more precise in the newer versions….


The Cost of Adaptive Execution Plans in Oracle (a Small Study) Part 3

November 26, 2013

My previous two posts, The Cost of Adaptive Execution Plans in Oracle (a Small Study) Part 1 and The Cost of Adaptive Execution Plans in Oracle (a Small Study) Part 2,  focused on estimating the impact of OPTIMIZER_ADAPTIVE_REPORTING_ONLY parameter in Oracle 12c. The parameter controls if the adaptive plan would be acted upon. The adaptive plan, however, is always generated.

The post will try to estimate the impact of adaptive execution plans, while taking into account the resources needed for generating the adaptive execution plans. Since I was not able to find a way to suppress the generation of adaptive plans in Oracle 12c, I decided to just use an older version of the optimizer. I did that by utilizing “optimizer_features_enable” parameter.

Here is the table/view setup script, and here is the test script.

We can see the difference in elapsed time using this query:

select avg(r), avg(a) , STATS_T_TEST_PAIRED (R,A) FROM track_v

avg(r) = 1.5880118 avg(a) = 2.5002121 STATS_T_TEST_PAIRED (R,A) /p/ = 0

The adaptive (12c)  plan takes almost 1 millisecond more than the non-adaptive, “old-style” 11gR2 plan.

This query can show where the difference comes from:

select c.name , avg (a.val) a , avg (r.val) r , STATS_T_TEST_PAIRED (r.val,A.val)
from stats_a a , stats_r r , v$statname c
where a.id = r.id
and a.stat = r.stat
and a.stat = c.statistic#
group by c.name

The only statistics that are difference are:
CCursor + sql area evicted – the SQL with AEP(12c) on consumed more that the SQL run with 11gR2
CPU used by this session – the SQL with AEP(12c) on consumed more that the SQL run with 11gR2
parse time cpu – the SQL with AEP(12c) on consumed more that the SQL run with 11gR2
parse time elapsed – the SQL with AEP(12c) on consumed more that the SQL run with 11gR2
recursive cpu usage – the SQL with AEP(12c) on consumed more that the SQL run with 11gR2
sql area evicted – the SQL with AEP(12c) on consumed more that the SQL run with 11gR2

In conclusion, “adaptive execution plans” is a wonderful feature. The resource it needs are typically negligible, except for very light (fast) OLTP type queries.


The Cost of Adaptive Execution Plans in Oracle (a Small Study) Part 2

November 21, 2013

In my previous post, I tried to find out the performance impact of adaptive execution plans (AEP). During the test, I used OPTIMIZER_ADAPTIVE_REPORTING_ONLY parameter to switch on and off adaptive execution plans(AEP). It should be noted that the parameter does not control the generation of AEP, but rather the executtion of the “adaptive” part. I came to a rather surprising result that turning off adaptive execution plans actually made the query run slower. This behavior seems to be a side -effect of frequent shared pool flushing. The original test, i.e. the one that showed tuning on adaptive execution plans speeds up a query, is reproducible, however! I ran it at least three times and I got similar results.

To confirm the peculiar results, I ran a new test that avoids SQL reuse by generating unique SQL statements. Here is the new table/view setup script. Here is the new test procedure. After running it, we can see the expected outcome – adaptive execution plans do cost something.

select avg(r), avg(a) , STATS_T_TEST_PAIRED (R,A) FROM track_v

avg(r) = 2.05312594     avg(a) = 2.07863544     STATS_T_TEST_PAIRED (R,A) /p/ = 0

The difference is quite small – approximately 2% of the overall time or a fraction of a millisecond.
The following query allows us drill down and see where the difference comes from :
select c.name , avg (a.val) a , avg (r.val) r , STATS_T_TEST_PAIRED (r.val,A.val)
from stats_a a , stats_r r , v$statname c
where a.id = r.id
and a.stat = r.stat
and a.stat = c.statistic#
group by c.name

The only statistics that are statistically different are:
CCursor + sql area evicted            – the SQL with AEP on consumed less that the SQL with AEP off
CPU used by this session               – the SQL with AEP on consumed more (marginally) that the SQL with AEP off
parse time elapsed                          – the SQL with AEP on consumed more that the SQL with AEP off
sql area evicted                                – the SQL with AEP on consumed less that the SQL with AEP off


The Cost of Adaptive Execution Plans in Oracle (a Small Study) Part 1

September 26, 2013

Adaptive execution plans is a new Oracle 12c feature. I consider it one of the most important ones.

Oracle 12c would pick the join method based on the actual number of rows retrieved during the first execution of the query.

There are a few great papers about this topic:
Here is the Oracle’s white paper and here is Kerry Osborbe’s Hostsos 2013 presentation. There is even a video.

Every time a new feature that fundamentally changes the way Oracle works is introduced, we should ask ourselves what is the cost, in terms of CPU or other resources, used by that feature.
For me, the cost is an issue only when the feature does not change the join type that was initially chosen.
If the feature causes a change of join type, then the benefits of it would be huge, so it is not worth worrying about the cost.

I’ll measure the elapsed times  / looking at internals is hard… :) / of a query with and without adaptive execution plans and then run a paired t-test to see if there is a statistically significant difference. I’ll use OPTIMIZER_ADAPTIVE_REPORTING_ONLY to turn on and off the feature and I’ll do my best to get rid of any bias in this test.

First, let’s see what happened when we have a query that uses Hash Join. The table/view set up scripts is here. The test procedure is here. I get the end-result using this simple query:
select avg(r),             avg(a) ,              STATS_T_TEST_PAIRED (R,A)   FROM track_v

For this case, we get avg(r) = 450.6991608  ,   avg(a) =      451.5071414  ,   STATS_T_TEST_PAIRED (R,A) /p/ =   0.48716074654373898

This means that for 10K runs, we are nowhere near statistical significance (p< 0.05). Things were not different for 100K runs:

avg(r) = 336.78178535 , avg(a) = 336.67725615 , STATS_T_TEST_PAIRED (R,A) /p/ = 0.82934281196842896

Therefore, the adaptive execution plan feature does not add any meaningful cost when Hash Joins are used.

Let’s see the behavior for a query the uses nested loops. The table/view set up scripts are here. The test procedure is here. I ran 100K iterations, and this is what I got:
select avg(r), avg(a) , STATS_T_TEST_PAIRED (R,A) FROM track_v

For this case, we get avg(r) = 15.31589686 ,   avg(a) =      15.11440871 ,   STATS_T_TEST_PAIRED (R,A) /p/ =   0.015795071536522352

Very interesting result – using adaptive execution plans is slightly faster even when no runtime change of join type is happening. The result seems to have statistical significance…

I then run 500K iterations and I got the even stronger result:
avg(r) = 20.4530 , avg(a) = 19.982423, STATS_T_TEST_PAIRED (R,A) /p/ = 0.00000320884674226468

The averages went up probably because there was some load on the box, but the statistical significance of the difference is out of the question.

So, it appears that the cost of adaptive execution plans is actually negative when it comes to nested loops (NL).

Updated 11/21/2013:
This result seems to be a side-effect of frequent shared pool flushing. It is still a reproducible result though. Check my new post that uses unique SQLs rather than flushing the shared pool to guarantee that no SQL plans are reused.

Overall, adaptive execution plans could bring huge benefits without any cost. I cannot think of a scenario where we should turn them off…


New York Oracle User Group (NYOUG) Fall 2013 Conference

September 12, 2013

Thanks to all who attended my session at NYOUG. As usual, it was a great experience!

Download the presentation here and the white paper here.

You can also download the spec here and the body here of JUST_STATS package.
You can download the README file here andf some examples of gathering stats in trigger here.

 

August 2014:

P.S.

There is a patch for JUST_STATS package – http://wp.me/p1DHW2-9L . It allows it to work with partitions.


Follow

Get every new post delivered to your Inbox.