oracle library cache pin,【案例】Oracle library cache lock/library cache:mutex X解决办法
【案例】Oracle library cache lock/library cache:mutex X解决办法
时间:2016-10-31 21:54 来源:Oracle研究中心 作者:HTZ 点击:
次
天萃荷净
SQL语法错误导致library cache lock/library cache:mutex X ，Oracle研究中心案例分析：运维DBA反映数据库出来大量的library cache lock/library cache:mutex X,原以为是BUG导致，最后确认是由于sql语法错误导致的，下面是在11.2.0.3环境中模拟现象
1，DB与OS版本
oracleplus.net> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production
oracleplus.net> !lsb_release -a
LSB Version: :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch
Distributor ID: RedHatEnterpriseAS
Description: Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
Release: 4
Codename: NahantUpdate8
2，测试脚本
[oracle@oracleplus.net sql]$cat /tmp/total1.sh
#!/bin/bash
for i in {1..1000}
do
nohup /tmp/runsql.sh &
echo $i
done
[oracle@oracleplus.net sql]$cat /tmp/total.sh
#!/bin/bash
for b in {1..100}
do
nohup /tmp/total1.sh >/tmp/total$b &
echo $b
done
[oracle@oracleplus.net sql]$cat /tmp/runsql.sh
sqlplus -s / as sysdba <
SELECT rowid,distinct ename from scott.emp;
SELECT rowid,distinct ename from scott.emp;
……………………………..
SELECT rowid,distinct ename from scott.emp;
exit
下面脚本用于生成trace文件用于分析
[oracle@oracleplus.net ~]$cat /tmp/manual.sh
sqlplus -s / as sysdba <
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
oradebug setmypid
oradebug tracefile_name;
oradebug event 10046 trace name context forever,level 12;
SELECT rowid,distinct ename from scott.emp;
oradebug event 10046 trace name context off;
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
oradebug dump systemstate 266
exit
3 现象模拟
[oracle@oracleplus.net sql]$sh -x /tmp/total.sh
+ for b in ‘{1..100}’
+ nohup /tmp/total1.sh
+ echo 1
1
+ for b in ‘{1..100}’
+ nohup /tmp/total1.sh
+ echo 2
2
+ for b in ‘{1..100}’
+ nohup /tmp/total1.sh
+ echo 3
3
+ for b in ‘{1..100}’
+ echo 4
4
+ for b in ‘{1..100}’
+ nohup /tmp/total1.sh
+ echo 5
5
+ for b in ‘{1..100}’
+ nohup /tmp/total1.sh
+ echo 6
…………………
运行/tmp/total.sh脚本。
3.1 查看等待事件
I SQL_ID COMMAND COUNT(*)
— —————— ————— ———-
1 19z6y6frqctwj UNKNOWN 665
6256zq0nau5g2 PL/SQL EXECUTE 1
5v4ww7bb9y4hp SELECT 1
I EVENT SQL_ID COUNT(*)
— —————————————- —————— ———-
1 SQL*Net message to client 81prbu1ddkd5c 1
library cache lock 19z6y6frqctwj 506
library cache: mutex X 19z6y6frqctwj 159
pipe get 6256zq0nau5g2 1
I EVENT COUNT(*)
— —————————————- ———-
1 library cache: mutex X 401
library cache lock 264
pipe get 1
SQL*Net message to client 1
这里已经现出了library cache lock,library cache: mutex X等待事件，并且blocking_session一直在变化。这里虽然能找到sqlid，但是在v$sql中sql_fulltext为空
3.2 执行manual脚本
执行manual脚本，生成分析信息
[oracle@oracleplus.net ~]$./manual.sh
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.72
Statement processed.
/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_16859.trc
Statement processed.
SELECT rowid,distinct ename from scott.emp
*
ERROR at line 1:
ORA-00936: missing expression
SQL用时3分钟
Elapsed: 00:03:53.71
Statement processed.
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.06
Statement processed.
3.3 trace文件分析
*** 2014-09-04 23:39:55.421
WAIT #182938303856: nam=’library cache lock’ ela= 167078 handle address=3731290056 lock address=2689821752 100*mode+namespace=5373954 obj#=-1 tim=140984519
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！