問題背景：

有時會考慮一件事情，如果在Oracle環(huán)境下出現(xiàn)了鎖阻塞的情況，如何定位到SQL源頭(通過session、lock、transaction等視圖僅能定位到會話)？或許有人會想有沒有可能通過AWR相關(guān)的數(shù)據(jù)庫性能歷史視圖找到有用的信息呢？答案是否定的，因為AWR倉庫中的歷史性能數(shù)據(jù)都是收集的有性能問題的會話、語句、活動，而最初加鎖的那個update語句執(zhí)行速度非常快，往往根本達不到AWR收集的閥值，不被Oracle注意到。而Oracle注意到的都是被“它”給阻塞的那些會話。

是不是沒方法，找到那個始作俑者？有的，只不過比較麻煩，有一個地方是始終會忠實記錄DML活動-----日志(打開Oracle的補充日志)

補充日志

補充日志不是獨立的日志，而是對重做日志變更矢量的補充。LogMiner、閃回事務(wù)、閃回事務(wù)查詢等功能需要開啟補充日志才能正常工作。

在啟動數(shù)據(jù)庫補充日志的時候，會使得所有緩存的游標(biāo)變得無效，突如其來的大量硬解析將對數(shù)據(jù)庫的性能帶來影響。啟動數(shù)據(jù)庫補充日志將等待當(dāng)前所有的事務(wù)結(jié)束，使得該操作之后記錄的所有Redo數(shù)據(jù)都有補充日志的屬性，如果數(shù)據(jù)庫有長事務(wù)或事務(wù)過于頻繁將導(dǎo)致該操作出現(xiàn)Hang住的情況，由此可見啟動數(shù)據(jù)庫的補充日志應(yīng)盡量避開數(shù)據(jù)庫高峰期。

補充日志分為：數(shù)據(jù)庫級補充日志、 表級補充日志

若結(jié)果返回YES或IMPLICIT則說明已開啟最小補全日志，當(dāng)使用ALL,PRIMARY,UNIQUE或FOREIGN補全日志時最小補全日志默認(rèn)開啟(即檢查結(jié)果為IMPLICIT)。

標(biāo)識關(guān)鍵字段補充日志：分為 主鍵、外鍵、唯一索引、全體字段 補充日志 4種

其他補充日志都是基于數(shù)據(jù)庫級最小補充日志之上的，啟用數(shù)據(jù)庫級的其他日志(主鍵、外鍵、唯一索引、ALL)會自動開啟最小補充日志；

如果希望啟用表級的其他類型補充日志，需要先手工開啟數(shù)據(jù)庫級的最小補充日志。

啟用最小補充日志命令：

ALTER DATABASE ADD Supplemental Log Data;

關(guān)閉最小補充日志命令：

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 種 同數(shù)據(jù)庫級 標(biāo)識關(guān)鍵字段補充日志效果用法一樣。只不過是在特定表上啟用補充日志

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 關(guān)鍵字，然后無論 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、重現(xiàn)問題后，啟動并分析redo日志

exec sys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);

4、查看分析結(jié)果

spool c:\log.txt

-- 根據(jù)阻塞源頭及被阻塞會話的sid，注意觀察對應(yīng)的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;

來自 “ ITPUB博客 ” ，鏈接：http://blog.itpub.net/31448824/viewspace-2139366/，如需轉(zhuǎn)載，請注明出處，否則將追究法律責(zé)任。

總結(jié)

以上是生活随笔為你收集整理的oracle 查阻塞日志,通过Oracle补充日志，找到锁阻塞源头的SQL的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。