Oracle Performance tuning of the Three swords--ash,awr,addm____oracle

Ash (Active session History)
Ash is based on V$session, sampled once per second, and records events waiting on the active sessions. Inactive sessions do not sample, and the sampling is done by the newly introduced background process MMNL. The
ASH buffers minimum value is 1MB and the maximum value is no more than 30MB. Record data in memory. Expectations are the contents of an hour of record. The

generates the ASH report:
Sqlplus>@?/rdbms/ashrpt.sql

Ash memory record data is always limited, and in order to preserve historical data, an automated load information base is introduced ( Automaticworkload Repository, AWR) is completed by the background process Mmon. The ash information is also collected and written to the AWR load library. Because the memory is not sufficient, the MMNL process writes out the information to the AWR load library after Ash is fully written. Ash is all written out to be unacceptable, so it typically writes only 10% of the data collected and uses Direct-pathinsert to minimize the build of the log, minimizing the database performance impact.

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

AWR (Automatic workload Repository) Automatic workload Information base
AWR is a new feature in Oracle 10g , similar to the previous statspack of 10g. However, it is simpler to use than Statspack, provides more performance metrics than statspack, and better helps DBAs to discover database performance bottlenecks.
AWR is automatically started when Oracle is installed and does not require special settings. The collected statistics are stored in Sysaux tablespace sys mode and are named in the wrm$_* and wrh$_* format, and the statistics collected in the last 7 days are retained by default. Each hour The collected information is written to the database, which is done by a process called Mmon.

Classification of data stored by AWR:
wrm$ table Stores awr metadata (Awrinfo.sql script)
wrh$ table stores historical data for sampled snapshots (Awrrpt.sql script)
wri$ table stores data related to database recommendation functions (ADDM related data)

I. Generate AWR reports:
Sql>@?/rdbms/admin/awrrpt

Follow the wizard to complete the build of the AWR report. It should be noted that in the selection of time range, there can be no downtime in the middle (if there is a blank line in the middle of the display, indicating that there is an outage). The default HTML is generally used when selecting report types for easy viewing.

Two. View the AWR settings for the database:
Sql> Select Snap_interval, retention from Dba_hist_wr_control;

Snap_interval
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
+00000 01:00:00.0 (collected once per hour)
+00007 00:00:00.0 (keep 7 days)

Three. Modify the default settings:
Begin
Dbms_workload_repository. Modify_snapshot_settings (interval => 20,
retention => 2*24*60);
End

Revise to collect statistics every 20 minutes, and keep the most recent 2-day statistic information.

Four. Manually collect statistics for a database:
EXEC dbms_workload_repository. Create_snapshot;

We can also use the Dbms_workload_repository package to complete the baseline, the default settings and other operations.

ADDM (Automatic Database diagnostic Monitor AWR)
Oracle is an internal consulting system, can automatically complete the most database of some optimization recommendations, giveSQLThe optimization, the creation of the index, the collection of statistics and other suggestions.

ADDM report Generation:
Sqlplus>@?/rdbms/addmrpt.sql

The most important part of Oracle performance tuning is the adjustment of the SQL that most affects performance. In one application, the only SQL that can affect the database is SQL. We can not rely on enhanced hardware, modify the system, database parameters to improve the performance of the database. More attention should be paid to those SQL statements that most affect performance. Ash reports, AWR reports, and ADDM reports can identify the most performance-impacting SQL tools. When analyzing Ash reports and AWR reports, it is most important to focus on the SQL ordered bygets and SQL ordered byreads two metrics that are most relevant to SQL Statistics,sql Statistics. A large amount of gets (logical reads) takes up a lot of CPU time. A large number of reads (physical reads) can cause IO bottlenecks to occur. Under normal circumstances, a large number of gets will be accompanied by a large number of reads appear. Of course, we can reduce the amount of reads by increasing the size of the SGA. It is important and necessary to find the most performance-impacting SQL through these two metrics. The next step is to improve the performance of SQL by creating an index that adjusts SQL to perform separately. Reduce the high gets,reads that occur when SQL executes. Of course, the overall performance impact is also related to excutions, if the number of times the execution of the SQL, the cumulative amount is still very large. You can then consider reducing the number of SQL executions by means of caching on the application. There is also a need to note that in the development process, SQL must use binding variables to reduce hard parsing (a large number of hard parsing also consumes a lot of CPU time, occupy a lot of latch). There is a principle in the development process: Small business. The operation completes the timely submission.

We use so many ways, reports have only one purpose: to find the SQL statements that most affect the performance of the system. Find SQL The next step is to tweak it.

When we monitor the database, if it is a problem that is currently occurring, we can use V$session+v$sqlarea to find the worst-performing SQL statement. If it happens within one hours, we can find the SQL by generating the ash report. If it is more than 1 hours or a few days we can use the AWR report to find out the SQL statements that affect the system for hours and days. The ADDM report is based on the AWR library and can be saved by default for a 30-day ADDM report.

We can also query directly to try:
V$session (currently occurring)
V$session_wait (currently occurring)
V$session_wait_history (last 10 wait events for the session)
V$active_session_history (in-memory ash gathers information, theory for 1 hours)
Wrh$_active_session_history (writes the ash information in the AWR library, theory is more than 1 hours)
Dba_hist_active_sess_history (view generated from wrh$_active_session_history)