轉 : http://www.itnose.net/detail/6440069.html
問題背景:
有時會考慮一件事情,如果在Oracle環境下出現了鎖阻塞的情況,如何定位到SQL源頭(通過session、lock、transaction等視圖僅能定位到會話)?或許有人會想有沒有可能通過AWR相關的資料庫性能曆史視圖找到有用的資訊呢?答案是否定的,因為AWR倉庫中的曆史性能資料都是收集的有性能問題的會話、語句、活動,而最初加鎖的那個update語句執行速度非常快,往往根本達不到AWR收集的閥值,不被Oracle注意到。而Oracle注意到的都是被“它”給阻塞的那些會話。
是不是沒方法,找到那個始作俑者?有的,隻不過比較麻煩,有一個地方是始終會忠實記錄DML活動-----日志(打開Oracle的補充日志)
補充日志
補充日志不是獨立的日志,而是對重做日志變更矢量的補充。LogMiner、閃回事務、閃回事務查詢等功能需要開啟補充日志才能正常工作。
在啟動資料庫補充日志的時候,會使得所有緩存的遊标變得無效,突如其來的大量硬解析将對資料庫的性能帶來影響。啟動資料庫補充日志将等待目前所有的事務結束,使得該操作之後記錄的所有Redo資料都有補充日志的屬性,如果資料庫有長事務或事務過于頻繁将導緻該操作出現Hang住的情況,由此可見啟動資料庫的補充日志應盡量避開資料庫高峰期。
補充日志分為:資料庫級補充日志、 表級補充日志
若結果傳回YES或IMPLICIT則說明已開啟最小補全日志,當使用ALL,PRIMARY,UNIQUE或FOREIGN補全日志時最小補全日志預設開啟(即檢查結果為IMPLICIT)。
辨別關鍵字段補充日志:分為 主鍵、外鍵、唯一索引、全體字段 補充日志 4種
其他補充日志都是基于資料庫級最小補充日志之上的,啟用資料庫級的其他日志(主鍵、外鍵、唯一索引、ALL)會自動開啟最小補充日志;
如果希望啟用表級的其他類型補充日志,需要先手工開啟資料庫級的最小補充日志。
啟用最小補充日志指令:
ALTER DATABASE ADD Supplemental Log Data;
關閉最小補充日志指令:
ALTER DATABASE DROP Supplemental Log Data;
啟用其他類型的日志:
alter database add supplemental log data (Primary key) columns;
alter database add supplemental log data (unique) columns;
alter database add supplemental log data (foreign key) columns;
alter database add supplemental log data (all) columns;
表級補充日志:
分為: 主鍵、唯一索引、外鍵、全體字段、使用者自定義字段 5種。
前 4 種 同資料庫級 辨別關鍵字段補充日志效果用法一樣。隻不過是在特定表上啟用補充日志
1、 alter table tb_name add supplemental log data (primary key) columns;
2、 alter table tb_name add supplemental log data (unique) columns;
3、 alter table tb_name add supplemental log data (foreign key) columns;
4、 alter table tb_name add supplemental log data (all) columns;
5、 alter table tb_name add supplemental log group group_name (col01, col02, col05, col09) always;
--自定義字段表級補充日志 使用者可以任意指定哪些字段的舊值需要被補充日志記錄。
有條件記錄式:記載是指 無論 update 那個字段 這組中的其他字段都的記錄其舊值
無條件記錄式:記載是指 有無條件記錄式記載 指令末尾 加 always 關鍵字,然後無論 update 該行那個字段,都記錄該組中的字段的舊值。
表級補充日志的情況 通過 dba_log_groups 和 dba_log_group_columns 視圖查詢獲得
通過補充日志定位阻塞源頭:
1、啟用補充日志
select log_mode, open_mode, flashback_on, supplemental_log_data_min from v$database;
alter database add supplemental log data;
2、将redo日志檔案添加到logmnr分析日志清單
exec sys.dbms_logmnr.add_logfile(logfilename=>'redo日志1', options=>sys.dbms_logmnr.new);
exec sys.dbms_logmnr.add_logfile(logfilename=>'redo日志2', options=>sys.dbms_logmnr.addfile);
exec sys.dbms_logmnr.add_logfile(logfilename=>'redo日志n', options=>sys.dbms_logmnr.addfile);
3、重制問題後,啟動并分析redo日志
exec sys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);
4、檢視分析結果
spool c:\log.txt
-- 根據阻塞源頭及被阻塞會話的sid,注意觀察對應的XID和table_name
select t.SCN, t.SESSION#, t.SERIAL#, t.XID, t.table_name, t.SQL_REDO, t.SQL_UNDO from v$logmnr_contents t where t.SESSION# IN (xxx)
spool off
5、停止logmnr
exec sys.dbms_logmnr.end_logmnr;
6、取消補充日志
alter database drop supplemental log data (primary key) columns;
alter database drop supplemental log data (unique) columns;
alter database drop supplemental log data;
本文轉自 張沖andy 部落格園部落格,原文連結: http://www.cnblogs.com/andy6/p/5721221.html ,如需轉載請自行聯系原作者