• 通过Oracle补充日志,找到锁阻塞源头的SQL


    转  :  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;

  • 相关阅读:
    DeepZoomPix照片浏览的新体验
    SilverTouch系列 SilverAlbum Ver1.0
    手把手玩转win8开发系列课程(28)
    百度之星试题每周一练
    手把手玩转win8开发系列课程(27)
    WP7和Android控件对照表
    门户网站负载均衡技术的六大新挑战
    RA_CUST_TRX_LINE_GL_DIST_ALL
    XML PUBLISHER输出excel存在科学计数
    中文字符按拼音排序
  • 原文地址:https://www.cnblogs.com/andy6/p/5721221.html
Copyright © 2020-2023  润新知