rmoff

September 13, 2011

DBMS_STATS – GATHER AUTO

Filed under: dbms_stats, oracle — rmoff @ 17:28

In Oracle 11g, the DBMS_STATS procedure GATHER_SCHEMA_STATS takes a parameter ‘options’ which defines the scope of the objects processed by the procedure call, as well as the action. It can be either GATHER or LIST (gather the stats, or list out the objects to be touched, respectively), and AUTO, STALE or EMPTY (defining the object selection to process).

  • GATHER on its own will gather stats on all objects in the schema
  • GATHER EMPTY / LIST EMPTY is self-explanatory – objects with no statistics.
  • GATHER STALE / LIST STALE is pretty obvious too – objects that have stale statistics (i.e. have had 10% change to them since statistics were last gathered). NB this 10% can be changed at an object/schema/DB level.
  • However, the documentation is ambiguous as to the precise function of GATHER AUTO / LIST AUTO.

There’s even a MOS note, “Differences between GATHER STALE and GATHER AUTO (Doc ID 228186.1)”, which strangely enough – given the precision of its title – doesn’t really explain the difference.

I’m quite fastidious about these things, particular in the documentation which I believe ought to be beyond fault. The frustrating thing for me is the sprinkling of fairy dust approach to describing the parameter:

  • “Oracle implicitly determines which objects” — ok, but how does it “implicitly determine”, what algorithm sits behind it?
  • “Oracle[…]determines how to gather” — does it intelligently determine how to gather the stats for each object’s characteristics, or does it passively fallback to the defaults? There’s a difference, since one is a good starting point and the other would be in theory an ideal.

Why does this matter? Because statistics matter so much, and so a stats gathering strategy that is accurate and efficient is important. We can only do this if we understand exactly what the product is doing. GIGO, and all that.

Reading, and re-reading, the documentation, I think the explanation is this:

  • GATHER AUTO gathers stats on objects with stats which are either STALE or EMPTY, i.e. the combined object list of
    • GATHER STALE
    • GATHER EMPTY
  • GATHER AUTO automagically defines all the other parameters relating to how the statistics are gathered – method_opt, estimate_percent, etc. User-specified values for these parameters are just ignored.

I’ve raised an SR to try and get proper clarification, and will update here if/when I find out.

UPDATE Oracle confirmed in an SR that “Gather AUTO Processes stale objects + objects without statistics (empty)”

Note Doc ID 1073120.1 which details method_opt that will be used in GATHER AUTO.

Finally, don’t forget that representative statistics != non-stale statistics. None other than Ask Tom points this out.

Just because statistics are not “stale”, doesn’t mean that are representative of your data. You should always understand your data and make sure you’re giving the CBO the most accurate information you can about the data.

June 15, 2011

Global statistics high/low values when using DBMS_STATS.COPY_TABLE_STATS

Filed under: copy_table_stats, dbms_stats, DWH, oracle, Statistics — rmoff @ 08:16

There is a well-documented problem relating to DBMS_STATS.COPY_TABLE_STATS between partitions where high/low values of the partitioning key column were just copied verbatim from the source partition. This particular problem has now been patched (see 8318020.8). For background, see Doug Burns’ blog and his excellent paper which covers the whole topic of statistics on partitioned tables.

This post Maintaining statistics on large partitioned tables on the Oracle Optimizer blog details what the dbms_stats.copy_table_stats does with regards to the high/low values:

It adjusts the minimum and maximum values of the partitioning column as follows; it uses the high bound partitioning value as the maximum value of the first partitioning column (it is possible to have concatenated partition columns) and high bound partitioning value of the previous partition as the minimum value of the first partitioning column for range partitioned table

However, two problems as I see them remain:

  1. Table global stats don’t update high_value for partitioning key
  2. high_value of one partition overlaps with low_value of the next.
    • Partition high-bound values are defined as LESS THAN, not LESS THAN OR EQUAL TO – therefore the maximum possible value of the column is less than this, not equal to it.
    • The minimum value of the partitioning column is correct using this method (although be aware of 10233186 if you use a MAXVALUE in your range partitioning).

Here’s a script that demonstrates the two issues, written and commented based on execution on 11.1.0.7: 

