2025年7月27日 星期日

Oracle Plan Baseline 固定之談



 
Oracle版本 : 19.3.0

觀察一個 DB 執行SQL 會忽快忽慢, 通常會 建立 SQL Plan Baseline , 目的是 防止 Optimizer 隨統計資訊波動而「選錯計畫」,用來 穩定高頻、高價值 SQL 的效能行為

可以看出來 oracle 的建議 



有出現兩個 plan hash , 一個是 0.047 秒, 另外一個是 3.923秒  
這樣我們就知道 建立 Plan Baseline 是 3426048666 是 0.047 秒這一個 plan hash 


BEGIN
  DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE(
    task_name        => 'TASK_111258',     -- 實際 Tuning Task 名稱
    owner_name       => 'SYS',                   -- 建立該 Task 的使用者
    plan_hash_value  => 3426048666        -- 最快計畫的 hash 值
  );
END;
/



*-----------------------------*

但建立  Plan Baseline 也不是萬無一失
以下操作會讓  Plan Baseline 中的 hash value 無法命中 , 而讓SQL執行爆走


Oracle 的 hash value 不是純邏輯計劃,而是對執行路徑中一連串 operation、成本估算、物件資訊的 hash 結果, 以下這些都會改變 hash 值 

Index Rebuild : 可能會變, Oracle 會重新估算 I/O 路徑與成本
Gather Statistics : 高機率會變 , 當 num_rows, distinct_keys, clustering_factor 改變
改變 Optimizer 參數 : 可能會變, 如 _optimizer_cost_model、optimizer_mode



通常 有觀察到 index rebuild 後  SQL 反而不穩定, 可以再次執行  建立 Plan Baseline , 讓他固定

執行完後 可以查一下 是否有使用剛剛建立的 base line 

SELECT sql_id, sql_plan_baseline, executions
FROM v$sql
WHERE sql_id = '6z7p1645q3gzga';


SELECT *
FROM dba_sql_plan_baselines



*-----------------------------*

企業生產上用法可以這樣

-- 在 rebuild 前,先記錄好 baseline plan hash
SELECT sql_handle, plan_name, plan_hash_value
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%SQL內容%';

-- Rebuild 後重新 evolve 舊 baseline 
EXEC DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => '...', plan_name => '...');

-- 或從 Cursor Cache reload 新的好計畫


或者 重新在跑一次評估, 再次建立新的 baseline 















2025年6月20日 星期五

RMAN-05535: WARNING: All redo log files were not defined correctly.


Oracle 版本 : 19.3


在要建立備庫時, 使用了 RMAN DUPLICATE for standby 時, 備庫 datafile 位置一切正常, 
但觀察到 備庫的 redo log , standby redo log 跑去了dbs/broken0 , 
變成一堆的 broken 開頭的檔案 
但我確定我的備庫 log_file_name_convert 參數有設定而且正確
且我是使用 duplicate target database for standby nofilenamecheck; 


nofilenamecheck 參數, 是我對於備庫有相同的file 被還原檔案覆蓋過去 有責任且確認可以被覆蓋
redo file 怎麼還會跑去別的地方



11g 從沒發生過的事情,  在 19.3 版本發生了 , 相關錯誤訊息是這樣 

RMAN-05535: warning: All redo log files were not defined properly.

alter database rename file '/db/oradata/LAB/redo01.log' to 'broken0'
Completed: alter database rename file '/ORA/db/oradata/LAB/redo01.log' to 'broken0'
alter database rename file '/db/oradata/LAB/redo02.log' to 'broken1'
Completed: alter database rename file '/db/oradata/LAB/redo02.log' to 'broken1'
alter database rename file '/db/oradata/LAB/redo03.log' to 'broken2'
Completed: alter database rename file '/db/oradata/LAB/redo03.log' to 'broken2'



看了一下文章是 

Here is a statement from the SR from Oracle:
“The code did change on 19c and new versions to avoid overwriting of the online/standby redo logs.
Currently, there is no option to avoid such behavior that I am aware of.
There were many cases of RMAN duplicate that caused overwriting on ONLINE redo logs at Primary so this changes must have been implemented to avoid such scenarios.

就是說 19c 之後 oracle 對於 假如你要設定主備庫的相同目錄下 , 這樣redo/standby 都會在別的地方還原, 且沒有設定可以改 


這邊提供兩個解決方法
1.自己操作備庫把檔案移到正確位置
--備庫先停同步
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

--改手動
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = MANUAL SCOPE = BOTH ; 



-- 搬移檔案
alter database rename file '/19.3/dbs/broken1' to '/db/oradata/LAB/redo02.log';
alter database rename file '/19.3/dbs/broken2' to '/db/oradata/LAB/redo03.log';
alter database rename file '/19.3/dbs/broken3' to '/db/oradata/LAB/redo04.log';
alter database rename file '/19.3/dbs/broken8'  to '/db/oradata/LAB/st_redo_01_02.log';
alter database rename file '/19.3/dbs/broken9'  to '/db/oradata/LAB/st_redo_01_03.log';
alter database rename file '/19.3/dbs/broken10' to '/db/oradata/LAB/st_redo_01_04.log';
alter database rename file '/19.3/dbs/broken11' to '/db/oradata/LAB/st_redo_01_05.log';

-- 查看一下是否在正確位置
select v1.thread#, v1.group#, v2.member from v$log

-- 重新 clear redo/standby 
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
alter database clear logfile group 8;

要記得 每一個都要 clear 一次, 不然你會漏掉

--備庫開啟同步 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

--改成自動
alter system set standby_file_management = AUTO scope = both ;




2. 就是重新同步備庫, 重新設定 log_file_name_convert  

這一次 把備庫的  log_file_name_convert , 設定成不同的目錄, 這樣File就會全部一起了

*.log_file_name_convert='/db/oradata/LAB','/db/oradata/LAB1'