利用dbms_scheduler收集统计信息

--OLAP的应用，表的统计信息总是过期，领导要求也个JOB自动收集统计信息，自己在本机做了个实验。方便以后使用。
首先先创建一个PROCEDURE，收集统计信息，这个可以随便写，如果数据库SCHEMA多的话，最好分开来并行一起。
create or replace procedure Gathering_Job1 as
begin
dbms_stats.gather_database_stats;
end;
/


--然后利用dbms_scheduler创建JOB，可以指定时间以及频率。
begin
     sys.dbms_scheduler.create_job(job_name            => 'Gathering_Job2_test',
                                    job_type            => 'STORED_PROCEDURE',
                                    job_action          => 'Gathering_Job1', 
                                    start_date          => to_timestamp('20150317 10:00:00','yyyymmdd hh24:mi:ss'),
                                    repeat_interval => 'FREQ=WEEKLY;BYHOUR=10;BYMINUTE=0;BYSECOND=0',
                                    end_date            => to_date(null),
                                    job_class           => 'DEFAULT_JOB_CLASS',
                                    enabled             => true,
                                    auto_drop           => false,
                                    comments            => 'Gathering_Job2');
    end;
    /

--这样就创建成功了。


--下面看一下按照SCHEMA收集的情况，由于我不是用SYS用户创建的（并没有SYS权限），创建存储过程和CREATE JOB是没有问题的
create or replace procedure Weekly_Gathering_Job1 as
begin
  dbms_stats.gather_schema_stats(ownname          => '"ABC"',
                                 cascade          => DBMS_STATS.AUTO_CASCADE,
                                 estimate_percent => dbms_stats.auto_sample_size,
                                 degree           => 16,
                                 no_invalidate    => DBMS_STATS.AUTO_INVALIDATE,
                                 granularity      => 'AUTO',
                                 method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
                                 options          => 'GATHER');
end;
/


SQL> begin
  2    sys.dbms_scheduler.create_job(job_name            => ' WEEKLY_GATHERING_JOB_TEST1 ',
  3                                  job_type            => 'STORED_PROCEDURE',
  4                                  job_action          => 'WEEKLY_GATHERING_JOB1',
  5                                  start_date          => to_timestamp('20150322 2:00:00','yyyymmdd hh24:mi:ss'),
  6                                  repeat_interval => 'FREQ=WEEKLY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
  7                                  end_date            => to_date(null),
  8                                  job_class           => 'DEFAULT_JOB_CLASS',
  9                                  enabled             => true,
10                                  auto_drop           => false,
11                                  comments            => 'Weekly Statistics Gathering Job1');
12  end;
13  /

PL/SQL procedure successfully completed.

--但是到下面去执行包的时候就会报错。

SQL>   -- run job
SQL> begin
  2     dbms_scheduler.run_job('WEEKLY_GATHERING_JOB_TEST1',TRUE);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 25335
ORA-06512: at "SYS.DBMS_STATS", line 25414
ORA-06512: at "SYS.DBMS_STATS", line 25367
ORA-06512: at "SSO502349750.WEEKLY_GATHERING_JOB1", line 3
ORA-06512: at "SYS.DBMS_ISCHED", line 196
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2

--下面在创建存储过程的时候指定SCHEMA名字
create or replace procedure  ABC .Weekly_Gathering_Job1 as
begin
  dbms_stats.gather_schema_stats(ownname          => '"ABC"',
                                 cascade          => DBMS_STATS.AUTO_CASCADE,
                                 estimate_percent => dbms_stats.auto_sample_size,
                                 degree           => 16,
                                 no_invalidate    => DBMS_STATS.AUTO_INVALIDATE,
                                 granularity      => 'AUTO',
                                 method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
                                 options          => 'GATHER');
end;
/


SQL> begin
  2        sys.dbms_scheduler.create_job(job_name            => 'WEEKLY_GATHERING_JOB_TEST1',
  3                                      job_type            => 'STORED_PROCEDURE',
  4                                      job_action          => ' ABC .WEEKLY_GATHERING_JOB1',--注意，这个地方也需要指定SCHEMA名字，否则会报莫名其妙的错误,ORA-06576: not a valid function or procedure name
  5                                      start_date          => sysdate,
  6                                      repeat_interval => 'FREQ=WEEKLY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
  7                                      end_date            => to_date(null),
  8                                      job_class           => 'DEFAULT_JOB_CLASS',
  9                                      enabled             => true,
10                                     auto_drop           => false,
11                                     comments            => 'Weekly Statistics Gathering Job1');
12     end;
13     /

PL/SQL procedure successfully completed.

--再执行的时候就OK了。
SQL>    begin
  2         dbms_scheduler.enable('WEEKLY_GATHERING_JOB_TEST1');
  3      end;
  4      /

PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26084062/viewspace-1462843/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26084062/viewspace-1462843/