| Statspack is a set of performance monitoring and | | | | '%'selecte.hash_value "E.HASH_VALUE" |
| reporting utilities provided by Oracle for Oracle8i and | | | | , e.module "Module" |
| above. A user is automatically created by the | | | | , e.buffer_gets - nvl(b.buffer_gets,0) "Buffer Gets" |
| installation script - this user, PERFSTAT, owns all | | | | , e.executions - nvl(b.executions,0) "Executions" |
| objects needed by this package. For more information | | | | , Round( decode ((e.executions - nvl(b.executions, 0)), 0, |
| about Statspack, read the documentation in file | | | | to_number(NULL) |
| $ORACLE_HOME/rdbms/admin/spdoc.txt. | | | | , (e.buffer_gets - nvl(b.buffer_gets,0)) / |
| Install Statspack | | | | (e.executions - nvl(b.executions,0))) ,3) "Gets / |
| To install the package, either change to the | | | | Execution" |
| ORACLE_HOME rdbms/admin directory, or fully | | | | , Round(100*(e.buffer_gets - nvl(b.buffer_gets,0)) |
| specify the ORACLE_HOME/rdbms/admin directory | | | | '),3) "Percent of Total" |
| when calling the installation script, SPCREATE. | | | | , Round((e.cpu_time - nvl(b.cpu_time,0))/1000000,3) |
| To run the installation script, you must use SQL*Plus | | | | "CPU (s)" |
| and connect as a user with SYSDBA privilege. For | | | | , Round((e.elapsed_time - nvl(b.elapsed_time,0)) |
| example, start SQL*Plus, then: | | | | 1000000,3) "Elapsed (s)" |
| On UNIX : SunOS/HP UX/Linux | | | | , Round(e.fetches - nvl(b.fetches,0)) "Fetches" |
| SQL> CONNECT / AS SYSDBA | | | | , sp920.getSQLText ( e.hash_value , 400) "SQL |
| SQL> @?/rdbms/admin/spcreate | | | | Statement"from stats$sql_summary e |
| On Windows: XP/NT/2000/2003 | | | | , stats$sql_summary bwhere b.snap_id(+) = |
| SQL> CONNECT / AS SYSDBA | | | | :pBgnSnapand b.dbid(+) = e.dbidand |
| SQL> @%ORACLE_HOME%rdbmsadminspcreate | | | | b.instance_number(+) = e.instance_numberand |
| The SPCREATE install script runs three other scripts. | | | | b.hash_value(+) = e.hash_valueand b.address(+) = |
| These scripts are called automatically, so you do not | | | | e.addressand b.text_subset(+) = e.text_subsetand |
| need to run them: | | | | e.snap_id = :pEndSnapand e.dbid = 2863128100and |
| * SPCUSR: Creates the user and grants privileges | | | | e.instance_number = :pInstNumand sp920.getSQLText |
| * SPCTAB: Creates the tables | | | | ( e.hash_value , 400) like '%ZPV_DATA%'order by 3 |
| * SPCPKG: Creates the package | | | | desc |
| Configuring Statspack | | | | How to retrieve entire SQL + Execution PLAN from |
| How to locate the current Ststapack level? | | | | Statspack for a table |
| - Look at table PERFSTST.STATS$SNAPSHOTor | | | | To retrieve SQL plan you need to have statspack |
| - Run spreport.sql and you will notice it alongwith the | | | | working on level 7 |
| snapids listed | | | | 1. sprepsql.sql |
| STATS$SNAPSHOT will show level for each | | | | The SQL report (sprepsql.sql) is a report for a specific |
| Snapshot recorded | | | | SQL statement. The SQL report is usually run after |
| Change Levelexecute statspack.snap (i_snap_level=> | | | | examining the high-load SQL sections of the instance |
| 7, i_modify_parameter=>'true'); | | | | health report.The SQL report provides detailed |
| Levels >= 0 General Performance Statistics | | | | statistics and data for a single SQL statement (as |
| Levels >= 5 Additional Data: SQL Statements | | | | identified by the Hash Value in Statspack report). |
| Levels >= 6 Additional Data: SQL Plans and SQL Plan | | | | 2. Hash Value is known |
| Usage | | | | - Select * from STATS$SQLTEXT where |
| Levels >= 10 Additional Statistics: Parent and Child | | | | hash_value='%from stats pack%' order by piece; |
| Latches | | | | - For an Object first locate the OBJECT_IDselect * |
| Using Statspack (gathering data)sqlplus perfstat | | | | from sys.obj$ where name='TRANSACTION'select |
| -- Take a performance snapshotexecute | | | | snap_timesnap_id,plan_hash_value, |
| statspack.snap; | | | | OBJECT# , |
| -- Get a list of snapshotscolumn snap_time format | | | | OBJECT_NAME , |
| a21select snap_id,to_char(snap_time,'MON dd, yyyy | | | | OPERATION , |
| hh24:mm:ss') snap_timefrom sp$snapshot; | | | | OPTIONS , |
| Running a Performance report | | | | COST , |
| -- Run the Statspack report: | | | | IO_COST , |
| @?/rdbms/admin/spreport.sql | | | | CARDINALITY , |
| Locate Hard hitting SQL from Statpack Reposistory | | | | POSITION , |
| 1. Login as PERFSTAT user on database. | | | | CPU_COST , |
| It won't work unless U login as PERFSTAT user. | | | | OPTIMIZER , |
| 2. Find DBID using | | | | SEARCH_COLUMNS , |
| "select dbid from stats$sql_summary" | | | | BYTES , |
| 3. Locate MIN(SNAP_ID) pBgnSnap & | | | | DISTRIBUTION , |
| MAX(SNAP_ID) pEndSnap fromselect | | | | TEMP_SPACE , |
| e) from stats$snapshotwhere | | | | ACCESS_PREDICATES , |
| to_number(to_char(snap_time,'HH24')) > 10 and | | | | FILTER_PREDICATESfrom stats$SQL_PLAN a , |
| to_number(to_char(snap_time,'HH24')) < 13 and | | | | STATS$SNAPSHOT b where |
| trunc(snap_time)=trunc(sysdate) | | | | object#='&&OBJECT_ID' and a.snap_id=b.snap_id; is a |
| Show All SQL Stmts ordered by Logical | | | | blog site of Sagar Patil, an independent oracle |
| Readsselecte.hash_value "E.HASH_VALUE" | | | | consultant with a great understanding of how the |
| , e.module "Module" | | | | Oracle database engine & Oracle Applications work |
| , e.buffer_gets - nvl(b.buffer_gets,0) "Buffer Gets" | | | | together. |
| , e.executions - nvl(b.executions,0) "Executions" | | | | I am an Oracle Certified RAC DBA with over ten |
| , Round( decode ((e.executions - nvl(b.executions, 0)), 0, | | | | years experience supporting Production and |
| to_number(NULL) | | | | Development instances of Oracle databases. |
| , (e.buffer_gets - nvl(b.buffer_gets,0)) / | | | | - Expert in building 10g RAC systems as well as rman, |
| (e.executions - nvl(b.executions,0))) ,3) "Gets / | | | | data guard backup and recovery strategies for |
| Execution" | | | | production, test and development systems. |
| , Round(100*(e.buffer_gets - nvl(b.buffer_gets,0)) | | | | - Instructor for developers on Oracle architecture, |
| '),3) "Percent of Total" | | | | Oracle9i /10g features, tuning methodologies, database |
| , Round((e.cpu_time - nvl(b.cpu_time,0))/1000000,3) | | | | replication, PL/SQL and Oracle Http Server. |
| "CPU (s)" | | | | - User of Statspack, SQL Trace, OEM Performance |
| , Round((e.elapsed_time - nvl(b.elapsed_time,0)) | | | | Manager, Quest Central, Quest Foglight, Quest SQL |
| 1000000,3) "Elapsed (s)" | | | | impact, Bennchamark factory, Quest SQL optimizer, |
| , Round(e.fetches - nvl(b.fetches,0)) "Fetches" | | | | TOAD and custom scripts. |
| , sp920.getSQLText ( e.hash_value , 400) "SQL | | | | - Writer of detailed standards & practices for Oracle |
| Statement"from stats$sql_summary e | | | | installs, upgrades, tuning & backups on Oracle |
| , stats$sql_summary bwhere b.snap_id(+) = | | | | Databases and PL/SQL.Developer, designer and |
| :pBgnSnapand b.dbid(+) = e.dbidand | | | | implementer of Disaster recovery backup Procedures |
| b.instance_number(+) = e.instance_numberand | | | | - Leader on major projects through all phases of |
| b.hash_value(+) = e.hash_valueand b.address(+) = | | | | development, testing and Support |
| e.addressand b.text_subset(+) = e.text_subsetand | | | | I can be hired on a short term notice, to handle |
| e.snap_id = :pEndSnapand e.dbid = :pDbIdand | | | | strategy, design, implementation, trouble-shooting, DBA |
| e.instance_number = :pInstNumorder by 3 desc | | | | cover, remote monitoring, and training. |
| Show SQL Stmts where SQL_TEXT like | | | | |