Vytvoření tablespace
create tablespace statspack_data datafile 'D:\Oracle\oradata\E1DBP01\statspack_data01.dbf' size 500M autoextend on maxsize 2G extent management local uniform size 1M segment space management auto;
Založení tabulek Statspacku
SQL> @$ORACLE_HOME\rdbms\admin\spcreate.sql
Choose the PERFSTAT user's password ----------------------------------- Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: oracle_4U oracle_4U Choose the Default tablespace for the PERFSTAT user --------------------------------------------------- Below is the list of online tablespaces in this database which can store user data. Specifying the SYSTEM tablespace for the user's default tablespace will result in the installation FAILING, as using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace. This is the tablespace in which the STATSPACK tables and indexes will be created.
TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE ------------------------------ --------- ---------------------------- ASHSTAT PERMANENT DD910I PERMANENT DD910T PERMANENT STATSPACK_DATA PERMANENT ... SYSAUX PERMANENT * USERS PERMANENT
Pressing <return> will result in STATSPACK's recommended default tablespace (identified by *) being used.
Enter value for default_tablespace: STATSPACK_DATA
Using tablespace STATSPACK_DATA as PERFSTAT default tablespace. Choose the Temporary tablespace for the PERFSTAT user ----------------------------------------------------- Below is the list of online tablespaces in this database which can store temporary data (e.g. for sort workareas). Specifying the SYSTEM tablespace for the user's temporary tablespace will result in the installation FAILING, as using SYSTEM for workareas is not supported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE ------------------------------ --------- -------------------------- TEMP TEMPORARY *
Pressing <return> will result in the database's default Temporary tablespace (identified by *) being used.
Enter value for temporary_tablespace: TEMP
Using tablespace TEMP as PERFSTAT temporary tablespace. ... Creating PERFSTAT user ... Installing required packages ... Creating views ... Granting privileges
NOTE: SPCUSR complete. Please check spcusr.lis for any errors.
SQL> SQL> -- SQL> -- Build the tables and synonyms SQL> connect perfstat/&&perfstat_password Connected. SQL> @@spctab SQL> Rem SQL> Rem $Header: rdbms/admin/spctab.sql /st_rdbms_11.2.0/1 2010/08/13 10:06:01 kchou Exp $ SQL> Rem SQL> Rem spctab.sql SQL> Rem SQL> Rem Copyright (c) 1999, 2010, Oracle and/or its affiliates. SQL> Rem All rights reserved. SQL> Rem SQL> Rem NAME SQL> Rem spctab.sql SQL> Rem SQL> Rem DESCRIPTION SQL> Rem SQL*PLUS command file to create tables to hold SQL> Rem start and end "snapshot" statistical information SQL> Rem SQL> Rem NOTES SQL> Rem Should be run as STATSPACK user, PERFSTAT SQL> set showmode off echo off;
If this script is automatically called from spcreate (which is the supported method), all STATSPACK segments will be created in the PERFSTAT user's default tablespace.
Using STATSPACK_DATA tablespace to store Statspack objects
... Creating STATS$SNAPSHOT_ID Sequence
Sequence created. Synonym created.
... Creating STATS$... tables
Table created.
SQL> set echo off; Creating Package STATSPACK...
Package created.
No errors. Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE: SPCPKG complete. Please check spcpkg.lis for any errors.
Nastavení snapshotů
SQL> select * from stats$level_description;
SNAP_LEVEL ---------- DESCRIPTION -------------------------------------------------------------------------------- 0 This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information 5 This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels 6 This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels 7 This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels 10 This level includes capturing Child Latch statistics, along with all data captured by lower levels
SQL> exec statspack.snap(i_snap_level => 7);
PL/SQL procedure successfully completed.
Nebo změna nastavení
SQL> exec statspack.modify_statspack_parameter(i_snap_level=>6, i_modify_parameter=>'true');
PL/SQL procedure successfully completed.
Nastavení snapshotů každou hodinu
SQL> variable jobno number;
SQL> variable instno number;
SQL> BEGIN
2 SELECT instance_number INTO :instno FROM v$instance;
3 DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', TRUNC(sysdate+1/24,'HH'), 'TRUNC(SYSDATE+1/24,''HH'')', TRUE, :instno);
4 COMMIT; 5 END; 6 / PL/SQL procedure successfully completed.
Nastavení snapshotů každých 20 minut
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name => 'perfstat.statspack_every20',
repeat_interval => 'FREQ=MINUTELY;BYMINUTE=10,30,50');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_SCHEDULER.CREATE_JOB( job_name => 'perfstat.sp_snapshot',
job_type => 'STORED_PROCEDURE',
job_action => 'perfstat.statspack.snap',
schedule_name => 'perfstat.statspack_every20',
comments => 'Statspack collection');
DBMS_SCHEDULER.ENABLE('perfstat.sp_snapshot');
END;
/
PL/SQL procedure successfully completed.
SQL> set linesize 130;
SQL> col what for a30;
SQL> col log_user for a12;
SQL> col interval for a30;
SQL> select job,what,log_user,
2 to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "Sysdate",
3 to_char(next_date,'dd-mon-yyyy hh24:mi:ss') "Next Date",
4 interval
5 from user_jobs;
JOB WHAT LOG_USER Sysdate Next Date INTERVAL ---------- ------------------------------ ------------ -------------------- -------------------- ------------------------------ 103 statspack.snap; PERFSTAT 08-dec-2014 10:00:44 08-dec-2014 11:00:00 TRUNC(SYSDATE+1/24,'HH')
1 row selected.
Generování jednoho reportu
SQL> connect perfstat/******
Connected. SQL> EXEC STATSPACK.snap;
Generování reportu
SQL> @spreport.sql SQL> Rem SQL> Rem $Header: spreport.sql 22-apr-2001.15:44:01 cdialeri Exp $ SQL> Rem SQL> Rem spreport.sql SQL> Rem SQL> Rem Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved. SQL> Rem SQL> Rem NAME SQL> Rem spreport.sql SQL> Rem SQL> Rem DESCRIPTION SQL> Rem This script defaults the dbid and instance number to that of the SQL> Rem current instance connected-to, then calls sprepins.sql to produce SQL> Rem the standard Statspack report. SQL> Rem SQL> Rem NOTES SQL> Rem Usually run as the STATSPACK owner, PERFSTAT SQL> Rem SQL> Rem MODIFIED (MM/DD/YY) SQL> Rem cdialeri 03/20/01 - 1747076 SQL> Rem cdialeri 03/12/01 - Created Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host ----------- -------- ------------ ------------ ------------ 4263223448 1 E1DBP01 e1dbp01 E1DBP01
Using 4263223448 for database Id Using 1 for instance number
Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots.
Listing all Completed Snapshots
Snap Instance DB Name Snap Id Snap Started Level Comment ------------ ------------ --------- ----------------- ----- -------------------- e1dbp01 DBPROD 1 08 Dec 2014 09:48 6 2 08 Dec 2014 09:55 7 3 08 Dec 2014 10:00 6 11 08 Dec 2014 11:00 6
Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 1 Begin Snapshot Id specified: 1
Enter value for end_snap: 11 End Snapshot Id specified: 11
Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_1_11. To use this name, press <return> to continue, otherwise enter an alternative.
Enter value for report_name: sp_report_8_12.txt
Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest C:\ORACLE\ADMIN\DBPROD\ADUMP audit_trail DB compatible 11.2.0.0.0 control_files D:\ORACLE\ORADATA\DBPROD\CONTROL 01.CTL, C:\ORACLE\FAST_RECOVERY_A REA\DBPROD\CONTROL02.CTL db_block_size 8192 db_domain db_name DBPROD db_recovery_file_dest f:\oracle\fra db_recovery_file_dest_size 322122547200 diagnostic_dest C:\ORACLE dispatchers (PROTOCOL=TCP) (SERVICE=E1DBP01XD B) filesystemio_options SETALL job_queue_processes 1000 memory_max_target 10737418240 memory_target 10737418240 nls_sort BINARY open_cursors 300 pga_aggregate_target 0 processes 600 remote_login_passwordfile EXCLUSIVE sessions 924 sga_target 0 undo_tablespace UNDOTBS1 -------------------------------------------------------------
End of Report ( sp_report_8_12.txt )
Údržba snímků – vymazání
SQL> @sppurge
Database Instance currently connected to ======================================== ♀ Instance DB Id DB Name Inst Num Name ----------- ---------- -------- ---------- 4263223448 DBPROD 1 dbserver
Snapshots for this database instance ==================================== ♀ Base- Snap Snap Id Snapshot Started line? Level Host -------- --------------------- ----- ----- --------------- Comment -------------------- 1 08 Dec 2014 09:48:31 6 dbserver
2 08 Dec 2014 09:55:57 7 dbserver
3 08 Dec 2014 10:00:00 6 dbserver
11 08 Dec 2014 11:00:00 6 dbserver
Warning ~~~~~~~ sppurge.sql deletes all snapshots ranging between the lower and upper bound Snapshot Id's specified, for the database instance you are connected to. Snapshots identified as Baseline snapshots which lie within the snapshot range will not be purged.
It is NOT possible to rollback changes once the purge begins.
You may wish to export this data before continuing.
Specify the Lo Snap Id and Hi Snap Id range to purge ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for losnapid: 1 Using 1 for lower bound.
Enter value for hisnapid: 2 Using 2 for upper bound.
Deleting snapshots 1 - 2. ♀Number of Snapshots purged: 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~ Purge of specified Snapshot range complete.
Vymazání všech snímků
@sptrunc.sql
Odninstalování Statspacku
@spdrop.sql