您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 數據庫性能護航四大支柱：分區、索引重建、段碎片整理、區合並

編輯：Oracle 資料庫基礎

核心理念

性能保護 ≠ 故障修復，而是預防性維護體系。針對大型數據庫，四大支柱直擊存儲層與訪問路徑核心痛點。

一、分區：大表治理的「分而治之」

1. 範圍分區 (Range Partitioning) 經典案例

CREATE TABLE employee (
  emp_no   VARCHAR2(10) PRIMARY KEY,
  name     VARCHAR2(30),
  dept_no  NUMBER(2)
)
PARTITION BY RANGE (dept_no) (
  PARTITION part1 VALUES LESS THAN (11) TABLESPACE part1_ts,
  PARTITION part2 VALUES LESS THAN (21) TABLESPACE part2_ts,
  PARTITION part3 VALUES LESS THAN (31) TABLESPACE part3_ts,
  PARTITION part4 VALUES LESS THAN (MAXVALUE) TABLESPACE part4_ts
);

2. 局部索引 (Local Index) —— 分區表標配

CREATE INDEX emp_dept_idx ON employee (dept_no) LOCAL (
  PARTITION part1 TABLESPACE part1_idx_ts,
  PARTITION part2 TABLESPACE part2_idx_ts,
  PARTITION part3 TABLESPACE part3_idx_ts,
  PARTITION part4 TABLESPACE part4_idx_ts
);

優勢： 分區剪枝、分區級維護 (TRUNCATE/DROP/EXCHANGE PARTITION)、索引分區與表分區一一對應、易管理。

3. 分區維護操作速查

操作	語法示例	典型場景
添加分區	`ALTER TABLE ... ADD PARTITION p5 VALUES LESS THAN (41) TABLESPACE ts5;`	數據增長擴展
刪除分區	`ALTER TABLE ... DROP PARTITION p1;`	歸檔清理舊數據
截斷分區	`ALTER TABLE ... TRUNCATE PARTITION p1;`	快速清空保留結構
交換分區	`ALTER TABLE ... EXCHANGE PARTITION p1 WITH TABLE staging_t;`	ETL 載入/歸檔零停機
移動分區	`ALTER TABLE ... MOVE PARTITION p1 TABLESPACE new_ts;`	存儲分級/壓縮
合併分區	`ALTER TABLE ... MERGE PARTITIONS p1,p2 INTO PARTITION p12;`	小分區合併
拆分分區	`ALTER TABLE ... SPLIT PARTITION p4 AT (35) INTO (PARTITION p4, PARTITION p5);`	熱點分區拆分

二、索引重建：回收「邏輯刪除」空間

現象

頻繁 DELETE/UPDATE 導致索引葉塊碎片化、高水位線不下降、掃描效率降低。
ANALYZE INDEX idx VALIDATE STRUCTURE → 查 INDEX_STATS 中 HEIGHT、LF_BLKS、DEL_LF_ROWS 比例。

重建策略

-- 在線重建 (10g+ 推薦，持鎖極短)
ALTER INDEX idx_name REBUILD ONLINE;

-- 並行重建大索引
ALTER INDEX idx_name REBUILD ONLINE PARALLEL 4;

-- 定期維護腳本 (每日凌晨低峰)
BEGIN
  FOR rec IN (SELECT index_name FROM dba_indexes 
              WHERE owner = 'APP_OWNER' AND status = 'VALID') LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.index_name || ' REBUILD ONLINE';
  END LOOP;
END;
/

三、段碎片整理：消除「多區段」弊端

判斷標準

SELECT segment_name, segment_type, extents
FROM dba_segments
WHERE owner = 'APP_OWNER' AND extents > 10
ORDER BY extents DESC;

經驗值： 單段 Extents > 10 且性能下降時考慮整理。

整理方案

CTAS 重建法 (需停機窗口)：

CREATE TABLE new_t TABLESPACE new_ts AS SELECT * FROM old_t;
DROP TABLE old_t;
RENAME new_t TO old_t;
-- 重建索引、授權、同義詞

Exp/Imp (Data Pump) 法：

expdp system/pwd tables=APP_OWNER.T1,APP_OWNER.T2 directory=DP_DIR dumpfile=t.dmp
-- Drop tables
impdp system/pwd directory=DP_DIR dumpfile=t.dmp TABLE_EXISTS_ACTION=REPLACE

10g+ 在線重定義 (DBMS_REDEFINITION)：業務零中斷，流程較複雜。

四、區合並：釋放「被切碎」的自由空間

原理

表空間中相鄰空閒區 (Free Extents) 默認不自動合並 (除非 PCTINCREASE != 0 觸發 SMON)。
碎片化自由空間導致：大段分配失敗 (ORA-1652/3/4)、空間利用率低。

強制合並

-- 表空間級合並
ALTER TABLESPACE users COALESCE;

-- 驗證效果
SELECT tablespace_name, COUNT(*) chunks, MAX(bytes)/1024/1024 max_mb
FROM dba_free_space
GROUP BY tablespace_name;