performance tuning tool06-database scheduler
TRANSCRIPT
Oracle1. :
1. Simplify management tasks by using the Scheduler;2. Create a job, program, and schedule;3. Monitor job execution;4. Use a time-based or event-based schedule for executing Scheduler jobs;5. Use job chains to perform a series of related tasks;6. Use advanced Scheduler concepts to prioritize jobs;
2. :1. Performing a series of month-end tasks on the last day of each month;2. Running a dequeue procedure as soon as a message is enqueued;3. Replicating table data via materialized view refreshes;4. Running a daily job to back up database;5. Computing table and index statistics twice a day;6. Starting the batch load as soon as the file arrives on the file system;7. Generating an hourly report on invalid server access attempts;8. Rebuilding an index when finished rebuilding the current index;
3. :1. EM->Administrator->Database Scheduler->Programs Program:
, , what ;(TYPE ,, ;)
2. EM->Administrator->Database Scheduler->Schedules Schedule:, , , ;(Scheduler
Type ;)
3. EM->Administrator->Database Scheduler->Jobs Job:
1. Logging Level:1. LOGGING_OFF: ;2. LOGGING_RUNS: , ;3. LOGGING_FULL: , , , , ;
2. Auto Drop: ;3. Restartable: ;4. :
1. Program;2. PL/SQL ;3. ;4. ;5. ;
5. Schedule Type:1. Standard;2. , Schedule;3. PL/SQL ;4. Window;5. Event;
4. EM->Administrator->Database Scheduler->Jobs History ;
4. Schedule:1. job , repeat_interval ;
1. :FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;2. 3 :FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0;3. 3 :FREQ=MONTHLY;BYMONTHDAY=-1;BYHOUR=3;BYMINUTE=0;BYSECOND=0;
2. Schedule:1. SCHEDULER EVENT :
1. Scheduler Events:Scheduler Events, schduler job, job , , , EVENT,EVENT application ;eg: ,
job 30 ,job , ,Scheduler EVENT, DBA, ;
2. Application Events: Events, SchedulerEvents, Scheduler EVENT Scheduler job ,
job event , EVENT , job ;3. Scheduler Oracle (DMBS_AQ ) Events, Schduler
Events ,Scheduler event ,applicationEvents; application Events ,application job;
2. Scheduler Events1. Scheduler Events job , ,job Events ;2. Scheduler job raise_events, job Events ,
CREATE_JOB , , EVENT,raise_events , job , SET_ATTRIBUTE jobraise_events ;
3. Scheduler Events:DBMS_SCHEDULER.SET_ATTRIBUTE('job_name', 'raise_events', DBMS_SCHEDULER.JOB_FAILED);
4. JOB Event :1. job_started CONSTANT PLS_INTEGER := 1;2. job_succeeded CONSTANT PLS_INTEGER := 2;3. job_failed CONSTANT PLS_INTEGER := 4;4. job_broken CONSTANT PLS_INTEGER := 8;5. job_completed CONSTANT PLS_INTEGER := 16;6. job_stopped CONSTANT PLS_INTEGER := 32;7. job_sch_lim_reached CONSTANT PLS_INTEGER := 64;8. job_disabled CONSTANT PLS_INTEGER := 128;9. job_chain_stalled CONSTANT PLS_INTEGER := 256;
10. job_all_events CONSTANT PLS_INTEGER := 511;11. job_run_completed CONSTANT PLS_INTEGER := job_succeeded + job_failed
+ job_stopped;5. sys.scheduler$_event_queue , ,dba ,
, events ;(sys.scheduler$_event_queue sys.scheduler$_event_qtab ,sys.scheduler$_event_qtab ;)
6. Scheduler 24 Events , ,SET_SCHEDULER_ATTRIBUTE , scheduler event_expiry_time ,
;3. Application Events
1. Scheduler Events , Events Scheduler job, job Events , jobs
,2. queue_spec: events ;3. event_condition: job ,
, , application , ,;
5. Job Chain:1. :
1. hr.chain, chains :CREATE TABLE hr.chain(CHAIN_ID DATE PRIMARY KEY, STEP1 INT, STEP2 INT);
2. hr.chain_history, chains :create table HR.CHAIN_HISTORY(EXECUTE_TIME DATE, IS_SUCCEED INTEGER);
2. Programs:1. START_CHAIN_PROGRAM: hr.chain , , ,
;2. START_SUCCEED_PROGRAM: , hr.chain , ;3. START_FAILD_PROGRAM: , hr.chain , hr.chain_history
;4. END_CHAIN_PROGRAM: , , hr.chain_history ;5. :
3. EM->Administrator->Database Scheduler->Chains Chain:1. :
2. :
3. Chain Job JOB_CHAIN_TEST :
4. , S1->S2->S4:
5. JOB, :S1->S3:
4. Chain :1. If the condition is expressed with SQL, it must use the syntax of a SELECT
statement WHERE clause. You can refer to chain step attributes by using the chain step name as a bind variable. The bind variable syntax is :step_name.attribute. (step_name refers to a typed object.) Possible attributes are: completed, state, start_date, end_date, error_code, and duration. Possible values for the state attribute include: 'NOT_STARTED', 'SCHEDULED', 'RUNNING', 'PAUSED', 'STALLED', 'SUCCEEDED', 'FAILED', and 'STOPPED'. If a step is in the state 'SUCCEEDED', 'FAILED', or 'STOPPED', its completed attribute is set to 'TRUE', otherwise completed is 'FALSE';
2. :1. TRUE2. FALSE3. stepname [NOT] SUCCEEDED/FAILED/STOPPED/COMPLETED; 4. stepname ERROR_CODE [NOT] IN (integer, integer, integer ...);5. stepname ERROR_CODE = / != / <> / > / >= / < / <= integer;6. expression AND expression;7. expression OR expression;8. NOT (expression);
3. :1. 'step1 completed';2. 'step1 succeeded and step2 succeeded';3. 'step1 error_code > 100';4. 'step1 error_code IN (1, 3, 5, 7)';5. ':step1.completed = ''TRUE'' AND :step1.end_date >SYSDATE-1/24';6. ':step1.duration > interval ''5'' minute';
5. Chain :1. :
1. [AFTER delay_interval] START step_1[,step_2 ...];2. STOP step_1[,step_2 ...];3. END [{end_value|step_name.error_code}];
2. :1. 'AFTER 01:00:00 START step1, step2';2. 'STOP step1';3. END step4.error_code';4. 'END' or 'END 0';5. 'END 100';
6. chain :1. SELECT * FROM dba_scheduler_chains;2. SELECT * FROM dba_scheduler_chain_steps;3. SELECT * FROM dba_scheduler_chain_rules;4. SELECT * FROM dba_scheduler_running_chains;5. SELECT * FROM dba_scheduler_jobs;
6. session :ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';7. Advanced Scheduler:
1. Job Class:1. EM->Administrator->Database Scheduler->Job Classes Job Class;2. Job , Consumer Group, session
; Windows, DBA Resource Group Job Classes ;3. Resource Consumer Group: JOB CLASS Consumer Group, Service Name
;4. Log Retention Period: ;
2. Windows:1. EM->Administrator->Database Scheduler->Windows Window;2. , Resource Plan , ;3. Priority: Window , Window , Window
, ;4. Schedule: Window , ;
3. Window Group:1. WINDOW GROUP WINDOW;2. WINDOW GROUP , job , 24 ,
job, WINDOW , WINDOW GROUP, WINDOW GROUPWINDOW, WINDOW ;
3. JOB , schedule_name WINDOW GROUP (SCHEDULE_NAMEWINDOW GROUP WINDOW), , , job window ;
4. Job Class:OLTP_JOB_CLASS, ;DW_JOB_CLASS, ;
5. PLAN:DAY_PLAN , OLTP_GROUP 80% CPU ;NIGHT_PLAN, DW_GROUP 80% CPU ;
6. WINDOW:DAY_WINDOW, 8 8 , DAY_PLAN Resource Plan;NIGHT_WINDOW, 8 8 , NIGHT_PLAN Resource Plan;
7. SH JOB, DW_JOB_CLASS, PL/SQL ,Resource_manager_plan CPU ( 3 );
8. :EXEC DBMS_SCHEDULER.OPEN_WINDOW('NIGHT_WINDOW', NULL, TRUE);resource_manager_plan CPU ( 8:2);
9. :EXEC DBMS_SCHEDULER.CLOSE_WINDOW('NIGHT_WINDOW'); ;
8. JOB :EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('jobn_name', ' job_priority', value), 3;
-- START_CHAIN_PROGRAM Program Scripts;BEGIN INSERT INTO hr.chain VALUES (TRUNC(SYSDATE), NULL, NULL); COMMIT;END;
-- START_SUCCEED_PROGRAM Program Scripts;BEGIN UPDATE hr.chain SET STEP1 = 1 WHERE CHAIN_ID = TRUNC(SYSDATE); COMMIT;END;
-- START_FAILD_PROGRAM Program Scripts;BEGIN UPDATE hr.chain SET STEP2 = 1 WHERE CHAIN_ID = TRUNC(SYSDATE); INSERT INTO hr.CHAIN_HISTORY VALUES(SYSDATE, 1); COMMIT;END;
-- END_CHAIN_PROGRAM Program Scripts;BEGIN INSERT INTO hr.CHAIN_HISTORY VALUES(SYSDATE, 0); COMMIT;END;
-- , CPU ;DECLARE i INTEGER := 1; j INTEGER := 1;BEGIN WHILE i >= 0 LOOP -- i := power(j, 2); i := sqrt(j); j := j + 1 ; END LOOP;END;