high wait for

lovestanford發表於2014-12-25
AI
在測試資料庫的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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章