Getting Most Out of Oracle 8i/9i Statspack

By: Sagar Patil

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.

About the Author

(ArticlesBase SC #405021)

Article Source: http://www.articlesbase.com/ - Getting Most Out of Oracle 8i/9i Statspack

Related Articles:


Getting the Most Out of Automated OS Recovery

So where do you turn when every day you read about new products and solutions being put out on the market promising to minimize impact to your business in the event of system failure? You turn to the leaders in the industry. Their mission statement is, €Persystent Technologies is committed to developing game-changing technology and innovative solutions to common business problems.€‌ This can be comforting if you have aligned yourself with one of the most innovative technology companies in the world...



Getting The Most Value out of Free Screensavers

As we all know, all computer users have some selection of screensavers. This is what comes with their standard operating system. The internet has since made it possible for thousands of free screensavers to be downloaded with the click of a mouse...



Getting the Most out of Web Analytics with a CMS

For most modern organizations, the Web site is now much more than just the company€™s online face. It can be brand builder, revenue generator, customer service point, and so much more. Getting the site up is only the beginning; the business has to then understand if the target audience is getting the information it needs, examine the benefits delivered to customers on literally every page, and use all the gathered information to continuously improve the experience for end-users...