Optimizer Stats: Treat some tables different than AutoTask does

This posting was inspired by an OTN thread. Since 10g, we have an Automatic Maintenance Task (AutoTask) that gathers Optimizer Statistics during the night. This task uses DBMS_STATS.GATHER_DATABASE_STATS with default values. For whatever reason, we may consider these defaults not appropriate for some tables. We have 2 options:

  1. Write our own procedure/job to gather statistics different (10g way)
  2. Change the Statistic Preferences for these tables (11g way)

We look at the 11g New Feature first, because it is the more efficient way to go:

SQL> create table adam.a as
 select
 rownum as id,
 rownum + 1 as flag,
 'Oracle Enterprise Edition' as product,
 mod(rownum,5) as channel_id,
 mod(rownum,1000) as cust_id ,
 5000 as amount_sold,
 trunc(sysdate - 10000 + mod(rownum,10000)) as order_date,
 trunc(sysdate - 9999 + mod(rownum,10000)) as ship_date
 from dual connect by level<=1e6;

Table created.


SQL> create table adam.b as select * from adam.a;

Table created.

SQL> select table_name,num_rows,sample_size,to_char(last_analyzed,'hh24:mi:ss') from user_tables;

TABLE_NAME                       NUM_ROWS SAMPLE_SIZE TO_CHAR(
------------------------------ ---------- ----------- --------
A
B
SQL> exec dbms_stats.gather_schema_stats('ADAM')

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,sample_size,to_char(last_analyzed,'hh24:mi:ss') from user_tables;

TABLE_NAME                       NUM_ROWS SAMPLE_SIZE TO_CHAR(
------------------------------ ---------- ----------- --------
A                                 1000000     1000000 10:03:11
B                                 1000000     1000000 10:03:12

You may notice that DBMS_STATS analyzed all the blocks/rows by default – that is because the tables are rather small in size. I want to change that for table b:

SQL> exec dbms_stats.set_table_prefs('ADAM','B','ESTIMATE_PERCENT','50')

PL/SQL procedure successfully completed.

Next, I will delete more than 10 Percent of both tables, so that DBMS_STATS will consider both as stale and collect new stats for them. Then I call again GATHER_SCHEMA_STATS as before, but b is treated different:

SQL> delete from a where rownum<200000;

199999 rows deleted.

SQL> delete from b where rownum<200000;

199999 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_schema_stats('ADAM')

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,sample_size,to_char(last_analyzed,'hh24:mi:ss') from user_tables;

TABLE_NAME                       NUM_ROWS SAMPLE_SIZE TO_CHAR(
------------------------------ ---------- ----------- --------
A                                  800001      800001 10:10:06
B                                  801196      400598 10:10:09

Notice the different sample size of b. Now to the old (10g) way to achieve the same. If we would just let the AutoTask collect all the stats (including our “different tables”) and after that collect stats again on those tables, we would do the doubled work for them. Therefore we lock the stats (10g New Feature). I drop & recreate the tables a & b the same as before. Then:

SQL> exec dbms_stats.lock_table_stats('ADAM','B')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('ADAM')

PL/SQL procedure successfully completed.
SQL> create procedure mygather
as
begin
dbms_stats.unlock_table_stats('ADAM','B');
dbms_stats.gather_table_stats('ADAM','B',estimate_percent=>50);
dbms_stats.lock_table_stats('ADAM','B');
end;


Procedure created.

SQL> exec mygather

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,sample_size,to_char(last_analyzed,'hh24:mi:ss') from user_tables;

TABLE_NAME                       NUM_ROWS SAMPLE_SIZE TO_CHAR(
------------------------------ ---------- ----------- --------
A                                 1000000     1000000 10:18:27
B                                  999662      499831 10:18:54

Conclusion: If we are aware of the (New) Features of each Oracle Database version, we are able to do our work more efficiently. One aspect of this general rule is the dealing with Optimizer Statistics, that we can collect different from the AutoTasks default for some tables if we are inclined to do so.

About these ads

,

  1. #1 by Laurent Schneider on February 16, 2011 - 13:38

    I used this nice feature when our stats job was failing every night, leaving half of the tables unanalyzed.

    Actually we did hit a bug in one our 10g database with a partitioned table and DBMS_STATS.AUTO_SAMPLE_SIZE (probably related to bug 997838, not reproducible by Oracle). So for those problematic tables we did set the table preferences ESTIMATE to NULL.

    BTW, set table prefs is a 10g feature

  2. #2 by Uwe Hesse on February 16, 2011 - 13:47

    Hi Laurent,
    thank you for sharing this information! But: DBMS_STATS.SET_TABLE_PREFS is not there in 10g – that’s why I know it from the 11g New Features course :-)
    Look at the list of DBMS_STATS subprograms of 10gR2:

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/toc.htm

  3. #3 by Laurent Schneider on February 22, 2011 - 10:04

    Indeed, I stand corrected. Thank you !

  1. Remove Histograms & Exclude Tables from getting them « The Oracle Instructor
  2. Auto gather statistics 11g differ from 10g « Shrikant's Blog
  3. OUGN Spring Seminar Day 2 « The Oracle Instructor
  4. OUGN Spring Seminar Day 2 | Oracle Administrators Blog - by Aman Sood

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,890 other followers

%d bloggers like this: