oracle in查询 一直等待,学习笔记:Oracle awr 分析解决inactive transaction branch等待事件...

天萃荷净

通过Oracle AWR报告分析inactive transaction branch等待事件的原因

分析一份awr,发现不太熟悉的等待事件”inactive transaction branch”,awr相关信息如下

分析top 1 sql中的对象

SQL> select * from v$version;

BANNER

-----------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL> select owner,object_type from dba_objects where object_name =upper('wCommonShortMsg');

OWNER OBJECT_TYPE

------------------------------ -------------------

PUBLIC SYNONYM

OFFONQUERY SYNONYM

SQL> COL DB_LINK FOR A12

SQL> select TABLE_OWNER,TABLE_NAME,DB_LINK from dba_SYNONYMS WHERE

2 SYNONYM_NAME=upper('wCommonShortMsg');

TABLE_OWNER TABLE_NAME DB_LINK

------------------------------ ------------------------------ ------------

WCOMMONSHORTMSG CRMDB_LINK

WCOMMONSHORTMSG DB_LINK_CRM

通过这里查询,可以确定引起dblink相关等待严重的是关于wCommonShortMsg同义词查询导致(使用dblink连接到其他库),结合数据库版本，大致可以确定inactive transaction branch等待和MOS中的bug 10413418相符

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle awr 分析解决inactive transaction branch等待事件