working with ASH and AWR

ash and awr[@more@]

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. 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. 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手工建立2個snapshot：
EXEC dbms_workload_repository.create_snapshot();
..................................
EXEC dbms_workload_repository.create_snapshot();

接著可以生成報告
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
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