High Level steps for Statspack install and configure :
1. How to check Statspack installed or not ?
select * from stats$level_description;
*If no output and error "table doesn't exists"
2. Create sperate tablespace for keep Statspack objects
create tablespace stat_pack datafile '/u01/app/oracle/oradata/INDIA/stat_pack01.dbf' size 500M autoextend on maxsize 30G;
#####################################################################
For Statspack installation, need to run below command using sysdba login
#####################################################################
@/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/spcreate.sql
SQL>
SQL> @/u01/app/oracle/product/19.0/dbhome1/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
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
----------------------------
STAT_PACK PERMANENT
SYSAUX PERMANENT
*
USERS PERMANENT
Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: STAT_PACK
Using tablespace STAT_PACK 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
Using tablespace TEMP as PERFSTAT temporary tablespace.
... Creating PERFSTAT user
... Installing required packages
... Creating views
... Granting privileges
.
.
.
…………will take 2 min to complete , need to check there is no error
############################
Below parameter should true
#############################
SQL> show parameter timed_statistic
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
SQL>
##########################################
To check Statspack Level for take snapshot
############################################
SQL> l
1* select * from stats$level_description
SQL> /
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
############################################
Login with perfstat user and take snapshot
##############################################
[oracle@10.22.3333.2222 admin]$ sqlplus perfstat/perfstat
SQL>
SQL> show user
USER is "PERFSTAT"
SQL>
SQL> select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS')
"Date/Time" from stats$snapshot,v$database;
SQL> execute statspack.snap(i_snap_level=>10);
PL/SQL procedure successfully completed.
########################
To run report
######################
[oracle@10.22.3333.2222 admin]$
[oracle@10.22.3333.2222 admin]$ sqlplus perfstat/perfstat
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 6 07:45:00 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Tue Dec 06 2022 07:41:01 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> @/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/spreport.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3280227920 INDIA 1 india
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3280227920 1 INDIA india ip-172-31-19
-40.ec2.inte
rnal
Using 3280227920 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
------------ ------------ --------- ----------------- ----- --------------------
india INDIA 1 06 Dec 2022 07:38 10
2 06 Dec 2022 07:41 10
3 06 Dec 2022 07:41 10
4 06 Dec 2022 07:41 10
5 06 Dec 2022 07:41 10
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 5
End Snapshot Id specified: 5
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_5. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: sp_1_5_report_india
Using the report name sp_1_5_report_india
