Thanks Tom. Clear answer as always.

Tom ,
By using :

perfstat@ABC> declare l_job number;
2 begin
3 dbms_job.submit(job => l_job,
4 what=> 'statspack.snap;',
5 next_date =>trunc(sysdate,'HH')+ 3/24/2,
6 interval=>'trunc(sysdate,''HH'')+3/24/2');
7 end;
8 /

I was able to run it every hour . Now how can I run it each half an hour if we want to do so ? I tried using different values for next_date/interval but could not make it work .

Thanks for your help .

that doesn't seem to run every hour - are you sure about that?

'trunc(sysdate,''HH'')+3/24/2'

that would back sysdate off to the top of the hour and then add 1/2 of 3/24.  3/24 is three hours, 1/2 of that is 1.5 hours?

you can use case in the interval, basically, you want to look at the current sysdate minutes and if >= 30 - add 1 hour to the trunc(sysdate,'hh') (to get the top of the next hour), else add 1/2 hour


ops$tkyte@ORA10GR2> select dt,
  2         case when to_char(dt,'mi')>=30
  3                  then 1/24
  4                          else 1/24/2
  5              end + trunc(dt,'hh') next_time
  6    from (select trunc(sysdate,'hh')+10/24/60*(rownum-1) dt
  7            from all_objects
  8                   where rownum <= 6)
  9  /

DT           NEXT_TIME
------------ ------------
18-apr 15:00 18-apr 15:30
18-apr 15:10 18-apr 15:30
18-apr 15:20 18-apr 15:30
18-apr 15:30 18-apr 16:00
18-apr 15:40 18-apr 16:00
18-apr 15:50 18-apr 16:00

6 rows selected.


in my case - you would be using sysdate instead of DT, I was using DT just to show that it "worked" - when it is 15:10 - this function returns 15:30 and so on (the next half hour) 

Thanks Tom . I was able to set it up and could see in dba_jobs that it has changed the next_date to half an hour interval .

The previous code was indeed being run every hour - I started it yesterday and here is the log from dbms_stats report:

*********************************************************
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
ABC ABC ..........................
..........................
15 17 Apr 2006 12:30 5
16 17 Apr 2006 13:30 5
17 17 Apr 2006 14:30 5
18 17 Apr 2006 15:30 5
19 17 Apr 2006 16:30 5
20 17 Apr 2006 17:30 5
21 17 Apr 2006 18:30 5
22 17 Apr 2006 19:30 5
23 17 Apr 2006 20:30 5
24 17 Apr 2006 21:30 5
25 17 Apr 2006 22:30 5
26 17 Apr 2006 23:30 5
27 18 Apr 2006 00:30 5
28 18 Apr 2006 01:30 5
29 18 Apr 2006 02:30 5
30 18 Apr 2006 03:30 5
31 18 Apr 2006 04:30 5
32 18 Apr 2006 05:30 5
33 18 Apr 2006 06:30 5
34 18 Apr 2006 07:30 5
35 18 Apr 2006 08:30 5
36 18 Apr 2006 09:30 5
37 18 Apr 2006 10:30 5
38 18 Apr 2006 11:30 5
39 18 Apr 2006 12:30 5
40 18 Apr 2006 13:30 5
41 18 Apr 2006 14:30 5
42 18 Apr 2006 15:30 5



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
***********************************************************

So it actually ran at every hour interval!


Thanks again for your help.

Hi Tom,
How about this one, will this work ??

variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/48,''MI'')', TRUE, :instno);
commit;
end;
/

Thanks
Manish

Hi Tom,
Yes you are right.

One clarification about slide, wouldn't this job run at every 1/2 hour like if it started at 2:22 then next runs would be at 2:52,3:22,3:52,4:22,4:52 and so on..
Am I right ???

Reason for not puting at whole hours like 22:00, 22:30 , as lot of times other jobs are scheduled like it, in order to distribute the server load.

Thanks
Manish

Hi Tom,
1. IF database get restarted between 2 statspack snap ids# 132,133 why I am not able to run the spreport between 132,133. It complains that database was restarted. Is this the expected behaviour and why ???

2. Why sometimes the snaps ids numbers gets skipped like this, please see in the end

Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
scrmg5 SCRMG5 151 20 Sep 2006 20:52 5
152 20 Sep 2006 21:22 5
153 20 Sep 2006 21:52 5
161 20 Sep 2006 22:22 5
162 20 Sep 2006 22:52 5
163 20 Sep 2006 23:22 5
164 20 Sep 2006 23:52 5
165 21 Sep 2006 00:22 5
166 21 Sep 2006 00:52 5
167 21 Sep 2006 01:22 5
168 21 Sep 2006 01:52 5
169 21 Sep 2006 02:22 5
170 21 Sep 2006 02:52 5
171 21 Sep 2006 03:22 5
172 21 Sep 2006 03:52 5
173 21 Sep 2006 04:22 5
174 21 Sep 2006 04:52 5
175 21 Sep 2006 05:22 5
176 21 Sep 2006 05:52 5
177 21 Sep 2006 06:22 5
178 21 Sep 2006 06:52 5
179 21 Sep 2006 07:22 5
180 21 Sep 2006 07:52 5
181 21 Sep 2006 08:22 5
182 21 Sep 2006 08:52 5

183 21 Sep 2006 09:22 5
184 21 Sep 2006 09:52 5

185 21 Sep 2006 10:22 5
195 21 Sep 2006 10:52 5

Thanks as always

Thanks

Hi Tom,
dbms_job.submit is a package, then why this procedure needs a commit after it ??

Thanks
Manish

statspack provide value information for performance tunning. Should we run it only during the tunning/analysis period say few hours or days and turn it off afterward. If we setup a cron job and run it in a busy databse, is there any netgtive impact or possibly increase the contension?

dear Tom, what is recommnded frequency of running statspack? (for oltp /batch app)

Is it ok to run statspack every 5 mins or 10 mins.

I personally think this is not necessary and it should be run less frequently (half or one hour)

-mk