/* copy_stats_1.sql

Illustrate apparent problem with high_val on partition statistics when using partition to partition statistics copy
  * Table global stats do not update high_value for partitioning key
  * high_value of one partition overlaps with low_value of the next.

Requires display_raw function by Greg Rahn, see here: http://tinyurl.com/display-raw

https://rnm1978.wordpress.com/

*/

set echo off
set timing off
set feedback off
set linesize 156
set pagesize 57
col owner for a10
col table_name for a30
col column_name for a30
col partition_name for a20
col low_val for a10
col high_val for a10
col num_rows for 999,999,999,999
col "sum of num_rows" for 999,999,999,999
break on stats_update_time skip 1 duplicates

clear screen

prompt ===== This script uses the DISPLAY_RAW function =======
prompt
prompt Available here: http://structureddata.org/2007/10/16/how-to-display-high_valuelow_value-columns-from-user_tab_col_statistics/
prompt
prompt ========================================================
prompt
prompt
prompt
prompt =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
prompt 1. Set up an partitioned table with data and examine the statistics
prompt
prompt
set echo on
pause
-- Create fact table
drop table BASE_DATA;
CREATE table BASE_DATA ( day_key integer, store_key INTEGER,  item_key  INTEGER, fact_001 NUMBER(15,0), fact_002 NUMBER(15,0), fact_003 NUMBER(18,2))
 PARTITION BY RANGE (DAY_KEY)
  SUBPARTITION BY HASH (store_key)
  SUBPARTITION TEMPLATE ( SUBPARTITION "SP1" , SUBPARTITION "SP2" , SUBPARTITION "SP3" , SUBPARTITION "SP4")
 ( PARTITION "PART_20110401"  VALUES LESS THAN (20110402))
 PARALLEL;

pause
-- Create indexes
CREATE UNIQUE INDEX BASE_DATA_PK ON BASE_DATA ("DAY_KEY", "STORE_KEY", "ITEM_KEY") LOCAL parallel;
create bitmap index base_data_ix2 on base_data (store_key) local parallel;
create bitmap index base_data_ix3 on base_data (item_key) local parallel;

pause 

-- Populate fact table
exec DBMS_RANDOM.SEED('StraussCookPieterson');
insert into BASE_DATA values (20110401,101,2000, dbms_random.value(0,999) , dbms_random.value(0,999) , dbms_random.value(0,999) );
insert into BASE_DATA values (20110401,102,2000, dbms_random.value(0,999) , dbms_random.value(0,999) , dbms_random.value(0,999) );
commit;

pause 

-- Gather full stats on table
set feedback on
exec dbms_stats.gather_table_stats(     ownname=>USER, tabname=>'BASE_DATA', granularity=>'AUTO');
set feedback off

pause 

select * from base_data order by day_key;
pause
-- Examine statistics

set echo off
prompt
prompt DBA_PART_TABLES
select partitioning_type, subpartitioning_type, partition_count from dba_part_tables where table_name='BASE_DATA' and owner=USER;

prompt
prompt DBA_TAB_STATS_HISTORY
SELECT table_name, partition_name, stats_update_time
FROM   dba_tab_stats_history
WHERE  owner = USER
AND table_name = 'BASE_DATA'
ORDER  BY stats_update_time asc;
pause

