high wait for

在测试数据库的awr报告中发现大量的 dfs lock handle，引出这个文档作为参考
25个并发，每个并发持续执行一个并行度为24的查询sql，在等待事件中 列出了等待时间最长的dfs lock handle

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 12.1.0.2B1 [Release 11.1 to 12.1]
Information in this document applies to any platform.

SYMPTOMS

 Queries running slow with high waits for 'latch: query server process' and 'DFS lock handle'

Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
DFS lock handle	1,336	29.4K	22043	52.3	Other
latch free	6,200	25.7K	4137	45.5	Other
DB CPU		463.3		.8

Latch Activity

Latch Name	Get Requests	Pct Get Miss	Avg Slps /Miss	Wait Time (s)	NoWait Requests	Pct NoWait Miss

query server process

80,297

4.01

2.68

25830

2,104

0.29

 

CHANGES

Table or index degree of parallelism has been altered.

CAUSE

Highly concurrent queries that normally take a fraction of a second to run started executing with a high degree or parallelism.

This leads to extreme contention for the 'latch: query server process' when spawning and cleaning up detached processes.

SOLUTION

 Do not run highly concurrent queries in parallel.

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

转载于:http://blog.itpub.net/21754115/viewspace-1380259/