performance tuning tool06-database scheduler

1
OracleӾጱӱ᧣ଶ 1. ԟፓຽ: 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, ളතکEVENTapplicationݢզ໑ഝᬯԶ௳מᬰᤈᭇ୮ጱቘ;eg:ኧԭᔮᕹॡᬦԭᔺ, ڊjobސݸ30ړ,jobՖᆐဌᚆᶲސڥ,ᮎԍᬯӻײ,SchedulerݢզಲڊӞEVENTक़᮱ጱଫአ,զक़᮱ଫአᚆड़᭗ᎣDBA,ᬰᤈቘ; 2. ApplicationݎEvents:क़᮱ጱଫአԞݢզݎEvents,ଚӬኧSchedulerളතଚቘᬯӞ ᔄጱEvents,ಅ᧲SchedulerEVENT੪ฎSchedulerސፘଫጱjobಗᤈፘى,ᬯᔄ jobڠୌӫᳪกԧeventጱቘ,ᬯ୮ളතکEVENT,ᬯᔄjob੪տސ; 3. SchedulerOracleṛᕆᴚڜ(DMBS_AQ۱)ڊզᲀEvents,୮ಲڊSchdulerݎEvents,Schedulerਖ਼ၾ௳فکἕᦊጱeventڜ,applicationڞ᭗ᬦ༄ᧆᴚڜEvents;୮ಲڊapplicationݎEvents,applicationਖ਼ၾ௳فکjobଫጱᴚ ڜӾ; 2. SchedulerڊEvents 1. 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ᬦᑕ,ץschedulerevent_expiry_time,ᧆᶱጱ զᑁԅܔ; 3. ApplicationڊEvents 1. Schedulerᚆड़ಲڊEventsᦏक़᮱ଫአቘ,क़᮱ጱଫአԞݢզಲڊEventsSchedulerސ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. TRUE 2. FALSE 3. 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 GROUPӾ ۱ތԧग़ӻWINDOW,ӻWINDOWڦړᨮᨱӧݶᳵᅩጱᩒრአᦇښ; 3. ݸڠJOB,schedule_nameහԅWINDOW GROUPݷ(SCHEDULE_NAMEݢզਧԅ WINDOW GROUPWINDOW),,ݢզ᭗ᬦஉᓌܔጱොୗ,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_PLANResource Plan;NIGHT_WINDOW,ฎภӤ8ک8ᅩጱහഝՙପጱӱۓ,NIGHT_PLANResource Plan; 7. SHአӥڠୌӞӻJOB,DW_JOB_CLASS,ಗᤈӞӻݷ܇PL/SQL,ᥡ୮ڹአጱ Resource_manager_planCPUጱአఘ٭(ሿฎӥ܌3); 8. ڔഘᑻݗ:EXEC DBMS_SCHEDULER.OPEN_WINDOW('NIGHT_WINDOW', NULL, TRUE);፡୮ڹresource_manager_planCPUጱአሲ(ᳵᳩളᬪ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;

Upload: zhaoyang-wang

Post on 09-Apr-2017

122 views

Category:

Technology


2 download

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;