前言:

近期处理了一起数据库AWR快照无法自动生成的问题，用户发现数据库在近期出现了AWR快照无法自动生成的问题，数据库整体负载正常，后面分析发现原因是由于AWR快照在生成过程中，执行的SQL语句出现超时导致。

问题:

Oracle11.2.0.4版本AWR快照无法自动生成，27-30号之间出现AWR快照无法自动生成问题

问题原因:

1 awr快照生成任务进程m00x发起wrh$_sql_bind_metadata表的插入语句执行超时，导致AWR快照生成失败

2 wrh$_sql_bind_metadata表的插入语句超时是由于插入语句需要访问内存基表x$kqlfbc(x$kqlfbc基表是绑定变量的内存缓存表)，而当前数据库里面语句使用了大量的绑定变量，导致在访问该X$基表时需要检索内存里面大量的绑定变量信息，执行效率非常缓慢。

问题分析:

查看数据库后台日志，可以发现有MMON进程挂起suspend 82800 seconds的告警

mmon进程的trc文件，可以发现类似的slave进程挂起操作挂起Unable to schedule a MMON slave at: Auto Flush Main 1，Slave action has been temporarily suspended，- Slave action had prior policy violations.

查看AWR快照子进程m000进程trc文件，出现关于wrh$_sql_bind_metadata表的插入语句执行超时的情况，报ORA-12751: cpu time or run time policy violation 

到这里，我们可以确认 awr快照生成任务进程m00x发起wrh$_sql_bind_metadata表的插入语句执行超时，导致AWR快照生成失败

语句主要通过关联查询基表x$kewrattrnew，x$kewrsqlidtab以及v$sql_bind_capture，将结果插入到wrh$_sql_bind_metadata表

insert into wrh$_sql_bind_metadata   
(snap_id, dbid,    sql_id, name, position, dup_position,    datatype, datatype_string,    character_sid, precision, scale, max_length)  
SELECT /*+ ordered use_nl(bnd) index(bnd sql_id) */      :lah_snap_id, :dbid,      bnd.sql_id, name, position, dup_position,      datatype, datatype_string,      character_sid, precision, scale, max_length    
FROM      x$kewrattrnew      new,      
x$kewrsqlidtab     tab,      
v$sql_bind_capture bnd     
WHERE new.str1_kewrattr     = tab.sqlid_kewrsie      AND tab.sqlid_kewrsie     = bnd.sql_id      AND tab.childaddr_kewrsie = bnd.child_address

从表的命名来看x$kewrattrnew是存放属性配置的表，x$kewrsqlidtab是存放sqlid信息的表，v$sql_bind_capture是访问v_$sql_bind_capture视图的同义词

查看v$sql_bind_capture的底层访问基表，通过查询视图的定义语句，我们可以确认底层的基表为x$kqlfbc

SQL> select VIEW_DEFINITION
from V$FIXED_VIEW_DEFINITION
where view_name ='GO$SQL_BIND_CAPTURE';  2    3  
​
VIEW_DEFINITION
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select INST_ID,     KQLFBC_PADD,         KQLFBC_HASH,      KQLFBC_SQLID,     KQLFBC_CADD,         KQLFBC_CHNO,      substr(KQLFBC_NAME, 1, 128),           KQLFBC_POS,       to_number(decode(KQLFBC_DUPPOS, 65535, NULL, KQLFBC_DUPPOS)),     KQLFBC_OACDTY,   substr(KQLFBC_DTYSTR, 1, 15),decode(KQLFBC_OACCSI, 0, to_number(null), KQLFBC_OACCSI),     decode(KQLFBC_OACPRE, 0, to_number(null), KQLFBC_OACPRE),       decode(KQLFBC_OACSCL, 0, to_number(null), KQLFBC_OACSCL),          KQLFBC_OACMXL,         decode(KQLFBC_WCAP, 0, 'NO', 'YES'),      decode(KQLFBC_WCAP, 0, to_date(NULL),        decode(KQLFBC_WCAP, 2, to
_date(NULL), KQLFBC_LCAP)),        KQLFBC_STRVAL,        decode(KQLFBC_WCAP, 0, NULL,              sys.sys$rawtoany(KQLFBC_BINVAL, KQLFBC_OACDTY,              KQLFBC_OACCSF, KQLFBC_OACCSI)),        CON_ID      
from x$kqlfbc

查询三个基表x$kewrattrnew，x$kewrsqlidtab，x$kqlfbc的数据，x$kewrattrnew、x$kewrsqlidtab基表的数据不多结果很快返回，而x$kqlfbc查询几分钟都没有返回结果，可见数据量很大

所以，到这里我们基本可以确认wrh$_sql_bind_metadata表的插入语句超时是由于插入语句需要访问内存基表x$kqlfbc(x$kqlfbc基表是绑定变量的内存缓存表)，而当前数据库里面语句使用了大量的绑定变量，导致在访问该X$基表时需要检索内存里面大量的绑定变量信息，执行效率非常缓慢。

