Why Statspack
STATSPACK is a performance diagnosis tool, available since Oracle8i. STATSPACK is a diagnosis tool for instance-wide performance problems; it also supports application tuning activities by providing data which identifies high-load SQL statements. STATSPACK can be used both proactively to monitor the changing load on a system, and also reactively to investigate a performance problem.
Always set timed_statistics to true for our instance. Setting this parameter provides timing data which is invaluable for performance tuning.
How to Install and Configure STATSPACK
The installation process is simple and straightforward. It can be outlined as:
1. Connect to the database as sysdba and create the PERFSTAT/TOOLS Tablespace if the user doesn’t exit
a) sqlplus /nolog;
b) connect / as sysdba
c) create the PERFSTAT Tablespace as below
SQL> CREATE TABLESPACE statspack
DATAFILE ‘/u01/oracle/db01/SULFI_statspack.dbf’ SIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT ONLINE;
Now that the tablespace exists, we can begin the installation process of the STATSPACK software. Note that you must have performed the following before attempting to install STATSPACK.
$ cd $ORACLE_HOME/rdbms/admin$ sqlplus “/ as sysdba”
SQL> start spcreate.sql
Set the default tablespaces (the tablespaces must exist if not must be created):
a) define default_tablespace = ‘STATSPACK’
b) define temporary_tablespace = ‘TEMP’
Choose the PERFSTAT user’s password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: 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 <return> will result in STATSPACK’s recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: STATSPACK
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
.....
.....Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
2.) Set the timed_statistics = true:
Statistics are collected and stored in trace files or displayed in V$SESSTAT and V$SYSSTAT.
ALTER SESSION SET timed_statistics = true;
or
ALTER SYSTEM SET timed_statistics = true;
STATSPACK Data Collection
The STATSPACK utility comes with two options for controlling the collection of data, level and threshold. The type of the collected data is controlled by the “level” parameter whereas the “threshold” parameter filters the SQL statement collection into the stats$sql_summary table.
· Level 0 – This level captures general statistics including system events, SGA, rollback segment, background events, wait statistics, lock statistics, row cache, session events and latch information.
· Level 5 (default) – This level include the level 0 statistics and the capture of high resource usage SQL statements.
· Level 6 – Capture of SQL plan and plan usage information for the SQL statements with high resource e usage along with information from level 5.
· Level 7 – Segment level statistics including physical and logical reads, row lock and buffer busy waits along with the information from level 6.
· Level 10 – Child latch statistics.
To see the current level of the STATSPACK, you can use the following:
SELECT *FROM stats$level_description ORDER BY snap_level;
· Set the level of the STATSPACK parameter( In this example it is 5)
Exec statspack.snap(i_snap_level=>5, i_modify_parameter => ‘true’);
3.) Create, View and Delete Snapshots
sqlplus perfstat/perfstat
SQL> exec statspack.snap;
OR
SQL> exec statspack.snap(i_snap_level => 6, i_modify_parameter => ‘true’);
SQL> select name,snap_id,to_char(snap_time,’DD.MM.YYYY:HH24:MI:SS’) “Date/Time” from stats$snapshot,v$database;
NAME SNAP_ID Date/Time
--------- ---------- -------------------
CITPROD 3346 21.12.2011:13:45:00
CITPROD 3347 21.12.2011:14:00:01
CITPROD 3348 21.12.2011:14:15:00
CITPROD 3349 21.12.2011:14:30:00
CITPROD 3350 21.12.2011:14:45:01
CITPROD 3351 21.12.2011:15:00:01
CITPROD 3352 21.12.2011:15:15:01
CITPROD 3353 21.12.2011:15:30:01
CITPROD 3354 21.12.2011:15:45:01
CITPROD 3355 21.12.2011:16:00:01
CITPROD 3356 21.12.2011:16:15:00
CITPROD 3357 21.12.2011:16:30:01
CITPROD 3358 21.12.2011:16:45:01
CITPROD 3359 21.12.2011:17:00:00
CITPROD 3360 21.12.2011:17:15:00
CITPROD 3361 21.12.2011:17:30:01
CITPROD 3362 21.12.2011:17:45:01
CITPROD 3363 21.12.2011:18:00:01
CITPROD 3364 21.12.2011:18:15:00
CITPROD 3365 21.12.2011:18:30:00
CITPROD 3366 21.12.2011:18:45:00
CITPROD 3367 21.12.2011:19:00:01
CITPROD 3368 21.12.2011:19:15:00
CITPROD 3369 21.12.2011:19:30:00
CITPROD 3370 21.12.2011:19:45:00
CITPROD 3371 22.12.2011:07:00:01
CITPROD 3372 22.12.2011:07:15:00
CITPROD 3373 22.12.2011:07:30:01
CITPROD 3374 22.12.2011:07:45:00
CITPROD 3375 22.12.2011:08:00:01
CITPROD 3376 22.12.2011:08:15:01
CITPROD 3377 22.12.2011:08:30:00
CITPROD 3378 22.12.2011:08:45:01
22 rows selected.
4.) Create the Report
sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/spreport.sql
We have to provide at the prompt: BEGIN_SNAP, END_SNAP, REPORT_NAME (we can use a default value as well)
It shows as follows
Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
CITPROD CITPROD 3438 23 Dec 2011 10:45 5
3439 23 Dec 2011 11:00 5
3440 23 Dec 2011 11:15 5
3441 23 Dec 2011 11:30 5
3442 23 Dec 2011 11:45 5
3443 23 Dec 2011 12:00 5
3444 23 Dec 2011 12:15 5
3445 23 Dec 2011 12:30 5
3446 23 Dec 2011 12:45 5
3447 23 Dec 2011 13:00 5
3448 23 Dec 2011 13:15 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3438
Begin Snapshot Id specified: 3438
Enter value for end_snap: 3439
End Snapshot Id specified: 3439
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_3438_3439. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: myreport.txt
resource_limit TRUE
rollback_segments r01, r02, r03, r04, R05, r06, r07
sessions 445
shared_pool_size 800M
sort_area_retained_size 1048576
sort_area_size 4190208
timed_statistics TRUE
transactions 368
transactions_per_rollback_seg 34
user_dump_dest /Production/u01/admin/CITPROD/udum
utl_file_dir /Production/CITPROD/rf/log, /Pr
-------------------------------------------------------------
End of Report
SQL>
1. View snapshot details in the current database:
SELECT name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')"Date/Time", SESSION_ID, SERIAL# FROM stats$snapshot,v$database;
2. Gather statistics on PERFSTAT schema to create reports faster:
execute dbms_stats.gather_schema_stats('PERFSTAT',DBMS_STATS.AUTO_SAMPLE_SIZE);
or
execute dbms_stats.gather_schema_stats('PERFSTAT');
3. Delete old snapshot:
@?/rdbms/admin/sppurge; (after that Enter the Lower and Upper Snapshot ID )
Enjoy The Spirit of Learning J.
Post your comments please
Contact me on sulfi4u@gmail.com for further clarifications. Thanks for reading.