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…

Advertisements

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