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

Relationship between ASH and AWR. Based on the V $ SESSION, ASH samples the event every second and records the event waiting for the Active SESSION. Non-active sessions are not sampled, and the sampling is completed by the newly introduced background process MMNL.
The ASH memory record data is always limited. In order to save historical data, the Automatic load information library (AWR) is introduced and completed by the background process MMON. ASH information is also collected and written into the AWR Load Library. Because the memory is limited, the MMNL process writes the information to the AWR Load Library after the ASH is full. ASH does not write all the data to guide AWR. Generally, it only writes 10% of the collected data, and uses direct-path insert to minimize log generation and minimize the impact on database performance.

To sum up, AWR determines the overall system performance based on the snapshot sampling interval. ASH has a finer granularity. For example, if your AWR is one-hour sampling, but if a fault lasts for only five minutes, and the database is normal before and after, what happened in the past five minutes should be viewed in ASH.
When we monitor the database, if it is a problem that is currently occurring, we can use v $ session + v $ sqlarea to find the SQL statement with the worst performance. If this happens within an hour, we can generate an ASH report to find the SQL statement. if it is more than one hour or a few days, we can use the AWR report to find out how many hours, the most influential SQL statement in the system for a few days. The ADDM report is based on the AWR library and can be saved for 30 days by default.
We can also directly query and try:
V $ session (currently in progress)
V $ session_wait (currently in progress)
V $ session_wait_history (the last 10 waiting events of a session)
V $ active_session_history (The ASH Collection information in the memory, which is theoretically 1 hour)
Wrh $ _ active_session_history (the ASH information written into the AWR library, which is more than one hour in theory)
Dba_hist_active_sess_history (view generated based on wrh $ _ active_session_history). Execute the following script to generate the ASH report:

@? /Rdbms/admin/ashrpt. sql1. you press enter to obtain the ASH report for the last 15 minutes. 2. based on the oldest ash sample available time, press enter to select the maximum ASH running condition that can be collected currently. you can select the time between Oldest ASH sample available and Latest ASH sample available, and then enter the duration. For example, 30 indicates 30 minutes. You can obtain the ASH report for any time period.

ASH reports can also be obtained manually, for example, select output from table (dbms_workload_repository.ash_report_html (dbid, inst_num, l_btime, l_etime );

Example: set pagesize 0 set linesize 121 spool d: \ ash_rpt.htmlselect output from table (dbms_workload_repository.ash_report_html (977587123,1, SYSDATE-30/1440, SYSDATE-1/1440); spool off -- Packet details: 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, rochelle SQL _id IN VARCHAR2 DEFAULT NULL, Rochelle wait_class IN VARCHAR2 DEFAULT NULL, Rochelle service_hash IN NUMBER DEFAULT NULL, Rochelle module IN VARCHAR2 DEFAULT NULL, Rochelle client_id IN VARCHAR2 DEFAULT NULL, rochelle plsql_entry IN VARCHAR2 default null)