• Oracle中 根据 file# 和 block# 找到对象


    我们在10046生产的trace 文件里经常看到下面的信息. 表示系统在等待散列读取某个文件号的某个块开始的8个块.

    WAIT #6: nam='db file scattered read' ela= 438472 file#=6 block#=2641 blocks=8
    WAIT #6: nam='db file scattered read' ela= 1039 file#=6 block#=833 blocks=8 obj#=90054 tim=878243950382
    WAIT #6: nam='db file scattered read' ela= 835 file#=10 block#=22961 blocks=8 obj#=90054 tim=878243957168
    WAIT #6: nam='db file scattered read' ela= 815 file#=11 block#=7409 blocks=8 obj#=90054 tim=878243966696
    ...
    因为我们想要根据file#,block#去找到系统正在读取哪个object(segment).

    现在主要有三种方式

    1. 查询dba_extents 表,效率比较差.
           select segment_name from dba_extents where and rownum = 1 and file_id = 6 and 2641 between block_id and block_id + blocks - 1;
    SEGMENT_NAME
    --------------------
    T1_N1
    
    Elapsed: 00:02:43.84
    
    Statistics
    ----------------------------------------------------------
           4676  recursive calls
              2  db block gets
        4077424  consistent gets
           6492  physical reads
              0  redo size
            418  bytes sent via SQL*Net to client
            400  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              5  sorts (memory)
              0  sorts (disk)
              1  rows processed
    2. 查询v$BH view, 查询很快,

    SQL> desc v$bh
    名称 是否为空? 类型
    ----------------------------------------- -------- ----------------------------
    FILE# NUMBER
    BLOCK# NUMBER
    CLASS# NUMBER
    STATUS VARCHAR2(10)
    XNC NUMBER
    FORCED_READS NUMBER
    FORCED_WRITES NUMBER
    LOCK_ELEMENT_ADDR RAW(4)
    LOCK_ELEMENT_NAME NUMBER
    LOCK_ELEMENT_CLASS NUMBER
    DIRTY VARCHAR2(1)
    TEMP VARCHAR2(1)
    PING VARCHAR2(1)
    STALE VARCHAR2(1)
    DIRECT VARCHAR2(1)
    NEW CHAR(1)
    OBJD NUMBER
    TS# NUMBER
    LOBID NUMBER
    CACHEHINT NUMBER

    SQL> select objd, file#,block#,class#,ts#,cachehint,status,dirty from v$bh where file#=2 and block#=59101;

    OBJD FILE# BLOCK# CLASS# TS# CACHEHINT STATUS DI
    ---------- ---------- ---------- ---------- ---------- ---------- -------------------- --
    6373 2 59101 1 1 15 xcur N

    3. dump出那个block块,查找object id.
     alter system dump datafile 6 block 2641;

    file which_obj2:

    define __FILE = &1
    define __BLOCK = &2
    alter system dump datafile &__FILE block &__BLOCK;
    set serveroutput on

    declare
    v_dba varchar2(100);
    v_type varchar2(100);
    v_obj_id number;
    v_obj_name varchar2(100);
    begin
    for r in (select column_value as t from table(get_trace_file1)) loop
    if regexp_like(r.t, 'buffer tsn:') then
    dbms_output.put_line('------------------------------------------------');
    v_dba := regexp_substr(r.t, '[[:digit:]]+/[[:digit:]]+');
    dbms_output.put_line(rpad('dba = ',20)|| v_dba);
    end if;

    if regexp_like(r.t, 'type: 0x([[:xdigit:]]+)=([[:print:]]+)') then
    v_type := substr(regexp_substr(r.t, '=[[:print:]]+'), 2);
    dbms_output.put_line(rpad('type = ',20)|| v_type);
    end if;

    if regexp_like(r.t, 'seg/obj:') then
    v_obj_id := to_dec(substr(regexp_substr(r.t,
    'seg/obj: 0x[[:xdigit:]]+'), 12));
    select object_name into v_obj_name from all_objects
    where data_object_id = v_obj_id;
    dbms_output.put_line(rpad('object_id = ',20)|| v_obj_id);
    dbms_output.put_line(rpad('object_name = ',20)|| v_obj_name);
    end if;

    if regexp_like(r.t, 'Objd: [[:digit:]]+') then
    v_obj_id := substr(regexp_substr(r.t, 'Objd: [[:digit:]]+'), 7);
    select object_name into v_obj_name from all_objects
    where data_object_id = v_obj_id;
    dbms_output.put_line(rpad('object_id = ',20)|| v_obj_id);
    dbms_output.put_line(rpad('object_name = ',20)|| v_obj_name);
    end if;

    end loop;

    dbms_output.put_line('------------------------------------------------');

    end;
    /

    @which_obj2 6 2641
    old 1: alter system dump datafile &__FILE block &__BLOCK
    new 1: alter system dump datafile 6 block 2641

    System altered.

    Elapsed: 00:00:00.01
    ------------------------------------------------
    dba = 6/2641
    type = FIRST LEVEL BITMAP BLOCK
    object_id = 9005
    Elapsed: 00:00:00.045
    object_name = T1_N1
    ------------------------------------------------
    PL/SQL procedure successfully completed.

    
    
  • 相关阅读:
    求逆元算法
    Almost Sorted Array
    最长不递减子序列
    (LIS)最长上升序列(DP+二分优化)
    rabbitmq延迟消息
    oracle 视图
    oracle 存储过程
    http 会话(session)详解
    系统测试
    Fiddler 手机抓包
  • 原文地址:https://www.cnblogs.com/princessd8251/p/3868487.html
Copyright © 2020-2023  润新知