10g 自动统计时间间隔 GATHER_STATS_JOB

最新推荐文章于 2024-04-30 16:44:46 发布

cuizhan1913

最新推荐文章于 2024-04-30 16:44:46 发布

阅读量82

点赞数

文章标签：数据库

sys@racdb1> SELECT job_name , SCHEDULE_NAME FROM DBA_SCHEDULER_JOBS;

JOB_NAME                       SCHEDULE_NAME
------------------------------ ------------------------------
AUTO_SPACE_ADVISOR_JOB         MAINTENANCE_WINDOW_GROUP
GATHER_STATS_JOB               MAINTENANCE_WINDOW_GROUP
FGR$AUTOPURGE_JOB
PURGE_LOG                      DAILY_PURGE_SCHEDULE
MGMT_STATS_CONFIG_JOB
MGMT_CONFIG_JOB                MAINTENANCE_WINDOW_GROUP
RLM$SCHDNEGACTION
RLM$EVTCLEANUP

sys@racdb1> select PROGRAM_ACTION
2    from dba_scheduler_programs
3    where PROGRAM_NAME = 'GATHER_STATS_PROG';

PROGRAM_ACTION
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
dbms_stats.gather_database_stats_job_proc

sys@racdb1>
sys@racdb1> select WINDOW_GROUP_NAME , NUMBER_OF_WINDOWS,COMMENTS from dba_SCHEDULER_WINDOW_GROUPS;

WINDOW_GROUP_NAME NUMBER_OF_WINDOWS COMMENTS
------------------------------ ----------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MAINTENANCE_WINDOW_GROUP 2

sys@racdb1> SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW

sys@racdb1>   select window_name, repeat_interval, duration
2    from dba_scheduler_windows
3    where window_name in ('WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW')
4 ;

WINDOW_NAME
------------------------------
REPEAT_INTERVAL
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DURATION
---------------------------------------------------------------------------
WEEKNIGHT_WINDOW
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
+000 08:00:00

WEEKEND_WINDOW
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
+002 00:00:00

-- 修改间隔时间
exec dbms_scheduler.set_attribute(name=>'GATHER_STATS_JOB',attribute=>'schedule_name',value=>'WEEKEND_WINDOW');

sys@racdb1> SELECT job_name , SCHEDULE_NAME FROM DBA_SCHEDULER_JOBS;

JOB_NAME                       SCHEDULE_NAME
------------------------------ ------------------------------
AUTO_SPACE_ADVISOR_JOB         MAINTENANCE_WINDOW_GROUP
GATHER_STATS_JOB               WEEKEND_WINDOW
FGR$AUTOPURGE_JOB
PURGE_LOG                      DAILY_PURGE_SCHEDULE
MGMT_STATS_CONFIG_JOB
MGMT_CONFIG_JOB                MAINTENANCE_WINDOW_GROUP
RLM$SCHDNEGACTION
RLM$EVTCLEANUP

exec sys.dbms_scheduler.set_attribute( name =>'"SYS"."GATHER_STATS_JOB"', attribute => 'SCHEDULE_NAME', value => 'WEEKEND_WINDOW');

exec dbms_scheduler.set_attribute('WEEKEND_WINDOW','DURATION','+000 05:00:00');

-----------------------------------------------------------------------------------------------------------

关于Oracle 10g的schedule job

2008-09-15 01:00

关于Oracle 10g的schedule job

常用操作：

-- job 权限
grant create job to somebody;

-- job 创建
begin
dbms_scheduler.create_job (
     job_name => 'AGENT_LIQUIDATION_JOB',
     job_type => 'STORED_PROCEDURE',
     job_action => 'AGENT_LIQUIDATION.LIQUIDATION', --存储过程名
     start_date => sysdate,
     repeat_interval => 'FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0', -- 按月，间隔为1个(月),每月1号,凌晨1点
     comments => '执行代理商清分程序'
);
end;
/

-- job 执行时间测试
DECLARE
    start_date date;
    return_date_after date;
    next_run_date date;
BEGIN
    start_date := sysdate;--to_timestamp_tz('10-OCT-2004 10:00:00','DD-MM-YYYY HH24:MI:SS');
    return_date_after := start_date;
FOR i IN 1..10 LOOP
     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0',start_date, return_date_after, next_run_date);
     DBMS_OUTPUT.PUT_LINE('next_run_date: ' || to_char(next_run_date,'yyyy-mm-dd HH24:MI:SS'));
     return_date_after := next_run_date;
    END LOOP;
END;
/

