############sql3:
https://blog.csdn.net/dataminer_2007/article/details/41363417
http://blog.51cto.com/hunt1574/2136259
b6usrg82hwsa3 28,795.60 call dbms_stats.gather_database_stats_job_proc ( )
select window_name, repeat_interval, duration, enabled from dba_scheduler_windows where enabled='TRUE';
select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name ='GATHER_STATS_JOB';
修改周六日自动任务的运行时间开始于22点,持续时间240分钟：
exec dbms_scheduler.disable( name => 'SATURDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'SATURDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0');
exec dbms_scheduler.set_attribute( name => 'SATURDAY_WINDOW', attribute => 'DURATION',value => numtodsinterval(240,'minute'));
exec dbms_scheduler.enable( name => 'SATURDAY_WINDOW');
exec dbms_scheduler.disable( name => 'SUNDAY_WINDOW, force => TRUE);
exec dbms_scheduler.set_attribute( name => 'SUNDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=SUN;byhour=22;byminute=0;bysecond=0');
exec dbms_scheduler.set_attribute( name => 'SUNDAY_WINDOW', attribute => 'DURATION',value => numtodsinterval(240,'minute'));
exec dbms_scheduler.enable( name => 'SUNDAY_WINDOW');
###debug
Rem Pls run as sys i.e "connect / as sysdba" on each instance
set pagesize 2000
set echo on
column OBJECT_NAME format a30
spool job
select object_id, OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'JOB';
select * from dba_scheduler_jobs where job_name ='GATHER_STATS_JOB';
select * from DBA_SCHEDULER_JOB_LOG where job_name ='GATHER_STATS_JOB';
select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name ='GATHER_STATS_JOB';
spool off
Rem Internal Job Queues Summary report.
Rem Pls run as sys i.e "connect / as sysdba" on each instance
column systimestamp format a35
column ATTR_INTV format a37
column ATTR_TSTAMP format a37
column start_time format a35
column obj_name format a20
column name format a30
column value format a30
column window_name format a10
column job_queue_name format a18
column job_type format 9999
column flag format 9999
column status format a6
column "SEQUENCE#" format 9999
column id format 99999
set pagesize 100
set echo on
spool scheduler_qs
-- Report Internal Queues
--select job_queue_name, JOB_TYPE,
-- (select object_name from dba_objects where object_id = job_oid union select name from
-- scheduler$_lwjob_obj where obj# = job_oid) OBJ_NAME, FLAG, start_time from x$jskjobq;
-- scheduler$_lwjob_obj is not available
select * from x$jskjobq;
-- Report current time in scheduler format
select dbms_scheduler.stime from dual;
-- Report all global attributes
SELECT o.name, a.*
FROM sys.obj$ o, sys.scheduler$_global_attribute a
WHERE o.obj# = a.obj# AND BITAND(a.flags,1) != 1;
-- Report resource manager info
select * from v$rsrc_plan;
select * from v$rsrc_plan_history;
select id,name, active_sessions, execution_waiters from v$rsrc_consumer_group;
select * from v$rsrc_consumer_group;
select state from v$rsrc_session_info where state != 'NOT MANAGED' ;
select name, CPU_WAITS, CONSUMED_CPU_TIME, YIELDS,
CPU_WAIT_TIME from V$RSRC_CONSUMER_GROUP;
Rem Scheduler job and window logs
column additional_info format a40
set pagesize 1000
set linesize 150
column REQ_START_DATE format a37
column ACTUAL_START_DATE format a37
column log_date format a37
column window_name format a10
column operation format a9
column error# format 99999
column status format a9
column CPU_USED format a16
column job_name format a18
column run_duration format a15
connect / as sysdba
select log_date, owner, job_name, operation, status from dba_scheduler_job_log
union
select log_date, 'SYS', window_name, operation, status from
dba_scheduler_window_log
order by 1;
Rem select log_date, actual_start_date-req_start_date, job_name, error#, status, run_duration,
Rem cpu_used, additional_info
Rem from dba_scheduler_job_run_details order by log_id;
select job_name, CAST (actual_start_date-req_start_date AS INTERVAL DAY(2) TO SECOND(2)) DELAY
from dba_scheduler_job_run_details order by log_id;
Rem select additional_info from dba_scheduler_job_run_details where additional_info is not null;
spool off;
Rem Internal Job Queues Summary report for 10.2.
Rem Pls run as sys i.e "connect / as sysdba" on each instance
set long 400
column log_date format a37
column operation format a8
column status format a6
column additional_info format a400
set pagesize 1000
set linesize 120
column systimestamp format a37
column next_start_date format a40
column start_date format a40
column manual_open_time format a40
column manual_duration format a40
column duration format a40
column end_date format a40
column last_start_date format a40
column window_name format a26
column enabled format a6
column active format a6
column systimestamp format a35
column ATTR_INTV format a37
column ATTR_TSTAMP format a37
column start_time format a35
column obj_name format a20
column name format a30
column value format a30
column job_queue_name format a18
column job_type format 9999
column flag format 9999
column status format a6
column "SEQUENCE#" format 9999
column id format 99999
spool wintest
select * from dba_scheduler_windows ;
select log_date, window_name, operation, status, substr(additional_info,1,350) from
dba_scheduler_window_log
where operation != 'CREATE'
order by 1;
select log_date, window_name, operation,substr(additional_info,1,350) from
dba_scheduler_window_log order by 1 ;
select window_name, substr(additional_info,1,350) x from
dba_scheduler_window_log where additional_info is not null;
-- Report Internal Queues v10.2
select job_queue_name, JOB_TYPE,
(select object_name from dba_objects where object_id = job_oid
) OBJ_NAME, FLAG, start_time from x$jskjobq;
-- Report current time in scheduler format
select dbms_scheduler.stime from dual;
-- Report all global attributes
SELECT o.name, a.*
FROM sys.obj$ o, sys.scheduler$_global_attribute a
WHERE o.obj# = a.obj# AND BITAND(a.flags,1) != 1;
-- Report resource manager info
select * from v$rsrc_plan;
select * from v$rsrc_plan_history;
select id,name, active_sessions, execution_waiters from v$rsrc_consumer_group;
select * from v$rsrc_consumer_group;
select state from v$rsrc_session_info where state != 'NOT MANAGED' ;
select name, CPU_WAITS, CONSUMED_CPU_TIME, YIELDS,
CPU_WAIT_TIME from V$RSRC_CONSUMER_GROUP;