Table of Contents
ASH Overview
- Every second, Active Session History polls the database to identify the active sessions and dumps relevant information in the SGA
- Data: User ID, state, the machine it is connected from, SQL statement, Wait events
- Even though a session is no longer present in the database instance, the ASH buffer has captured its information
- Because Active Session History records activities every second, it can show a second-by-second snapshot of the activities in a session.
- Note that when the ASH buffer is filled, the data is written to disk and the snapshots are taken every 10 seconds rather than every second
Display all cluster gc wait events since today 0.00
SQL> set pagesize 100
SQL> set linesize 180
SQL> col object_name format A32
SQL> col object_type format A20
SQL> col owner format A10
SQL> col cnt format 999999999
SQL> col current_file# format 99999 head file
SQL> col current_block# format 9999999 head block
SQL> define event="gc%"
SQL> define grp_count=4;
SQL> define sample_time_start="trunc(sysdate)";
SQL> define sample_time_end="sysdate";
SQL> col event format A32
SQL> select /*+ materialize */ inst_id, event, current_obj#, current_file#, current_block#, Count(*) CNT,
2 min(to_char(sample_time,'DD-MON HH:MI')) First_Wait, max(to_char(sample_time,'DD-MON HH:MI')) Last_Wait
3 from gv$active_session_history where event like lower('&event') and sample_time>&sample_time_start and sample_time<&sample_time_end
4 group by inst_id,event, current_obj#, current_file#, current_block#
5 having count(*) >= &grp_count
6 order by CNT desc;
old 3: from gv$active_session_history where event like lower('&event') and sample_time>&sample_time_start and sample_time<&sample_time_end
new 3: from gv$active_session_history where event like lower('gc%') and sample_time>trunc(sysdate) and sample_time<sysdate
old 5: having count(*) >= &grp_count
new 5: having count(*) >= 4
INST_ID EVENT CURRENT_OBJ# file block CNT FIRST_WAIT LAST_WAIT
---------- -------------------------------- ------------ ------ -------- ---------- ------------ ------------
1 gcs log flush sync -1 0 0 22 24-NOV 11:59 24-NOV 12:01
2 gcs log flush sync -1 0 0 18 24-NOV 11:59 24-NOV 12:01
3 gcs log flush sync -1 0 0 13 24-NOV 08:51 24-NOV 12:01
1 gc buffer busy acquire 88864 4 575035 9 24-NOV 11:59 24-NOV 11:59
2 gc buffer busy acquire 88864 4 575035 6 24-NOV 11:59 24-NOV 12:00
1 gc buffer busy acquire 88864 4 576756 6 24-NOV 12:00 24-NOV 12:00
1 gc cr block 2-way -1 0 0 6 24-NOV 09:11 24-NOV 12:00
2 gc current block 2-way -1 0 0 6 24-NOV 10:00 24-NOV 11:42
2 gc cr block 3-way -1 0 0 5 24-NOV 09:12 24-NOV 11:17
2 gc cr block 2-way -1 0 0 4 24-NOV 08:57 24-NOV 11:23
3 gc buffer busy acquire 88864 4 575035 4 24-NOV 12:00 24-NOV 12:00
1 gc current block 2-way -1 0 0 4 24-NOV 10:00 24-NOV 11:50
Display hot blocks with gc wait events ( see book reference below )
Script: ash_gcwait_to_block.sql
Display objects with high gc wait events ( see book reference below )
Script: ash_gcwait_to_obj.sql
Display SQL statements producing with hight gc wait events ( see book reference below )
Script: ash_gcwait_to_sql.sql
Reference:
Expert Oracle RAC 12c – Chapter 10 – RAC database optimization / Rivaj Shamsudeen