Oracle Hang Analysis

发表人:rainnyzhong | 发表时间: 2010年一月18日, 10:03

Author: rainnyzhong

Date:2010-1-15

1．症状描述：

FALB12从EXCEL IMPORT DATA到DB，预计事务会运行1个多小时，在开始操作后40分钟左右，ORACLE挂死，任何用户都不可以再登陆了。

2．分析

（1）下面是挂死时OS的资源状况：

09:37:54 up 73 days, 23:31, 1 user, load average: 0.11, 0.24, 0.18

194 processes: 191 sleeping, 3 running, 0 zombie, 0 stopped

CPU states: cpu user nice system irq softirq iowait idle

total 6.7% 0.0% 0.3% 0.3% 0.0% 1.9% 90.4%

Mem: 2186616k av, 2168780k used, 17836k free, 0k shrd, 154716k buff

1633584k actv, 498176k in_d, 64108k in_c

Swap: 2040244k av, 18624k used, 2021620k free 2283068k cached

从top命令的输出中，我们可以看出，MEMORY使用几乎已经达到100%。

（2）下面是ORACLE报警日志的内容：

Thu Jan 14 16:22:44 2010

Errors in file /opt/oracle9i/admin/ORA16G/udump/ora16g_ora_29758.trc:

ORA-00604: error occurred at recursive SQL level 4

ORA-04021: timeout occurred while waiting to lock object

ORA-00604: error occurred at recursive SQL level 4

ORA-04021: timeout occurred while waiting to lock object

ORA-00604: error occurred at recursive SQL level 3

ORA-04021: timeout occurred while waiting to lock object

Thu Jan 14 16:26:06 2010

可以看出，在解析递归SQL时出问题了，在等待某种锁时延时了。

（3）下面是跟踪文件ora16g_ora_29758.trc的部分内容

*** 2010-01-14 16:22:44.412

=====================

PARSE ERROR #8:len=56 dep=2 uid=0 oct=3 lid=0 tim=1233845082434255 err=604

select value$ from props$ where name = 'GLOBAL_DB_NAME'

*** 2010-01-14 16:22:44.412

ksedmp: internal or fatal error

ORA-00604: error occurred at recursive SQL level 4

ORA-04021: timeout occurred while waiting to lock object

ORA-00604: error occurred at recursive SQL level 4

ORA-04021: timeout occurred while waiting to lock object

ORA-00604: error occurred at recursive SQL level 3

ORA-04021: timeout occurred while waiting to lock object

Current SQL statement for this session:

select value$ from props$ where name = 'GLOBAL_DB_NAME'

……………

SO: 0x5e6ec29c, type: 4, owner: 0x5e61d48c, flag: INIT/-/-/0x00

(session) sid: 132 trans: (nil), creator: 0x5e61d48c, flag: (c0000041) USR/- BSY/-/-/-/-/-

DID: 0001-0015-00000004, short-term DID: 0000-0000-00000000

txn branch: (nil)

oct: 0, prv: 0, sql: (nil), psql: (nil), user: 86/FALB12

O/S info: user: , term: , ospid: 1234, machine: ecv09

program:

last wait for 'library cache pin' blocking sess=0x(nil) seq=7 wait_time=2929670 seconds since wait started=906

handle address=5e24edf4, pin address=5b0f5364, 100*mode+namespace=c8

Dumping Session Wait History

for 'library cache pin' count=1 wait_time=2929670

handle address=5e24edf4, pin address=5b0f5364, 100*mode+namespace=c8

for 'library cache pin' count=1 wait_time=2929670

handle address=5e24edf4, pin address=5b0f5364, 100*mode+namespace=c8

从跟踪文件可以看出，在解析ORACLE递归SQL时报错。何为recursive SQL？

当用户发布SQL语句时，ORACLE需要对SQL语句进行语法与语义检查，这时，ORACLE会发布一些内部的SQL语句，对这条SQL所访问的对象进行各种检查，如检查访问的表存不存在，用户有没有权限对该表进行访问等，这种ORACLE内部的SQL称之为递归SQL。递归SQL也是分LEVEL的。如，如果是由LEVEL 1递归SQL再引起的recursive SQL，则此条recursive SQL的LEVEL就是2，由此类推。

