Statspack utility to monitor the performance of your database. It is free, included in your license. As of oracle 10g, you can use the Automatic Workload Repository to gather and analyze statistics. It provides an ad hoc capability to analyze database statistics in a similar fashion-by taking snapshots of the database statistics at different times and generating reports based on the differences.
STATSPACK Performance Diagnosis tool Utility:
a. As per the server workload the top five wait event changes and user activity can be proactively monitored.
b. Despite on investigating of any incident on reactive basis the same information can be Dig out proactively as per checking the report of Statspack.
c. It gives us the clear picture of physical
read, write access to the Database on tablespace as well as datafile level.
d. The user sessions information can also be
monitored on a periodic basis as per the Historical database reports precise in
the system.
Installing and Configuring STATSPACK
Create
PERFSTAT Tablespace
SQL> CREATE
TABLESPACE perfstat
DATAFILE '/u01/oracle/db/PERF1_perfstat01.dbf'
SIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT
ONLINE;
Run
the Create Scripts
SQL> @?/rdbms/admin/dbmspool.sql
Session altered.
Package created.
Grant succeeded.
Session altered.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
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
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
PW_SC PERMANENT
PW_SC_IND PERMANENT
PW_SC_IND_NEW PERMANENT
PW_SC_IND_OLD PERMANENT
PW_SC_IND_OTHERS PERMANENT
PW_SC_NEW PERMANENT
PW_SC_OLD PERMANENT
PW_SC_OTHERS PERMANENT
PW_STA PERMANENT
PW_STA_OLD PERMANENT
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
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.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
Adjusting
the STATSPACK Collection Level
STATSPACK has two types
of collection options, level and threshold. The level parameter
controls the type of data collected from Oracle, while the threshold parameter
acts as a filter for the collection of SQL statements into the stats$sql_summary table.
SQL> SELECT *
FROM stats$level_description ORDER BY snap_level;
|
Level 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. |
|
Level 5 |
This level includes
capturing high resource usage SQL Statements, along with all data captured by
lower levels. |
|
Level 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. |
|
Level 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. |
|
Level 10 |
This level includes
capturing Child Latch statistics, along with all data captured by lower
levels. |
You can change the
default level of a snapshot with the statspack.snap function. The i_modify_parameter => 'true' changes the level permanent for all
snapshots in the future.
SQL> exec
statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');
OR,
SQL> exec STATSPACK.MODIFY_STATSPACK_PARAMETER(i_snap_level=>6, i_buffer_gets_th=>100000,i_modify_parameter=>'true');
Changing
the interval of statistics collection
Sqlplus / as sysdba
spool statspack_snap_for_evry_half_an_hours_time.lst
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'MI'), 'trunc(SYSDATE+1/48,''MI'')', TRUE, :instno);
commit;
end;
/
prompt the job:
print jobno
prompt Job queue process
prompt ~~~~~~~~~~~~~~~~~
show parameter job_queue_processes
prompt
prompt Next scheduled run
prompt ~~~~~~~~~~~~~~~~~~
prompt The next scheduled run for this job is:
select job, next_date, next_sec
from user_jobs
where job = :jobno;
spool off
OUTPUT:
the job:
SQL> print jobno
JOBNO
----------
2
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
The next scheduled run for this job is:
SQL> select job, next_date, next_sec
2 from user_jobs
3 where job = :jobno;
JOB NEXT_DATE NEXT_SEC
---------- --------- --------------------------------
2 19-AUG-19 13:31:00
Create,
View and Delete Snapshots
sqlplus
perfstat/perfstat
SQL> exec statspack.snap;
Create the Report
sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/spreport.sql
Generate Execution Plan for given SQL statement
sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/sprepsql.sql
Delete Snapshots
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
--------- ---------- -------------------
PERF1
4 14.11.2004:10:56:01
PERF1
1 13.11.2004:08:48:47
PERF1
2 13.11.2004:09:00:01
PERF1
3 13.11.2004:09:01:48
SQL> @?/rdbms/admin/sppurge;
Enter the Lower and Upper
Snapshot ID
Note:- We are actually deleting statspack snapshot but it will ultimately going to store in our UNDO tablespace. So I am going to show you how to completely drop Statspack and recreate it again to reclaim space for PERFSTAT tablespace.
v PERFSTAT Purging
activity:
|
Step 1:
First
we will take backup of PERFSTAT tablespace.
|
|
|
Step 2: Now, login as sysdba
and drop the Statspack utility: $
sqlplus / as sysdba SQL> @?/rdbms/admin/spdrop.sql |
|
|
Step 3: Recreate
the Statspack utility: SQL> @?/rdbms/admin/spcreate.sql |
If you like this post, then please share with others.
Reference:- Oracle Press - oracle database 10g dba handbook
https://www.akadia.com/services/ora_statspack_survival_guide.html
Good one!!
ReplyDelete