Statspack 5-Automatic Task Planning (resend)

After statspack is correctly installed, we can set a scheduled task to start data collection. You can use spatuo. SQL to define automatic tasks.

Let's take a look at the key content of spauto. SQL:

 

Dbms_job.submit (: jobno, 'statspack. Snap ;', Trunc (sysdate + 1/24, 'hh '), 'trunc (sysdate + 1/24, ''hh '')', true,: instno );

 

This job defines the time interval for data collection:

A day has 24 hours, 1440 minutes, so:

1/24	HH every hour
1/48	Mi every 30 minutes
1/144	Mi every 10 minutes
1/288	Mi every five minutes

We can modify spauto. SQL to change the execution interval, for example:

 

Dbms_job.submit (: jobno, 'statspack. Snap ;', Trunc (sysdate + 1/48, 'mi'), 'trunc (sysdate + 1/48, ''mi'') ', true,: instno );

 

Then we execute spauto, so that we create a data collection plan that is executed every 30 minutes. You can view spauto. Lis to obtain the output information:

SQL> @ spauto PL/SQL procedure successfully completed.   Job number for automated statistics collection for this instance ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~ Note that this job number is needed when modifying or removing The job:   Jobno ---------- 28 Job Queue Process ~~~~~~~~~~~~~~~~~ Below is the current setting of the job_queue_processes init. ora Parameter-the value for this parameter must be greater Than 0 to use automatic statistics gathering:   Nametypevalue ----------------------------------------------------------------------------- Job_queue_processesinteger 5     Next scheduled run ~~~~~~~~~~~~~~~~~~ The next scheduled run for this job is:   Job next_date next_sec ----------------------------------- 28 15-aug-0316: 00: 00

 

We usually recommend that you set the sampling interval to 1 hour. For environments with special needs, you can set shorter values, such as half an hour as the sampling interval,
However, we do not recommend shorter periods. Because the execution of statspack consumes resources, for busy production systems, too short sampling will produce greater performance for the system.
(It may even cause statspack execution to occur in the sample data ).

 

From: http://www.eygle.com/statspack/statspack05.htm