Oracle 實戰經驗（二）：會話監控、鎖等待與記憶體效能診斷 SQL

誰正在存取資料庫？

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;

殺掉會話：ALTER SYSTEM KILL SESSION 'sid,serial#';

誰被鎖住？（等待鎖）

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;

誰在鎖表？（持有鎖的活動會話）

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 sys.v_$process;
SELECT COUNT(*) FROM sys.v_$transaction;

哪些 Package 需要 KEEP 在 Shared Pool？

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) 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';

Rollback Segment 爭用檢測

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');
-- 若 waitstat.count / sysstat.sum > 1% 應考慮增加 Rollback Segment

進行中交易所在的 Rollback Segment

COLUMN osuser FORMAT A15
COLUMN username FORMAT A15
COLUMN segment_name FORMAT A80
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(+);