statspack scheduling

We want statspack to take snapshots every 15 minutes  excluding during the window starting midnight until 7am, at this h time we would like snapshots taken every 2 hours(midnight, 2, 4 and 6).
LVL 11
it-rexAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
All you need to know about dbms_scheduler is in the docs:
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_sched.htm#ARPLS72260

You probably want to set up a schedule with dbms_scheduler.create_schedule.  There is an example in that link.  Look for: Combining Schedules
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
What is your database 9i or 10g.
In 10g you can use dbms_workload_repository package with
a. dbms_schedule to schedule a job or
b. make entry in cronjob.
0
 
it-rexAuthor Commented:
a. dbms_schedule to schedule a job or
I need the code for that.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
it-rexAuthor Commented:
how about crontab?
0
 
Devinder Singh VirdiConnect With a Mentor Lead Oracle DBA TeamCommented:
For cronjob, you need to write small shell script that will call one sql file.
Put DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT.
call this shell file in cronjob like
0,15,30,45 8-23 * * * YOUR_SHELL_FILE.sh
0 0,2,4,6 * * *  YOUR_SHELL_FILE.sh

You are calling shell file two times to maintain two diff thing.

Why you want to take snapshot like that. Is there any Performance issue going on?
0
 
slightwv (䄆 Netminder) Commented:
>>how about crontab?

You can use it but do you really want to involve the OS to perform a database activity?  If you use dbms_scheduler, everything is contained inside the databsae.
0
 
it-rexAuthor Commented:
thanks all
0
All Courses

From novice to tech pro — start learning today.