6 Replies Latest reply: Sep 30, 2013 11:22 PM by Madhu.149 RSS

    Reg: Statspack schedule

    Madhu.149
      Hello All,

      Oracle 11g standard edition 11.2.0.1

      I want to take statspack snap twice a day. i.e

      Morning 9AM to 10AM
      Afternoon 14:00 to 15:00

      I want this to be happened automatically.
      To achieve this I performed the following operations.

      1. I create a function NEXTRUNTIME
      CREATE OR REPLACE FUNCTION NextRunTime RETURN DATE as
          nextDate DATE;
          nowDate DATE;
      BEGIN
      -- get the current date/time
          nowDate := SYSDATE;
      -- every fifteen minutes
      if((to_char(nowDate, 'HH24') > 9 and to_char(nowDate, 'HH24') < 10) or 
         (to_char(nowDate, 'HH24') > 14 and to_char(nowDate, 'HH24') < 15)) then
      nextDate := nowDate  + (15/1440);
      elsif (to_char(nowDate, 'HH24') > 10 and to_char(nowDate, 'HH24') < 14) then
      nextDate := SYSDATE;
      else
      nextDate := nowDate + 18/24;
      end if;
      return nextDate;
      end;
      /
      
      HOW TO PUT THE NEXT DAY SCHEDULE IN ELSE PART (i.e for 9AM and 2PM)
      2. I scheduled the statspack job by calling the above function.
      variable jobno number;
      variable instno number;
      BEGIN
      SELECT instance_number INTO :instno FROM v$instance;
      -- --------------------------------------------------------------------------------
      -- Submit job to begin at 9 am and run every 15 minutes
      -- ---------------------------------------------------------------------------------
          dbms_job.submit(
          :jobno,
          'statspack.snap;',
          to_date('03/oct/12 09:00:00', 'dd/mon/yy HH24:MI:SS'),
          'NextRunTime',
          TRUE,
          :instno);
      -- -----------------------------------------------------------------------------
      -- Submit job to begin at 1400 and run every 15 minutes
      -- ------------------------------------------------------------------------------
          dbms_job.submit(
          :jobno,
          'statspack.snap;',
          to_date('03/oct/12 14:00:00', 'dd/mon/yy HH24:MI:SS'),
          'NextRunTime',
          TRUE,
          :instno);
          COMMIT;
      END;
      /
      But, I am confused with the timing.
      My statspack is running every 15 minutes, in other times also (other than scheduled time).
      I also queried the below command, which resulted in "no row selected"
      SQL>SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;
      no rows selected
      Can you please help me to schedule statspack snap only during 9:00-10:00 and 14:00 to 15:00 every day.


      Thanks,
      Madhu

      Edited by: Madhu.149 on Oct 3, 2012 5:51 PM

      Edited by: Madhu.149 on Oct 3, 2012 5:56 PM

      Edited by: Madhu.149 on Oct 3, 2012 5:59 PM
        • 1. Re: Reg: Statspack schedule
          phaeus
          Hello,
          in your case you can use dbms_scheduler and not dba_jobs. So you can define your windows in which your job should run. There are many sample about dbms_scheduler in this forum.

          regards
          Peter
          • 2. Re: Reg: Statspack schedule
            EdStevens
            Madhu.149 wrote:
            Hello All,

            Oracle 11g standard edition 11.2.0.1

            I want to take statspack snap twice a day. i.e

            Morning 9AM to 10AM
            Afternoon 14:00 to 15:00

            I want this to be happened automatically.
            To achieve this I performed the following operations.

            1. I create a function NEXTRUNTIME
            <snip>
            >
            Edited by: Madhu.149 on Oct 3, 2012 5:51 PM

            Edited by: Madhu.149 on Oct 3, 2012 5:56 PM

            Edited by: Madhu.149 on Oct 3, 2012 5:59 PM
            You don't need to create a function at all. Lose it now.

            Instead:
            begin
              dbms_scheduler.create_job (
                job_name         => 'perfstat.statspack_snapshots',
                job_type         => 'PLSQL_BLOCK',
                job_action       => 'begin statspack.snap;end;',
                repeat_interval  => 'freq=daily; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=6,7,8,9,10,11,12,13,14,15,16,17; BYMINUTE=0',
                enabled          => TRUE
               );
            END;
            /
            And to do housekeeping on old snapshots:
            begin
              dbms_scheduler.create_job (
                job_name         => 'perfstat.statspack_purge',
                job_type         => 'PLSQL_BLOCK',
                job_action       => 'begin statspack.purge(trunc(sysdate-40),true);end;',
                repeat_interval  => 'freq=daily; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=5; BYMINUTE=10',
                enabled          => TRUE
               );
            END;
            /
            The above from Steve Callen's article in Database Journal, Oct 22, 2008
            http://www.databasejournal.com/features/oracle/article.php/3779241/Hidden-treasures-in-Oracles-STATSPACK-utility.htm

            Edited by: EdStevens on Oct 3, 2012 8:03 AM
            • 3. Re: Reg: Statspack schedule
              Madhu.149
              Thank You....
              • 4. Re: Reg: Statspack schedule
                Madhu.149
                I sheduled my statspack snap as below and it is working fine.
                begin
                  dbms_scheduler.create_job (
                    job_name         => 'perfstat.statspack_snapshots',
                    job_type         => 'PLSQL_BLOCK',
                    job_action       => 'begin statspack.snap;end;',
                    repeat_interval  => 'freq=daily; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,14; BYMINUTE=0,10,20,30,40,50',
                    enabled          => TRUE
                   );
                END;
                / 
                But now, I want to generate report automatically for snaps between 9:00 and 10:00, and between 14:00 and 15:00 (i.e two reports)
                Also, I have to mail these reports.


                Can anyone please help, how to implement this?



                Need to implement automatic report generation for the snaps taken at specified intervals.

                Edited by: Madhu.149 on Oct 12, 2012 3:06 PM

                Edited by: Madhu.149 on Oct 12, 2012 3:11 PM
                • 5. Re: Reg: Statspack schedule
                  EdStevens
                  Madhu.149 wrote:
                  I sheduled my statspack snap as below and it is working fine.
                  begin
                  dbms_scheduler.create_job (
                  job_name         => 'perfstat.statspack_snapshots',
                  job_type         => 'PLSQL_BLOCK',
                  job_action       => 'begin statspack.snap;end;',
                  repeat_interval  => 'freq=daily; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,14; BYMINUTE=0,10,20,30,40,50',
                  enabled          => TRUE
                  );
                  END;
                  / 
                  But now, I want to generate report automatically for snaps between 9:00 and 10:00, and between 14:00 and 15:00 (i.e two reports)
                  Also, I have to mail these reports.


                  Can anyone please help, how to implement this?



                  Need to implement automatic report generation for the snaps taken at specified intervals.

                  Edited by: Madhu.149 on Oct 12, 2012 3:06 PM

                  Edited by: Madhu.149 on Oct 12, 2012 3:11 PM
                  That won't be nearly as easy, and offhand I can't think of a way to do it. The standard way of producing the report is by executing $ORACLE_HOME/rdbms/admin/spreport.sql. It is interactive and asks for the beginning and ending snapshot id (after doing a SELECT to show the list of available ids). I guess I'd start by making a custom copy of that sql script, studying it, and look for clues about how you could automate the translation of a snapshot time to a snapshot id. Probably will take some PL/SQL programming.
                  • 6. Re: Reg: Statspack schedule
                    Madhu.149

                    I got the solution.

                    i.e by using vb-script for sending mail,batch program to generate statspack report and dbms_scheduler to take snaps