Use Statspack for Oracle Performance Analysis

The host can obtainCPU, Memory, process count parameters are used as reports, but it is difficult for the database to report performance parameters for a long time.StatspackYesOraclePerformance analysis is a report, but this report can only take the value of a specified period of time, can be used to analyze the problem, but does not reflect the trend over time, it is difficult to display charts.

OracleRelated toolsToadProvidedStatspackBut it is very troublesome to select a time point, and there are only a few parameters. In principle, functions implemented by tools are usedSQLStatement. Below is a usageStatspackProceedOracleA simple example of performance analysis.

DetailsSQLThe statement is as follows:

SelectW. snap_id, Sn. SNAP_TIME, W. event, W. total_waits-w. last_wait waits, W. total_timeouts-w. last_timeout timeouts, (W. time_waited-w. last_time_waited )/1000000Time_waited_S FromPerfstat. stats $ snapshot sn, (Select snap_id, dbid, instance_number, event, Total_waits, Lag (total_waits,1) Over (order by event, snap_id) last_wait, Total_timeouts, Lag (total_timeouts,1) Over (order by event, snap_id) last_timeout,Time_waited_micro time_waited, Lag (time_waited_micro,1) Over (order by event, snap_id) Last_time_waitedFrom perfstat. stats $ system_event WhereEvent not in (select event from perfstat. stats $ IDLE_EVENT) AndSnap_id in (select snap_id from stats $ snapshot where snap_time> sysdate-300)---- 300 days ) W Where w. time_waited-w. last_time_waited>0 AndW. snap_id> (select min (snap_id) from stats $ snapshot where snap_time> sysdate-300)---- 300 days -- AndSN. snap_id in (22391,22392, 22393) AndSn. snap_id = w. snap_id AndSn. dbid = w. dbid AndSn. instance_number = w. instance_number AndEvent not like'% Message %' AndEvent not like'SQL * Net %' And(W. time_waited-w. last_time_waited)>600*1000000 ---- Wait time> 10 min UNION ALL SelectSs. snap_id, sn. SNAP_TIME,'Cpu time',0,0, ROUND (ss. value-lag (ss. value,1) Over (order by ss. snap_id ))/100,0) As value_cputime FromPerfstat. stats $ sysstat ss, perfstat. stats $ snapshot sn WhereSs. snap_id = sn. snap_id AndSs. name ='Cpu used by this session' And ss. snap_id in (select snap_id from stats $ snapshot where snap_time> sysdate-300) -- And ss. snap_id in (22391,22392, 22393) And ss. dbid = sn. dbid And ss. instance_number = sn. instance_number Order1,6Desc

PassSQLThe queried data is importedExcelMedium (availableTaod,Pl/SQLAnd other tools ).ExcelTo draw a chart.2011Year1MonthlyTop wait event. The figure shows that the main wait event isDb file sequential readAndCpu time.Db file sequentialreadEvent multi-Description SystemIOHigh pressure.

Hit rate chart (this week), the low point is generally in the early morning4Point, other hit rates98%Above

Theoretically,StatspackAll performance parameters in the report can be displayed in charts, provided that data is collected on a regular basis.