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 ORACLEHOMErdbmsadminspdoc.txt.Install StatspackTo Install The Package, Either Change To The ORACLEHOME Rdbmsadmin Directory, Or Fully Specify The ORACLEHOMErdbmsadmin Directory When Calling The Installation Script, SPCREATE.To Run The Installation Script, You Must Use SQLPlus And Connect As A User With SYSDBA Privilege. For Example, Start SQLPlus, Then:On UNIX : SunOSHP UXLinuxSQL CONNECT AS SYSDBASQL ?rdbmsadminspcreateOn Windows: XPNT20002003SQL CONNECT AS SYSDBASQL ORACLEHOMErdbmsadminspcreateThe 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.STATSSNAPSHOTor- Run Spreport.sql And You Will Notice It Alongwith The Snapids ListedSTATSSNAPSHOT Will Show Level For Each Snapshot RecordedChange Levelexecute Statspack.snap (isnaplevel 7, Imodifyparameter'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 Snaptime Format A21select Snapid,tochar(snaptime,'MON Dd, Yyyy Hh24:mm:ss') Snaptimefrom Spsnapshot;--Running A Performance Report---- Run The Statspack Report:--?rdbmsadminspreport.sqlLocate Hard Hitting SQL From Statpack Reposistory1. Login As PERFSTAT User On Database.It Won't Work Unless U Login As PERFSTAT User.2. Find DBID Using"select Dbid From Statssqlsummary"3. Locate MIN(SNAPID) PBgnSnap MAX(SNAPID) PEndSnap Fromselect Min(snapid),max(snapid),min(snaptime),max(snaptime) From Statssnapshotwhere Tonumber(tochar(snaptime,'HH24')) 10 And Tonumber(tochar(snaptime,'HH24')) 13 And Trunc(snaptime)trunc(sysdate)Show All SQL Stmts Ordered By Logical Readsselecte.hashvalue "E.HASHVALUE", E.module "Module", E.buffergets - Nvl(b.buffergets,0) "Buffer Gets", E.executions - Nvl(b.executions,0) "Executions", Round( Decode ((e.executions - Nvl(b.executions, 0)), 0, Tonumber(NULL), (e.buffergets - Nvl(b.buffergets,0)) (e.executions - Nvl(b.executions,0))) ,3) "Gets Execution", Round(100(e.buffergets - Nvl(b.buffergets,0))sp920.getGets(:pDbID,:pInstNum,:pBgnSnap,:pEndSnap,'NO'),3) "Percent Of Total", Round((e.cputime - Nvl(b.cputime,0))1000000,3) "CPU (s)", Round((e.elapsedtime - Nvl(b.elapsedtime,0))1000000,3) "Elapsed (s)", Round(e.fetches - Nvl(b.fetches,0)) "Fetches", Sp920.getSQLText ( E.hashvalue , 400) "SQL Statement"from Statssqlsummary E, Statssqlsummary Bwhere B.snapid() :pBgnSnapand B.dbid() E.dbidand B.instancenumber() E.instancenumberand B.hashvalue() E.hashvalueand B.address() E.addressand B.textsubset() E.textsubsetand E.snapid :pEndSnapand E.dbid :pDbIdand E.instancenumber :pInstNumorder By 3 DescShow SQL Stmts Where SQLTEXT Like ''selecte.hashvalue "E.HASHVALUE", E.module "Module", E.buffergets - Nvl(b.buffergets,0) "Buffer Gets", E.executions - Nvl(b.executions,0) "Executions", Round( Decode ((e.executions - Nvl(b.executions, 0)), 0, Tonumber(NULL), (e.buffergets - Nvl(b.buffergets,0)) (e.executions - Nvl(b.executions,0))) ,3) "Gets Execution", Round(100(e.buffergets - Nvl(b.buffergets,0))sp920.getGets(:pDbID,:pInstNum,:pBgnSnap,:pEndSnap,'NO'),3) "Percent Of Total", Round((e.cputime - Nvl(b.cputime,0))1000000,3) "CPU (s)", Round((e.elapsedtime - Nvl(b.elapsedtime,0))1000000,3) "Elapsed (s)", Round(e.fetches - Nvl(b.fetches,0)) "Fetches", Sp920.getSQLText ( E.hashvalue , 400) "SQL Statement"from Statssqlsummary E, Statssqlsummary Bwhere B.snapid() :pBgnSnapand B.dbid() E.dbidand B.instancenumber() E.instancenumberand B.hashvalue() E.hashvalueand B.address() E.addressand B.textsubset() E.textsubsetand E.snapid :pEndSnapand E.dbid 2863128100and E.instancenumber :pInstNumand Sp920.getSQLText ( E.hashvalue , 400) Like 'ZPVDATA'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 71. 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 STATSSQLTEXT Where Hashvalue'from Stats Pack' Order By Piece;- For An Object First Locate The OBJECTIDselect From Sys.obj Where Name'TRANSACTION'select Snaptimesnapid,planhashvalue,OBJECT ,OBJECTNAME ,OPERATION ,OPTIONS ,COST ,IOCOST ,CARDINALITY ,POSITION ,CPUCOST ,OPTIMIZER ,SEARCHCOLUMNS ,BYTES ,DISTRIBUTION ,TEMPSPACE ,ACCESSPREDICATES ,FILTERPREDICATESfrom StatsSQLPLAN A , STATSSNAPSHOT B Where Object'OBJECTID' And A.snapidb.snapid;a Href"http:OracleDbaSupport.co.uk"http:OracleDbaSupport.co.uka 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, PLSQL 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 PLSQL.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.
