351 积分	0 好友	8 主题

发消息

[SQL调优] 数据库锁阻塞

1^#

发表于 2012-12-11 09:51:25 | 查看: 5005| 回复: 4

本帖最后由 gdpr-dba 于 2012-12-11 10:18 编辑

平台：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报告)已上传。

data.zip

1.8 MB, 下载次数: 805

分享0

收藏0 回复只看该作者道具举报

Liu Maclean(刘相兵

2^#

发表于 2012-12-11 11:56:37

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

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.

回复只看该作者道具举报