Some of the statistics collection commands which I often use in 11gR2

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;