DBA Hub

📋Steps in this guide1/11

STATSPACK Utility

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 … Continue reading STATSPACK →

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

Step 1: Overview Step 2: How to find statspack installed or not

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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.

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>
2

Section 2

Step 3: Install statspack Output Step 4: Available SNAP_LEVEL

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
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).

[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>
3

Section 3

Step 4: Available SNAP_LEVEL Step 5: Set timed_statistics to true Step 6: Taking STATSPACK snapshots

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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>

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;
4

Section 4

Step 6: Taking STATSPACK snapshots Step 7: List snapshots Step 8: Generate STATSPACK reports

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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>
5

Section 5

Step 8: Generate STATSPACK reports Output Step 9: How to find current SNAP level

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
[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

[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>
6

Section 6

Step 9: How to find current SNAP level Step 10: Modify SNAP LEVEL (Only if required) Step 11: Schedule automatic STATSPACK

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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> 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>
7

Section 7

Step 11: Schedule automatic STATSPACK every hour (default). Step 12: View the JOB

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
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
8

Section 8

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; 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 Step 13: Purge a snapshot
9

Section 9

Step 14: Uninstall Statspack Output

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
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.

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>
10

Section 10

Step 15: Other statspack scripts 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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
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
11

Section 11

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!