从跟踪文件我们可以看出，ORACLE在等待library cache pin。我们知道，ORACLE解析用户的SQL和PLSQL代码是在共享池(share pool)中进行的，库缓存是共享池的一个组件(library cache,简称LC)，还有一个缓存叫做字典缓存(dictionary cache,简称DC)，ORACLE每次解析SQL语句时，都要获取一块LC内存地址并把它锁住(独占，即把内存钉住，称之为pin)，此例中，很显然，ORACLE没办法再钉住任何一块LC的内存地址了，所以一直在等。当新的用户登陆时，ORACLE要发布内部SQL对该用户进行权限验证等，这些递归SQL也需要获取LC内存来进行解析，由于LC已经没有任何空闲的地址了，所以，这些用户验证的SQL也没办法运行，所以导致新的用户没办法登录ORCLE（SYS用户也没办法登录），整个数据库出现挂死的现象。

3. 诊断

初步诊断为ORACLE内存严重不足。IMPORT DATA时由于事务很长，没办法释放资源（表锁，内存锁等），导致ORACLE挂死。

4．建议

将IMPORT DATA放到一个比较大内存的ORACLE服务器上执行，且在做IMPORT DATA时，不要做太多其它的操作。

-->

一个ORACLE死锁问题的追踪

发表人:rainnyzhong | 发表时间: 2010年一月11日, 11:06

Author: rainnyzhong

Date: 2010-01-08

不得不记录一下。这个问题花了我一天的时间来追踪。

最先是怀疑外键字段是否没有建索引。然后我给相关的外键字段都建上索引，死锁依旧。

后来怀疑是ORACLE的BUG，因为ORACLE确实有一个BUG：2530125，某些版本的oracle检查到deadlock后会触发一个关于deadlock的bug。

我将整个SCHEMA导出到另外一个DB(64位平台)，发现死锁依旧。所以暂时去除了是ORACLE BUG的嫌疑.

再后来怀疑存储过程或JAVA中是否有生成动态SQL(ad-hoc SQL),检查代码，发现确实有动态SQL。将JAVA和PROCEDURE中的动态SQL去掉，死锁依旧。

靠！到这一步，心里郁闷的不得了。

再次仔细检查死锁的trace文件，发现有一个LATCH：

Parent+children enqueue hash chains level=4

SELECT * FROM V$LATCH L WHERE L.ADDR=upper('20004fc4')；

--

1 20004FC4 19 4 enqueue hash chains 1456202064 584423 76 0 14395 1 0 0 76 0 0 0 0 0 0 0 0 0 0 0 0

发现确实存在这么一个latch：enqueue hash chain.我重启DB，发现这个latch依然存在，并且发现v$latch这个视图的记录在DB重启之前和重启之后总数量是一样的(382行)。于是，我就再次写SQL故意重现一把死锁，再检视模拟出来的死锁的trace文件，发现里面也有这个latch,可见，只要是死锁，这个latch就会出现在trace文件中，没有什么特别的地方。

思索再思索，最后发现唯一特别的地方是死锁图(deadlock graph)：

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TX-0009000d-0000207b 38 721 X 38 721 X

session 721: DID 0001-0026-00001DE8 session 721: DID 0001-0026-00001DE8

Rows waited on:

Session 721: obj - rowid = 0000EAF8 - AAAPzHAAGAAAVGzAAA

(dictionary objn - 60152, file - 6, block - 86451, slot - 0)

正常的死锁图，一般是有两行，而我的只有一行，且是同一SESSION（如上图），而正常的死锁图应该是像下面这样：

DEADLOCK DETECTED

[Transaction Deadlock]

Current SQL statement for this session:

DELETE FROM pm_user_temp WHERE rownumber=4

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TX-00070015-00001881 41 772 X 94 670 X

TX-0001000b-00001822 94 670 X 41 772 X

session 772: DID 0001-0029-00000008 session 670: DID 0001-005E-00000004

