One can automate taking of snapshots of STATSPACK by executing DBMS_JOB or by scheduling it through crontab.
Schedule automatic STATSPACK through DBMS_JOB – By executing @?/rdbms/admin/spauto.sql it will schedule to run statspack once every hour which is the default.
Output:
SQL> connect perstat@TESTDB
Password: **********
SQL> @?/rdbms/admin/spauto.sql
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
———-
2
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:
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 1000
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC
———- ——— ——–
2 27-DEC-11 19:00:00
One can view the job by executing the following SQL.
SQL> select * FROM User_jobs WHERE job = 2;
JOB LOG_USER PRIV_USER
———- —————————— ——————————
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
—————————— ——— ——– ——— ——– ———
NEXT_SEC TOTAL_TIME B
——– ———- –
INTERVAL
——————————————————————————–
FAILURES
———-
WHAT
——————————————————————————–
NLS_ENV
——————————————————————————–
MISC_ENV INSTANCE
—————————————————————- ———-
1 SYS SYS
SYS 27-DEC-11
19:00:00 0 N
trunc(SYSDATE+1/24,'HH')
statspack.snap;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000202000000 1
To change the snap interval one can execute dbms_job.interval in the example below it changes to collect every 1/2 hr
SQL> exec dbms_job.interval(1, 'trunc(SYSDATE+1/48,''HH'')');
PL/SQL procedure successfully completed.
SQL> select * FROM User_jobs;
JOB LOG_USER PRIV_USER
———- —————————— ——————————
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
—————————— ——— ——– ——— ——– ———
NEXT_SEC TOTAL_TIME B
——– ———- –
INTERVAL
——————————————————————————–
FAILURES
———-
WHAT
——————————————————————————–
NLS_ENV
——————————————————————————–
MISC_ENV INSTANCE
—————————————————————- ———-
1 SYS SYS
SYS 27-DEC-11
19:00:00 0 N
trunc(SYSDATE+1/48,'HH')
statspack.snap;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000202000000 1
alexlima.com 