idbasolutions

Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.

Analyze and gather stats samples:
This document contains examples of how to analyze Oracle tables and indexes, using the Analyze table command or dbms_stats.gather_schema_stats command.

Analyze table &table_name COMPUTE STATISTICS;
–>This will analyze the entire table rather than a sample.

Analyze index &index_name compute statistics;
–>Analyzing just index.

ANALYZE TABLE &table_name COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXED COLUMNS;
–>This analyzes the entire table and all underlining indexes. This would run for a long time on a large table, rather use the following example which would analyze with sample 40 percent which should be enough for the optimizer in most environment and situations.

ANALYZE TABLE &table_name estimate statistics sample 40 percent
FOR TABLE FOR ALL INDEXED COLUMNS;
–>This analyzes (estimates) the table and all underlining indexes, with sample 40 percent which should be enough for the optimizer in most environment and situations.

Using dbms_stats.gather_schema_stats command:
exec dbms_stats.gather_schema_stats(ownname=> ‘AP’ , estimate_percent=> 40 , cascade=> TRUE );
–>This will gather stats for schema AP sampling is 40% and it will do all tables and indexes.

exec dbms_stats.gather_schema_stats(ownname=> ‘PA’ , estimate_percent=> 40 , cascade=> TRUE );
–>This will gather stats for schema PA sampling is 40% and it will do all tables and indexes.

exec dbms_stats.gather_table_stats(ownname=> ‘AR’, tabname=> ‘CUST’, partname=> NULL , estimate_percent=> 30 , cascade=> TRUE );
–>This to only analyze table CUST and all underlining indexes.

Analyzing SYS objects:
exec dbms_stats.gather_schema_stats (‘SYS’,dbms_stats.auto_sample_size);
–>On a 1.5.10.2 and 9.2.0.7 database this run for over 20 minutes.
exec dbms_stats.gather_schema_stats (‘SYSTEM’,dbms_stats.auto_sample_size);

10g: EXEC DBMS_STATS.GATHER_DICTIONARY_STATS:

If monitoring is enabled:
If monitoring is enabled run the following at regular basis:
First turn monitoring on:
exec dbms_stats.ALTER_SCHEMA_TAB_MONITORING(‘AR’,TRUE);
exec dbms_stats.ALTER_SCHEMA_TAB_MONITORING(‘BEN’,TRUE);
SQL to verify:
select OWNER,MONITORING,count(*)
from dba_tables
where OWNER=’AR’
group by OWNER,MONITORING;

A table is considered stale if there is a change of more than 10% in the rows (Insert/Update/Delete).
Then analyze frequently using either of the following, only stale tables will be analyzed:
exec dbms_stats.gather_schema_stats(ownname=> ‘AP’ , options=> ‘GATHER AUTO’, cascade=> TRUE)
exec dbms_stats.gather_schema_stats(ownname=> ‘BEN’ , options=> ‘GATHER STALE’, cascade=> TRUE)

Script:
Dynamic SQL to analyze all schema:
This script will generate dynamic SQL to analyze all objects in the database by reading through
user accounts in dba_users.

Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.

set heading off
set feedback off
set pagesize 500
set linesize 200
set echo off
spool gather_stats_now.sql

select ‘ exec dbms_stats.gather_schema_stats(ownname=> ‘|| ”” || USERNAME || ”” ||’ , estimate_percent=> 40 , cascade=> TRUE ) ; ‘
from dba_users
/
prompt hos rm -f gather_stats_now.sql
spool off
set feedback on
set echo on