STATSPACK Utility
Step 1: Overview
Step 2: How to find statspack installed or not
Step 3: Install statspack
Step 4: Available SNAP_LEVEL
Step 5: Set timed_statistics to true
Step 6: Taking STATSPACK snapshots
Step 7: List snapshots
Step 8: Generate STATSPACK reports
Step 9: How to find current SNAP level
Step 10: Modify SNAP_LEVEL (Only if required)
Step 11: Schedule automatic STATSPACK
Step 12: View the JOB
Step 13: Purge a snapshot
Step 14: Uninstall Statspack
Step 15: Other statspack scripts
STATSPACK is a performance diagnosis tool, available since Oracle8i: Oracle 8.1.6 introduced statspack as a replacement for the UTLBSTAT/UTLESTAT scripts.
The Statspack package is a set of SQL, PL/SQL and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data.
Using Statspack we can collect statistics which are put in specific tables.
When we need, we can run reports based on these tables (snapshots) to tune the database.
Please note Statspack makes data collection easy to automate using either DBMS_JOB or an operating system utility to schedule collection tasks.
Step 2: How to find statspack installed or not
SQL> select * from stats$level_description; select * from stats$level_description * ERROR at line 1: ORA-00942: table or view does not exist <-- STATSPACK not installed before SQL>
SQL> CREATE TABLESPACE perfstat DATAFILE '/u01/app/oracle/product/10.2.0/db_1/oradata/w148p/perfstat.dbf' SIZE 500M autoextend on maxsize 2G; 2 3 Tablespace created. SQL> cd $ORACLE_HOME/rdbms/admin sqlplus / as sysdba @spcreate.sql -- Enter tablespace names when prompted -- Please note user PERFSTAT automatically will get create. No need to create manually(tested in 10gR2).
Output
[oracle@rac1 ~]$ cd $ORACLE_HOME/rdbms/admin [oracle@rac1 admin]$ ls -ltr spcreate.sql -rw-r--r-- 1 oracle oinstall 861 May 17 2002 spcreate.sql [oracle@rac1 admin]$ [oracle@rac1 admin]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Thu Oct 27 13:05:56 2016 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @spcreate.sql <--- Choose the PERFSTAT user's password ----------------------------------- Not specifying a password will result in the installation FAILING Enter value for perfstat_password: PERFSTAT <---- PERFSTAT 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 ------------------------------ --------- ---------------------------- PERFSTAT PERMANENT SYSAUX PERMANENT * USERS PERMANENT Pressing will result in STATSPACK's recommended default tablespace (identified by *) being used. Enter value for default_tablespace: PERFSTAT <---- Using tablespace PERFSTAT 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 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. ... --- trimed text content --- trimed text content ... NOTE: SPCPKG complete. Please check spcpkg.lis for any errors. SQL>
There are five snap shot levels (0, 5, 6, 7, 10) with 5 being the default. SQL> set pages 999 lines 180 SQL> col DESCRIPTION for a60 SQL> select * from stats$level_description; SNAP_LEVEL DESCRIPTION ---------- ------------------------------------------------------------ 0 This level captures general statistics, including rollback s egment, row cache, SGA, system events, background events, se ssion events, system statistics, wait statistics, lock stati stics, and Latch information 5 This level includes capturing high resource usage SQL Statem ents, along with all data captured by lower levels 6 This level includes capturing SQL plan and SQL plan usage in formation for high resource usage SQL Statements, along with all data captured by lower levels 7 This level captures segment level statistics, including logi cal 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 5 rows selected. SQL>
Step 5: Set timed_statistics to true
SQL> show parameter timed_statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
ALTER SYSTEM SET timed_statistics = true;
or
ALTER SESSION SET timed_statistics = true;
Step 6: Taking STATSPACK snapshots
exec statspack.snap; <---- Default Level 5 -- Run your job --- exec statspack.snap; -- OR -- SQL> exec statspack.snap(i_snap_level => 7); PL/SQL procedure successfully completed. SQL> -- Run your job --- SQL> exec statspack.snap(i_snap_level => 7); PL/SQL procedure successfully completed. SQL> -- OR -- For a particular session, in this case SID = 158 exec statspack.snap(i_session_id=>158);
SQL> select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS')
"Date/Time" from stats$snapshot,v$database; 2
NAME SNAP_ID Date/Time
--------- ---------- --------------------
W148P 1 27-OCT-2016:13:25:41
W148P 2 27-OCT-2016:13:48:01
2 rows selected.
SQL>
Step 8: Generate STATSPACK reports
[oracle@rac1 admin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@rac1 admin]$ ls -ltr spreport.sql
-rw-r--r-- 1 oracle oinstall 1284 Apr 23 2001 spreport.sql
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL> @spreport.sql
-- Please note it will create the file w148p_snap_1_2.txt on current directory
Output
[oracle@rac1 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/ [oracle@rac1 admin]$ sqlplus / as sysdba SQL> @spreport.sql <----- Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 3175692859 W148P 1 w148p Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ----------- -------- ------------ ------------ ------------ 3175692859 1 W148P w148p rac1.rajasek har.com Using 3175692859 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 without specifying a number lists all completed snapshots. Listing all Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level Comment ------------ ------------ --------- ----------------- ----- -------------------- w148p W148P 1 27 Oct 2016 13:25 7 2 27 Oct 2016 13:48 7 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 1 <---- Begin Snapshot Id specified: 1 Enter value for end_snap: 2 <---- End Snapshot Id specified: 2 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_1_2. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: w148p_snap_1_2.txt Using the report name w148p_snap_1_2.txt STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 3175692859 w148p 1 27-Oct-16 12:40 10.2.0.5.0 NO Host Name: rac1.rajasekhar. Num CPUs: 1 Phys Memory (MB): 3,013 ~~~~ Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------- Begin Snap: 1 27-Oct-16 13:25:41 17 3.9 End Snap: 2 27-Oct-16 13:48:01 18 5.1 Elapsed: 22.33 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 648M Std Block Size: 8K Shared Pool Size: 212M Log Buffer: 5,954K Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- Redo size: 1,504.88 224,059.56 Logical reads: 16.82 2,504.00 Block changes: 5.33 793.56 Physical reads: 0.10 15.56 Physical writes: 1.12 166.33 User calls: 0.04 5.22 Parses: 0.93 138.11 Hard parses: 0.09 13.11 Sorts: 0.57 85.56 Logons: 0.01 1.11 Executes: 2.23 332.33 Transactions: 0.01 % Blocks changed per Read: 31.69 Recursive Call %: 99.83 Rollback per transaction %: 0.00 Rows per Sort: 38.26 Instance Efficiency Percentages ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.99 Redo NoWait %: 100.00 Buffer Hit %: 99.38 In-memory Sort %: 100.00 Library Hit %: 92.10 Soft Parse %: 90.51 Execute to Parse %: 58.44 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 125.00 % Non-Parse CPU: 96.24 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 44.32 46.56 % SQL with executions>1: 47.98 62.40 % Memory for SQL w/exec>1: 48.77 66.29 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ CPU time 1 62.7 db file sequential read 145 0 2 12.6 log file parallel write 92 0 2 9.5 control file parallel write 486 0 0 8.4 os thread startup 6 0 13 3.8 ------------------------------------------------------------- Host CPU (CPUs: 1) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 0.00 0.00 0.41 0.42 99.14 0.05 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 0.13 % of busy CPU for Instance: 15.45 %DB time waiting for CPU - Resource Mgr: Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): 3,012.6 3,012.6 SGA use (MB): 876.0 876.0 PGA use (MB): 49.1 51.5 % Host Mem used for SGA+PGA: 30.7 30.8 ------------------------------------------------------------- ... --- trimed text content --- trimed text content ... End of Report ( w148p_snap_1_2.txt ) SQL>
Step 9: How to find current SNAP level
SQL> select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;
SNAP_ID SNAP_LEVEL
---------- ----------
1 7
2 7
3 7
4 7
4 rows selected.
Step 10: Modify SNAP LEVEL (Only if required)
SQL> select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;
SNAP_ID SNAP_LEVEL
---------- ----------
1 7
2 7
3 7
4 7
4 rows selected.
SQL> exec statspack.modify_statspack_parameter(i_snap_level=>10, i_modify_parameter=>'true');
PL/SQL procedure successfully completed.
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;
SNAP_ID SNAP_LEVEL
---------- ----------
1 7
2 7
3 7
4 7
5 10 <--- snap level changed
5 rows selected.
SQL>
Step 11: Schedule automatic STATSPACK
The collection of system snapshots can be automated with the DBMS_JOB package. The spauto.sql script can be used to schedule system snapshot collections on the hour, every hour (default). $ORACLE_HOME/rdbms/admin/spauto.sql [oracle@rac1 admin]$ pwd /u01/app/oracle/product/10.2.0/db_1/rdbms/admin [oracle@rac1 admin]$ sqlplus PERFSTAT/PERFSTAT; <--- SQL> @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 ---------- 1 <--- Please note this 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 10 Next scheduled run ~~~~~~~~~~~~~~~~~~ The next scheduled run for this job is: JOB NEXT_DATE NEXT_SEC ---------- --------- -------- 1 27-OCT-16 18:00:00 SQL>
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; SQL> set lines 180 SQL> col SCHEMA_USER for a20 SQL> col INTERVAL for a30 SQL> col WHAT for a30 SQL> select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=1; JOB SCHEMA_USER INTERVAL B WHAT ---------- -------------------- ------------------------------ - ------------------------------ 1 PERFSTAT trunc(SYSDATE+1/24,'HH') N statspack.snap; SQL> SQL> select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database; NAME SNAP_ID Date/Time --------- ---------- -------------------- .. W148P 5 27-OCT-2016:17:00:58 W148P 6 27-OCT-2016:18:00:01 W148P 7 27-OCT-2016:19:02:40
REM Change the snap interval from 1 hr to 1/2 hour <— please test before use
REM SQL> exec dbms_job.interval(1, ‘trunc(SYSDATE+1/48,”HH”)’);
REM
—- FYI —-
REM — Every one hour from now: sysdate+1/24
REM — Every 1/2 hour from now: sysdate+1/48
REM — Every 15 Minuts from now: sysdate+15/1440
REM — Gather STATS FOR PERFSTAT SCHMEA TO SEE CHANGES —
conn PERFSTAT / PERFSTAT
select JOB, LOG_USER, LAST_DATE, NEXT_DATE, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=1;
Remove a range of snapshots using ORACLE_HOME/rdbms/admin/sppurge.sql -- Remove all snapshots using ORACLE_HOME/rdbms/admin/sptrunc.sql --- SQL> @sppurge.sql Database Instance currently connected to ======================================== Instance DB Id DB Name Inst Num Name ----------- ---------- -------- ---------- 3175692859 W148P 1 w148p Snapshots for this database instance ==================================== Base- Snap Snap Id Snapshot Started line? Level Host Comment -------- --------------------- ----- ----- --------------- -------------------- 1 27 Oct 2016 16:58:00 7 rac1.rajasekhar 2 27 Oct 2016 16:58:52 7 rac1.rajasekhar 3 27 Oct 2016 17:00:19 7 rac1.rajasekhar 4 27 Oct 2016 17:00:21 7 rac1.rajasekhar 5 27 Oct 2016 17:00:58 10 rac1.rajasekhar 6 27 Oct 2016 18:00:01 10 rac1.rajasekhar 7 27 Oct 2016 19:02:40 10 rac1.rajasekhar 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: 4 Using 4 for upper bound. Deleting snapshots 1 - 4. <---- Number of Snapshots purged: 4 <--- selected 4 snapshots deleted. ~~~~~~~~~~~~~~~~~~~~~~~~~~~ Purge of specified Snapshot range complete. SQL> SQL> select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database; 2 NAME Snap Id Date/Time --------- -------- -------------------- W148P 5 27-OCT-2016:17:00:58 W148P 6 27-OCT-2016:18:00:01 W148P 7 27-OCT-2016:19:02:40 6 rows selected. SQL>
If you decide you do not need Statspack installed any more you can remove/uninstall Statspack with ORACLE_HOME/rdbms/admin/spdrop.sql
The spdrop.sql needs be dropped by a user with SYSDBA.
Remember to remove any jobs you might have created to manage the Statspack environment.
Output
SQL> @spdrop.sql <----
Dropping old versions (if any)
Synonym dropped.
Sequence dropped.
Synonym dropped.
Table dropped.
..
..
..
User dropped.
NOTE:
SPDUSR complete. Please check spdusr.lis for any errors.
SQL>
SQL>
SQL>
Step 15: Other statspack scripts
Some of the other statspack scripts are: sppurge.sql - Purge (delete) a range of Snapshot Id's between the specified begin and end Snap Id's spauto.sql - Schedule a dbms_job to automate the collection of STATPACK statistics spcreate.sql - Installs the STATSPACK user, tables and package on a database (Run as SYS). spdrop.sql - Deinstall STATSPACK from database (Run as SYS) spreport.sql - Report on differences between values recorded in two snapshots sptrunc.sql - Truncates all data in Statspack tables
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Reference:
http://www.orafaq.com/faq/how_does_one_add_a_day_hour_minute_second_to_a_date_value
http://www.orafaq.com/wiki/Statspack
Thank you,
Rajasekhar Amudala