Last active
February 8, 2019 14:17
-
-
Save fljdin/30eaadf02c8dcba67c93803ca2ce2615 to your computer and use it in GitHub Desktop.
Install statspack tools and shedules
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- exécuter un snapshot sur les DEUX INSTANCES | |
EXEC perfstat.statspack.snap; | |
variable jobno NUMBER; | |
BEGIN | |
FOR inst IN (SELECT dbid, inst_id AS id FROM gv$database) LOOP | |
STATSPACK.MODIFY_STATSPACK_PARAMETER( | |
i_snap_level => 7, | |
i_dbid => inst.dbid, | |
i_instance_number => inst.id | |
); | |
DBMS_JOB.SUBMIT( | |
job => :jobno, | |
what => 'perfstat.statspack.snap;', | |
next_date => TRUNC(sysdate,'HH') + INTERVAL '1' HOUR, | |
INTERVAL => 'INTERVAL ''1'' HOUR', | |
no_parse => TRUE, | |
instance => inst.id | |
); | |
COMMIT; | |
END LOOP; | |
END; | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Clustered instances | |
EXEC DBMS_SCHEDULER.DROP_JOB('perfstat.sp_snapshot_1'); | |
EXEC DBMS_SCHEDULER.DROP_JOB('perfstat.sp_snapshot_2'); | |
BEGIN | |
FOR inst IN (SELECT dbid, inst_id AS id FROM gv$database) LOOP | |
STATSPACK.MODIFY_STATSPACK_PARAMETER( | |
i_snap_level => 7, | |
i_dbid => inst.dbid, | |
i_instance_number => inst.id | |
); | |
DBMS_SCHEDULER.CREATE_JOB( | |
job_name => 'perfstat.sp_snapshot_' || inst.id, | |
job_type => 'STORED_PROCEDURE', | |
job_action => 'perfstat.statspack.snap', | |
start_date => TRUNC(sysdate,'HH') + INTERVAL '1' HOUR, | |
repeat_interval => 'FREQ=HOURLY', | |
enabled => TRUE, | |
auto_drop => FALSE, | |
comments => 'Statspack collection for instance #' || inst.id | |
); | |
DBMS_SCHEDULER.SET_ATTRIBUTE( | |
name => 'perfstat.sp_snapshot_' || inst.id, | |
attribute => 'instance_id', | |
VALUE => inst.id | |
); | |
END LOOP; | |
END; | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
BEGIN | |
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( | |
job_name => 'perfstat.sp_purge', | |
argument_position => 1, | |
argument_value => '30' | |
); | |
END; | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLESPACE perfstat DATAFILE '+DATA' SIZE 56M AUTOEXTEND ON MAXSIZE 1G; | |
define perfstat_password=****** | |
define default_tablespace=perfstat | |
define temporary_tablespace=temp | |
@?/rdbms/admin/spcreate | |
-- regenerate idle events | |
delete from STATS$IDLE_EVENT; | |
insert into STATS$IDLE_EVENT select name from V$EVENT_NAME where wait_class='Idle'; | |
commit; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- for both standalone and clustered instances | |
EXEC DBMS_SCHEDULER.DROP_JOB('perfstat.sp_purge'); | |
CREATE OR REPLACE PROCEDURE perfstat.sp_purge_auto (num_days INTEGER) IS | |
BEGIN | |
FOR inst IN (SELECT dbid, instance_number AS id FROM stats$database_instance) LOOP | |
perfstat.statspack.purge( | |
i_num_days => num_days, | |
i_dbid => inst.dbid, | |
i_instance_number => inst.id | |
); | |
END LOOP; | |
COMMIT; | |
END sp_purge_auto; | |
/ | |
BEGIN | |
DBMS_SCHEDULER.CREATE_JOB( | |
job_name => 'perfstat.sp_purge', | |
job_type => 'STORED_PROCEDURE', | |
job_action => 'perfstat.sp_purge_auto', | |
start_date => trunc(sysdate,'DD')+47/48, | |
repeat_interval => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=30', | |
enabled => FALSE, | |
auto_drop => FALSE, | |
comments => 'Statspack purge collection older than defined days' | |
); | |
DBMS_SCHEDULER.SET_ATTRIBUTE( | |
name => 'perfstat.sp_purge', | |
attribute => 'number_of_arguments', | |
VALUE => '1' | |
); | |
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( | |
job_name => 'perfstat.sp_purge', | |
argument_position => 1, | |
argument_value => '7' | |
); | |
DBMS_SCHEDULER.ENABLE(name=> 'perfstat.sp_purge'); | |
END; | |
/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Standalone instance | |
EXEC DBMS_SCHEDULER.DROP_JOB('perfstat.sp_snapshot'); | |
BEGIN | |
STATSPACK.SNAP(i_snap_level => 7, i_modify_parameter => 'true'); | |
DBMS_SCHEDULER.CREATE_JOB( | |
job_name => 'perfstat.sp_snapshot', | |
job_type => 'STORED_PROCEDURE', | |
job_action => 'perfstat.statspack.snap', | |
start_date => TRUNC(sysdate,'HH') + INTERVAL '1' HOUR, | |
repeat_interval => 'FREQ=HOURLY', | |
enabled => TRUE, | |
auto_drop => FALSE, | |
comments => 'Statspack collection' | |
); | |
END; | |
/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment