Installing,Configuring Statspack and Identifying issues

Now that we have AWR you may wonder do we still need statspack, well first of all you need an enterprise edition of the database and the diagnostic license. These are the steps to install statspack. Installing Statspack First create the tablespace to hold the Statspack tables. SQL> create tablespace stats_data datafile '+ASM' size 500M autoextend on maxsize 2G; Tablespace created. SQL> Run the ORACLE_HOME/rdbms/admin/spcreate.sql script to create the PERFSTAT schema. SQL> @spcreate.sql Choose the PERFSTAT user's password ----------------------------------- Not specifying a password will result in the installation FAILING Enter value for perfstat_password: Provide a password for the PERFSTAT user and hit enter. 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. Enter value for default_tablespace: STATS_DATA --Temp 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 Taking Snapshots SQL> l 1* select * from stats$level_description SQL> / 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 ad 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 To take the snapshot SQL> exec statspack.snap; PL/SQL procedure successfully completed. SQL> To take a snapshot at a different level: SQL> exec statspack.snap(i_snap_level=>10); PL/SQL procedure successfully completed. SQL> Automating snapshot collection Few ways you can do this: 1) If Linux, create a crontab script. 2) If Windows create a batch scripts and schedule a job. 3) Use the database jobs to schedule ie SQL> BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name => '30_perfstat.statspack', repeat_interval => 'FREQ=MINUTELY;BYMINUTE=10,40'); DBMS_SCHEDULER.CREATE_JOB( job_name => 'perfstat.sp_snapshot', job_type => 'STORED_PROCEDURE', job_action => 'perfstat.statspack.snap', schedule_name =>'30_perfstat.statspack’, comments =>’Collect Stats’); DBMS_SCHEDULER.ENABLE('perfstat.sp_snapshot'); END; / PL/SQL procedure successfully completed. SQL> Running a report There are two types a reports you can run: an Instance report and a SQL Report. ORACLE_HOME\rdbms\admin\spreport.sql is used to generate an instance level report. For an instance level report the beginning and ending snapshot id and the name of the output report are required. [oracle@server1 admin]$ sqlplus perfstat SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 27 15:52:31 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @spreport Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 906027225 DBlive 1 DBlive1 Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ----------- -------- ------------ ------------ ------------ 906027225 2 DBlive DBlive2 server2 906027225 1 DBlive DBlive1 server1 Using 906027225 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 ------------ ------------ --------- ----------------- ----- -------------------- DBlive1 DBlive 25428 07 Jan 2015 00:45 5 25429 07 Jan 2015 01:45 5 25430 07 Jan 2015 02:45 5 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 25428 Begin Snapshot Id specified: 25428 Enter value for end_snap: 25429 End Snapshot Id specified: 25429 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_25428_25429. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: Press RETURN Once you enter the name of the output file the Statspack report will be generated to the both the screen and the file. Identifying SQL If we look in the report we can see Top 5 Timed Events and see we have a lot of sequential read happening ie IO Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ db file sequential read 2,176,566 26,435 12 54.7 Here we can see the sql causing the IO and obtain the hash value SQL ordered by Reads DB/Inst: DBLIVE/dblive1 Snaps: 22805-22808 -> End Disk Reads Threshold: 1000 Total Disk Reads: 4,042,728 -> Captured SQL accounts for 99.0% of Total Disk Reads -> SQL reported below exceeded 1.0% of Total Disk Reads CPU Elapsd Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 973,296 12,321 79.0 24.1 104.02 13539.53 1034611734 SELECT /*+ ALL_ROWS */ T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED, T1.LAST_UPD_BY, T1.CREATED_BY, T1.MODIFICATION_NUM, T1.ROW_ID, T1.CTRL_GRP_FLG, T1.DCP_ID, T1.SRC_ID, T1.CONTACTED_DT, T sqlplus perfstat/** set long 80000 set pagesize 200 set linessize 200 SQL> l select SQL_FULLTEXT from v$sqlarea where OLD_HASH_VALUE=1034611734 SQL> / SQL_FULLTEXT -------------------------------------------------------------------------------- SELECT /*+ ALL_ROWS */ T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED, T1.LAST_UPD_BY, T1.CREATED_BY, T1.MODIFICATION_NUM, T1.ROW_ID, T1.CTRL_GRP_FLG, T1.DCP_ID, T1.SRC_ID, T1.CONTACTED_DT, T1.CON_PER_ID, T1.LST_DISTR_ID, T1.KEY_01, T1.KEY_02, T1.KEY_03, T1.KEY_04, T1.KEY_05, T1.KEY_06, T1.KEY_07, T1.LOAD_NUM, T1.CAMP_WAVE_ID, T1.BU_ID, T1.POSTN_ID, T1.PRSP_CON_PER_ID, T1.BATCH_NUM, T1.PR_CALL_LST_ID, T1.CAMP_CON_NUM, T1.TARGET_NUM FROM SIEBEL.S_CAMP_CON T1 WHERE (T1.CON_PER_ID = :1) Snapshot maintenance Remove a snapshot or a range of snapshots using ORACLE_HOME/rdbms/admin/sppurge.sql this will give you options which snapshots to remove. Remove all snapshots using ORACLE_HOME/rdbms/admin/sptrunc.sql Uninstall Statspack 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.

Leave a Reply

Your email address will not be published. Required fields are marked *