解析错误导致library cache lock+library cache: mutex X

1.查看当前数据库等待事件

##数据库等待事件发现大量的library cache lock

##从下图中的等待事件查询，基本上可以判定是一条SQL语句导致的，大量回话被1996回话阻塞，1996被回话1354回话阻塞，1354对应的blockding session为空。且所有的回话执行的都是同一条SQL

 

 

2.获取SQL_TEXT

##通过sql_id无法获取到sql_text

##分析到这里根据经验大概是因为错误的SQL文本解析导致的

23:14:57 SQL> select sql_text from v$sql where sql_id='avsaksqw45m7g';

no rows selected

 

##mos提供如下方式进行查询错误的SQL文本导致的解析问题sql_id

 

 

3.抓取awr报告获取如下信息，并分析

##抓取awr报告，从awr报告里面进一步确定问题

 

 

 

##时间统计模型

##从这里可以看到系统主要时间消耗是解析失败

 

##分析到这里基本上可以确定是因为解析失败导致的问题故障，这里我们需要进一步确定问题SQL，因为数据库正常情况下无法抓取解析失败的SQL，我们需要设置event事件来辅助解决此类问题。

 

##查看有关解析SQL

##这里的第一位sql_id和我们从数据查找到的等待事件显然不是一个问题，对应sql_id的sql_text无法找到

##说明：这里的一个调度进程insert，是查看数据库性能信息的监控程序，这里解析1239次，一次也没有执行成功显然是不正常的，对于版本过多的SQL，一次软解析甚至不如重新执行一次硬解析来的高效，这里因为数据库版本是11.2.0.4，因此可以通过修改隐含参数"_CURSOR_OBSOLETE_THRESHOLD",当SQL版本超过这个参数设定后，直接重新硬解析。这里是问题之外的其他问题，这里先不做深究。

 

 4.开启trace事件进行解析失败SQL追踪

ALTER SYSTEM SET EVENTS '10035 trace name context forever, level 1';

##设置event事件后，数据库alter日志开始出现大量硬解析问题SQL，但是单单从SQL语句中感觉不到什么太大问题

##检查对应的SQL语句可以正常执行

23:18:10 SQL> select Max(CHECKTIME) from key.M_GLOBALPROCESSINFO;

MAX(CHECKTIME)

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

2022-05-07 09:26:16

 

5.为什么可以正常执行呢？？？？

##检查错误代码输出--无效的字符？？但是从日志中看解析失败的语句是正常的啊？？？为啥呢？？？？

[oracle@oracle11g ~]$ oerr ora 911

00911, 00000, "invalid character"

// *Cause: identifiers may not start with any ASCII character other than

//         letters and numbers.  $#_ are also allowed after the first

//         character.  Identifiers enclosed by doublequotes may contain

//         any character other than a doublequote.  Alternative quotes

//         (q'#...#') cannot use spaces, tabs, or carriage returns as

//         delimiters.  For all other contexts, consult the SQL Language

//         Reference Manual.

// *Action:

 

 

##测试1执行错误的文本

SQL> select * from afadsfa;

select * from afadsfa

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

##ALTER日志对应的信息

##从这里我们发现，SQL语句在数据库中解析后，并没有最后的分号？是不是这个问题导致的呢？我们接着测试

PARSE ERROR: ospid=20936, error=942 for statement:

select * from tabasfa

 

 

##测试2执行错误的文本多出一个分号

 

SQL> select count(*) from t1;;

select count(*) from t1;

                       *

ERROR at line 1:

ORA-00911: invalid character

 

##alert日志对应的信息

##这里alert日志中输出的信息，发现解析文本后面确确实实多了一个分号，和我们上述的情况是一致的。

PARSE ERROR: ospid=24879, error=911 for statement:

select count(*) from t1;

 

问题分析到这里，基本上可以判断确确实实是SQL语法有问题，但是开发不可能蠢到一个语句的结束出现两个分号，因此判断是plsql代码中存在问题，进行了如下测试

 

 6.问题处理解决

 ##大量无效语句会导致CPU和library cache征用，最好的处理办法就是解决应用错误的SQL语句，但是作为临时解决方法，可以通过调整隐含参数减轻不正确解析的影响

##_cursor_features_enabled隐含参数默认是2，我们需要在这个基础上加32，需要重新启动数据库

SQL> alter system set "_cursor_features_enabled" = 34 scope=spfile;

System altered.

 

##设置参数并重启后，解析错误的记录在表sqlerror$中，后续的错误的SQL解析成本会降低，不会产生SQL硬解析所有的成本，而是一小部分成本，这样就减少了资源消耗

##每条错误的SQL只会记录一条记录

 

 

参考MOS:1353015.1