How to install, configure, and use Stats pack in Oracle. During this article, we’ll see the way to install, configure, and use Statspack in Oracle Database. Here we will do this activity in Oracle 19c Database.
What is Statspack in Oracle?
The collected Data are analyzed using the Statspack reports, which has an instance health and load summary page, high resource SQL statements, and therefore the traditional wait events and initialization parameters in Oracle Database.
Once you run the Statspack installation script, it will automatically created the PERFSTAT user.
Required Space for Statspack:
Minimum 64MB space is required to install Statspack.
The required space of database for the Statspack package installation, it will depends on the frequency of snapshots, the size of the database and instance, and the amount of data collected, which can be configured.
Firstly, Before installing Statspack I always wish to Create a separate tablespace for the STATSPACK user:
SQL> create tablespace PERFSTAT datafile '/u01/app/oracle/oradata/PROD/PERFSTAT01.dbf' size 100m autoextend on maxsize 5G;
Tablespace created.
SQL>
SQL> select FILE#,name from v$datafile;
FILE# NAME
---------- ------------------------------------------------------------
1 /u01/app/oracle/oradata/PROD/system01.dbf
3 /u01/app/oracle/oradata/PROD/sysaux01.dbf
4 /u01/app/oracle/oradata/PROD/undotbs01.dbf
5 /u01/app/oracle/oradata/PROD/PERFSTAT01.dbf
7 /u01/app/oracle/oradata/PROD/users01.dbf
SQL>Secondly, run the below script necessary to make the PERFSTAT user and therefore the objects:
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
Session altered.
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: perfstat
<----Enter password for perfstat user
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: PERFSTAT
<--- Enter tablespace name
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 <return> will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace: TEMP
<--- Enter Temporary tablespace name
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.
SQL>
SQL> -- Next two scripts run as perfstat user
SQL> ALTER SESSION SET CURRENT_SCHEMA = PERFSTAT;
Session altered.
SQL>
SQL> -- Create statspack tables
SQL> @@spctab
SQL> Rem
SQL> Rem $Header: rdbms/admin/spctab.sql /main/56 2017/05/28 22:46:10 stanaya Exp $
SQL> Rem
SQL> Rem spctab.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2017, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem spctab.sql
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*PLUS command file to create tables to hold
SQL> Rem start and end "snapshot" statistical information
SQL> Rem
SQL> Rem NOTES
SQL> Rem Should be run as STATSPACK user, PERFSTAT
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/spctab.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/spctab.sql
SQL> Rem SQL_PHASE: UTILITY
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem pjotawar 09/16/16 - Bug 23481673 Support Integrated Replicat
SQL> Rem zhefan 11/06/14 - Bug #19933671
SQL> Rem pmurthy 02/20/14 - To Fix Bug - 18284201 and 18273117
SQL> Rem kchou 10/30/13 - Bug# 17504669: Add New Column
SQL> Rem remaster_type to STATS$DYNAMIC_REMASTER_STATS
SQL> Rem shsong 06/29/11 - shsong 06/28/11 - Bug 12702106: display
SQL> Rem v$IOSTAT_FUNCTION_DETAIL
SQL> Rem traney 04/06/11 - 35209: long identifiers dictionary upgrade
SQL> Rem kchou 01/10/11 - Forward Merge of Bug Fix 9800868 to 12.1 Mainline
SQL> Rem kchou 08/11/10 - Bug#9800868 - Add Missing Idle Events for
SQL> Rem 11.2.0.2for Statspack & Standby Statspack
SQL> Rem kchou 08/11/10 - Bug#9800868 - Add missing idle events to 11.2.0.2
SQL> Rem kchou 01/10/11 - XbranchMerge kchou_bug-9800868 from
SQL> Rem st_rdbms_11.2.0
SQL> Rem cgervasi 05/13/09 - add idle event: cell worker idle
SQL> Rem cgervasi 04/02/09 - bug8395154: missing idle events
SQL> Rem rhlee 02/22/08 -
> Rem cdgreen 03/14/07 - 11 F2
SQL> Rem shsong 06/14/07 - Add idle events
SQL> Rem cdgreen 02/28/07 - 5908354
SQL> Rem cdgreen 04/26/06 - 11 F1
SQL> Rem cdgreen 06/26/06 - Increase column length
SQL> Rem cdgreen 05/10/06 - 5215982
SQL> Rem cdgreen 05/24/05 - 4246955
SQL> Rem cdgreen 04/18/05 - 4228432
SQL> Rem cdgreen 03/08/05 - 10gR2 misc
SQL> Rem vbarrier 02/18/05 - 4081984
SQL> Rem cdgreen 10/29/04 - 10gR2_sqlstats
SQL> Rem cdgreen 07/16/04 - 10gR2
SQL> Rem cdialeri 03/25/04 - 3516921
SQL> Rem vbarrier 02/12/04 - 3412853
SQL> Rem cdialeri 12/04/03 - 3290482
SQL> Rem cdialeri 11/05/03 - 3202706
SQL> Rem cdialeri 10/14/03 - 10g - streams - rvenkate
SQL> Rem cdialeri 08/05/03 - 10g F3
SQL> Rem cdialeri 02/27/03 - 10g F2: baseline, purge
SQL> Rem vbarrier 02/25/03 - 10g RAC
SQL> Rem cdialeri 11/15/02 - 10g F1
SQL> Rem cdialeri 09/27/02 - sleep4
SQL> Rem vbarrier 03/20/02 - 2143634
SQL> Rem vbarrier 03/05/02 - Segment Statistics
SQL> Rem cdialeri 02/07/02 - 2218573
SQL> Rem cdialeri 01/30/02 - 2184717
SQL> Rem cdialeri 01/11/02 - 9.2 - features 2
SQL> Rem cdialeri 11/30/01 - 9.2 - features 1
SQL> Rem cdialeri 04/22/01 - Undostat changes
SQL> Rem cdialeri 03/02/01 - 9.0
SQL> Rem cdialeri 09/12/00 - sp_1404195
SQL> Rem cdialeri 04/07/00 - 1261813
SQL> Rem cdialeri 03/20/00 - Support for purge
SQL> Rem cdialeri 02/16/00 - 1191805
SQL> Rem cdialeri 01/26/00 - 1169401
SQL> Rem cdialeri 11/01/99 - Enhance, 1059172
SQL> Rem cmlim 07/17/97 - Added STATS$SQLAREA to store top sql stmts
SQL> Rem gwood 10/16/95 - Version to run as sys without using many views
SQL> Rem cellis.uk 11/15/89 - Created
SQL> Rem
SQL>
SQL> set showmode off echo off;
If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.
Using PERFSTAT tablespace to store Statspack objects
... Creating STATS$SNAPSHOT_ID Sequence
Sequence created.
Synonym created.
... Creating STATS$... tables
Table created.
Synonym created.
Table created.
1 row created.
....
....
1 row created.
Commit complete.
Synonym created.
Synonym created.
NOTE:
SPCTAB complete. Please check spctab.lis for any errors.
SQL>
SQL> -- Create the statistics Package
SQL> @@spcpkg
SQL> Rem
SQL> Rem $Header: rdbms/admin/spcpkg.sql /main/56 2017/05/28 22:46:10 stanaya Exp $
SQL> Rem
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2017, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*PLUS command file to create statistics package
SQL> Rem
SQL> Rem NOTES
SQL> Rem Must be run as the STATSPACK owner, PERFSTAT
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/spcpkg.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/spcpkg.sql
SQL> Rem SQL_PHASE: UTILITY
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem kchou 11/04/13 - Bug# 17504669:Add New Column remaster_type to
SQL> Rem STATS$DYNAMIC_REMASTER_STATS
SQL> Rem shsong 06/29/11 - shsong 06/28/11 - Bug 12702106: display
SQL> Rem v$IOSTAT_FUNCTION_DETAIL
SQL> Rem arogers 01/23/08 - 6523482 - change VM_IN/OUT_BYTES id numbers
SQL> Rem cdgreen 03/14/07 - 11 F2
SQL> Rem shsong 06/14/07 - Fix BUFFER_GETS
SQL> Rem cdgreen 04/05/07 - 5691086
SQL> Rem cdgreen 03/02/07 - use _FG for v$system_event
SQL> Rem cdgreen 03/02/07 - 5913378
SQL> Rem cdgreen 05/16/06 - 11 F1
SQL> Rem cdgreen 05/10/06 - 5215982
SQL> Rem cdgreen 05/24/05 - 4246955
SQL> Rem cdgreen 04/18/05 - 4228432
SQL> Rem cdgreen 02/28/05 - 10gR2 misc
SQL> Rem vbarrier 02/18/05 - 4081984
SQL> Rem cdgreen 01/25/05 - 4143812
SQL> Rem cdgreen 10/29/04 - 10gR2_sqlstats
SQL> Rem cdgreen 10/25/04 - 3970898
SQL> Rem cdgreen 07/16/04 - 10g R2
SQL> Rem vbarrier 03/18/04 - 3517841
SQL> Rem vbarrier 02/12/04 - 3412853
SQL> Rem cdialeri 12/04/03 - 3290482
SQL> Rem cdialeri 11/05/03 - 3202706
SQL> Rem cdialeri 10/14/03 - 10g - streams - rvenkate
SQL> Rem cdialeri 08/05/03 - 10g F3
SQL> Rem cdialeri 07/31/03 - 2804307
SQL> Rem vbarrier 02/25/03 - 10g RAC
SQL> Rem cdialeri 01/28/03 - 10g F2: baseline, purge
SQL> Rem cdialeri 11/15/02 - 10g F1
SQL> Rem cdialeri 10/29/02 - 2648471
SQL> Rem cdialeri 09/11/02 - 1995145
SQL> Rem vbarrier 04/18/02 - 2271895
SQL> Rem vbarrier 03/20/02 - 2184504
SQL> Rem spommere 03/19/02 - 2274095
SQL> Rem vbarrier 03/05/02 - Segment Statistics
SQL> Rem spommere 02/14/02 - cleanup RAC stats that are no longer needed
SQL> Rem spommere 02/08/02 - 2212357
SQL> Rem cdialeri 02/07/02 - 2218573
SQL> Rem cdialeri 01/30/02 - 2184717
SQL> Rem cdialeri 01/09/02 - 9.2 - features 2
SQL> Rem cdialeri 11/30/01 - 9.2 - features 1
SQL> Rem hbergh 08/23/01 - 1940915: use substrb on sql_text
SQL> Rem cdialeri 04/26/01 - 9.0
SQL> Rem cdialeri 09/12/00 - sp_1404195
SQL> Rem cdialeri 04/07/00 - 1261813
SQL> Rem cdialeri 03/28/00 - sp_purge
SQL> Rem cdialeri 02/16/00 - 1191805
SQL> Rem cdialeri 11/01/99 - Enhance, 1059172
SQL> Rem cgervasi 06/16/98 - Remove references to wrqs
SQL> Rem cmlim 07/30/97 - Modified system events
SQL> Rem gwood.uk 02/30/94 - Modified
SQL> Rem densor.uk 03/31/93 - Modified
SQL> Rem cellis.uk 11/15/89 - Created
SQL> Rem
SQL>
SQL> set echo off;
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
SQL>
SQL> -- Bug#25233027: xxx Set this parameter to FALSE for creating common objects in consolidated database
SQL> alter session set "_oracle_script" = FALSE;
Session altered.
SQL>
SQL>Once installed you’ll be able to take a snapshot of the system statistics using the subsequent procedure call.
SQL>
SQL> execute statspack.snap;
PL/SQL procedure successfully completed.
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
--------- ---------- --------------------
ORCL 1 15-JAN-21:10:02:38
SQL>Run the below script to automate the gathering of system snapshots:
The spauto.sql script is accustomed schedule system snapshot collections on the hour, every hour.
SQL>
SQL> show user
USER is "PERFSTAT"
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/spauto.sql
Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
JOBNO
----------
1
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 20
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
JOB
----------
NEXT_DATE
---------------------------------------------------------------------------
NEXT_SEC
--------------------------------
1
15-JAN-21 10.00.00.000000 PM +05:30
22:00:00
SQL>View the JOB using below query
set lines 180 pages 200
col SCHEMA_USER for a20
col INTERVAL for a30
col WHAT for a30
select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=1;How to change Snap interval for Statspack:
We can change or modify snap interval for spreport using below command.
==>>> To modify for Every one hour use: sysdate+1/24
exec dbms_job.interval(1, 'trunc(SYSDATE+1/24,''HH'')');
==>>> To modify for Every 30 minutes use: sysdate+1/48
exec dbms_job.interval(1, 'trunc(SYSDATE+1/48,''HH'')');
==>>> To modify for Every 15 minutes use: sysdate+15/1440
exec dbms_job.interval(1, 'trunc(SYSDATE+15/1440,''HH'')');
After modify check INTERVAL status:-
================================
set lines 180 pages 200
col SCHEMA_USER for a20
col INTERVAL for a30
col WHAT for a30
select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=1;After that, If you’re automating snapshot collection you will must delete snapshots from time to time. this might be done by running the sppurge.sql file because the PERFSTAT user. This script deletes a selection of snapshots by prompting for the start and end points.
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/sppurge.sql
Database Instance currently connected to
========================================
Instance
DB Id DB Name Inst Num Name
----------- ---------- -------- ----------
473602848 PROD 1 PROD
Snapshots for this database instance
====================================
Base- Snap
Snap Id Snapshot Started line? Level Host Comment
-------- --------------------- ----- ----- --------------- --------------------
1 15 Jan 2021 20:53:47 5 vm-1.localdomai
11 15 Jan 2021 21:02:57 5 vm-1.localdomai
21 15 Jan 2021 21:06:12 5 vm-1.localdomai
22 15 Jan 2021 21:06:18 5 vm-1.localdomai
23 15 Jan 2021 21:06:24 5 vm-1.localdomai
24 15 Jan 2021 21:06:29 5 vm-1.localdomai
31 15 Jan 2021 21:06:37 5 vm-1.localdomai
32 15 Jan 2021 21:06:46 5 vm-1.localdomai
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: 21
Using 21 for lower bound.
Enter value for hisnapid: 24
Using 24 for upper bound.
Deleting snapshots 21 - 24.
Number of Snapshots purged: 4
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Purge of specified Snapshot range complete.
SQL>Lastly, Once you’ve got a minimum of two snapshots after that, you’ll run the statspack report and see the change within the statistics over the analysis period. The script running time it prompts you for enter the start and end snapshots along with a filename for the output report.
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
473602848 PROD 1 PROD
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
473602848 1 PROD PROD vm-1.localdo
main
Using 473602848 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 <return> without
specifying a number lists all completed snapshots.
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
PROD PROD 1 15 Jan 2021 20:53 5
11 15 Jan 2021 21:02 5
31 15 Jan 2021 21:06 5
32 15 Jan 2021 21:06 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
<---- Enter begin Snapshot Id
Begin Snapshot Id specified: 1
Enter value for end_snap: 31
<---- Enter End Snapshot Id
End Snapshot Id specified: 31
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_31. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
<---- Enter spreport name if you want
Using the report name sp_1_31
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
473602848 PROD 1 15-Jan-21 19:52 19.0.0.0.0 NO
.....
.....
.....
Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 25.72 25.87 .56
Cursor to Parent ratio: 1.06 1.07 .64
Total Cursors: 306 293 -4.44
Total Parents: 288 274 -5.11
-------------------------------------------------------------
init.ora Parameters DB/Inst: PROD/PROD Snaps: 1-31
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_file_dest /u01/app/oracle/admin/PROD/adump
audit_trail DB
compatible 19.0.0
control_files /u01/app/oracle/oradata/PROD/cont
rol01.ctl, /u01/app/oracle/fast_r
ecovery_area/PROD/control02.ctl
db_block_size 8192
db_name PROD
db_recovery_file_dest /u01/app/oracle/fast_recovery_are
a
db_recovery_file_dest_size 8657043456
diagnostic_dest /u01/app/oracle
dispatchers (PROTOCOL=TCP) (SERVICE=PRODXDB)
local_listener LISTENER_PROD
log_archive_format %t_%s_%r.dbf
memory_target 1073741824
nls_language AMERICAN
nls_territory AMERICA
open_cursors 300
processes 300
remote_login_passwordfile EXCLUSIVE
undo_tablespace UNDOTBS1
-------------------------------------------------------------
End of Report ( sp_1_31.lst )
SQL>I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.
1 thought on “How to install, configure, and use Statspack in Oracle”