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

June 3, 2011

mac

Tags:

Comments

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

xs

June 3, 2011

第一个查的是sys.dba_tables吧

Reply
1. admin
  
  June 3, 2011
  
  第一段复制错了，因该是sys.dba_all_tables，已修正。
  
  Reply

admin

June 17, 2011


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
/

xin

August 17, 2012

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

Reply

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

Comments

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

Leave a Reply Cancel reply