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 *