Oracle Learning Performance Optimization (15) ASH, ADDM, AWR

ASH (Active Session history)

Ash is based on v$session, sampling once per second, recording events waiting for an active session. Inactive sessions are not sampled, and sampling work is done by the newly introduced background process MMNL. The minimum value of ASH buffers is 1MB and the maximum value is not more than 30MB. Record data in memory. Expectation is the recording of one hour of content.

AWR (Automatic Workload Repository)

Automated Workload Repository

Ash memory record data is always limited, in order to save historical data, the introduction of the automatic load information base (Automaticworkload Repository, AWR) by the background process Mmon periodically synchronize ash to the AWR load library. All Ash writes are unacceptable, so generally only 10% of the collected data is written, and the Direct-path insert is used to minimize log generation, minimizing the impact of database performance.

The ash information that is written to the AWR load library is recorded in the AWR base table Wrh$active_session_hist, Wrh$active_session_hist is a partitioned table, and Oracle automatically cleans the data.

AWR is automatically started when Oracle is installed and does not require special settings. The statistics collected are stored in the Sysaux tablespace sys mode, named in the format wrm$_* and wrh$_*, and the statistics collected for the last 7 days are retained by default. Every hour The collected information is written to the database, and this sequence of operations is done by a process called Mmon.

The snapshot generation cycle is hourly and data is retained for 8 days.

Performing snapshots Manually

Exec Dbms_workload_repository.create_snapshot;

You can query through views

Sql> select * from Dba_hist_wr_control; DBID snap_interval RETENTION topnsql----------------------------------------------------------------------------- -------- --------------------------------------------------------------------------- ---------------------------- --1417794109 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

You can use Dbms_workload_repository. Modify_snapshot_settings to modify snap_interval and retention.

ADDM (Automatic Database Diagnostic Monitor AWR)

A consultant system within Oracle can automate some of the optimization recommendations for the most databases, giving recommendations for SQL optimization, index creation, and statistics collection. Each time an awr snapshot is generated, a ADDM report is automatically given based on the current snapshot and the last snapshot. The ADDM report shows only the items that have a problem with the system task.

Generate Ash Reports

Sql> @?/rdbms/admin/ashrpt.sql

em->performance650) this.width=650; "src=" Https://192.168.199.163:1158/em/cabo/images/t.gif "width=" 5 "height=" 1 "style=" font-family:arial, Helvetica, Geneva, Sans-serif;font-size:13.3333330154419px;font-weight:bold; Background-color:rgb (255,255,255); "alt=" T.gif "/>->average Active sessions upper right [Run ASH report]

Generate AWR reports

Sql> @?/rdbms/admin/awrrpt.sql

Instances between snapshots cannot be closed.

Create baseline

Execdbms_workload_repository. Create_baseline (start_snap_id,end_snap_id, baseline_name);

AWR Comparison Report

@?/rdbms/admin/awrddrpt

RAC Global AWR

@?/rdbms/admin/awrgrpt

This article is from the "Ding Dong" blog, please be sure to keep this source http://lqding.blog.51cto.com/9123978/1693935

Oracle Learning Performance Optimization (15) ASH, ADDM, AWR