Oracle 應用開發實戰經驗彙編（二）：效能監控、鎖分析與系統查詢腳本庫

即時監控查詢腳本庫

誰在存取資料庫？

SELECT c.sid, c.serial#, c.username, a.object_id, b.object_name,
       c.program, c.status, d.name, c.osuser
FROM   v$locked_object a,
       all_objects b,
       v$session c,
       audit_actions d
WHERE  a.object_id = b.object_id
AND    a.session_id = c.sid(+)
AND    c.command = d.action;

強制中斷 Session

ALTER SYSTEM KILL SESSION '&sid,&serial#';

特定使用者活動會話

SELECT a.sid, a.serial#, a.username, a.status, a.program, b.name, a.osuser
FROM   v$session a, audit_actions b
WHERE  a.command = b.action
AND    a.username = '&username';

鎖與阻塞分析

等待鎖的 Session

SELECT a.sid, a.serial#, a.username, a.lockwait, a.status, a.program, b.name
FROM   v$session a, audit_actions b
WHERE  a.command = b.action
AND    a.lockwait IS NOT NULL;

持有鎖的 Session

SELECT a.sid, a.serial#, a.username, a.lockwait, a.status, a.program, b.name
FROM   v$session a, audit_actions b
WHERE  a.command = b.action
AND    a.status = 'ACTIVE';

等待列級鎖的詳細資訊

SELECT sid, serial#, object_name, row_wait_block#,
       row_wait_row#, row_wait_file#
FROM   all_objects, v$session
WHERE  row_wait_obj# = object_id
AND    type = 'USER'
AND    lockwait IS NOT NULL;

鎖鏈追蹤：誰鎖住誰

SELECT sl.username, sl.sid, sl.serial#
FROM   v$lock l1, v$session s1
WHERE  EXISTS (
    SELECT * FROM v$lock l2, v$session s2
    WHERE  l2.sid = s2.sid
    AND    l2.id1 = l1.id1
    AND    s2.lockwait = l2.kaddr
    AND    l2.request = 0
    AND    l1.sid = s1.sid
);

系統負載指標

SELECT COUNT(*) FROM v$session;       -- 當前連線數
SELECT COUNT(*) FROM v$process;       -- 背景/前景進程數
SELECT COUNT(*) FROM v$transaction;   -- 作用中交易數

共用池物件固定候選查詢

COLUMN owner FORMAT A10
SELECT owner, name, type,
       source_size + code_size + parsed_size + error_size AS bytes
FROM   dba_object_size
WHERE  type = 'PACKAGE BODY'
ORDER BY 4 DESC;

使用者區段分佈查詢

SELECT tablespace_name, owner, segment_name, segment_type
FROM   dba_segments
WHERE  owner = 'SYS'
AND    segment_type = 'ROLLBACK'
ORDER BY tablespace_name, owner, segment_name;

BREAK ON owner ON segment_name
COLUMN segment_name FORMAT A15
COLUMN tablespace_name FORMAT A15
COLUMN file_name FORMAT A20
SELECT a.owner, a.segment_name, b.tablespace_name, b.file_name,
       SUM(a.bytes) bytes
FROM   dba_extents a, dba_data_files b
WHERE  a.file_id = b.file_id
GROUP BY a.owner, a.segment_name, b.tablespace_name, b.file_name;

緩衝區命中率計算

SELECT cur.value db_block_gets,
       con.value consistent_gets,
       phy.value physical_reads,
       ROUND((cur.value + con.value) / (cur.value + con.value + phy.value) * 100, 2) AS hit_ratio
FROM   v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE  cur.name = 'db block gets'
AND    con.name = 'consistent gets'
AND    phy.name = 'physical reads';

回滾段爭用檢測

SELECT class, count
FROM   v$waitstat
WHERE  class IN ('system undo header', 'system undo block', 'undo header', 'undo block');

SELECT SUM(value) FROM v$sysstat
WHERE  name IN ('db block gets', 'consistent gets');

-- 若 wait_count / total_gets > 1% → 需增加回滾段

作用中交易所在回滾段

COLUMN osuser FORMAT A15
COLUMN username FORMAT A15
COLUMN segment_name FORMAT A30
SELECT s.osuser, s.username, r.segment_name, sa.sql_text
FROM   v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa
WHERE  s.taddr = t.addr
AND    t.xidusn = r.segment_id(+)
AND    s.sql_address = sa.address(+);