Pickleball spielen

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.