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 does not write all of the data in the AWR, generally only write the collected 10% of the amount of data, and use Direct-path insert complete, to minimize the generation of the log, so as to minimize 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 assuming a fault lasts only 5 minutes, before and after the database is normal, then what happened in 5 minutes, to see Ash.
When we were monitoring the database, we assumed that the problem was currently occurring and we were able to find the worst-performing SQL statement through V$session+v$sqlarea. 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 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 (Session 10 recent wait events)
V$active_session_history (in-memory Ash collection information, theory of 1 hours)
Wrh$_active_session_history (Ash information written in the AWR library, theory is more than 1 hours)
Dba_hist_active_sess_history (based on wrh$_active_session_history generated view)
Ash's build runs such as the following script to generate the Ash report:
@?/rdbms/admin/ashrpt.sql1. You are all the way to the return, is to get the latest 15 minutes of ash report. 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 choose the time between oldest Ash sample available and latest Ash sample available, and then enter the length, for example 30 for 30 minutes, whichever time you want to take the ash report
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
