DBMS_XPLAN.DISPLAY_AWR
The plan_hash_value requires BIGINT or NUMERIC in PostgreSQL due to potential values exce… 針對透過 Oracle DBMS_XPLAN.DISPLAY_AWR 匯出的文字檔（如 sql-plan-DBANME-[export_date].[SQL_ID].txt），將其寫入 PostgreSQL 資料庫 report 時，資料表設計與實務考量如下：
──────
關鍵注意事項（DBA 實務踩坑點）
#
- plan_hash_value 務必使用 BIGINT（不可用 INTEGER）：
• Oracle 的 Plan Hash Value 是 unsigned 32-bit 整數（範圍 0 ∼ 4, 294, 967, 295）。
• PostgreSQL 的 INTEGER 為 signed 32-bit（最大值約 21.4 億）。在您的 analyze-sql/ 資料集中，許多 PHV（例如 3814512925、4064160458）已超過 21 億，若使用 INTEGER 會導致 value out of range for type integer 錯誤。
- 單一 SQL_ID 可能包含多個 Plan Hash Value：
• 在 AWR 歷史中，一個 SQL 語句若歷經執行計畫漂移（Plan Regression）或統計資訊重算，DISPLAY_AWR 產出內會同時包含多組 Plan（例如專案中的 56ut6awb07108.txt 即包含 3814512925 與 4064160458 兩個計畫）。
• 因此，不能單獨以 sql_id 作為唯一鍵，應以 (db_name, sql_id, plan_hash_value) 為組合唯一鍵。
- 區分不同資料庫來源 (db_name)：
• 專案中有多個 DB 環境（如 [DBANME]、K1MES、K1DM），不同 DB 可能出現相同 SQL_ID，必須加入 db_name 欄位以作區隔。
──────
推薦設計：單表扁平化設計（最適合分析與查詢）
#
參考您資料庫既有的命名風格（如 rpt_top_sql_report、rpt_awr_topsql），建議建表名稱為 rpt_awr_sql_plan：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
CREATE TABLE rpt_awr_sql_plan (
-- 系統自增主鍵
id BIGSERIAL PRIMARY KEY,
-- 核心業務欄位
db_name VARCHAR(50) NOT NULL, -- 來源資料庫 (例如: [DBANME])
sql_id VARCHAR(13) NOT NULL, -- Oracle 13碼 Base32 SQL ID
plan_hash_value BIGINT NOT NULL, -- 務必用 BIGINT 避免溢位
sql_fulltext TEXT NOT NULL, -- 完整 SQL 語句 (Full SQL Content)
plan_content TEXT NOT NULL, -- 完整執行計畫內容 (含表格與Predicates)
-- 建議補充的輔助欄位 (利於檢索與分析)
cost BIGINT, -- 執行計畫總成本 (從 Id: 0 取出，方便下 WHERE cost > 10000)
source_file VARCHAR(255), -- 來源檔案名稱 (例如: sql-plan-[DBANME]-20260922.[SQL_ID].txt)
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- 確保同一個 DB 下，相同 SQL 的同一個 Plan 不會重複匯入
CONSTRAINT uq_rpt_awr_sql_plan UNIQUE (db_name, sql_id, plan_hash_value)
);
-- 常用查詢索引
CREATE INDEX idx_rpt_awr_sql_plan_sql_id ON rpt_awr_sql_plan (sql_id);
CREATE INDEX idx_rpt_awr_sql_plan_db_sid ON rpt_awr_sql_plan (db_name, sql_id);
CREATE INDEX idx_rpt_awr_sql_plan_phv ON rpt_awr_sql_plan (plan_hash_value);
|
──────
替代方案：主從正規化設計（若希望 SQL 與 Plan 完全解耦）
#
若系統有大量歷史記錄且同一 SQL 有極多 Plan 版本，為了避免 sql_fulltext 重複儲存，可拆為兩張表：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
-- 1. SQL 語句主表
CREATE TABLE rpt_awr_sql (
db_name VARCHAR(50) NOT NULL,
sql_id VARCHAR(13) NOT NULL,
sql_fulltext TEXT NOT NULL,
first_seen TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (db_name, sql_id)
);
-- 2. 執行計畫子表
CREATE TABLE rpt_awr_plan (
id BIGSERIAL PRIMARY KEY,
db_name VARCHAR(50) NOT NULL,
sql_id VARCHAR(13) NOT NULL,
plan_hash_value BIGINT NOT NULL,
plan_content TEXT NOT NULL,
cost BIGINT,
source_file VARCHAR(255),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (db_name, sql_id) REFERENCES rpt_awr_sql (db_name, sql_id) ON DELETE CASCADE,
CONSTRAINT uq_rpt_awr_plan UNIQUE (db_name, sql_id, plan_hash_value)
);
|
│ 建議：對於調校分析與產出報告而言，單表扁平化設計（rpt_awr_sql_plan） 查詢最直覺，無需每次做
│ JOIN，後續配合 Python 腳本或您自製的 sql CLI 工具寫入也最為單純。
──────
解析與寫入邏輯概要
#
從 analyze-sql/sql-plan-*.txt 切割資料的邏輯：
- db_name 與 sql_id：可直接從檔名 sql-plan-{db_name}-{date}.{sql_id}.txt 解析。
- sql_fulltext：擷取 SQL_ID {sql_id} 標題之後，到第一個 Plan hash value: 之前的文字區塊。
- Plan hash value 與 plan_content：
• 使用正規表達式匹配 Plan hash value:\s*(\d+)。
• 將每一個 Plan Hash Value 及其下方的 ASCII Plan 表格與 Predicate Information 區塊獨立切出。
• 每個 Plan 各存入一筆資料，若遇重複則可採用 PostgreSQL 的 ON CONFLICT (db_name, sql_id,
plan_hash_value) DO UPDATE … 進行更新。
