- Introduction: STATSPACK is a performance diagnosis tool, available since Oracle8i. Along with additional reporting, statspack can store snapshots of system statistics over time, allowing greater accuracy and flexibility. This guide will walk you through the STATSPACK utility step-by-step.
- Collecting Snapshots: Oracle Snapshots are moment-in-time collections of all of the database statistics that the Oracle database continuously collects. Snapshots can be collected a various levels, each increasing level collecting a better information about the database.So to collect the better information we can increase the snaspshot level. Below table will describe the levels
- Prerequisites: o control_management_pack_access should be set NONE
- Environment:
Hostname: apex - Now let's start the STATSPACK configuration step by step:
| Level | Information Collected |
|---|---|
| 0 | General Performance Statistics |
| 5 | Addition Data: SQL Statements |
| 6 | Addition Data: SQL Plans and SQL Plan Usage |
| 7 | Addition Data: Segment Level Statistics |
| 10 | Addition Data: Parent and Child Latches |
IP: 192.168.101.7
ORACLE_SID : APEXDB
DB NAME: APEXDB
PDB NAME: APEXPDB
Parameter check
[oracle@apex ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 4 15:55:13 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 APEXPDB READ WRITE NO
SQL> show parameter control_management_pack_access
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string NONE
SQL> select username from dba_users where username like '%STAT%';
no rows selected
SQL>
SQL> select * from stats$level_description;
select * from stats$level_description
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> col file_name for a150
SQL>
SQL> set lines 200 pages 1000
SQL> select file_name from dba_data_files;
FILE_NAME
---------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_sysaux_mvyr3vf8_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_system_mvyr435s_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_undotbs1_mvyr4bj1_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_users_mvyr4hbg_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/user01.dbf
SQL> CREATE TABLESPACE PERFSTAT DATAFILE '/u02/app/oracle/oradata/APEXPDB/datafile/perfstat01.dbf' SIZE 500M autoextend on;
Tablespace created.
SQL> select file_name from dba_data_files;
FILE_NAME
---------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_sysaux_mvyr3vf8_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_system_mvyr435s_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_undotbs1_mvyr4bj1_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_users_mvyr4hbg_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/user01.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/perfstat01.dbf
7 rows selected.
SQL> exit
Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
[oracle@apex ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@apex admin]$
[oracle@apex admin]$ ll | grep -i spcreate.sql
-rwxr-xr-x. 1 oracle oinstall 1796 May 29 2017 spcreate.sql
[oracle@apex admin]$
[oracle@apex admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 4 16:00:26 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 APEXPDB READ WRITE NO
SQL> alter session set container=APEXPDB;
Session altered.
SQL> !pwd
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin
SQL> @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: PERF##stat#123 --> Provide the password for perfstat user & note it.
PERF##stat#123
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 user'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
*
TABLESPACE_NAME CONTENTS
------------------------------ ---------------------
STATSPACK DEFAULT TABLESPACE
----------------------------
USERS PERMANENT
Pressing will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: PERFSTAT --> Provide the default tablespace.
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 --> Provide the Temporary tablespace.
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.
... output continue ...
... output continue ...
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> 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 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 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
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 APEXPDB READ WRITE NO
SQL>
SQL> exec 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
--------- ---------- -----------------------------
APEXDB 1 04-APR-2025:16:14:01
1 row selected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 APEXPDB READ WRITE NO
SQL> alter session set container=APEXPDB;
Session altered.
SQL> show user
USER is "SYS"
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 APEXPDB READ WRITE NO
SQL> GRANT CREATE JOB TO PERFSTAT ;
Grant succeeded.
SQL> conn PERFSTAT/PERF##stat#123@APEXPDB
Connected.
SQL> @?/rdbms/admin/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
----------
63 ---> Note the Job No
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 80
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC
---------- --------- --------------------------------
63 05-APR-25 22:00:00
SQL>
SQL> SELECT job, what, next_date, broken FROM dba_jobs WHERE what LIKE '%statspack.snap%';
JOB WHAT NEXT_DATE B
---------- ------------------------------ -------------------- -
61 statspack.snap; 04-APR-2025 17:00:00 N
1 row selected.
select name, snap_id, to_char(snap_time, 'DD-MON-YYYY:HH24:MI:SS') "Date/Time"
from stats$snapshot, v$database
order by 2;
NAME SNAP_ID Date/Time
--------- ---------- -----------------------------
APEXDB 1 04-APR-2025:16:14:01
APEXDB 2 04-APR-2025:17:00:00
APEXDB 3 04-APR-2025:18:00:00
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 APEXPDB READ WRITE NO
SQL> @?/rdbms/admin/spreport
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1196724728 APEXDB 1 APEXDB
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
1196724728 1 APEXDB APEXDB APEXDB
Using 1196724728 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
APEXDB APEXDB 1 04 Apr 2025 16:14 5
2 05 Apr 2025 17:00 5
3 05 Apr 2025 18:00 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2
Begin Snapshot Id specified: 2
Enter value for end_snap: 3
End Snapshot Id specified: 3
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_2_3. To use this name, press to continue, otherwise enter an alternative.
Enter value for report_name: sp_2_3
Using the report name sp_2_3
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
3003259501 apexdb 1 02-Apr-25 20:14 19.0.0.0.0 NO
Thanks for visiting!!
0 comments:
Post a Comment