Ash and awr relationships 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.
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 limited, the MMNL process writes out the information to the AWR load library when Ash is full. Ash also does not write out all of the data in the AWR, generally write only the collected 10% of the amount of data, and use Direct-path insert complete, to minimize the generation of log, so as to minimize the performance impact of the database.
To summarize: Awr is based on the snapshot sampling interval to determine the overall performance of the system. And ash has finer granularity. For example, your awr is sampled 1 hours a time, but if a fault lasts for only 5 minutes, before and after the database is normal, what happens in 5 minutes is going to see ash.
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 out the SQL by generating the ASH report. If it is more than 1 hours or a few days we can find out the AWR report for hours and days, the most affecting SQL statements for the system. 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)
Ash's build executes the following script to generate the Ash report:
@?/rdbms/admin/ashrpt.sql1. You are all the way to the return, which is the ash report for the last 15 minutes. 2. You are based on oldest ash sample available time and then enter to select the longest ash run condition that can currently be collected 3. You can choose the time between oldest Ash sample available and latest Ash sample available, and then enter the duration, such as 30 for 30 minutes, to take the ash report for any period you want to take
Ash reports can also be obtained manually, such as Select output from table (dbms_workload_repository.ash_report_html (dbid,inst_num,l_btime,l_etime) ;
example: 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 Detail parameters: 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 on number default null,l_sql_id in VARCHAR2 default Null,l_wait_class I N VARCHAR2 default Null,l_service_hash in number default Null,l_module in VARCHAR2 default null,l_action in VA RCHAR2 default null,l_client_id in VARCHAR2 default null,l_plsql_entry in VARCHAR2 default NULL)
