[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

--//反覆測試效果一樣。