-- job 查询
select owner, job_name, state from dba_scheduler_jobs;
select job_name, state from user_scheduler_jobs;

-- job 启用
begin
dbms_scheduler.enable('BACKUP_JOB');
end;
/

-- job 运行
begin
dbms_scheduler.run_job('COLA_JOB',TRUE); -- true代表同步执行
end;
/

-- job 停止(不太好用)
begin
dbms_scheduler.stop_job(job_name => 'COLA_JOB',force => TRUE);
end;
/

-- job 删除(对停job来说好用)
begin
dbms_scheduler.drop_job(job_name => 'COLA_JOB',force => TRUE);)
end;
/

---------------------------------------------------------------------------------

GATHER_STATS_JOB是10g开始引入的自动统计数据收集功能的重要组成部分，但是这个定时任务带来的问题也是较多的，应当根据应用的具体情况进行定制，通过DBA_SCHEDULER_JOBS可以查询JOB的执行情况：

SQL> col job_name for a20
SQL> col owner for a5
SQL> col last_start_date for a36
SQL> col last_run_duration for a30
SQL> col state for a10
SQL> SELECT owner,job_name,state,last_start_date,last_run_duration,failure_count
2 FROM dba_scheduler_jobs WHERE job_name = 'GATHER_STATS_JOB';

OWNER JOB_NAME STATE LAST_START_DATE LAST_RUN_DURATION FAILURE_COUNT
----- -------------------- ---------- ------------------------------------ ------------------------------ -------------
SYS GATHER_STATS_JOB SCHEDULED 09-SEP-09 10.00.01.091140 PM +08:00 +000000000 00:10:02.536139 0

进一步的，通过dba_scheduler_job_run_details表可以获得JOB的执行情况细节，一下显示JOB都执行成功：

SQL> col job_name for a20
SQL> SELECT log_id, job_name, status,
2         TO_CHAR (log_date, 'DD-MON-YYYY HH24:MI') log_date
3    FROM dba_scheduler_job_run_details
4   WHERE job_name = 'GATHER_STATS_JOB';

    LOG_ID JOB_NAME             STATUS                         LOG_DATE
---------- -------------------- ------------------------------ --------------------
     52135 GATHER_STATS_JOB     SUCCEEDED                      12-AUG-2009 22:04
     53615 GATHER_STATS_JOB     SUCCEEDED                      25-AUG-2009 22:02
     52755 GATHER_STATS_JOB     SUCCEEDED                      18-AUG-2009 22:03
     54075 GATHER_STATS_JOB     SUCCEEDED                      29-AUG-2009 06:03
     54735 GATHER_STATS_JOB     SUCCEEDED                      05-SEP-2009 06:02
     52415 GATHER_STATS_JOB     SUCCEEDED                      15-AUG-2009 06:03
     53995 GATHER_STATS_JOB     SUCCEEDED                      28-AUG-2009 22:03
     52055 GATHER_STATS_JOB     SUCCEEDED                      11-AUG-2009 22:03
     53895 GATHER_STATS_JOB     SUCCEEDED                      27-AUG-2009 22:02
     52655 GATHER_STATS_JOB     SUCCEEDED                      17-AUG-2009 22:04
     54296 GATHER_STATS_JOB     SUCCEEDED                      31-AUG-2009 22:03

    LOG_ID JOB_NAME             STATUS                         LOG_DATE
---------- -------------------- ------------------------------ --------------------
     54395 GATHER_STATS_JOB     SUCCEEDED                      01-SEP-2009 22:03
     54495 GATHER_STATS_JOB     SUCCEEDED                      02-SEP-2009 22:02
     54595 GATHER_STATS_JOB     SUCCEEDED                      03-SEP-2009 22:02
     52235 GATHER_STATS_JOB     SUCCEEDED                      13-AUG-2009 22:03
     52355 GATHER_STATS_JOB     SUCCEEDED                      14-AUG-2009 22:03
     54675 GATHER_STATS_JOB     SUCCEEDED                      04-SEP-2009 22:02
     54995 GATHER_STATS_JOB     SUCCEEDED                      07-SEP-2009 22:04
     55115 GATHER_STATS_JOB     SUCCEEDED                      08-SEP-2009 22:03
     55256 GATHER_STATS_JOB     SUCCEEDED                      09-SEP-2009 22:10
     52916 GATHER_STATS_JOB     SUCCEEDED                      19-AUG-2009 22:10
     53775 GATHER_STATS_JOB     SUCCEEDED                      26-AUG-2009 22:03

    LOG_ID JOB_NAME             STATUS                         LOG_DATE
