数据库锁阻塞

gdpr-dba

平台：rh5.5

数据库版本：10.2.0.4 rac

场景描述：
根据用户反映系统很卡，无法使用，在节点1上查看dba_waiters发现有几万行的阻塞，节点2上没有阻塞，首先想到的是在节点1做个hanganalyze，命令如下：
oradebug setmypid
oradebug setinst all
oradebug -g def hanganalyze 3

不过我不太会分析文件的内容，刘大帮忙分析一下，附件(含那个时段的addm和awr报告)已上传。

gdpr-dba

Liu Maclean(刘相兵 发表于 2012-12-11 12:02
这并不困难 参考ASH 和 library cache lock 指向的对象 分析是否有共性，分析SQL为什么导致该问题即可。 ...

哦，好像有点暗难度，不过我先试试看，如果不行再来请教。

Liu Maclean(刘相兵

gdpr-dba 发表于 2012-12-11 12:00
谢谢回复。应用确实不断删除和创建临时表，不过造成这么多library cache lock等待的根本原因可以查出来吗 ...

这并不困难 参考ASH 和 library cache lock 指向的对象 分析是否有共性，分析SQL为什么导致该问题即可。

gdpr-dba

Liu Maclean(刘相兵 发表于 2012-12-11 11:56
给我的感觉是 主要问题在于解析 而非行锁

谢谢回复。应用确实不断删除和创建临时表，不过造成这么多library cache lock等待的根本原因可以查出来吗？

Liu Maclean(刘相兵

给我的感觉是 主要问题在于解析 而非行锁

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
library cache lock	103,041	34,117	331	45.8	Concurrency
CPU time		16,483		22.1
enq: TX - row lock contention	31,618	15,451	489	20.8	Application
row cache lock	90,851	3,687	41	5.0	Concurrency
enq: HW - contention	9,540	2,203	231	3.0	Configuration

	Per Second	Per Transaction
Redo size:	128,799.00	7,039.64
Logical reads:	345,069.03	18,860.08
Block changes:	546.23	29.85
Physical reads:	2,016.56	110.22
Physical writes:	48.56	2.65
User calls:	404.59	22.11
Parses:	342.25	18.71
Hard parses:	27.68	1.51
Sorts:	175.35	9.58
Logons:	0.65	0.04
Executes:	833.92	45.58
Transactions:	18.30

TOp event包括 library cache lock ，每秒硬解析27次

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
7,858	7,570	23,198	0.34	10.55	0hhmdwwgxbw0r		select obj#, type#, flags, ...
7,079	1,924	0		9.51	4fx2dm2r1qsnn	JDBC Thin Client	INSERT INTO t_gl_assistbalance...
5,901	1,593	0		7.93	3nsf9dx81xuzn	JDBC Thin Client	INSERT INTO T_FA_FAMONCARD_LOG...
5,238	0	0		7.04	2vxdcs8b571d3	JDBC Thin Client	UPDATE t_gl_voucherlog SET fis...

最耗时的SQL居然是

select obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$ where ts#=:1 and to_number(bitand(flags, 16)) = 16 order by dropscn
delete from RecycleBin$ where purgeobj=:1


搞清楚为什么上面的语句 执行许多次 而且每次耗时都不断， 怀疑你的应用大量DROP临时表

此外大量 select IN-LIST的语句 直接列了几百项，这会拖慢解析的

FINDING 1: 47% impact (34669 seconds)-------------------------------------Contention for latches related to the shared pool was consuming significant database time.    RECOMMENDATION 1: Application Analysis, 47% benefit (34669 seconds)      ACTION: Investigate the cause for latch contention using the given          blocking sessions or modules.      RATIONALE: Sessions with Service "hseasrac" and Module "JDBC Thin          Client" were the blocking sessions responsible for 97% of this          recommendation's benefit.