Performance - Statistics

Gather database statistics

Gather stats for a single schema...

Delete stats

Gather system stats

Export/Import optimizer statistics

Old style analyze

Show a count of analyezed tables broken down by schema

Show tables that have analyze dates older than today

Gather database statistics

Gather stats on the entire database...

execute dbms_stats.gather_database_stats;

Or...

execute dbms_stats.gather_database_stats( -
estimate_percent => 1, -
method_opt => 'FOR ALL COLUMNS SIZE 1',-
cascade => TRUE);

Gather stats for a single schema...

execute dbms_stats.gather_schema_stats('SCOTT');

Or...

execute dbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
estimate_percent => 1, -
method_opt => 'FOR ALL COLUMNS SIZE 1',-
cascade => TRUE);

You can let oracle come up with the estimate figure by using dbms_stats.auto_sample_size

or...

execute dbms_stats.gather_schema_stats( -
ownname => 'SYS', -
cascade => TRUE);

Table statistics

exec dbms_stats.gather_table_stats('<owner>', '<table_name>');

Delete stats

exec dbms_stats.delete_database_stats;

exec dbms_stats.delete_schema_stats('SCOTT');

exec dbms_stats.delete_table_stats('SCOTT', 'EMPLOYEES');

exec dbms_stats.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

Gather system stats

execute dbms_stats.gather_system_stats('Start');

Wait for a while - idealy with the database under a typical workload

execute dbms_stats.gather_system_stats('Stop');

To see the current system statistics

select pname
, pval1 
from  sys.aux_stats$ 
where  sname = 'SYSSTATS_MAIN'
/

Export/Import optimizer statistics

Create a table to hold the statistics...

exec dbms_stats.create_stat_table(ownname => 'SYS',-
stattab => 'prod_stats', tblspace => 'USERS'); 

Populate the table with the current stats...

exec dbms_stats.export_schema_stats(ownname => 'SCOTT',-
statown=>'SYS', stattab=>'prod_stats');

At this point you need to:
1) take an export of the 'prod_stats' table
2) import 'prod_stats' into the target database
3) load the statistics using this command...

exec dbms_stats.import_schema_stats(ownname => 'SCOTT',-
statown=>'SYS', stattab=>'prod_stats');

Old style analyze

analyze table employees compute statistics;

analyze table employees estimate statistics sample 100 rows;

analyze table employees estimate statistics sample 15 percent;

analyze index employees_ind compute statistics;

Show a count of analyezed tables broken down by schema

set pages 999 lines 100
select a.owner
, a.total_tables tables
, nvl(b.analyzed_tables,0) analyzed
from (select owner
 , count(*) total_tables
 from dba_tables
 group by owner) a
, (select owner
 , count(last_analyzed) analyzed_tables
 from dba_tables
 where last_analyzed is not null
 group by owner) b
where a.owner = b.owner (+)
and a.owner not in ('SYS', 'SYSTEM')
order by a.total_tables - nvl(b.analyzed_tables,0) desc
/

Show tables that have analyze dates older than today

This is useful if you are running an analyze and want to see how much is left to do

select count(last_analyzed) left_to_do
from dba_tables
where owner = '&schema'
and trunc(last_analyzed) < trunc(sysdate)
order by 1
/