Getting Most Out of Oracle 8i/9i Statspack
Statspack is a set of performance monitoring and reporting utilities provided by Oracle for Oracle8i and above. A user is automatically created by the installation script – this user, PERFSTAT, owns all objects needed by this package. For more information about Statspack, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.
Install Statspack
To install the package, either change to the ORACLE_HOME rdbms/admin directory, or fully specify the ORACLE_HOME/rdbms/admin directory when calling the installation script, SPCREATE.
To run the installation script, you must use SQL*Plus and connect as a user with SYSDBA privilege. For example, start SQL*Plus, then:
On UNIX : SunOS/HP UX/Linux
SQL> CONNECT / AS SYSDBA
SQL> @?/rdbms/admin/spcreate
On Windows: XP/NT/2000/2003
SQL> @%ORACLE_HOME%rdbmsadminspcreate
The SPCREATE install script runs three other scripts. These scripts are called automatically, so you do not need to run them:
* SPCUSR: Creates the user and grants privileges
* SPCTAB: Creates the tables
* SPCPKG: Creates the package
Configuring Statspack
How to locate the current Ststapack level?
– Look at table PERFSTST.STATS$SNAPSHOT
or
– Run spreport.sql and you will notice https://medium.com/tag/serp-movies it alongwith the snapids listed
STATS$SNAPSHOT will show level for each Snapshot recorded
Change Level
execute statspack.snap (i_snap_level=> 7, i_modify_parameter=>’true’);
Levels >= 0 General Performance Statistics
Levels >= 5 Additional Data: SQL Statements
Levels >= 6 Additional Data: SQL Plans and SQL Plan Usage
Levels >= 10 Additional Statistics: Parent and Child Latches
Using Statspack (gathering data)
sqlplus perfstat
—
execute statspack.snap;
— Get a list of snapshots
column snap_time format a21
select snap_id,to_char(snap_time,’MON dd, yyyy hh24:mm:ss’) snap_time
from sp$snapshot;
Running a Performance report
— Run the Statspack report:
@?/rdbms/admin/spreport.sql
1. Login as PERFSTAT user on database.
It won’t work unless U login as PERFSTAT user.
2. Find DBID using
“select dbid from stats$sql_summary”
3. Locate MIN(SNAP_ID) pBgnSnap & MAX(SNAP_ID) pEndSnap from
select min(snap_id),max(snap_id),min(snap_time),max(snap_time) from stats$snapshot
where to_number(to_char(snap_time,’HH24′)) > 10 and to_number(to_char(snap_time,’HH24′)) < 13 and trunc(snap_time)=trunc(sysdate)
Show All SQL Stmts ordered by Logical Reads
select
e.hash_value "E.HASH_VALUE"
, e.module "Module"
, e.buffer_gets - nvl(b.buffer_gets,0) "Buffer Gets"
, e.executions - nvl(b.executions,0) "Executions"
, Round( decode ((e.executions - nvl(b.executions, 0)), 0, to_number(NULL)
, (e.buffer_gets - nvl(b.buffer_gets,0)) /
(e.executions - nvl(b.executions,0))) ,3) "Gets / Execution"
, Round(100*(e.buffer_gets - nvl(b.buffer_gets,0))/sp920.getGets(:pDbID,:pInstNum,:pBgnSnap,:pEndSnap,'NO'),3) "Percent of Total"
, Round((e.cpu_time - nvl(b.cpu_time,0))/1000000,3) "CPU (s)"
, Round((e.elapsed_time - nvl(b.elapsed_time,0))/1000000,3) "Elapsed (s)"
, Round(e.fetches - nvl(b.fetches,0)) "Fetches"
, sp920.getSQLText ( e.hash_value , 400) "SQL Statement"
from stats$sql_summary e
, stats$sql_summary b
where b.snap_id( ) = :pBgnSnap
and b.dbid( ) = e.dbid
and b.instance_number( ) = e.instance_number
and b.hash_value( ) = e.hash_value
and b.address( ) = e.address
and b.text_subset( ) = e.text_subset
and e.snap_id = :pEndSnap
and e.instance_number = :pInstNum
order by 3 desc
Show SQL Stmts where SQL_TEXT like '%'
and e.dbid = 2863128100
and sp920.getSQLText ( e.hash_value , 400) https://serpexperiments.medium.com/ like '%ZPV_DATA%'
How to retrieve entire SQL Execution PLAN from Statspack for a table
To retrieve SQL plan you need to have statspack working on level 7
1. sprepsql.sql
The SQL report (sprepsql.sql) is a report for a specific SQL statement. The SQL report is usually run after examining the high-load SQL sections of the instance health report.The SQL report provides detailed statistics and data for Click for more info a single SQL statement (as identified by the Hash Value in Statspack report).
2. Hash Value is known
- Select * from STATS$SQLTEXT where hash_value='%from stats pack%' order by piece;
- For an Object first locate the OBJECT_ID
select * from sys.obj$ where name='TRANSACTION'
select snap_time
snap_id,
plan_hash_value,
OBJECT# ,
OBJECT_NAME ,
OPERATION ,
OPTIONS ,
COST ,
IO_COST ,
CARDINALITY ,
POSITION ,
CPU_COST ,
OPTIMIZER ,
SEARCH_COLUMNS ,
BYTES ,
DISTRIBUTION ,
TEMP_SPACE ,
ACCESS_PREDICATES ,
FILTER_PREDICATES
from stats$SQL_PLAN a , STATS$SNAPSHOT b where object#='&&OBJECT_ID' and a.snap_id=b.snap_id;