[Oracle]-Performance optimization tool (2)-ASH

Ash and awr relationships Ash is based on v$session, sampling once per second, recording events waiting for an active session. Inactive sessions do not take samples, and sample work is done by the newly introduced background process MMNL.
Ash memory record data is always limited, in order to save historical data, the introduction of their own active load information base (Automatic Workload Repository, AWR) by the background process Mmon complete. The ash information is set out in the AWR load library. Because memory is limited, the MMNL process writes out the information to the AWR load library when Ash is full.

Ash is not writing all the data on the AWR. Typically, only 10% of the collected data is written, and the Direct-path insert is used to minimize the generation of the log, thereby minimizing the performance impact of the database.

To summarize: Awr is based on the snapshot sampling interval for system overall performance inference. And ash has finer granularity.

For example, your awr is a 1-hour sample, but suppose a fault lasts for only 5 minutes, before and after the database is normal. So what happened in these 5 minutes. Going to see ash.
When we monitor the database, we assume that the problem is currently occurring. We can use V$session+v$sqlarea to find the worst-performing SQL statements. Assuming that it happens within one hours, we can find SQL by generating the ASH report. Assuming it's over 1 hours or a few days we can find out the AWR report for hours and days that most affect the system's SQL statements. The ADDM report is based on the AWR library. The default is to save 30 days of ADDM reports.
    We are also able to query directly:
    v$session                   and nbsp                 (currently occurring)
    v$session_wait       &NBS P                   (currently occurring)
    v$session_wait_history   &NB Sp         (Session 10 wait events)
    v$active_session_history           (internal Stored Ash Collection information, theory of 1 hours)
    wrh$_active_session_history     (write ash information in the AWR library. Theory is more than 1 hours)
    Dba_hist_active_sess_history   (based on wrh$_active_session_history generated view)
Ash generation runs such as the following script to generate the Ash report:

@?
/rdbms/admin/ashrpt.sql1. You're all the way to the carriage. is to get the ash report for the recent 15 minutes.
2. You are based on oldest ash sample available time and then enter, choosing the longest ash execution 3 that can be collected at the moment. You can select the time between oldest Ash sample available and latest Ash sample available, and then enter the duration, for example 30 for 30 minutes. Take the ash report that you want to take no matter what time of day

The ash report can also be obtained directly by hand, for example, select Output from table (dbms_workload_repository.ash_report_html (dbid,inst_num,l_btime,l_etime) ;

Demo Sample: Set pagesize 0set linesize 121spool d:\ash_rpt.htmlselect output from table (Dbms_workload_repository.ash_report_ HTML (977587123,1,sysdate-30/1440,sysdate-1/1440)); spool off--Package Specific reference: dbms_workload_repository.ash_report_html (L_ dbid         in number, L_inst_num     in number, l_btime in Date,l_etime in date,l_options in number      DEFAULT 0 , l_slot_width in number   default 0,l_sid in number          default null,l_sql_id in       VARCHAR2 default Null,l_wait_ Class in   VARCHAR2 default Null,l_service_hash in number default null,l_module in       VARCHAR2 default null,l_action< C10/>in VARCHAR2 default null,l_client_id in    VARCHAR2 default null,l_plsql_entry in  VARCHAR2 default NULL)

[Oracle]-Performance optimization tool (2)-ASH