[20201218]快速替代查询dba_extents.txt

[20201218]快速替代查询dba_extents.txt https://blog.dbi-services.com/efficiently-query-dba_extents-for-file_id-block_id/ --//我看了我以前的笔记，原始链接已经无法访问。 http://db-blog.web.cern.ch/blog/franck-pachot/2018-09-efficiently-query-dbaextents-fileid-blockid --//我做了格式话处理，缺点就是需要sys用执行： $ cat find_obj.sql /* Formatted on 2019/1/3 21:08:48 (QP5 v5.227.12220.39754) */ COLUMN owner FORMAT a6 COLUMN segment_type FORMAT a20 COLUMN segment_name FORMAT a15 COLUMN partition_name FORMAT a15 SET LINESIZE 200 --SET TIMING ON TIME ON ECHO ON AUTOTRACE ON STAT WITH l      AS (  /* LMT extents indexed on ktfbuesegtsn,ktfbuesegfno,ktfbuesegbno */          SELECT ktfbuesegtsn segtsn                ,ktfbuesegfno segrfn                ,ktfbuesegbno segbid                ,ktfbuefno extrfn                ,ktfbuebno fstbid                ,ktfbuebno + ktfbueblks - 1 lstbid                ,ktfbueblks extblks                ,ktfbueextno extno            FROM sys.x$ktfbue)     ,d      AS (                           /* DMT extents ts#, segfile#, segblock# */          SELECT ts# segtsn                ,segfile# segrfn                ,segblock# segbid                ,file# extrfn                ,block# fstbid                ,block# + LENGTH - 1 lstbid                ,LENGTH extblks                ,ext# extno            FROM sys.uet$)     ,s      AS (  /* segment information for the tablespace that contains afn file */          SELECT /*+ materialized */                f1.fenum afn                ,f1.ferfn rfn                ,s.ts# segtsn                ,s.FILE# segrfn                ,s.BLOCK# segbid                ,s.TYPE# segtype                ,f2.fenum segafn                ,t.name tsname                ,blocksize            FROM sys.seg$ s                ,sys.ts$ t                ,sys.x$kccfe f1                ,sys.x$kccfe f2           WHERE     s.ts# = t.ts#                 AND t.ts# = f1.fetsn                 AND s.FILE# = f2.ferfn                 AND s.ts# = f2.fetsn)     ,m      AS (       /* extent mapping for the tablespace that contains afn file */          SELECT /*+ use_nl(e) ordered */                s.afn                ,s.segtsn                ,s.segrfn                ,s.segbid                ,extrfn                ,fstbid                ,lstbid                ,extblks                ,extno                ,segtype                ,s.rfn                ,tsname                ,blocksize            FROM s, l e           WHERE     e.segtsn = s.segtsn                 AND e.segrfn = s.segrfn                 AND e.segbid = s.segbid          UNION ALL          SELECT /*+ use_nl(e) ordered */                s.afn                ,s.segtsn                ,s.segrfn                ,s.segbid                ,extrfn                ,fstbid                ,lstbid                ,extblks                ,extno                ,segtype                ,s.rfn                ,tsname                ,blocksize            FROM s, d e           WHERE     e.segtsn = s.segtsn                 AND e.segrfn = s.segrfn                 AND e.segbid = s.segbid          UNION ALL          SELECT /*+ use_nl(e) use_nl(t) ordered */                f.fenum afn                ,NULL segtsn                ,NULL segrfn                ,NULL segbid                ,f.ferfn extrfn                ,e.ktfbfebno fstbid                ,e.ktfbfebno + e.ktfbfeblks - 1 lstbid                ,e.ktfbfeblks extblks                ,NULL extno                ,NULL segtype                ,f.ferfn rfn                ,name tsname                ,blocksize            FROM sys.x$kccfe f, sys.x$ktfbfe e, sys.ts$ t           WHERE     t.ts# = f.fetsn                 AND e.ktfbfetsn = f.fetsn                 AND e.ktfbfefno = f.ferfn          UNION ALL          SELECT /*+ use_nl(e) use_nl(t) ordered */                f.fenum afn                ,NULL segtsn                ,NULL segrfn                ,NULL segbid                ,f.ferfn extrfn                ,e.block# fstbid                ,e.block# + e.LENGTH - 1 lstbid                ,e.LENGTH extblks                ,NULL extno                ,NULL segtype                ,f.ferfn rfn                ,name tsname                ,blocksize            FROM sys.x$kccfe f, sys.fet$ e, sys.ts$ t           WHERE t.ts# = f.fetsn AND e.ts# = f.fetsn AND e.file# = f.ferfn)     ,o      AS (SELECT s.tablespace_id segtsn                ,s.relative_fno segrfn                ,s.header_block segbid                ,s.segment_type                ,s.owner                ,s.segment_name                ,s.partition_name            FROM SYS_DBA_SEGS s)     ,datafile_map      AS (SELECT afn file_id                ,fstbid block_id                ,extblks blocks                ,NVL                 (                    segment_type                   ,DECODE (segtype, NULL, 'free space', 'type=' || segtype)                 )                    segment_type                ,owner                ,segment_name                ,partition_name                ,extno extent_id                ,extblks * blocksize bytes                ,tsname tablespace_name                ,rfn relative_fno                ,m.segtsn                ,m.segrfn                ,m.segbid            FROM m, o           WHERE     extrfn = rfn                 AND m.segtsn = o.segtsn(+)                 AND m.segrfn = o.segrfn(+)                 AND m.segbid = o.segbid(+)          UNION ALL          SELECT   file_id                 + (SELECT TO_NUMBER (VALUE)                      FROM v$parameter                     WHERE name = 'db_files')                    file_id                ,1 block_id                ,blocks                ,'tempfile' segment_type                ,'' owner                ,file_name segment_name                ,'' partition_name                ,0 extent_id                ,bytes                ,tablespace_name                ,relative_fno                ,0 segtsn                ,0 segrfn                ,0 segbid            FROM dba_temp_files) SELECT *   FROM datafile_map  WHERE file_id = &&1 AND &&2 BETWEEN block_id AND block_id + blocks; --//在生产系统测试看看，主要测试环境无法测试出来效果。 xxxx> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production xxxx> set timing on xxxx> set verify off xxxx> @ dba 4003C9     RFILE#     BLOCK# BIGFILE_BLOCK# DUMP_CMD ---------- ---------- -------------- -----------------------------------------          1        969        4195273 -- alter system dump datafile 1 block 969 Elapsed: 00:00:00.00 Press enter to find the segment using V$BH (this may take CPU time), CTRL+C to cancel: STATE      BLOCK_CLASS        OBJECT_TYPE         object                                          TCH  MODE_HELD D T P S D FLG_LRUFLG                  DQ ---------- ------------------ ------------------- ---------------------------------------- ---------- ---------- - - - - - ------------------- ---------- scur       data block         INDEX               SYS.I_DEFROLE1                                  175          0 N N N N N A200000:8                    0 Elapsed: 00:00:01.26 Press enter to query what segment resides there using DBA_EXTENTS (this can be IO intensive), CTRL+C to cancel: OWNER  SEGMENT_NAME         PARTITION_NAME                 TABLESPACE_NAME ------ -------------------- ------------------------------ ------------------------------ SYS    I_DEFROLE1                                          SYSTEM Elapsed: 00:00:03.75 --//查询需要3.75秒。 xxxx> @ find_obj 1 969    FILE_ID   BLOCK_ID     BLOCKS SEGMENT_TYPE         OWNER  SEGMENT_NAME    PARTITION_NAME   EXTENT_ID      BYTES TABLESPACE_NAME                RELATIVE_FNO     SEGTSN     SEGRFN     SEGBID ---------- ---------- ---------- -------------------- ------ --------------- --------------- ---------- ---------- ------------------------------ ------------ ---------- ---------- ----------          1        968          8 INDEX                SYS    I_DEFROLE1                               0      65536 SYSTEM                                    1          0          1        968 Elapsed: 00:00:00.69 --//反复测试效果一样。