Oracle Performance Tuning ASH,AWR,ADDM

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.

Generate Ash Report:
Sqlplus>@?/rdbms/ashrpt.sql

Ash memory record data is always limited, in order to save historical data, the introduction of the automatic load information base (Automatic Workload Repository, AWR) by the background process Mmon completed. The ash information is also collected and written out to the AWR load library. Because memory is not sufficient, the MMNL process writes out the information to the AWR load library when Ash is full. 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 (Automatic Workload Repository) Automated Workload repository
AWR is a new feature in Oracle 10g, similar to the previous 10g statspack. However, it is simpler to use than Statspack and provides a lot more performance indicators than Statspack, which can help DBAs to find the performance bottleneck of the database better.
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.

Data classification for AWR storage:
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)

One. Generate the AWR report:
Sql>@?/rdbms/admin/awrrpt

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

Two. View the settings for the awr of 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 (reserved for 7 days)

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

Modified to collect statistics every 20 minutes, keeping the most recent 2-day statistics information.

Four. Manually collect statistical information about the database once:
EXEC dbms_workload_repository. Create_snapshot;

We can also use the Dbms_workload_repository package to complete the baseline, modification of the default settings and so on.

ADDM (Automatic Database Diagnostic Monitor AWR)
is a consultant system within Oracle that can automate some of the optimization recommendations for the most databases, give recommendations for SQL optimization, index creation, and statistics collection.

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

The most important thing about Oracle Performance tuning is the tuning of the SQL that most affects performance. In an application, only SQL can affect the database, and it can only be SQL. We can not rely on the enhancement of hardware, modify the system, database parameters to improve the performance of the database. More attention should be paid to the SQL statements that most affect performance. The ash report, the AWR report, and the ADDM report all have the ability to identify the most performance-impacting SQL tools.When analyzing the ash report, the AWR report, the most important thing is to focus on SQL Statistics, the most concern in SQL statistics is the SQL ordered by gets and SQL ordered by reads two metrics. A large number of gets (logical reads) can consume a lot of CPU time. A large number of reads (physical reads) can cause an IO bottleneck to occur. In general, 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 indicators.the next step is to improve the performance of SQL when it executes independently by creating indexes and adjusting SQL. Reduce the high gets,reads that occur when SQL executes. Of course, the overall performance impact is also related to Excutions, if the SQL executes too many times, the cumulative amount is still very large. You can then consider reducing the number of SQL executions by means of caching on the application.Another problem to note is that SQL must use bound variables during development to reduce hard parsing(A lot of hard parsing also consumes a lot of CPU time and consumes 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 to report only one purpose: to find the SQL statements that most affect the performance of the system. The next step in finding SQL is to tweak it.

When we monitor the database, we can use V$session+v$sqlarea to find the worst-performing SQL statement if it is a problem that is currently occurring. If it happens within one hours, we can find 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 have affected 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 to:
V$session (currently taking place)
V$session_wait (currently taking place)
V$session_wait_history (the last 10 wait events for the session)
V$active_session_history (Ash capture information in memory, theory is 1 hours)
Wrh$_active_session_history (Ash information written in the AWR library, theory is more than 1 hours)
Dba_hist_active_sess_history (views generated based on wrh$_active_session_history)

ADDM collects data and recommends invoking different advisors for database optimization, and SQL-related advisors mainly include SQL Access Advisor and SQL Tuning Advisor.

the difference between SQL Access Advisor and SQL Tuning Advisor(http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P 11_question_id:1794009000346753857):

In a Nutshell-the Tuning Advisor
O suggests SQL profiles
o gathering more or stale statistics
o indexes that might be VERY useful
o Query rewrites

The Access Advisor
O suggests indexes that might be useful (a possibly different set than the Tuning Advisor above)
O materialized views
o materialized View logs
o Partitions (in 11g)

SQL Access Advisor can be called through dbms_advisor, and SQL Tuning Advisor can be called through Dbms_sqltune.

@?rdbms/admin/awrrpt.sql is an extension of the previous statspack that collects information in more detail to view long-term database conditions.
@?rdbms/admin/ashrpt.sql to view the current database situation because Ash is sampling from v$session per second, and AWR collects much more data than ash.
The general collection of database information should be combined with awr and ash.
@?rdbms/admin/addmrpt. SQL is equivalent to being an expert residing in Oracle and is a self-diagnosing engine. Generates Symptom,problem,infomation, provides recommendations for problem solving, and automatically fixes some specific failures.
@?rdbms/admin/awrinfo.sql Displays information about AWR, including snapshot information, Sysaux space usage, AWR components, Ash, and more.
=============================================================================
                             ,         &NB Sp               Simplified    single    total     knot
======================== The main difference between the =====================================================
awr and Ash is that: awr is flat, Overall, Ash is three-dimensional, more focused on session event tracking,
because the business volume of the database event wait is constantly changing, awr is likely to be monitored, in order to compensate for this deficiency, Ash can track the session event.

the difference between ash and ADDMis that the ADDM is based on the analysis of the current state of the database, providing guidance on the existing problems, and it can be said that ASH,ADDM is the addition of AWR,
AWR collects the state of the database in a comprehensive manner, but ASH/ADDM is focused on analyzing the collected data and providing some useful advice.

Reference article:
more on AWR: http://blog.itpub.net/23135684/viewspace-1127938/

Oracle Performance Tuning ASH,AWR,ADDM