93 lines
3.8 KiB
SQL
93 lines
3.8 KiB
SQL
-- +----------------------------------------------------------------------------+
|
|
-- | Jeffrey M. Hunter |
|
|
-- | jhunter@idevelopment.info |
|
|
-- | www.idevelopment.info |
|
|
-- |----------------------------------------------------------------------------|
|
|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
|
|
-- |----------------------------------------------------------------------------|
|
|
-- | DATABASE : Oracle |
|
|
-- | FILE : sp_auto.sql |
|
|
-- | CLASS : Statspack |
|
|
-- | PURPOSE : This script is responsible to configuring a DBMS Job to be run |
|
|
-- | at the top of each hour to execute a Statspack snapshot. |
|
|
-- | NOTE : As with any code, ensure to test this script in a development |
|
|
-- | environment before attempting to run it in production. |
|
|
-- +----------------------------------------------------------------------------+
|
|
|
|
prompt
|
|
prompt =========================================================================
|
|
prompt The following script will create a new DBMS Job to be run at the top
|
|
prompt of each hour. The job will perform a Statspack snapshot using the
|
|
prompt Oracle supplied STATSPACK package.
|
|
prompt
|
|
prompt Note that this script should be run as the owner of the
|
|
prompt STATSPACK repository. (i.e. PERFSTAT)
|
|
prompt This script will prompt you for the PERFSTAT password.
|
|
prompt
|
|
prompt Also note that in order to submit and run a job, the init.ora parameter
|
|
prompt job_queue_processes must be set to a value greater than zero.
|
|
prompt =========================================================================
|
|
prompt
|
|
prompt Hit [ENTER] to continue or CTRL-C to cancel ...
|
|
pause
|
|
|
|
prompt Supply the password for the PERFSTAT user:
|
|
connect perfstat
|
|
|
|
|
|
-- +------------------------------------------------------------------------+
|
|
-- | SCHEDULE A SNAPSHOT TO BE RUN ON THIS INSTANCE EVERY HOUR, ON THE HOUR |
|
|
-- +------------------------------------------------------------------------+
|
|
|
|
VARIABLE jobno NUMBER;
|
|
VARIABLE instno NUMBER;
|
|
|
|
BEGIN
|
|
|
|
SELECT instance_number into :instno
|
|
FROM v$instance;
|
|
|
|
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
|
|
|
|
COMMIT;
|
|
|
|
END;
|
|
/
|
|
|
|
prompt
|
|
prompt
|
|
prompt +----------------------------------+
|
|
prompt | JOB NUMBER |
|
|
prompt |------------------------------------------------------------------+
|
|
prompt | The following job number should be noted as it will be required |
|
|
prompt | when modifying or removing prompt the job: |
|
|
prompt +------------------------------------------------------------------+
|
|
prompt
|
|
print jobno
|
|
|
|
|
|
prompt
|
|
prompt
|
|
prompt +----------------------------------+
|
|
prompt | JOB QUEUE PROCESS CONFIGURATION |
|
|
prompt |------------------------------------------------------------------+
|
|
prompt | Below is the current setting of the job_queue_processes init.ora |
|
|
prompt | parameter - the value for this parameter must be greater than 0 |
|
|
prompt | to use automatic statistics gathering: |
|
|
prompt +------------------------------------------------------------------+
|
|
prompt
|
|
show parameter job_queue_processes
|
|
|
|
prompt
|
|
prompt
|
|
prompt +----------------------------------+
|
|
prompt | NEXT SCHEDULED RUN |
|
|
prompt |------------------------------------------------------------------+
|
|
prompt | The next scheduled run for this job is: |
|
|
prompt +------------------------------------------------------------------+
|
|
prompt
|
|
SELECT job, next_date, next_sec
|
|
FROM user_jobs
|
|
WHERE job = :jobno;
|
|
|