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 StatspackTo 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/LinuxSQL> CONNECT / AS SYSDBASQL> @?/rdbms/admin/spcreateOn Windows: XP/NT/2000/2003SQL> CONNECT / AS SYSDBASQL> @%ORACLE_HOME%rdbmsadminspcreateThe 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 packageConfiguring StatspackHow to locate the current Ststapack level?
- Look at table PERFSTST.STATS$SNAPSHOTor
- Run spreport.sql and you will notice it alongwith the snapids listedSTATS$SNAPSHOT will show level for each Snapshot recordedChange Levelexecute statspack.snap (i_snap_level=> 7, i_modify_parameter=>’true’);
Levels >= 0 General Performance StatisticsLevels >= 5 Additional Data: SQL StatementsLevels >= 6 Additional Data: SQL Plans and SQL Plan UsageLevels >= 10 Additional Statistics: Parent and Child LatchesUsing Statspack (gathering data)sqlplus perfstat
–
– Take a performance snapshot
–execute statspack.snap;
–
– Get a list of snapshots
–column snap_time format a21select snap_id,to_char(snap_time,’MON dd, yyyy hh24:mm:ss’) snap_timefrom sp$snapshot;
–Running a Performance report
–
– Run the Statspack report:
–@?/rdbms/admin/spreport.sqlLocate 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 fromselect min(snap_id),max(snap_id),min(snap_time),max(snap_time) from stats$snapshotwhere 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 Readsselecte.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 bwhere b.snap_id(+) = :pBgnSnapand b.dbid(+) = e.dbidand b.instance_number(+) = e.instance_numberand b.hash_value(+) = e.hash_valueand b.address(+) = e.addressand b.text_subset(+) = e.text_subsetand e.snap_id = :pEndSnapand e.dbid = :pDbIdand e.instance_number = :pInstNumorder by 3 descShow SQL Stmts where SQL_TEXT like '%'selecte.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 bwhere b.snap_id(+) = :pBgnSnapand b.dbid(+) = e.dbidand b.instance_number(+) = e.instance_numberand b.hash_value(+) = e.hash_valueand b.address(+) = e.addressand b.text_subset(+) = e.text_subsetand e.snap_id = :pEndSnapand e.dbid = 2863128100and e.instance_number = :pInstNumand sp920.getSQLText ( e.hash_value , 400) like '%ZPV_DATA%'order by 3 descHow to retrieve entire SQL + Execution PLAN from Statspack for a tableTo retrieve SQL plan you need to have statspack working on level 7
1. sprepsql.sqlThe 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_IDselect * from sys.obj$ where name=’TRANSACTION’select snap_timesnap_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_PREDICATESfrom 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 SupportI 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.