• 转 event 'utl_file I/O':


    The ASH report shows tables and data files with wait event 'utl_file I/O':

    Utl_file

     

    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.

  • 相关阅读:
    1055. [HAOI2008]玩具取名【区间DP】
    BZOJ2435:[NOI2011]道路修建 (差分)
    1084. [SCOI2005]最大子矩阵【网格DP】
    1060. [ZJOI2007]时态同步【树形DP】
    1050. [HAOI2006]旅行【并查集+枚举】
    2463. [中山市选2009]谁能赢呢?【博弈论】
    luogu P1195 口袋的天空
    luogu P1162 填涂颜色
    luogu P1223 排队接水
    luogu P1331 海战
  • 原文地址:https://www.cnblogs.com/feiyun8616/p/8329482.html
Copyright © 2020-2023  润新知