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

Comments

1. xs says

   2011/06/03 at 12:50

   第一个查的是sys.dba_tables吧

   回复
   • admin says

     2011/06/03 at 14:01

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

     回复
2. admin says

   2011/06/17 at 00:38

   
   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 says

     2012/08/17 at 11:15

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

     回复