---x$kewrattrnew，x$kewrsqlidtab基表的数据不多，很快返回
select count(*) from  X$KEWRATTRNEW;
select count(*) from X$KEWRSQLIDTAB;
---x$kqlfbc查询几分钟都没有返回结果，数据量很大
select count(*) from x$kqlfbc;

问题解决:

由于涉及到x$数据库内核基表，我们首先通过Oracle的官方mos查看是否有相关案例的处理办法

在文档Error ORA-32701 'On Current SQL: insert into wrh$_sql_bind_metadata' (Doc ID 2226216.1)里面找到了类似相关问题的处理办法

原因跟我们之前分析的基本一致，x$kqlfbc基表是绑定变量的内存缓存表，而当数据库里面语句使用了大量的绑定变量，会导致在访问该X$基表时需要检索内存里面大量的绑定变量信息，使执行效率非常缓慢

​

而提供的解决方法为

1 收集基表x$kewrattrnew，x$kewrsqlidtab，确保语句的执行计划生成正确，避免由于执行计划的错误，导致语句执行缓慢

2 重启数据库释放内存基表的数据

3 刷新shared_pool释放内存基表的数据

随后，我们采纳了方法1，对基表x$kewrattrnew，x$kewrsqlidtab进行统计信息收集，但问题没有得到解决，方法1不适合本场景问题

而对于方法2，3，我们并没有采纳实施，因为两个方法对在线的业务都会造成影响，并且都是通过释放内存临时规避内存里面绑定变量过多的问题，随着数据库的运行，内存里面的绑定变量依然会继续堆积起来，无法解决根本的问题

接下来，我们继续寻求其他的解决方法，由于是内存里面的绑定变量过多导致了这个问题的发生，而绑定变量绝大部分都是应用的SQL所带来的，所以，我们可以通过优化减少应用的绑定变量使用，来解决这个问题

查询使用绑定变量语句过多的SQL,可以发现多个语句使用了大量的绑定变量

---查询绑定变量过多的sql_id
select a.sql_id,count(POSITION)
from (select sql_id,POSITION
from v$sql_bind_capture
where rownum<100000) a
group by a.sql_id
order by 2;
---通过sql_id去确认sql语句
set linesize 400
set long 99999
set longc 99999
select sql_fulltext
from v$sql
where sql_id='';

而SQL语句使用这么多的绑定变量，使为了将数据通过union+dual的方式去构造表进行匹配比对

MERGE INTO xxxxx sUSING (select :1  AS XXX, :3AS XXX,:6  AS XXX,:9  AS XXX,:10  AS XXX, :11  ASxxx,:12  AS XXX,:13  AS XXX,:16  AS XXX,:17  AS XXX,:19  AS XXX,:20  ASxxxxXXX,:21  AS XXX, :22  ASxxxx,:23  AS XXX,:24  AS XXX,:25  AS XXX,:26  AS XXX,:27  AS XXX,:29  AS XXX, :30AS XXX,:31  AS XXX,:32  AS XXX,:33  AS XXX, :34  AS xxxxfrom dual.......unionselect :5407  AS XXX, :5409AS XXX,:5412  AS XXX,:5415  AS XXX,:5416  AS XXX, :5417  ASxxxx,:5418  AS XXX,:5419  AS XXX,:5422  AS XXX,:5423  AS XXX,:5425  AS XXX,:5426  ASxxxx,:5427  AS XXX, :5428  ASxxxx,:5429  AS XXX,:5430  AS XXX,:5431  AS XXX,:5432  AS XXX,:5433  AS XXX,:5435  AS XXX, :5436AS XXX,:5437  AS XXX,:5438  AS XXX,:5439  AS XXX, :5440  AS xxxfrom dual

找到使用绑定变量过多的SQL之后，我们将语句提供给了应用进行优化整改，并建议不要使用union+dual这种大量绑定变量的方式构造表，而是从应用端先将数据分批写入表，在与关联表进行匹配比对

后续，应用也采纳了我们的建议，进行优化整改，数据库内存的绑定变量数量也慢慢得到释放，awr快照自动生成也恢复了正常。

其他问题:

1 由于应用的语句改造需要时间，在此期间有没有其他办法，确保awr快照自动生成

除了可以通过重启数据库，刷新shared_pool释放内存临时规避，还可以通过禁用awr刷新表wrh$_sql_bind_metadata进行规避，但这会导致awr缺少关于此表的数据

alter system set  "_AWR_DISABLED_FLUSH_TABLES"='wrh$_sql_bind_metadata';

2 手动kill了MMON进程，但MMON进程没有重新启动，怎么解决

• 重启数据库实例，恢复MMON进程，对应用有影响，需要停机窗口操作

• 设置实例为restricted session，然后在设置回正常模式，注意这种设置也会造成数据库不可用，对应用有影响，需要停机窗口操作

alter system enable restricted session;
alter system disable restricted session;

• 在11.2.0.4版本，存在Bug 19565533，导致MMON进程被KILLED之后无法自动启动的情况,需要应用one-off补丁Patch 19565533解决