The ASH report shows tables and data files with wait event 'utl_file I/O':
CHANGES
No changes.
CAUSE
ASH reports retrieve data from the dba_hist_active_sess_history view. The information in that view is aggregated from what happened in between each sample from v$active_session_history, which means that it can potentially include multiple events (such as CPU and I/O events).
In the following example , code is executed that performs CPU and I/O operations in a short period of time:
alter system flush buffer_cache;
alter system flush shared_pool;
connect system/manager
set time on
set timing on
set serveroutput on
declare
x number;
begin
dbms_output.enable(1000000);
for i in (select s.*,rownum r from dba_source s) loop
x:=dbms_utility.get_hash_value(i.name,1,163830000);
if mod(x,99)=0 then
dbms_output.put('+');
else
dbms_output.put('.');
end if;
if mod(i.r,30000)=0 then
dbms_output.put_line('*');
end if;
end loop;
end;
/
If we query v$active_session_history, we see:
column event format a30
column p1p2p3 format a20
select sample_id,NVL(event, 'CPU') AS event,p1||'-'||p2||'-'||p3 p1p2p3, TM_DELTA_CPU_TIME,TM_DELTA_DB_TIME,DELTA_READ_IO_REQUESTS
from v$active_session_history
where (session_id,SESSION_SERIAL#)=(select sid,serial# from v$session where sid=(select sid from v$mystat where rownum=1))
order by sample_id
/
SAMPLE_ID EVENT P1P2P3 TM_DELTA_CPU_TIME TM_DELTA_DB_TIME DELTA_READ_IO_REQUESTS
---------- ------------------------------ -------------------- ----------------- ---------------- ----------------------
125687 CPU 1-9720-8 141978 126117 311
125688 CPU 1-31856-16 1093833 1105182 142
125689 CPU 1-75808-16 100
125690 CPU 1-77248-16 2009695 2020332 66
125691 CPU 1-79952-16 64
Notice that all of the EVENTs listed are CPU events but the PL/SQL must have had to perform I/O. This I/O is reflected in DELTA_READ_IO_REQUESTS. By looking at the columns p1,p2,p3, it can be found some residual values from what probably was a 'db file scattered read' where p1 is file_no, p2 block_id , and p3 is blocks.
This is confirmed by looking up those blocks in the data dictionary.
SQL> select segment_name from dba_extents where file_id =1 and 9720 between block_id and block_id+blocks;
SEGMENT_NAME
---------------------------------------------------------------------------------
SOURCE$
SQL> select segment_name from dba_extents where file_id =1 and 79952 between block_id and block_id+blocks;
SEGMENT_NAME
---------------------------------------------------------------------------------
SOURCE$
SOLUTION
This is expected behavior.
The wait event surfaces when a PL/SQL program is running SQLs and using UTL_FILE in close proximity. Multiple IOs to the DB and UTL_FILE calls were executed in fast succession and the p1p2p3 sampled were residual from DB IO events to the datafiles. The "utl_file I/O" event was included as part of the sample. So when the ASH report is generated, both the "utl_file I/O" event and the information of datafiles accessed are present.
