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

Oracle 資源管理器（DRM）與 SQL 連接優化全攻略：CPU 分配、連接池、SQL 寫法、後台處理九大維度

編輯：Oracle 資料庫基礎

系統資源管理分配計劃：Database Resource Manager (DRM)

Oracle DRM 讓 DBA 以資源消費者群組為單位，控制 CPU、並行度、閒置逾時等資源分配，實現多租戶/混合負載隔離。

核心概念與架構

元件	角色	範例
Resource Consumer Group	用戶/會話的邏輯分組	OLTP_GROUP, BATCH_GROUP, REPORT_GROUP
Resource Plan	定義群組間資源分配比例	DAY_PLAN: OLTP 75%, BATCH 25%
Plan Directive	具體分配參數（CPU%、並行度、逾時）	OLTP: CPU_P1=75, MAX_DEGREE_PARALLEL=4
Mapping Rule	會話自動歸屬群組規則	USER=SCOTT → OLTP_GROUP; MODULE='ETL' → BATCH_GROUP

快速建立範例：日間 OLTP 優先、夜間批次優先

-- 1. 建立消費者群組
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group => 'OLTP_GROUP', comment => '線上交易優先');
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group => 'BATCH_GROUP', comment => '批次處理');
END;
/

-- 2. 建立資源計畫
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
    plan => 'DAY_PLAN', comment => '白天計畫：OLTP 75% CPU');
  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
    plan => 'NIGHT_PLAN', comment => '夜間計畫：批次 70% CPU');
END;
/

-- 3. 定義指令：多級 CPU 分配（P1 最高優先級）
BEGIN
  -- 白天：OLTP 保證 75%，批次 25%
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'DAY_PLAN', group_or_subplan => 'OLTP_GROUP',
    cpu_p1 => 75, parallel_degree_limit_p1 => 4);
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'DAY_PLAN', group_or_subplan => 'BATCH_GROUP',
    cpu_p1 => 25, parallel_degree_limit_p1 => 2);
    
  -- 夜間：批次 70%，OLTP 30%
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'NIGHT_PLAN', group_or_subplan => 'BATCH_GROUP',
    cpu_p1 => 70, parallel_degree_limit_p1 => 8);
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'NIGHT_PLAN', group_or_subplan => 'OLTP_GROUP',
    cpu_p1 => 30, parallel_degree_limit_p1 => 4);
END;
/

-- 4. 映射規則：依用房/模組自動分組
BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER, value => 'APP_USER',
    consumer_group => 'OLTP_GROUP');
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute => DBMS_RESOURCE_MANAGER.MODULE, value => 'ETL_JOB',
    consumer_group => 'BATCH_GROUP');
END;
/

-- 5. 啟用計畫（可排程切換）
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DAY_PLAN' SCOPE=BOTH;
-- 夜間排程：DBMS_SCHEDULER 建立 JOB 切換 NIGHT_PLAN

DRM 進階能力：MAX_IDLE_TIME 閒置斷線、MAX_IDLE_BLOCKER_TIME 阻塞釋放、SWITCH_GROUP 超時降級、ACTIVE_SESS_POOL 並發佇列、UNDO_POOL 回滾段配額。

SQL 優化與連接方案九大維度

1. 直接 OLE DB 連接（Windows/ADO 環境）

避開 ODBC → OLE DB 轉換層，減少呼叫開銷。連接字串：

Provider=OraOLEDB.Oracle;Data Source=ORCL;User Id=scott;Password=tiger;

2. 連接池機制：效能倍增器

無連接池	有連接池
每請求建立/銷毀物理連接	預建連接複用，毫秒級獲取
用戶等待網路握手、認證	無等待，直接從池取用
資料庫進程頻繁啟動/清理	進程穩定，減少 OS/DB 開銷

配置關鍵（以 IIS/ASP.NET 為例）：

3. 高效 SQL 設計七原則

減少資料庫往返：批次處理、陣列綁定、PL/SQL 封裝邏輯
SQL 文本複用：使用綁定變數（Bind Variables），相同邏輯不同值共用解析樹與執行計畫
限制動態 SQL：即使文本相同，動態 SQL 仍會 Hard Parse
避免無條件全表掃描：SELECT * FROM big_table → 必加 WHERE、分頁、限制欄位
約束前移至 DDL：NOT NULL, CHECK, FK 在建表定義，而非應用層驗證
批次提交控制：關閉 Auto-Commit，COMMIT/ROLLBACK 显式控制事務邊界
行預取優化：SET ARRAYSIZE 100 / JDBC setFetchSize(200) 減少網路往返

4. 後台處理減少網路流量

技術	適用場景	實現方式
臨時表/全域暫存表	多步驟中間結果、複雜報表	`CREATE GLOBAL TEMPORARY TABLE gtt_rpt ... ON COMMIT PRESERVE ROWS`
物化視圖	預聚合、預連接、跨庫同步	`CREATE MATERIALIZED VIEW mv_sales SUMMARY ... REFRESH FAST ON COMMIT`
PL/SQL 套件/存儲過程	封裝業務邏輯、批次處理	將多條 SQL 移入資料庫端單次呼叫
資料庫連結/遠程程序	分散式查詢、異構同步	`DBLINK` + `REMOTE PROCEDURE CALL`
快照/資料複製	唯讀報表卸載、異地備份	`CREATE SNAPSHOT ... REFRESH FAST EVERY 1 HOUR`