Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Advanced scheduling of STATSPACK snapshots

Oracle Tips by Heidi Schmidt
April 30,  2015

Heidi Schmidt provides this great article on advanced scheduling of STATSPACK snapshots with dbms_scheduler.  Note her requirements to change the STATSPACK snapshot scheduling interval to accommodate changes in workload, a very common practice in mission-critical systems.

For more on scheduling STATSPACK snapshots, see my related notes:


Advanced scheduling of STATSPACK snapshots

Oracle Tips by Heidi Schmidt

Scope and Goals:
==============
Please install statspack/perfstat  to take performance snapshots (done)

1) every 15 minutes on the 15 minutes except during our batch window

2) midnight  until 7am ; during which time we would like performance snapshots taken every 2 hours at the top of the hour (midnight, 2, 4 and 6).

The client is  really interested in the scheduling part not the content of the perfstat reports themselves


Infrastructure needed in order to submit the job
====================================

a)     job_queue_processes     > 0
b)     aq_tm_processes         > 0

What desc DBMS_JOB submit section looks like
=====================================

DBMS_JOB.SUBMIT
 
PROCEDURE SUBMIT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          OUT
 WHAT                           VARCHAR2                       IN
 NEXT_DATE                      DATE                           IN     DEFAULT
 INTERVAL                       VARCHAR2                       IN     DEFAULT
 NO_PARS                        BOOLEAN                        IN     DEFAULT
 INSTANCE                       BINARY_INTEGER                 IN     DEFAULT
 FORCE                          BOOLEAN                        IN     DEFAULT


*** Remember that the dbms_job.submit() procedure accepts three parameters:

    * the name of the job to submit
    * the start time for the job
    * and the interval to execute the job

Key to clean up of testing :-)
======================
 exec dbms_job.remove(#);
 

The basic jobs in separateness
=========================

--
-- No one really addresses from what I have read in books or on websites that sysdate doesn't like being set in the past,
-- nor will it read your mind and properly truncate sysdate to midnight.
-- This is the polite sledge hammer version that seems to make it all possible in basic dbms_job form
--

variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
-- --------------------------------------------------------------------------------
-- Submit job to begin at midnight and run every two hours
-- ---------------------------------------------------------------------------------
    dbms_job.submit(
    :jobno,
    'statspack.snap;',
    to_date('30/apr/08 00:00:00', 'dd/mon/yy HH24:MI:SS'),
    'trunc(SYSDATE+2/24,''HH'')',
    TRUE,
    :instno);
-- -----------------------------------------------------------------------------
-- Submit job to begin at 0700 and run every 15 minutes
-- ------------------------------------------------------------------------------
    dbms_job.submit(
    :jobno,
    'statspack.snap;',
    to_date('30/apr/08 07:00:00', 'dd/mon/yy HH24:MI:SS'),
    'trunc(sysdate+15/1440,''MI'')',
    TRUE,
    :instno);
    COMMIT;
END;
/

What it looks like to the system once queried
==================================

SQL> SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;

       JOB
----------
SUBSTR(WHAT,1,35)
--------------------------------------------------------------------------------
NEXT_DATE NEXT_SEC
--------- ------------------------
INTERVAL
--------------------------------------------------------------------------------
         5
statspack.snap;
30-APR-08 00:00:00
trunc(SYSDATE+2/24,'HH')

         6
statspack.snap;
30-APR-08 07:00:00
trunc(sysdate+15/1440,'MI')



That above won't cover the "window" that needs to be enforced.
We'll get 2,4,6,8,10,12,etc for the 2 hour interval when we want 2AM, 4AM and 6AM only; and we want 7:15 - 23:45 PM window in 15 minute intervals.
Pafumi got me headed in the right direction,  a function needed to be created that knows to look for the first time period and enforce the other


Testing the logic of the range using my husbands turn of phrase
=================================================
SQL> select case when to_char(sysdate,'HH24') > 7 then 'pants' else 'duck' END from dual;

CASEW
-----
pants

SQL> c/7/20
  1* select case when to_char(sysdate,'HH24') > 20 then 'pants' else 'duck' END from dual
SQL> /

CASEW
-----
duck


What the function looks like
=====================
CREATE OR REPLACE FUNCTION NextRunTime RETURN DATE as
    nextDate DATE;
    nowDate DATE;
BEGIN
    -- get the current date/time
    nowDate := SYSDATE;
       -- If the range is between 7AM and midnight then add fifteen minutes to it's next interval run; i.e. run every fifteen minutes
       -- if the range is after midnight and before 7AM then add 2 hours to it's next interval run; i.e. run every two hours on the hour
    if (to_char(nowDate,'HH24') > 7 and to_char(nowDate,'HH24') < 23) then
        nextDate := nowDate  + (15/1440);
    elsif (to_char(nowDate,'HH24') >  0 and to_char(nowDate,'HH24') < 7) then
        nextDate :=  TRUNC(nowDate+2/24,'HH24');
    end if;
return nextDate;
end;
/

How to use the function NextRunTime
============================

DECLARE
         jobno number(5);
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
-- Now for midnight range
        dbms_job.submit(
        :jobno,
        'statspack.snap;',
        to_date('30/apr/08 00:00:00', 'dd/mon/yy HH24:MI:SS'),
        'NextRunTime',
        TRUE,
        :instno);
-- Now for 7 am
        dbms_job.submit(
        :jobno,
        'statspack.snap;',
        to_date('30/apr/08 07:00:00', 'dd/mon/yy HH24:MI:SS'),
        'NextRunTime',
        TRUE,
        :instno);
        COMMIT;
END;



PL/SQL procedure successfully completed.



Verifying the results
===============
SQL> SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;

       JOB
----------
SUBSTR(WHAT,1,35)
--------------------------------------------------------------------------------
NEXT_DATE NEXT_SEC
--------- ------------------------
INTERVAL
--------------------------------------------------------------------------------
         9
statspack.snap;
30-APR-08 00:00:00
NextRunTime

        10
statspack.snap;
30-APR-08 07:00:00
NextRunTime



And will have to run tomorrow morning to get back to you with more information
===============================================================

Column Job# format a4
Column What format a27
Column Last_Date format a15
Column Next_Date format a15
select substr(to_char(job),1,3) Job#,
       substr(what,1,27) What,
       to_char(last_date, 'dd-MON-YY HH24:MI') Last_Date,
       to_char(Next_Date, 'dd-MON-YY HH24:MI') Next_Date, failures fail#,
       total_time
from user_jobs;

 

Right now it reports it never failed.

JOB# WHAT      LAST_DATE       NEXT_DATE            FAIL#  TOTAL_TIME
---- ---------------- ---------------       ---------------         ---------- ------------------

9    statspack.snap;     (NULL)  30-APR-08 00:00     0
10   statspack.snap;   (NULL)   30-APR-08 07:00     0


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.