---------- -------------------- ------------------------------ --------------------
     53455 GATHER_STATS_JOB     SUCCEEDED                      24-AUG-2009 22:04
     53235 GATHER_STATS_JOB     SUCCEEDED                      22-AUG-2009 06:02
     53055 GATHER_STATS_JOB     SUCCEEDED                      20-AUG-2009 22:02
     53155 GATHER_STATS_JOB     SUCCEEDED                      21-AUG-2009 22:04

26 rows selected.

这个JOB任务运行的具体过程如下：

SQL> select PROGRAM_ACTION from dba_scheduler_programs where PROGRAM_NAME = 'GATHER_STATS_PROG';

PROGRAM_ACTION
------------------------------------------------------------------------------------------------------------------------
dbms_stats.gather_database_stats_job_proc

以下是一个TB级海量数据库的统计数据收集情况，可以看到这个任务在很多时候于凌晨6：00被强制停止，因为数据量太大，分析不完了，而在周末全天运行时，有时候可以完成任务：

SQL> SELECT log_id, job_name, status,
2         TO_CHAR (log_date, 'DD-MON-YYYY HH24:MI') log_date
3    FROM dba_scheduler_job_run_details
4   WHERE job_name = 'GATHER_STATS_JOB' order by 1;

    LOG_ID JOB_NAME             STATUS                         LOG_DATE
---------- -------------------- ------------------------------ ------------------------------
     23749 GATHER_STATS_JOB     STOPPED                        18-AUG-2009 06:00
     23803 GATHER_STATS_JOB     STOPPED                        19-AUG-2009 06:00
     23857 GATHER_STATS_JOB     STOPPED                        20-AUG-2009 06:00
     23911 GATHER_STATS_JOB     STOPPED                        21-AUG-2009 06:00
     23965 GATHER_STATS_JOB     STOPPED                        22-AUG-2009 06:00
     23978 GATHER_STATS_JOB     SUCCEEDED                      22-AUG-2009 10:15
     24109 GATHER_STATS_JOB     STOPPED                        24-AUG-2009 23:53
     24129 GATHER_STATS_JOB     STOPPED                        25-AUG-2009 00:27
     24133 GATHER_STATS_JOB     SUCCEEDED                      29-AUG-2009 19:43
     24162 GATHER_STATS_JOB     STOPPED                        25-AUG-2009 06:00
     24216 GATHER_STATS_JOB     STOPPED                        26-AUG-2009 06:00

    LOG_ID JOB_NAME             STATUS                         LOG_DATE
---------- -------------------- ------------------------------ ------------------------------
     24270 GATHER_STATS_JOB     STOPPED                        27-AUG-2009 06:00
     24324 GATHER_STATS_JOB     STOPPED                        28-AUG-2009 06:00
     24378 GATHER_STATS_JOB     STOPPED                        29-AUG-2009 06:00
     24533 GATHER_STATS_JOB     STOPPED                        01-SEP-2009 06:00
     24587 GATHER_STATS_JOB     STOPPED                        02-SEP-2009 06:00
     24641 GATHER_STATS_JOB     STOPPED                        03-SEP-2009 06:00
     24695 GATHER_STATS_JOB     STOPPED                        04-SEP-2009 06:00
     24749 GATHER_STATS_JOB     STOPPED                        05-SEP-2009 06:00
     24759 GATHER_STATS_JOB     SUCCEEDED                      05-SEP-2009 09:27
     24906 GATHER_STATS_JOB     STOPPED                        08-SEP-2009 06:00
     24946 GATHER_STATS_JOB     STOPPED                        08-SEP-2009 23:54

    LOG_ID JOB_NAME             STATUS                         LOG_DATE
---------- -------------------- ------------------------------ ------------------------------
     24966 GATHER_STATS_JOB     STOPPED                        09-SEP-2009 00:06
     24970 GATHER_STATS_JOB     STOPPED                        09-SEP-2009 05:58
     25123 GATHER_STATS_JOB     STOPPED                        10-SEP-2009 06:00
     25177 GATHER_STATS_JOB     STOPPED                        11-SEP-2009 06:00
     25231 GATHER_STATS_JOB     STOPPED                        12-SEP-2009 06:00
     25257 GATHER_STATS_JOB     SUCCEEDED                      12-SEP-2009 16:31
     25379 GATHER_STATS_JOB     SUCCEEDED                      15-SEP-2009 01:10
     25429 GATHER_STATS_JOB     SUCCEEDED                      15-SEP-2009 23:28

30 rows selected.