1 / 37

DBMS_SCHEDULER: The How, What and Why

DBMS_SCHEDULER: The How, What and Why. David Hicken Flying J, Inc. david.hicken@flyingj.com. Event Driven Schedules DBMS_JOBS to DBMS_SCHEDULER Monitoring the Scheduler Things to Look Out For Q & A. Oracle DBMS_SCHEDULER. Scheduling Options DBMS_Scheduler Options

smithnancy
Download Presentation

DBMS_SCHEDULER: The How, What and Why

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DBMS_SCHEDULER:The How, What and Why David Hicken Flying J, Inc. david.hicken@flyingj.com

  2. Event Driven Schedules DBMS_JOBS to DBMS_SCHEDULER Monitoring the Scheduler Things to Look Out For Q & A Oracle DBMS_SCHEDULER • Scheduling Options • DBMS_Scheduler Options • Breaking Down the DBMS_SCHEDULER • Individual parts of the DBMS_SCHEDULER • Time Driven Schedules

  3. Oracle Scheduling Options • Cron and at in Unix and Linux • Windows Scheduler • DBMS_JOBS • Oracle Scheduler • Oracle 9i Scheduler • Oracle 10g Scheduler • Oracle 11g Scheduler

  4. DBMS_Scheduler Options • Time Driven Schedules • Flexible and Easy • Event Driven Schedules • Harder to set up • Chaining Schedules • Can be used with Time or Event

  5. Breaking Down DBMS_Scheduler • Programs • Schedules • Jobs • Job Classes • Windows • Window Groups • Chains

  6. DBMS_SCHEDULER: Programs • Stored Procedure • Can pass parameters • PL/SQL Block • Executable • Dependent upon the OS.

  7. DBMS_SCHEDULER: Schedules • Named Schedules • On the fly • Can use old DBMS_JOBS scheduling syntax, or new syntax. • EVALUATE_CALENDAR_STRING

  8. BYMONTH BYDAY BYHOUR BYMINUTE BYSECOND BYWEEKNO BYDATE BYMONTHDAY INCLUDE DBMS_SCHEDULERSchedule words • FREQ • Yearly • Monthly • Weekly • Hourly • Minutely • Secondly • INTERVAL • Number And many others!

  9. DBMS_SCHEDULER: Jobs begin dbms_scheduler.create_job ( job_name => 'HR_STATS_REFRESH', job_type => 'PLSQL_BLOCK', job_action => '- - Gather HR Stats BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''hr''); END;', start_date => systimestamp, repeat_interval => 'FREQ=DAILY';byhour=9;byminute=0, enabled => TRUE, comments => 'Refreshes the HR Schema at 9:00 PM' ); end; /

  10. DBMS_SCHEDULER: Job Classes • Create Job Class to group jobs • Assign Resources to Job Class • Control Jobs according to Class • Start and Stop a group of jobs • Set Priorities for a Job Class

  11. DBMS_SCHEDULER: Windows • Caution: Windows are NOT schedules • Only one window open at a time • When windows conflict, Priority and “First Open” • Stop on Window Close • Force Windows to Open and/or Close

  12. DBMS_SCHEDULER:Window Groups • Combine Windows • Weeknights • Weekends MAINTENANCE_WINDOW

  13. DBMS_SCHEDULER:Event Driven Schedules • Requires Advanced Queuing • Defined Events • More advanced than this class ....

  14. JOB_DISABLED JOB_CHAIN_STALLED JOB_OVER_MAX_DURATION Events raised by applications DBMS_SCHEDULER Events • JOB_STARTED • JOB_SUCCEEDED • JOB_FAILED • JOB_BROKEN • JOB_COMPLETED • JOB_STOPPED • JOB_SCH_LIM_REACHED

  15. DBMS_SCHEDULER:Chains • Can be Time Driven or Event Driven • Define Programs • Define Steps • A program • Another chain (nested chain) • An Event

  16. Monitoring and Managingthe Scheduler • Oracle Enterprise Manager • Straight forward, but many screens deep • Toad • Implemented poorly in current versions, but next version (Toad 10) is greatly improved • DBA_ Views

  17. DBA_QUEUE_SCHEDULES DBA_SCHEDULER_JOB_ARGS DBA_SCHEDULER_WINDOW_LOGDBA_SCHEDULER_RUNNING_CHAINS DBA_SCHEDULER_GLOBAL_ATTRIBUTE DBA_SCHEDULER_WINDOW_GROUPS DBA_SCHEDULER_PROGRAMS DBA_SCHEDULER_JOB_CLASSES DBA_SCHEDULER_JOB_LOG DBA_SCHEDULER_CHAIN_RULES DBA_SCHEDULER_CHAINS Scheduler DBA_ Views • DBA_SCHEDULER_JOBS • DBA_SCHEDULER_PROGRAM_ARGS • DBA_SCHEDULER_WINGROUP_MEMBERS • DBA_SCHEDULER_SCHEDULES • DBA_SCHEDULER_WINDOWS • DBA_SCHEDULER_JOB_RUN_DETAILS • DBA_SCHEDULER_WINDOW_DETAILS • DBA_SCHEDULER_RUNNING_JOBS • DBA_SCHEDULER_CHAIN_STEPS

  18. Things to Watch for... • OEM has trouble defining schedules • Toad's implementation is poor • Bug in creating programs. Test run script first • Uses TIMESTAMP, which has a bug prior to 10.2.0.4. Makes it hard to see when schedules fire • Some of the DBA_ views may require some “tweaking” due to above bug • ALTER SYSTEM SET job_queue_processes=0; doesn't stop schedules!

  19. DBMS_Scheduler vs. DBMS_Jobs • More versatile scheduling • Scheduling syntax is readable • Event driven ability • Chaining ability • Enable/Disable • Easily editable • Parameters • Logging • … and that's the ones off the top of my head.

  20. Quick Comparison

  21. Quick Comparison 2

  22. Converting DBMS_JOBS toDBMS_SCHEDULER • Can use old syntax trunc(sysdate)+1+3/24 • Can use new syntax freq=daily;interval=1;byhour=3;byminute=24 • Caution with self-replicating DBMS_JOBS

  23. Questions? DBMS_SCHEDULERThe How What and Why

More Related