In 10g/11g,working with ASH and AWR

shiyihai發表於2007-07-23

The ASH contains recent information on active sessions sampled every second. The AWR takes snapshots of the database every hour, so the information in the AWR could be almost an hour old and will not help in diagnosing issues that are current on the database.

[@more@]

Typically, to resolve issues currently on the database, detailed information pertaining to the last 5 or 10 minutes is critical. Because recording session activity is expensive, ASH samples V$SESSION every second and records the events for which the sessions are waiting.ASH is designed as a rolling buffer in memory; old information is overwritten after saving it to the AWR. You can query ASH information using the V$ACTIVE_SESSION_HISTORY view.The view contains one row for each active session per sample and returns the latest session’s sample rows first.

The AWR is a collection of persistent system performance statistics owned by the SYS schema.Over time, you should purge the statistics, and sometimes you may want to get a snapshot of the system for performance diagnosing outside the regular interval. Oracle 10g provides several programs in a package named DBMS_WORKLOAD_REPOSITORY. Using these programs, you can manage the snapshots and perform baselines.

下面透過呼叫dbms_workload_repository包中的create_snapshot來手工產生兩個快照:
EXEC dbms_workload_repository.create_snapshot();
。。。 。。。 。。。
EXEC dbms_workload_repository.create_snapshot();

緊接著透過awrrpt.sql來產生報告(非常類似statspack中的spreport.sql)
You can view AWR reports using the awrrpt.sql and awrrpti.sql scripts located in the
$ORACLE_HOME/rdbms/admin directory. The awrrpt.sql script displays statistics for a range
of snapshot IDs. The report can be saved as text file or HTML file. The awrrpti.sql script is
similar to awrrpt.sql; the only difference is you can specify the database ID and instance ID
as parameters. The report contains the following categories of information:
Report summary
Wait events statistics
SQL statistics
Instance activity statistics
I/O statistics
Buffer pool statistics
Advisory statistics
Wait statistics
Undo statistics
Latch statistics
Segment statistics
Dictionary cache statistics
Library cache statistics
SGA statistics
Resource limit statistics
init.ora parameters

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/38542/viewspace-927575/,如需轉載,請註明出處,否則將追究法律責任。

相關文章