Oracle的邏輯結構(表空間、段、區間、塊)――Oracle數據塊（二）

‍關於熱點塊的查詢 ==== 查詢當前數據庫 最繁忙的 Buffer ， TCH(Touch) 表示訪問次數越高，熱點快競爭問題就存在 ===== SELECT * FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch            FROM x$bh        ORDER BY tch DESC) WHERE ROWNUM < 11; ==== 查詢當前數據庫最繁忙的 Buffer ，結合 dba_extents 查詢得到這些熱點 Buffer 來自哪些對象 ===== SELECT 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; ============= 如果在 Top 5 中發現 latch free 熱點塊事件時，可以從 V$latch_children 中查詢具體的子 Latch 信息 ============ SELECT * FROM (SELECT addr, child#, gets, misses, sleeps, immediate_gets igets,                 immediate_misses imiss, spin_gets sgets            FROM v$latch_children           WHERE NAME = 'cache buffers chains'        ORDER BY sleeps DESC) WHERE ROWNUM < 11; ================ 獲取當前持有最熱點數據塊的 Latch 和 buffer 信息 ========== 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; =============== 利用前面的 SQL 可以找到這些熱點 Buffer 的對象信息 =========== 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; ================ 結合 SQL 視圖可以找到操作這些對象的相關 SQL ，然後通過優化 SQL 減少數據的訪問， 或者優化某些容易引起爭用的操作（如 connect by 等操作）來減少熱點塊競爭 ================= break on hash_value skip 1 SELECT /*+ rule */ hash_value,sql_text    FROM v$sqltext WHERE (hash_value, address) IN (            SELECT a.hash_value, a.address              FROM v$sqltext a,                   (SELECT DISTINCT a.owner, a.segment_name, a.segment_type                               FROM dba_extents a,                                    (SELECT dbarfil, dbablk                                       FROM (SELECT dbarfil, dbablk                                                 FROM x$bh                                             ORDER BY tch DESC)                                      WHERE ROWNUM < 11) b                              WHERE a.relative_fno = b.dbarfil                                AND a.block_id <= b.dbablk                                AND a.block_id + a.blocks > b.dbablk) b             WHERE a.sql_text LIKE '%' || b.segment_name || '%'               AND b.segment_type = 'TABLE') ORDER BY hash_value, address, pIEce;
也可以參看 熱點塊競爭和解決（cache buffers chains）