prompt
prompt  DBA_TAB_STATISTICS (table level only):
prompt  **************************************
select table_name,num_rows,
to_char(LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED"
from DBA_TAB_STATISTICS
where table_name='BASE_DATA' and owner=USER
and partition_name is null
;

pause 

compute sum of num_rows on report
prompt
prompt DBA_TAB_STATISTICS (Partition level):
prompt *************************************
select table_name,partition_name,num_rows,
to_char(LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED"
from DBA_TAB_STATISTICS
where table_name='BASE_DATA' and owner=USER
and partition_name is not null
and subpartition_name is null
order by table_name,partition_name
;
clear computes

pause 

prompt DBA_PART_COL_STATISTICS:
prompt ************************
select a.partition_name,a.column_name,to_char(a.LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED",
display_raw(a.low_value,b.data_type) as low_val,display_raw(a.high_value,b.data_type) as high_val
from DBA_PART_COL_STATISTICS a
inner join dba_tab_cols b on a.table_name=b.table_name and a.column_name=b.column_name and a.owner=b.owner
where a.table_name='BASE_DATA' and a.owner=USER and a.partition_name is not null
and a.column_name = 'DAY_KEY'
;

prompt
prompt Observe: Partition high/low values for DAY_KEY - currently 1st April
pause 

prompt
prompt DBA_TAB_COL_STATISTICS:
prompt ***********************
select a.column_name,to_char(a.LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED",
display_raw(a.low_value,b.data_type) as low_val,display_raw(a.high_value,b.data_type) as high_val
from DBA_TAB_COL_STATISTICS a inner join dba_tab_cols b on a.table_name=b.table_name and a.column_name=b.column_name and a.owner=b.owner
where a.table_name='BASE_DATA' and a.owner=USER and a.column_name = 'DAY_KEY'
;
prompt
prompt Observe: Table high/low values for DAY_KEY - currently 1st April
pause 

prompt
prompt
prompt
prompt =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
prompt 2. Create new partition and use dbms_stats.copy_table_stats to set the stats for it. Leave data in the table unchanged.
prompt
prompt
pause

set feedback on
set echo on
alter table base_data add PARTITION "PART_20110402"  VALUES LESS THAN (20110403);
exec dbms_stats.copy_table_stats(ownname=>USER, tabname=>'BASE_DATA',SRCPARTNAME=>'PART_20110401',DSTPARTNAME=>'PART_20110402');
pause
set feedback off

select * from base_data order by day_key;
pause
-- Examine statistics

set echo off
prompt
prompt DBA_PART_TABLES
select partitioning_type, subpartitioning_type, partition_count from dba_part_tables where table_name='BASE_DATA' and owner=USER;

prompt
prompt DBA_TAB_STATS_HISTORY
SELECT table_name, partition_name, stats_update_time
FROM   dba_tab_stats_history
WHERE  owner = USER
AND table_name = 'BASE_DATA'
ORDER  BY stats_update_time asc;
pause

prompt  DBA_TAB_STATISTICS (table level only):
prompt  **************************************
select table_name,num_rows,
to_char(LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED"
from DBA_TAB_STATISTICS
where table_name='BASE_DATA' and owner=USER
and partition_name is null
;

compute sum of num_rows on report
prompt
prompt DBA_TAB_STATISTICS (Partition level):
prompt *************************************
select table_name,partition_name,num_rows,
to_char(LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED"
from DBA_TAB_STATISTICS
where table_name='BASE_DATA' and owner=USER
and partition_name is not null
and subpartition_name is null
order by table_name,partition_name
;
clear computes

prompt
prompt Side note: Oracle doesn't aggregate the partition num_rows statistic up to global when doing a copy stats, 
prompt            so whilst the sum of partition num_rows is four, the global num_rows is still two.
prompt            Of course, at this point, there are only actually two rows of data in the table.
prompt   
prompt (also, observe that LAST_ANALYZED for the new partition is that of the partition from where the stats were copied, and isn't
prompt  the same as STATS_UPDATE_TIME for the partition on DBA_TAB_STATS_HISTORY - which makes sense when you think about it)
pause

prompt
prompt DBA_TAB_PARTITIONS:
prompt ********************
select partition_name, high_value from dba_tab_partitions where table_name='BASE_DATA' and table_owner=USER;
prompt
prompt DBA_PART_COL_STATISTICS:
prompt ************************
select a.partition_name,a.column_name,to_char(a.LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED",
display_raw(a.low_value,b.data_type) as low_val,display_raw(a.high_value,b.data_type) as high_val
from DBA_PART_COL_STATISTICS a
inner join dba_tab_cols b on a.table_name=b.table_name and a.column_name=b.column_name and a.owner=b.owner
where a.table_name='BASE_DATA' and a.owner=USER and a.partition_name is not null
and a.column_name = 'DAY_KEY'
;
prompt
prompt See the Partition high/low values for DAY_KEY in the new partition (PART_20110402) into which we copied the stats:
prompt --> low_value is correct
prompt --> high_value is out of range for possible data in that partition
prompt -----> high_value of the partition is < 20110403, ** not **  <= 20110403
prompt
pause
prompt
prompt DBA_TAB_COL_STATISTICS:
prompt ***********************
select a.column_name,to_char(a.LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED",
display_raw(a.low_value,b.data_type) as low_val,display_raw(a.high_value,b.data_type) as high_val
from DBA_TAB_COL_STATISTICS a inner join dba_tab_cols b on a.table_name=b.table_name and a.column_name=b.column_name and a.owner=b.owner
where a.table_name='BASE_DATA' and a.owner=USER and a.column_name = 'DAY_KEY'
;
prompt
prompt See the Table high/low values for DAY_KEY - currently 1st April, even though the stats on individual partitions has a (wrong) high_val of 3rd April.
pause 
prompt
prompt
prompt
prompt =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
prompt 3. Add another new partition and use dbms_stats.copy_table_stats to set the stats for it. Leave data in the table unchanged.
prompt
prompt
pause

set feedback on
set echo on
alter table base_data add PARTITION "PART_20110403"  VALUES LESS THAN (20110404);
exec dbms_stats.copy_table_stats(ownname=>USER, tabname=>'BASE_DATA',SRCPARTNAME=>'PART_20110401',DSTPARTNAME=>'PART_20110403');
pause
set feedback off

select * from base_data order by day_key;
pause
-- Examine statistics

set echo off
prompt
prompt DBA_PART_TABLES
select partitioning_type, subpartitioning_type, partition_count from dba_part_tables where table_name='BASE_DATA' and owner=USER;

prompt
prompt DBA_TAB_STATS_HISTORY
SELECT table_name, partition_name, stats_update_time
FROM   dba_tab_stats_history
WHERE  owner = USER
AND table_name = 'BASE_DATA'
ORDER  BY stats_update_time asc;
pause

prompt  DBA_TAB_STATISTICS (table level only):
prompt  **************************************
select table_name,num_rows,
to_char(LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED"
from DBA_TAB_STATISTICS
where table_name='BASE_DATA' and owner=USER
and partition_name is null
;

compute sum of num_rows on report
prompt
prompt DBA_TAB_STATISTICS (Partition level):
prompt *************************************
select table_name,partition_name,num_rows,
to_char(LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED"
from DBA_TAB_STATISTICS
where table_name='BASE_DATA' and owner=USER
and partition_name is not null
and subpartition_name is null
order by table_name,partition_name
;
clear computes

pause

prompt
prompt DBA_TAB_PARTITIONS:
prompt ********************
select partition_name, high_value from dba_tab_partitions where table_name='BASE_DATA' and table_owner=USER;
prompt
prompt DBA_PART_COL_STATISTICS:
prompt ************************
select a.partition_name,a.column_name,to_char(a.LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED",
display_raw(a.low_value,b.data_type) as low_val,display_raw(a.high_value,b.data_type) as high_val
from DBA_PART_COL_STATISTICS a
inner join dba_tab_cols b on a.table_name=b.table_name and a.column_name=b.column_name and a.owner=b.owner
where a.table_name='BASE_DATA' and a.owner=USER and a.partition_name is not null
and a.column_name = 'DAY_KEY'
;
prompt
prompt You can see that the high_value for the new partition is again too high for the possible values the partition could contain
prompt
prompt But this time we can also see the overlapping high_value of previous column with low_value of the next.
prompt   PART_20110401 has real stats 
prompt   PART_20110402 has copied stats, with a (wrong) high_value of 20110403
prompt   PART_20110403 has copied stats, with a low_value of 20110403 - which is the same as the high_value of the previous partition

prompt
pause
prompt
prompt DBA_TAB_COL_STATISTICS:
prompt ***********************
select a.column_name,to_char(a.LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED",
display_raw(a.low_value,b.data_type) as low_val,display_raw(a.high_value,b.data_type) as high_val
from DBA_TAB_COL_STATISTICS a inner join dba_tab_cols b on a.table_name=b.table_name and a.column_name=b.column_name and a.owner=b.owner
where a.table_name='BASE_DATA' and a.owner=USER and a.column_name = 'DAY_KEY'
;
prompt
prompt Table high/low values for DAY_KEY - still 1st April, even though the stats on individual partitions has a (wrong) high_val of 4th April.
pause 

prompt
prompt
prompt
prompt =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
prompt 4. Add data to the table, gather real statistics, examine them.
prompt
prompt
pause

set echo on
-- Populate fact table
insert into BASE_DATA values (20110402,101,2000, dbms_random.value(0,999) , dbms_random.value(0,999) , dbms_random.value(0,999) );
insert into BASE_DATA values (20110403,101,2000, dbms_random.value(0,999) , dbms_random.value(0,999) , dbms_random.value(0,999) );
commit;

pause 

-- gather full stats
exec dbms_stats.gather_table_stats(     ownname=>USER, tabname=>'BASE_DATA', granularity=>'AUTO');
pause

select * from base_data order by day_key;
pause
-- Examine statistics

set echo off
prompt
prompt DBA_PART_TABLES
select partitioning_type, subpartitioning_type, partition_count from dba_part_tables where table_name='BASE_DATA' and owner=USER;

prompt
prompt DBA_TAB_STATS_HISTORY
SELECT table_name, partition_name, stats_update_time
FROM   dba_tab_stats_history
WHERE  owner = USER
AND table_name = 'BASE_DATA'
ORDER  BY stats_update_time asc;
pause

prompt  DBA_TAB_STATISTICS (table level only):
prompt  **************************************
select table_name,num_rows,
to_char(LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED"
from DBA_TAB_STATISTICS
where table_name='BASE_DATA' and owner=USER
and partition_name is null
;
pause

compute sum of num_rows on report
prompt
prompt DBA_TAB_STATISTICS (Partition level):
prompt *************************************
select table_name,partition_name,num_rows,
to_char(LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED"
from DBA_TAB_STATISTICS
where table_name='BASE_DATA' and owner=USER
and partition_name is not null
and subpartition_name is null
order by table_name,partition_name
;
clear computes
prompt
prompt Table num_rows is now accurate
pause 

prompt
prompt DBA_TAB_PARTITIONS:
prompt ********************
select partition_name, high_value from dba_tab_partitions where table_name='BASE_DATA' and table_owner=USER;
prompt
prompt DBA_PART_COL_STATISTICS:
prompt ************************
select a.partition_name,a.column_name,to_char(a.LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED",
display_raw(a.low_value,b.data_type) as low_val,display_raw(a.high_value,b.data_type) as high_val
from DBA_PART_COL_STATISTICS a
inner join dba_tab_cols b on a.table_name=b.table_name and a.column_name=b.column_name and a.owner=b.owner
where a.table_name='BASE_DATA' and a.owner=USER and a.partition_name is not null
and a.column_name = 'DAY_KEY'
;

prompt
prompt Partition high/low values for DAY_KEY in each partition is correct
pause

prompt
prompt DBA_TAB_COL_STATISTICS:
prompt ***********************
select a.column_name,to_char(a.LAST_ANALYZED,'YYYY-MM-DD-HH24:MI:SS') "LAST_ANALYZED",
display_raw(a.low_value,b.data_type) as low_val,display_raw(a.high_value,b.data_type) as high_val
from DBA_TAB_COL_STATISTICS a inner join dba_tab_cols b on a.table_name=b.table_name and a.column_name=b.column_name and a.owner=b.owner
where a.table_name='BASE_DATA' and a.owner=USER and a.column_name = 'DAY_KEY'
;
prompt
prompt Table high/low values for DAY_KEY are now correct
pause 

/* #EOF */

From everything that I’ve read, representative stats are essential for Oracle to generate the most efficient explain plan to give the most optimal performance. Out of range problems caused by inaccurate statistics is something frequently referenced. However I’m out of my depth here to determine whether that’s true for the global statistics of this partitioning column not getting updated.

Copying stats have never been intended as a replacement for real stats, that much is clear and frequently stated. They should be part of a carefully designed stats gathering method, based on your applications data and frequency of loading. Hopefully the above, along with the other articles about copy stats out there, will add to the understanding of the functionality and importantly, its limitations. Copying the stats will just buy you time in a critical load schedule, postponing the point at which you do a proper gather. All copy stats is doing is making the statistics a bit more representative of the data – it’s not a proper sample of the data so the quality of the stats will never be as good as if you do a proper gather. When you do the real gather should be whichever comes first of:

  • the point at which you have time in your batch schedule
    or,
  • the stats are too unrepresentative of your data for the Oracle optimizer to generate a sufficiently efficient explain plan in order for your queries to run in the time which the users require.

Maria Colgan from Oracle has kindly reviewed my script and findings, and commented:

Your argument that copy stats sets the high_value wrongly (to high), is correct. We do over estimate the high value by setting it to the partition definition. As you correctly point out no value in the partition will have reach that high_value because a range partition is always specified as less than. We did this so that we can ensure there will be no greater value than this in the partition, otherwise we would have to guess what the max value is.

Maria also pointed out that with regards to the overlapping high/low values

However, this is not the expected behavior. The goal of copy_stats is to provide a temporary fix to the out of range problem by providing a representative set of statistics for a new partition. It is not supposed to be a replacement for statistics gathering.

Reading:

Watch out for these other bugs that I came across reference to:

  • 10234419 Extend dbms_stats.copy_table_stats to all range partitioning key columns
  • Doc ID 1292269.1ORA-01422 While running dbms_stats.copy_table_stats
    • ” This issue would occur when there are more than one schema with same table name.”

Many thanks to Maria Colgan and Doug Burns for reviewing this post and providing useful feedback.

May 26, 2011

Data Warehousing and Statistics in Oracle 11g – Automatic Optimizer Statistics Collection

Filed under: dbms_stats, DWH, oracle, Statistics — rmoff @ 13:35

Chucking a stick in the spokes of your carefully-tested ETL/BI …

My opinion is that automated stats gathering for non-system objects should be disabled on Oracle Data Warehouses across all environments.

All it does it cover up poor design or implementation which has omitted to consider statistics management. Once you get into the realms of millions or billions of rows of data, the automated housekeeping may well not have time to stat all of your tables on each run. And then it becomes a quasi-lottery when your tables will get processed. Or what if you’re working with intra-day loads (eg. near real-time) – the housekeeping job only runs once a day by default.

Even if you have a suitable window and are happy that the job gathers all that it needs to all of the time, what if you want to run your batch at the same time as the task window defined? If you want to run your batch highly parallel (and why wouldn’t you?) then will the stats gather suffer? or affect your batch by running stats highly parallel too?

Suppose you are relying on the auto stats job, and don’t want to run it at the same time as your batch, so you come up with a suitable schedule for them to run at different times. What happens when your DW grows and you need to add a new batch process, and so have to move the window again? How do you know that moving it won’t affect the previous batch’s stats?

If you’re building on an existing system and want to test the performance of your new batch, how are you going to simulate the behaviour of your auto stats job? Even if you trigger it manually, are you going to accurately simulate the statistics that it may or may not need to gather each night? How do you factor in the magical 10% staleness to trigger a stats gather? That is one serious test rig if you want to reproduce all of that.

If you have stats management in place, then turning the auto stats off (for non-system objects) won’t hurt. And if you’re not, then the auto stats job will cover this up in your environments all the way from Dev through to Prod. The first time someone will ask about stats management is when you’re scratching your head over a report or ETL stage “that used to work fine”. And then the horrible truth will dawn that you screwed up, and should have built it into your design from the beginning.

As we say around here, if you want a job done properly, do it tha’ sen. Or rather, as Greg Rahn more articulately says:

I tend to advise people that for a DW the stats gathering should be part of the data flow (ETL/ELT) process and to disable the default job
[…]
If you wish to collect your statistics manually, then you should change the value of AUTOSTATS_TARGET to ORACLE instead of AUTO (DBMS_STATS.SET_PARAM(‘AUTOSTATS_TARGET’,’ORACLE’)). This will keep the dictionary stats up to date and allow you to manually gather stats on your schemas

Julian Dyke says something supporting this view too in his presentation here:

In complex databases do not rely on Auto job
– Unpredictable collection behaviour / duration
– Unpredictable execution plan changes

If you can’t disable the autostats job for whatever reason (maybe another application on the same DB would require changes to accommodate it), then you can shield yourself from its nefarious influences by using LOCK_SCHEMA_STATS to lock the stats on your schema(s). When you manually maintain the stats yourself, you either unlock them first, or use the FORCE option of the stats procedures.

Stabilisers on a high-performance motorbike

It’s easy enough to understand why Oracle built the Automated Stats job, and why it’s enabled by default. In an effort to move towards the Self Managing Database, it makes sense to automate whatever you can, so that the scope for poor performance is reduced. Abstracting things slightly, the optimizer is just part of the DB code, and stats reason for being is to support the optimizer, so why not keep it under the covers where possible?
The trouble with this is that it might be fine for the middle of the road. The bog standard, quick-win, fire it and run project doing nicely standard OLTP work. One fewer thing for the developer to worry about. It’s probably quite good for lots of things. But Oracle RDBMS is a big beast, and an expensive bit of kit. Do you really want to meander along in the slow lane all the time, content to be using a one-size-fits-all approach?
Kawasaki motorbike with stabilisers

If you’re serious about exploiting the power of Oracle for your data warehouse, then you need to understand what needs to be done to get it to perform. One of the big factors is accurate, representative statistics. And to get these you have to take the stabilisers off and learn how to do it properly yourself, because you’re the one that understands your data. Data loads are going to be different, data distribution is going to be different, reporting is going to be different. There’s a finite set of patterns that you’ll find in standard DW methodology, but it’s up to you to read about them (Greg Rahn, Doug Burns, et al) and understand how they apply to your system, and not rely on Oracle’s approximation of a stats method for an average system.

Why do I need to manage the stats myself? Doesn’t Oracle do it automagically when they’re stale?

Doesn’t Oracle gather stats automagically when they’re stale?
Yes, it does, BUT:

  • Only if the window allocated to it allows for time
  • not stale ≠ representative stats .
    Or to rearrange the equation: your stats can be unrepresentative of your data, and the stats not be ‘stale’.

So even whilst they’re not “stale”, that’s not to say the global statistics for your table are still representative. After one day, the statistics are already becoming unrepresentative of the data (think max value of date, transaction number, etc), but are still not “stale”.
Oracle will, by default, consider a table “stale” once 10% has changed. But most DWs are going to be loading many millions of rows a day, so the 10% (default) change for a table to be considered stale is going to be quite high. A table loading 20 million rows per day will hit c.1 billion rows in total after less than two months. But of a billion rows, a hundred million (10%) need to change before the table’s statistics are “stale”. 20 into 100 goes 5 … so your statistics would only become “stale” roughly every five days.

There’s a good presentation from OpenWorld 2008 by Oracle’s Real World Performance Group, entitled Real-World Database Performance Techniques and Methods. In it they discuss statistics management in detail, including the following “Six Challenges to the Cost Based Optimizer”:

1. Data skew
2. Bind peeking
3. Column low/high values
4. Data correlation between columns
5. Cardinality Approximations
6. The debugging process

At least two of these (data skew, and column low/high values – out-of-range) can occur (which is bad, mm’kay?) with statistics which are STALE=FALSE.

The point is, if you’re serious about getting the best explain plan from the CBO, you can’t rely on STALE as a sole indicator of how representative your statistics are of your data.

Let’s remember why we even care about good statistics. Some people seem to think that it’s optional. That it’s the geek equivalent of spending every weekend lovingly polishing the exterior of one’s favourite car – nice to have and ideally should be done, but ultimately just for show and won’t make it go any faster.
The DB is there to support the users of whatever application it is. And users being users, want their answers now. This gives us our starting point, and a logical flow of conclusions drawn from this:

  • Our requirement is for performance, and repeatable, consistent performance.
    • To get this we want Oracle to execute the query as efficiently as possible.
    • To do this, Oracle needs to understand the data that it’s being asked to query.
    • If it doesn’t understand the data, how can we expect to access it in the most efficient way?
    • This understanding is imparted to Oracle through statistics.
    • So statistics need to be representative of the data.

As soon as you are changing data (eg a DW batch load), you need to consider whether the stats are still going to give the CBO the best chance of getting the right plan. If they aren’t as representative of your data as they could be then you can’t expect the CBO to come up with the best plan.
If your data doesn’t change much and once a week works for you then great. But the point is you need to understand your data, so that you can plan your statistics strategy around it so that Oracle can understand it.

Reading & References

Thanks to Greg Rahn for reviewing my post and suggesting some changes

Blog at WordPress.com.