session 670: DID 0001-005E-00000004 session 772: DID 0001-0029-00000008

Rows waited on:

Session 670: obj - rowid = 0000EAF8 - AAAPzHAAGAAAVGwAAA

(dictionary objn - 60152, file - 6, block - 86448, slot - 0)

Session 772: obj - rowid = 0000EAF8 - AAAPzHAAGAAAVGwAAC

(dictionary objn - 60152, file - 6, block - 86448, slot - 2)

Information on the OTHER waiting sessions:

Session 670:

pid=94 serial=464 audsid=35675 user: 69/XPC92_IMPORT_JEFF

O/S info: user: rainnyzhong, term: RAINNYZHONG-ECV, ospid: 3008:1404, machine: MSHOMERAINNYZHONG-ECV

program: plsqldev.exe

application name: PL/SQL Developer, hash value=1190136663

action name: SQL Window - New, hash value=3399691616

Current SQL Statement:

DELETE FROM pm_user_temp WHERE rownumber=2

End of information on OTHER waiting sessions.

上面是我模拟出来的死锁产生的trace文件中包含的死锁图。在一个正常的死锁图中，一般是至少是有2个资源被锁住，而本例的死锁图中，只有一个资源被锁住，且是被同一会话锁住。这有点特别。

无奈之下，再次回退最近所有新修改的PROCEDURE和JAVA代码，死锁依旧。

这个PROCDUER已经运行一段时间了，QA也测了一段时间，一直没有报这种死锁的问题，那么，为什么现在会报出来呢？苦思，再苦思，最后，开发人员突然想起来说，他用的是MOCK，而不是JBOSS。我们马上将代码放到JBOSS运行，死锁不再抛出，到此，困了我一天的死锁问题终于找到原因。经过咨询开发人员，原来MOCK是我们的AD TEADM研发出来的一个东东，有点类似模拟JBOSS，主要是用于开发人员的内部测试，在一般的测试中，开发人员只要用MOCK，而不需用JBOSS就可以调试代码。但我们做的MOCK没办法真正模拟出JBOSS的事务管理机制，没办法实现嵌套事务，自治事务等。检视ORACLE 的PROCEDURE，确实存在自治事务，所以，用MOCK一运行，就抛出来莫名其妙的死锁，产生的死锁图也是非常特别。靠！要不是开发人员自己说出来，我调2天估计也查不出死锁的原因。

下面为完整的死锁TRACE文件

PARSING IN CURSOR #27 len=153 dep=1 uid=69 oct=6 lid=69 tim=1233331432659515 hv=3592496474 ad='46e29a38'

UPDATE PM_USER_TEMP T SET T.DEFAULTROLENAME = 'core.pm/label.companyAdministrator' WHERE T.DEFAULTROLENAME = :B3 AND T.USERID = :B2 AND T.FILENAME = :B1

END OF STMT

PARSE #27:c=0,e=282,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1233331432659507

DEADLOCK DETECTED

[Transaction Deadlock]

Current SQL statement for this session:

UPDATE PM_USER_TEMP T SET T.DEFAULTROLENAME = 'core.pm/label.companyAdministrator' WHERE T.DEFAULTROLENAME = :B3 AND T.USERID = :B2 AND T.FILENAME = :B1

----- PL/SQL Call Stack -----

object line object

handle number name

0x3d26c364 470 procedure XPC92_IMPORT_JEFF.AIT_LOADTEMPDATA

0x3d26c51c 1 anonymous block

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TX-0009000d-0000207b 38 721 X 38 721 X

session 721: DID 0001-0026-00001DE8 session 721: DID 0001-0026-00001DE8

Rows waited on:

Session 721: obj - rowid = 0000EAF8 - AAAPzHAAGAAAVGzAAA

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21094976/viewspace-1031455/ ，如需转载，请注明出处，否则将追究法律责任。

上一篇：安装VMware tools

下一篇：没有了~

请登录后发表评论登录

全部评论

ITPub博客

Oracle Hang Analysis

Oracle Hang Analysis

一个ORACLE死锁问题的追踪

最新文章