Getting Most Out of Oracle 8i 9i Statspack

MRR and PLR Articles Pack

Below is a MRR and PLR article in category Computers Technology -> subcategory Software.

Title:
Getting Most Out of Oracle 8i/9i Statspack

Word Count:
1076

Summary:
Oracle Dba, Statspack, Oracle 10g, Oracle Tuning, Oracle Performance


Keywords:
Oracle Dba, Statspack, Oracle 10g, Oracle Tuning, Oracle Performance


Article Body:
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> CONNECT / AS SYSDBA
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 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
--
-- Take a performance snapshot
--
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

Locate Hard hitting SQL from Statpack Reposistory
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.dbid = :pDbId
and e.instance_number = :pInstNum
order by 3 desc

Show SQL Stmts where SQL_TEXT like '%'
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.dbid = 2863128100
and e.instance_number = :pInstNum
and sp920.getSQLText ( e.hash_value , 400) like '%ZPV_DATA%'
order by 3 desc

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 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;

http://OracleDbaSupport.co.uk is a blog site of Sagar Patil, an independent oracle consultant with a great understanding of how the Oracle database engine & Oracle Applications work together.

I am an Oracle Certified RAC DBA with over ten years experience supporting Production and Development instances of Oracle databases.
- Expert in building 10g RAC systems as well as rman, data guard backup and recovery strategies for production, test and development systems.
- Instructor for developers on Oracle architecture, Oracle9i /10g features, tuning methodologies, database replication, PL/SQL and Oracle Http Server.
- User of Statspack, SQL Trace, OEM Performance Manager, Quest Central, Quest Foglight, Quest SQL impact, Bennchamark factory, Quest SQL optimizer, TOAD and custom scripts.
- Writer of detailed standards & practices for Oracle installs, upgrades, tuning & backups on Oracle Databases and PL/SQL.Developer, designer and implementer of Disaster recovery backup Procedures
- Leader on major projects through all phases of development, testing and Support

I can be hired on a short term notice, to handle strategy, design, implementation, trouble-shooting, DBA cover, remote monitoring, and training.
Please visit www.oracledbasupport.co.uk for your oracle DBA needs.




You can find an AI generated version of this article here: Getting Most Out of Oracle 8i 9i Statspack.

You can browse and read all the articles for free. If you want to use them and get PLR and MRR rights, you need to buy the pack. Learn more about this pack of over 100 000 MRR and PLR articles.

“MRR and PLR Article Pack Is Ready For You To Have Your Very Own Article Selling Business. All articles in this pack come with MRR (Master Resale Rights) and PLR (Private Label Rights). Learn more about this pack of over 100 000 MRR and PLR articles.”