Shulou(Shulou.com)06/01 Report--

ASH (Active Session History)

ASH is based on V$SESSION and is sampled once a second to record events that active sessions are waiting for. Inactive sessions are not sampled, and sampling is done by the newly introduced background process MMNL. The minimum value of ASH buffers is 1MB, and the maximum value does not exceed 30MB. Record data in memory. The expected value is to record the content of one hour.

AWR (Automatic Workload Repository)

Automatic workload information base

The data recorded in ASH memory is always limited. In order to preserve the historical data, the automatic load information base (AutomaticWorkload Repository, AWR) is introduced. The background process MMON periodically synchronizes the ASH information to the AWR load library. It is unacceptable for ASH to write out all of the data, so only 10% of the collected data is generally written and done using direct-path insert to minimize log generation and minimize database performance impact.

The ASH information written to the AWR load library is recorded in the underlying table wrh$active_session_hist of AWR. Wrh$active_session_hist is a partition table, and Oracle automatically cleans up the data.

AWR is started automatically after Oracle is installed, and no special settings are required. The collected statistics are stored in the SYSAUX tablespace SYS schema, named in WRM$_* and WRH$_* formats, and the statistics collected in the last 7 days are retained by default. The collected information is written to the database every hour, and this series of operations are done by a process called MMON.

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

Perform snapshots manually

Exec dbms_workload_repository.create_snapshot

You can query through the view

SQL > select * from DBA_HIST_WR_CONTROL DBID SNAP_INTERVAL RETENTION TOPNSQL -- 1417794109 + 00000 01virtual 00.0 + 00008 00001000000.0 DEFAULT

You can use DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS to modify snap_interval and retention.

ADDM (Automatic Database Diagnostic Monitor AWR)

Oracle internal consultant system, can automatically complete some of the most database optimization recommendations, give SQL optimization, index creation, statistics collection and other suggestions. Whenever a snapshot for AWR is generated, an ADDM report is automatically given based on the current snapshot and the previous snapshot. The ADDM report shows only items with problems with system tasks.

Generate ash report

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

Upper right of EM- > Performance- > Average Active Sessions [Run ASH Report]

Generate awr report

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

Instances cannot be closed between snapshots.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.