Script:列出数据库中5%以上链式行的表

以下脚本用于列出数据库中chained/migrated rows达到5%的表,注意查询结果来源于统计信息,如果数据库长期没有gather_stats则结果不真实:

REM List Tables with > 5 % chained rows and > 500 total rows 

  SELECT owner,
         table_name,
         pct_free,
         ROUND (100 * chain_cnt / num_rows, 0) chain_pct
    FROM sys.dba_all_tables
   WHERE ROUND (100 * chain_cnt / num_rows, 0) > 5
         AND owner NOT IN
                ('SYS',
                 'SYSTEM',
                 'SYSMAN',
                 'EXFSYS',
                 'WMSYS',
                 'OLAPSYS',
                 'OUTLN',
                 'DBSNMP',
                 'ORDSYS',
                 'ORDPLUGINS',
                 'MDSYS',
                 'CTXSYS',
                 'AURORA$ORB$UNAUTHENTICATED',
                 'XDB',
                 'FLOWS_030000',
                 'FLOWS_FILES')
         AND num_rows IS NOT NULL
         AND num_rows > 500
ORDER BY 1, 2
/

REM List Table Partitions with > 5 % chained rows and > 500 total rows 

  SELECT table_owner,
         table_name,
         partition_name,
         pct_free,
         ROUND (100 * chain_cnt / num_rows, 0) chain_pct
    FROM sys.dba_tab_partitions
   WHERE ROUND (100 * chain_cnt / num_rows, 0) > 5
         AND table_owner NOT IN
                ('SYS',
                 'SYSTEM',
                 'SYSMAN',
                 'EXFSYS',
                 'WMSYS',
                 'OLAPSYS',
                 'OUTLN',
                 'DBSNMP',
                 'ORDSYS',
                 'ORDPLUGINS',
                 'MDSYS',
                 'CTXSYS',
                 'AURORA$ORB$UNAUTHENTICATED',
                 'XDB',
                 'FLOWS_030000',
                 'FLOWS_FILES')
         AND num_rows IS NOT NULL
         AND num_rows > 500
ORDER BY 1, 2
/

Posted

in

by

Tags:

Comments

4 responses to “Script:列出数据库中5%以上链式行的表”

  1. xs Avatar
    xs

    第一个查的是sys.dba_tables吧

    1. admin Avatar
      admin

      第一段复制错了,因该是sys.dba_all_tables,已修正。

  2. admin Avatar
    admin
    
    REM find session who fetch continued rows
    
     set linesize 140 pagesize 1400;
     
     SELECT /*+ ordered */
            s.sid || '_' || s.serial# sid_serial,
             s.SID,
             s.serial#,
             NVL (
                DECODE (TYPE,
                        'BACKGROUND', 'SYS (' || b.ksbdpnam || ')',
                        s.username),
                SUBSTR (p.program, INSTR (p.program, '(')))
                username,
             ss.VALUE AS continued_fetch,
             s.status,
             s.server,
             s.osuser,
             s.process,
             s.machine,
             s.program,
             s.TYPE,
             (block_gets + consistent_gets) logical_reads,
             physical_reads,
             sql.sql_text
        FROM v$statname sn,
             v$sesstat ss,
             v$session s,
             v$process p,
             x$ksbdp b,
             v$sess_io sio,
             v$sql SQL
       WHERE     sn.statistic# = ss.statistic#
             AND s.SID = ss.SID
             AND s.SID = sio.SID
             AND s.prev_sql_addr = SQL.address(+)
             AND 0 = SQL.child_number(+)
             AND s.prev_hash_value = SQL.hash_value(+)
             AND sn.NAME = 'table fetch continued row'
             AND ss.VALUE > 200
             AND s.paddr = p.addr
             AND b.inst_id(+) = USERENV ('INSTANCE')
             AND p.addr = b.ksbdppro(+)
    ORDER BY ss.VALUE DESC
    /
    
    1. xin Avatar
      xin

      这个应该要先 analyze 不然看不到chain_cnt 吧
      用包分析好象不可以的

Leave a Reply

Your email address will not be published. Required fields are marked *