Some of the statistics collection commands which I often use in 11gR2
One drawback with METHOD_OPT => 'FOR ALL COLUMNS SIZE
AUTO' is that Oracle will collect histograms.
If you don’t want that, give FOR
ALL COLUMNS SIZE 1 so that it won’t collect histograms.
To collect statistics for an entire schema
SQL> exec dbms_stats.gather_schema_stats (ownname => 'MADHU', estimate_percent => 5, method_opt=>'FOR ALL COLUMNS SIZE AUTO', cascade=>true, degree => 4, no_invalidate=>false, GRANULARITY=> 'ALL');
To collect statistics for an entire schema even if statistics is LOCKED for that schema
SQL> exec dbms_stats.gather_schema_stats (ownname => 'MADHU', estimate_percent => 5, method_opt=>'FOR ALL COLUMNS SIZE AUTO', cascade=>true, degree => 4, no_invalidate=>false, GRANULARITY=> 'ALL',FORCE=>TRUE);
To collect statistics on a particular table
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'MADHU', tabname => 'TASK' , estimate_percent => 10 , cascade => TRUE, degree => 4, no_invalidate=>false, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', GRANULARITY=> 'DEFAULT');
To collect statistics on a particular table even if statistics is LOCKED for that table
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'MADHU', tabname => 'TASK' , estimate_percent => 10 , cascade => TRUE, degree => 4, no_invalidate=>false, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', GRANULARITY=> 'DEFAULT', FORCE=> TRUE);
Use following option in case table has '/' character inside the table name like we might have seen in SAP instances. Note that I have used double quotes around the table name and used single quote outside.
exec DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'SAPBIW'
,TabName => '"/BIC/AZEIP_O0400"'
,Estimate_Percent => 10
,Method_Opt => 'FOR ALL COLUMNS SIZE 1'
,Degree => 4
,Cascade => FALSE
,No_Invalidate => FALSE);
To collect statistics for only tables which has NULL stats in a particular schema
SQL> exec dbms_stats.gather_schema_stats (ownname => 'MADHU', estimate_percent => 5, method_opt=>'FOR ALL COLUMNS SIZE AUTO', cascade=>true, degree => 4, no_invalidate=>false, GRANULARITY=> 'ALL',options=>'GATHER EMPTY');
Use following command to find tables where stats is locked
SQL> select owner, table_name, stattype_locked from dba_tab_statistics where owner='MADHU';
command to lock table stats
exec dbms_stats.lock_table_stats('OWNER_NAME','TABLE_NAME');
command to unlock locked stats
exec dbms_stats.unlock_table_stats('OWNER_NAME','TABLE_NAME');
Details about statistics collection history
For table
SQL> select * from dba_tab_stats_history where table_name='SKUSAFETYSTOCKPARAM';
SQL> select * from sys.WRI$_OPTSTAT_tab_history where obj#=62595 order by savtime; - to see other details
or
select a.savtime,a.flags,a.rowcnt,a.blkcnt,a.avgrln,a.samplesize,to_char(a.analyzetime,'DD-MON-YY HH24:MI:SS') as anlzetime
from sys.WRI$_OPTSTAT_tab_history a, dba_objects b where a.obj#=b.object_id and b.owner='MADHU'
and b.object_name='PROCESSRECONCILE' order by 1
For index
SQL>select a.savtime,a.flags,a.rowcnt,a.blevel,a.leafcnt,a.samplesize,to_char(a.analyzetime,'DD-MON-YY HH24:MI:SS') as anlzetime
from sys.WRI$_OPTSTAT_ind_history a, dba_objects b where a.obj#=b.object_id and
b.owner='MADHU' and b.object_name='I173124' order by 1;
Stats commands on partitions.
-- Command to export stats of a table
set echo on
spool export_partitions.log
rem Create stats table
exec dbms_stats.create_stat_table('MADHU','STAT_W_TRANSACTION_MAY16');
rem Export stats for MADHU.WHOLESALE_TRANSACTION to above stats table
exec dbms_stats.export_table_stats(ownname => 'MADHU',tabname => 'WHOLESALE_TRANSACTION',stattab =>'STAT_W_TRANSACTION_MAY16', cascade => true, statown =>'MADHU');
spool off;
-- Command to copy table partition stats
set echo on
set trimspool on
spool copy_partitions_stats.log
rem Script to copy partition stats from partition A to partition B
exec dbms_stats.copy_table_stats(ownname => 'MADHU',tabname => 'WHOLESALE_TRANSACTION',srcpartname => 'WHOLESALE_TRANSACTION_20111231',dstpartname => 'WHOLESALE_TRANSACTION_20120430',FORCE => TRUE);
spool off;
Gathering system stats
exec dbms_stats.gather_system_stats('Start');
run for 2 hours and stop it by executing
exec dbms_stats.gather_system_stats('Stop');
or by
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval => 60)
To disable auto stats collection in 10g and 11g. We could also do this from Grid Control from Automated Maintenance Tasks under
Scheduler Central.
BEGIN
dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
END;
Changing dbms_stats time
BEGIN
sys.dbms_scheduler.disable( '"SYS"."GATHER_STATS_JOB"' );
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."GATHER_STATS_JOB"', attribute => 'schedule_name');
sys.dbms_scheduler.set_attribute( name => '"SYS"."GATHER_STATS_JOB"', attribute => 'end_date', value => to_timestamp_tz('2010-12-10 09:45:00 America/Los_Angeles', 'YYYY-MM-DD HH24:MI:SS TZR'));
sys.dbms_scheduler.enable( '"SYS"."GATHER_STATS_JOB"' );
END;
BEGIN
sys.dbms_scheduler.disable( '"SYS"."GATHER_STATS_JOB"' );
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."GATHER_STATS_JOB"', attribute => 'schedule_name');
sys.dbms_scheduler.set_attribute( name => '"SYS"."GATHER_STATS_JOB"', attribute => 'repeat_interval', value => 'FREQ=DAILY;BYHOUR=10;BYMINUTE=0;BYSECOND=0');
sys.dbms_scheduler.enable( '"SYS"."GATHER_STATS_JOB"' );
END;
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."WEEKNIGHT_WINDOW"',
force=>TRUE);
END;
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."WEEKNIGHT_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=10;BYMINUTE=0;BYSECOND=0');
END;
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."WEEKNIGHT_WINDOW"');
END;
