Oracle 性能檢測 SQL 進階項目：MTS、碎片、CPU 熱點

MTS (共享服務器) 監控

-- Dispatcher 忙碌度 > 0.5 需擴容
SELECT BUSY/(BUSY+IDLE) "Shared_Servers_Busy" FROM V$DISPATCHER;

-- Dispatcher 等待比例
SELECT SUM(WAIT)/SUM(TOTALQ) "Dispatcher_Waits"
FROM V$QUEUE WHERE TYPE = 'DISPATCHER';

-- Dispatcher 數量
SELECT COUNT(*) FROM V$DISPATCHER;

-- Shared Server 高水位接近 MTS_MAX_SERVERS 需擴容
SELECT SERVERS_HIGHWATER FROM V$MTS;

表空間碎片程度分析

-- 1. 空閒塊數 > 10 判定碎片嚴重
SELECT TABLESPACE_NAME, COUNT(TABLESPACE_NAME)
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
HAVING COUNT(TABLESPACE_NAME) > 10;

-- 2. 整理碎片
ALTER TABLESPACE  COALESCE;
ALTER TABLE  DEALLOCATE UNUSED;

-- 3. 視圖化塊分佈
CREATE OR REPLACE VIEW TS_BLOCKS_V AS
SELECT TABLESPACE_NAME, BLOCK_ID, BYTES, BLOCKS, 'FREE SPACE' SEGMENT_NAME
FROM DBA_FREE_SPACE
UNION ALL
SELECT TABLESPACE_NAME, BLOCK_ID, BYTES, BLOCKS, SEGMENT_NAME
FROM DBA_EXTENTS;

SELECT * FROM TS_BLOCKS_V ORDER BY TABLESPACE_NAME, BLOCK_ID;

-- 4. 空閒空間統計
SELECT TABLESPACE_NAME, SUM(BYTES), MAX(BYTES), COUNT(BLOCK_ID)
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME;

高碎片表識別

SELECT SEGMENT_NAME TABLE_NAME, COUNT(*) EXTENTS
FROM DBA_SEGMENTS
WHERE OWNER NOT IN ('SYS','SYSTEM')
GROUP BY SEGMENT_NAME
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM DBA_SEGMENTS GROUP BY SEGMENT_NAME);

表/索引存儲情況檢查

-- 表
SELECT SEGMENT_NAME, SUM(BYTES), COUNT(*) EXT_QUAN
FROM DBA_EXTENTS
WHERE TABLESPACE_NAME = '&TABLESPACE_NAME' AND SEGMENT_TYPE = 'TABLE'
GROUP BY TABLESPACE_NAME, SEGMENT_NAME;

-- 索引
SELECT SEGMENT_NAME, COUNT(*)
FROM DBA_EXTENTS
WHERE SEGMENT_TYPE = 'INDEX' AND OWNER = '&OWNER'
GROUP BY SEGMENT_NAME;

CPU 熱點會話定位

-- STATISTIC#=12 為 "CPU used by this session"
SELECT A.SID, SPID, STATUS, SUBSTR(A.PROGRAM,1,40) PROG,
       A.TERMINAL, OSUSER, VALUE/60/100 VALUE
FROM V$SESSION A, V$PROCESS B, V$SESSTAT C
WHERE C.STATISTIC# = 12
  AND C.SID = A.SID
  AND A.PADDR = B.ADDR
ORDER BY VALUE DESC;

解讀：VALUE 單位為百分之一秒，除以 6000 得分鐘級 CPU 時間。優先優化佔用 CPU 前列的會話 SQL。