Obtaining Most Out of Oracle 8i/9i Statspack

1076

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

Keywords and phrases:
Oracle Dba, Statspack, Oracle 10g, Oracle Tuning, Oracle Overall performance

Statspack is often a set of overall performance monitoring and reporting utilities offered by Oracle for Oracle8i and above. A user is automatically developed by the installation script – this user, PERFSTAT, owns all objects required by this package. For far more info about Statspack, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.

Install Statspack
To install the package, either change towards the ORACLE_HOME rdbms/admin directory, or completely specify the ORACLE_HOME/rdbms/admin directory when calling the installation script, SPCREATE.

To run the installation script, you should use SQL*Plus and connect as a user with SYSDBA privilege. As an example, begin 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 3 other scripts. These scripts are named automatically, so you usually do not need to have to run them:

* SPCUSR: Creates the user and grants privileges
* SPCTAB: Creates the tables
* SPCPKG: Creates the package

Configuring Statspack

The best way to locate the existing 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 every single Snapshot recorded

Alter Level
execute statspack.snap (i_snap_level=> 7, i_modify_parameter=>’true’);

Levels >= 0 General Efficiency Statistics
Levels >= 5 Further Data: SQL Statements
Levels >= 6 Further Information: SQL Plans and SQL Program Usage
Levels >= 10 Extra Statistics: Parent and Kid Latches

Applying 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 Overall performance report

— Run the Statspack report:

@?/rdbms/admin/spreport.sql

Find Hard hitting SQL from Statpack Reposistory
1. Login as PERFSTAT user on database.
It will not work unless U login as PERFSTAT user.

2. Locate DBID utilizing
“select dbid from stats$sql_summary”

three. Locate MIN(SNAP_ID) pBgnSnap & MAX(SNAP_ID) pEndSnap from

choose min(snap_id),max(snap_id),min(snap_time),max(snap_time) from stats$snapshot
where to_number(to_char(snap_time,’HH24′)) > ten 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; OracleDbaSupport.co.uk is usually a blog site of Sagar Patil, an independent oracle consultant with a great understanding of how the Oracle database engine & Oracle Applications perform 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, information 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 Efficiency 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 oracledbasupport.co.uk for your oracle DBA needs.