p183.sql

oracal的入门知识

SELECT b.addr, a.ts#, a.dbarfil, a.dbablk, a.tch, b.gets, b.misses, b.sleeps
  FROM (SELECT *
          FROM (SELECT   addr, ts#, file#, dbarfil, dbablk, tch, hladdr
                    FROM x$bh
                ORDER BY tch DESC)
         WHERE ROWNUM < 11) a,
       (SELECT addr, gets, misses, sleeps
          FROM v$latch_children
         WHERE NAME = 'cache buffers chains') b
 WHERE a.hladdr = b.addr
/

SELECT distinct e.owner, e.segment_name, e.segment_type
           FROM dba_extents e,
                (SELECT *
                   FROM (SELECT   addr, ts#, file#, dbarfil, dbablk, tch
                             FROM x$bh
                         ORDER BY tch DESC)
                  WHERE ROWNUM < 11) b
          WHERE e.relative_fno = b.dbarfil
            AND e.block_id <= b.dbablk
            AND e.block_id + e.blocks > b.dbablk;