gatheringCBOstatistics.txt
Bikle
---------------------------

This page discusses the collection of CBO statistics.

Assume I'm logged in to SQL*Plus as SCOTT/TIGER.

I may run this command:

EXEC dbms_stats.delete_schema_stats ('SCOTT')

The above command deletes all CBO statistics for the schema named SCOTT.

The query below will verify that the CBO statistics have been deleted:

SELECT table_name,num_rows,last_analyzed
FROM user_tables;

Next, I may run this command to collect CBO statistics for a specific
table owned by SCOTT.

EXEC dbms_stats.gather_table_stats( -
    ownname          => NULL,    -
    tabname          => 'BIGEMP', -
    estimate_percent => .0001)

Notice that I set estimate_percent to a tiny value.  This syntax might
be useful for the situation when I want to avoid gathering statistics
for an extremely large table.  The idea I will present here is that
the gather_schema_stats() procedure is useful for gathering statistics
for all the tables owned by the SCOTT schema.  If, however, SCOTT owns
an extremely large table, I use the gather_table_stats() procedure (as
demonstrated above) to prevent the gather_schema_stats() procdure from
gathering statistics for that specific table.

Another way to prevent the gather_schema_stats() procdure from
gathering statistics for a specific table is to make use of the
set_table_stats() procedure:

EXEC dbms_stats.set_table_stats ( -
   ownname  => 'SCOTT',           -
   tabname  => 'BIGEMP')

I use the query listed below to verify that the above procedure calls
have gathered (or simulated gathering) a tiny amount of statistics for
the extremely large table:

ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-YYYY HH24:MI:SS';
SELECT num_rows,last_analyzed
FROM user_tables
WHERE table_name = 'BIGEMP';

Next, I may gather statistics for the entire schema.  The procedure
call below will not cause statistics to be gathered for the large
table since they were already gathered via the above call to
gather_table_stats() or set_table_stats():

EXEC dbms_stats.gather_schema_stats(                 - 
    ownname          => NULL,                        - 
    estimate_percent => dbms_stats.auto_sample_size, - 
    cascade          => TRUE,                        - 
    options          => 'GATHER EMPTY')   

Now, suppose I want to monitor tables in a way which allows me to
collect statistics at a later point in time but only for tables which
have changed.  A demonstration of syntax which does this is displayed below:

ALTER TABLE emp MONITORING;

Then,  at   a  later  time,  I   may  make  an  appropriate  call  to
gather_schema_stats() which  will collect statistics for  EMP (and all
other monitored tables  owned by SCOTT) only if  data within the table
has significantly  changed.  The phrase used to describe  the fact
that  data  within  the  table  has  significantly  changed  is: 
"The statistics are stale".

EXEC  dbms_stats.gather_schema_stats(                -
    ownname          => NULL,                        -
    estimate_percent => dbms_stats.auto_sample_size, -
    cascade          => TRUE,                        -
    options          => 'GATHER STALE')

Notice use of the keywords 'GATHER STALE' above.

So, the above procedure calls will gather CBO statistics for all
objects owned by the SCOTT schema where the objects' statistics
attributes are in the state of being either EMPTY or STALE.

I may query information from these views:

USER_TABLES
DBA_TAB_MODIFICATIONS 
DBA_TAB_COL_STATISTICS

Some queries:

SELECT table_name,num_rows,last_analyzed
FROM user_tables
ORDER BY table_name;

Keep in mind that the queries below are against views which are
privileged:

SELECT table_owner, table_name, inserts, updates, deletes, timestamp, truncated
FROM dba_tab_modifications
WHERE table_owner='SCOTT'
ORDER BY table_owner, table_name;

SELECT 
owner	
,table_name	
,column_name	
,num_distinct	
,low_value	
,high_value	
,density	
,num_nulls	
,num_buckets	
,last_analyzed	
,sample_size	
,global_stats	
,user_stats	
,avg_col_len	
FROM dba_tab_col_statistics
WHERE owner='SCOTT'
ORDER BY
owner	
,table_name	
,column_name;

So, the main points to remember from this page are that we may use procedures in
the dbms_stats package to manipulate CBO statistics.  Also we may query the views
listed below to obtain information about CBO statistics:

dba_tab_col_statistics
dba_tab_modifications
user_tables

Finally, more information about CBO statistics may be found here:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats.htm
http://technet.oracle.com/products/oracle9i/daily/Aug17.html