Playing with statistics modification hints

How often did you have the situation when performance problem disappeared just after refreshing object statistics? Of course it is nice to solve the problem quickly and simply, but is always better to understand what has exactly happened.

Recently I had such a problem and wanted to understand why after collecting the statistics on one of the queried tables, the plan automagically changed to much better one.

Generally speaking, my action plan was as follows:

  1. Gather timing and runtime statistics about the current plan.
  2. Elapsed: 00:09:19.48
    
    Statistics
    ----------------------------------------------------------
       11580231  recursive calls
              0  db block gets
          98103  consistent gets
          95032  physical reads
              0  redo size
            628  bytes sent via SQL*Net to client
            543  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
            119  sorts (memory)
              0  sorts (disk)
              0  rows processed
    
  3. Export existing statistics of the table you want to gather statistics for (the one having the most “suspicious” access method).
  4. exec dbms_stats.create_stat_table(ownname  => 'MYUSER', stattab => 'MYSTATSTAB');
    
    exec dbms_stats.export_table_stats(statown => 'MYUSER', stattab => 'MYSTATSTAB', ownname => 'APPUSER', tabname => 'APPTABLE', statid => 'OLD_STATS');
    
  5. Refresh table statistics.
  6. exec dbms_stats.gather_table_stats('APPUSER', 'APPTABLE', no_invalidate => FALSE);
    
  7. Check if plan is better.
  8. Elapsed: 00:00:00.72
    
    Statistics
    ----------------------------------------------------------
           2022  recursive calls
              0  db block gets
           3322  consistent gets
             98  physical reads
              0  redo size
            628  bytes sent via SQL*Net to client
            543  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
            119  sorts (memory)
              0  sorts (disk)
              0  rows processed
    

Much better…

So now, would be good to check what exactly led to much better plan.

To easily compare the previous and new statistics, I’ve decided to export new ones too:

exec dbms_stats.export_table_stats(statown => 'MYUSER', stattab => 'MYSTATSTAB', ownname => 'APPUSER', tabname => 'APPTABLE', statid => 'NEW_STATS');

Now we have several options. We can compare what has changed by querying our stat table. It should be easy to spot the change if there are obvious differences, but it is not always like that (which was in my case, see below).

If nothing can be observed just by looking at the numbers, we can try setting the statistics manually, using for example:

DBMS_STATS.SET_INDEX_STATS
DBMS_STATS.SET_TABLE_STATS
DBMS_STATS.SET_COLUMN_STATS

But faster way to do it is to use statistics modification hints. After putting all of them into our query, we should get better plan even if old statistics were imported again. Then, by eliminating them one by one, we should be able to find which numbers make the difference. Of course it may happen that not only one specific statistic leads to better plan, but in my case, it was just like that.

To quickly get the list of hints with table, index and column statistics (but no histograms), one can use below queries:

-- stat views version

select 'table_stats(' || owner || '.' || table_name || ', scale, rows=' || num_rows || ' blocks=' || blocks || ')' hints
from dba_tab_statistics
where table_name = 'APPTABLE'
and owner = 'APPUSER'
union all
select 'index_stats(' || owner || '.' || table_name || ', ' || index_name || ', scale, index_rows=' || num_rows || ' blocks=' || leaf_blocks || ' keys=' || distinct_keys || ' clustering_factor=' || clustering_factor || ')' hints
from dba_ind_statistics
where table_name = 'APPTABLE'
and owner = 'APPUSER'
union all
select 'column_stats(' || owner || '.' || table_name || ', ' || column_name || ', scale, length=' || avg_col_len || ' distinct=' || num_distinct || ' nulls=' || num_nulls || ' min=' || low_value || ' max=' || high_value || ')' hints
from dba_tab_col_statistics
where table_name = 'APPTABLE'
and owner = 'APPUSER';

-- stattable version

select 'table_stats(' || c5 || '.' || c1 || ', scale, rows=' || n1 || ' blocks=' || n2 || ')' hints
from myuser.mystatstab
where statid = 'NEW_STATS'
and type = 'T'
union all
select 'index_stats(' || c5 || '.' || c4 || ', ' || c1 || ', scale, index_rows=' || n1 || ' blocks=' || n2 || ' keys=' || n3 || ' clustering_factor=' || n6 || ')' hints
from myuser.mystatstab
where statid = 'NEW_STATS'
and type = 'I'
union all
select 'column_stats(' || c5 || '.' || c1 || ', ' || c4 || ', scale, length=' || n8 || ' distinct=' || n3 || ' nulls=' || n5 || ' min=' || r1 || ' max=' || r2 || ')' hints
from (
    select distinct c1, c4, c5, n3, n5, n8, r1, r2 
    from myuser.mystatstab
    where statid = 'NEW_STATS'
    and type = 'C');

This should give you the list of following types of hints:

   
table_stats(APPUSER.APPTABLE, scale, rows=X blocks=X)
index_stats(APPUSER.APPTABLE, APPTABLEIDX, scale, index_rows=X blocks=X keys=X clustering_factor=X)
column_stats(APPUSER.APPTABLE, APPTABLECOL, scale, length=X distinct=X nulls=X min=X max=X)    

Having this list, you have to import original (bad) statistics, double check that the plan is bad again and play a little bit with the list of generated hints. In my case, it appeared that only one INDEX_STATS hint was needed.

Checking the difference further, I’ve got:

select statid, 'index_stats(' || c5 || '.' || c4 || ', ' || c1 || ', scale, index_rows=' || n1 || ' blocks=' || n2 || ' keys=' || n3 || ' clustering_factor=' || n6 || ')' hints
from myuser.mystatstab
where c5 = 'APPUSER'
and c4 = 'APPTABLE'
and c1 = 'APPTABLEIDX'
and type = 'I';

STATID     HINTS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OLD_STATS  index_stats(APPUSER.APPTABLE, APPTABLEIDX, scale, index_rows=13641826 blocks=37901 keys=2557899 clustering_factor=10762981)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
NEW_STATS  index_stats(APPUSER.APPTABLE, APPTABLEIDX, scale, index_rows=13684014 blocks=38032 keys=2545298 clustering_factor=10871401)     

Further test has shown that it was only INDEX_ROWS value which made a difference… Imagine that going from 13 641 826 to 13 684 014 (only about 0.3% difference in rows!) just for one index (with several other created on that table and other tables used by the query) made such a big difference!

Advertisements

2 thoughts on “Playing with statistics modification hints”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s