RAC 环境中最常见的 5 个数据库和/或实例性能问题 (文档 ID 1602
- WBOY原創
- 2016-06-07 15:47:532104瀏覽
文档内容 用途 适用范围 详细信息 问题 1：大量块丢失 (gc lost blocks, gc current/cr lost blocks) 问题 2：大量 log file sync 等待 问题 3：Mutexes 上的长时间等待 问题 4：高 enq: TX -row lock contention, enq: TX - index contention, enq: TX - IT
文档内容
| 用途 |
| 适用范围 |
| 详细信息 |
| 问题 1：大量块丢失 (gc lost blocks, gc current/cr lost blocks) |
| 问题 2：大量 log file sync 等待 |
| 问题 3：Mutexes 上的长时间等待 |
| 问题 4：高 enq: TX -row lock contention, enq: TX - index contention, enq: TX - ITL allocate entry 等待 |
| 问题 5：高 CPU 和内存开销 |
| 参考 |
适用于:
Oracle Database - Enterprise Edition - 版本 10.1.0.2 到 11.2.0.3 [发行版 10.1 到 11.2]本文档所含信息适用于所有平台
用途
本文旨在提供关于 RAC 环境中最常见的数据库和/或实例性能问题的摘要以及可能的解决方案
请注意，问题 3（Mutexes 上的大量等待）和问题 5（高 CPU 和内存开销）是一般性数据库问题，并非特定于 RAC 的问题。不过，本文中讨论这些问题是因为这些是最常见的问题之一，会在 RAC 环境中的某一个实例发生。
适用范围
DBAs
详细信息
问题 1：大量块丢失 (gc lost blocks, gc current/cr lost blocks)
症状
I. AWR 报告中显示有大量块丢失。
II. netstat -s 报告数据包重新组装故障（reassambly failure）和丢失数据包（dropped packets）增加。
解决方案
使用以下文档进行故障排除并解决丢失块问题。该文档描述了症状、可能原因以及解决方案。
Document 563566.1 - gc block lost diagnostics
问题 2：大量 log file sync 等待
症状
I. AWR 报告中显示 log file sync 始终位于 Top 5 等待事件列表中。
II. 平均 log file sync 时间很长（> 20 毫秒）。
III. 平均 log file parallel write 时间很长（> 10 毫秒）。
III. 平均 redo write broadcast ack time 或者 wait for scn ack 时间很长（> 10 毫秒）。
IV. 平均 log file sync 时间很短，但 log file sync 等待次数太多。
背景
用户会话在提交或回退时，会话的重做信息需要由 LGWR 刷新到重做日志文件。用户会话等待“log file sync”的同时，等待 LGWR 通知确认所有重做更改已安全存放在磁盘上。
示例：
WAIT #0: nam='log file sync' ela= 977744 buffer#=754 p2=0 p3=0 obj#=114927 tim=1212384670107387
参数：
P1 = buffer#
P2 = 未使用
P3 = 未使用
obj# = object_id
对这个 buffer（在重做日志缓冲区）的所有更改必须刷新到磁盘并确认写入，以确保事务处理已提交，并且在实例关闭之前一直保持为已提交状态。
“log file sync”等待的典型生命周期
1. 用户会话发布提交或回退操作，然后开始等待 log file sync。
2. LGWR 收集要写入重做日志文件的重做信息，发布 IO 并将 BOC 入队到 LMS 进程，然后通知 LMS 进程。
3. LGWR 等待将重做信息刷新到磁盘以及等待 SCN 被确认收到
4. 完成 IO 并从 LMS 收到 SCN 的确认信息，表示写入完成。LGWR 然后通知前台进程继续。
5. 前台进程被唤醒，并且 log file sync 等待结束。
与 log file sync 相关的重要统计信息和事件
redo write time - 从重做日志缓冲区向当前重做日志文件写入所用的总时间，以10毫秒为单位。
redo writes - LGWR 向重做日志文件写入的总次数。“写入重做块数”除以此 统计信息等于每次写入的块数。
log file parallel write - 完成 I/O 的用时。虽然重做记录是并行写入的，但在最后一个 I/O 写入到磁盘上之前，并行写入不会完成。
redo write broadcast ack time - 在提交时，除了日志写入等待时间之外，与广播相关的总等待时间长度，以毫秒为单位。
user commits - 用户提交的数量。在用户提交事务时，必须将所生成的、反映对数据库块更改的重做写入到磁盘。提交操作通常代表的是最接近用户事务率的内容。
user rollbacks - 用户手动发布 ROLLBACK 语句的次数或者用户事务处理出误的次数。
Document 1064487.1 - Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql) 中提供的脚本可以用于搜集诊断log file sync问题的有用信息。
可能原因
I. 重做日志文件所在设备的 IO 服务时间和吞吐量不理想。
II. Oracle Bug。有关已知 Oracle Bug，请查看 WAITEVENT: "log file sync" Reference (Document 34592.1)。
III. LMS 未在 RT（实时）类中运行。
IV. LGWR 进程调度延迟。
V. 提交数太多。
VI. 操作系统资源紧张。
解决方案
I. 如果日志文件并行写入始终很高（> 10 毫秒），则将重做日志文件移动到具有较高吞吐量和更短响应时间的磁盘上。日志文件并行写入理想范围应在 1-2 毫秒之间。
II. 应用适用于您的环境的已知 Oracle Bug 修复。获取这些修复的最有效方法是应用最新 PSU 补丁程序。Document 756671.1 提供了有关最新 PSU 的详细信息。
III. 确保 LMS 进程在 RT 类中运行。LMS 进程默认情况下在 RT 类中运行。
IV. 使用批量提交而不是在每次 DML 操作之后提交，可以减少提交数量。
V. 查看是否有任何活动可以使用 NOLOGGING/UNRECOVERABLE 选项安全地完成。
VI. 查看是否可以使用 COMMIT NOWAIT 选项。有关详细信息，请参阅
Document 857576.1。
@For Support Only: Renice LGWR to run at higher priority or run LGWR in RT class by adding LGWR to the parameter: _high_priority_processes='VKTM|LMS|LGWR". Consider doing this only if log file sync is high and scheduling delay of LGWR is found to be causing it. Be prepared to test it thoroughly.
问题 3：Mutexes 上的长时间等待
Mutexes 是比闩锁(latches)更为轻型且粒度更细的并行机制。获取 Mutex 的目的是为了确保正确管理特定操作的并行执行，例如，如果一个会话正在更改内存中的数据结构，则其他会话等到获取Mutex 后，才能进行类似的更改。下面是最常见的与 Mutex 相关的等待：
A. cursor: pin S wait on X
B. cursor: pin S
C. library cache: Mutex X
症状 (A)
AWR 报告中显示“cursor: pin S wait on X as one of the top wait”。
背景 (A)
会话等待此事件是在它尝试获取共享模式的 Mutex 锁时，其他会话在相同游标对象上以独占方式持有 Mutex 锁。通常，等待“Cursor: pin S wait on X”是症状而非原因。其中可能需要进行底层的优化或者是已知问题。
可能原因 (A)
请查看 Troubleshooting 'cursor: pin S wait on X' waits (Document 1349387.1).
解决方案 (A)
请查看 Troubleshooting 'cursor: pin S wait on X' waits (Document 1349387.1).
症状 (B)
AWR 报告中显示“cursor: pin S as one of the top waits”
背景 (B)
会话在申请共享模式下特定游标上的特定 Mutex 时，虽然没有并行的排他持有者，但无法立即获取 Mutex，这时会等待“cursor: pin S”。这看上去有些不合理，因为用户可能会不理解为什么在没有排他模式持有者的情况下会存在这种等待。出现这种等待的原因是，为了在共享模式下获取 Mutex（或释放Mutex），会话必须增加（或减少）Mutex 引用计数，这需要对 Mutex 结构本身进行独占原子更新。如果有并行会话在尝试对 Mutex 进行这样的更新，则一次只有一个会话能够实际增加（或减少）引用计数。如果由于其他并行请求使得某个会话无法立即进行这种原子更新，则会出现等待“cursor:
pin S”。
在 RAC 环境中，Mutex 只作用于本地实例。
参数：
P1 = idn
P2 = 值
P3 = where（10.2 中为 where|sleeps）
idn 是 Mutex 标识符值，与正在等待以获取 Mutex 的 SQL语句的 HASH_VALUE 相匹配。可以用以下格式的查询找到使用对应 IDN 的 SQL 语句：
SELECT sql_id, sql_text, version_count
FROM V$SQLAREA where HASH_VALUE=&IDN;
如果游标显示的 SQL_TEXT 格式为“table_x_x_x_x”，则这是特殊的内部游标，有关将此类游标映射到对象的详细信息，请参阅 Document 1298471.1。
P1RAW 是采用十六进制值的相同值，可用于在跟踪文件中搜索与该 hash（散列）值匹配的 SQL。
可能原因 (B)
I. 某个特定 Mutex 有大量并行操作，特别是在多个 CPU 的系统上。
II. 在高负载情况下，会等待非常多不同的“idn”值。
III. Oracle Bugs
Bug 10270888 - ORA-600[kgxEndExamine-Bad-State] / mutex waits after a self deadlock
Bug 9591812 - Wrong wait events in 11.2 ("cursor: mutex S" instead of "cursor: mutex X")
Bug 9499302 - Improve concurrent mutex request handling
Bug 7441165 - Prevent preemption while holding a mutex (fix only works on Solaris)
Bug 8575528 - Missing entries in V$MUTEX_SLEEP.location
解决方案 (B)
I. 应用
Bug 10411618 的修复。
II. 对于任何已标识的“热点”SQL，用户可以通过将 SQL 替换为一些由其他会话执行的变体，来减少特定游标上的并行操作。有关详细信息，请查看 WAITEVENT: cursor: pin S Reference (Document 1310764.1)。
III. 应用其他已知 Oracle bug 的修复。获取修复的最有效方法是应用最新 PSU patch（补丁程序）。
Document 756671.1 提供了有关推荐补丁程序的详细信息。
症状 (C)
AWR 报告中显示“library cache: Mutex X as one of the TOP waits”。
背景 (C)
在以前的 Oracle 版本中，获取 library cache Mutex 与获取 library cache latches 的目的相似。在 10g 中，为 library cache 中的特定操作引入了 Mutex。从 11g 开始，Mutex 取代了 library cache latches。只要某个会话以独占模式持有 library cache mutex 并且其他会话需要等待释放 Mutex，就会出现此等待事件。
单独等待：
参数：
P1 = "idn" = 唯一的Mutex标识符
P2 = "value" = 持有Mutex的会话ID
P3 = "where" = 等待 Mutex 的代码中的位置（内部标识符）
系统范围等待：
在系统范围级别，有两个视图可用于帮助诊断此等待：
GV$MUTEX_SLEEP（对于非 RAC 为 V$MUTEX_SLEEPS）
和 GV$MUTEX_SLEEP_HISTORY（对于非 RAC 为 V$MUTEX_SLEEP_HISTORY）
在实例启动后，这些视图在实例范围内跟踪 Mutex 的使用情况。由于这些视图显示了自启动以来的总数，在出现问题时，您可以获取短时间间隔内值的差异，因此这些数据是非常有意义的。了解这些信息最简单的方法是查看 AWR 或 statspack 报告的“Mutex Sleep Summary”部分。
可能原因 (C)
I. 频繁硬解析。
II. 高版本数。
III. 失效和重新加载。
IV. Oracle Bug。有关详细信息，请查看 WAITEVENT: "library cache: mutex X" (Document 727400.1) 以获取已知 Oracle Bug 列表。
解决方案 (C)
I. 减少硬解析、失效和重新加载。有关详细信息，请查看 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention (Document 62143.1)。
II. 查看 Troubleshooting: High Version Count Issues (Document 296377.1) 以解决高版本数问题。
III. 应用已知 Oracle bug 的修复。获取修复的最有效方法是应用最新 PSU patch（补丁程序）。Document 756671.1 提供了有关推荐 patch（补丁程序）的详细信息。
问题 4：高 enq: TX -row lock contention, enq: TX - index contention, enq: TX - ITL allocate entry 等待
A. enq: TX - Index Contention
B. enq: TX - ITL allocate entry waits
C. enq: TX - row lock contention
症状 (A)
I. AWR 报告中显示，在模式 4 下，“enq: TX - index contention and enq: TX - row lock contention”上有大量等待。
II. AWR 报告中显示有大量的branch node splits, leaf node splits 和leaf node 90-10 splits
III. AWR 报告（Segments by Row Lock Waits）中显示特定段上有大量行锁等待。
IV. AWR 报告中显示其他等待，例如索引分支或叶块上的 gc buffer busy 等待、远程回滚段段头上的 gc current block busy 和 gc current split。
示例：
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - index contention 29,870 1,238 41 9.52 Concurrency
Instance Activity Stats:
Statistic Total per Second per Trans
branch node splits 945 0.26 0.00
leaf node 90-10 splits 1,670 0.46 0.00
leaf node splits 35,603 9.85 0.05
Segments by Row Lock Waits:
Owner Tablespace Object Name Obj.Type Row Lock Waits % of Capture
ACSSPROD ACSS_IDX03 ACSS_ORDER_HEADER_PK INDEX 3,425 43.62
ACSSPROD ACSS_IDX03 ACSS_ORDER_HEADER_ST INDEX 883 11.25
ACSSPROD ACSS_IDX03 ACSS_ORDER_HEADER_DT INDEX 682 8.69
背景 (A)
如果事务在索引中插入行时需要等待其他事务正在操作的索引块拆分结束，则 会话将等待事件“enq: TX - index contention”。
可能原因 (A)
I. 大量并行插入导致过量索引块拆分。
II. 对于索引值由序列产生的索引，它不断向右增长。
解决方案 (A)
解决方案是采用避免争用或热点的方式重新组织索引。选项包括
I. 将索引重新创建为反向索引
II. Hash（散列）分区索引
III. 如果索引关键字是从序列（sequence）生成的，则增加序列的高速缓存大小，在应用程序支持时，使用“无序”序列。
症状 (B)
AWR 报告中显示，在模式 4 下，“enq: TX - allocate ITL”和“enq: TX - row lock contention”上有大量等待。
背景 (B)
会话希望锁定块中的一行但一个或多个其他会话锁定了相同块中的行，并且块中没有空闲的 ITL 槽时，该会话会等待“enq: TX - allocate ITL”。通常，Oracle 数据库将动态添加另一个 ITL 槽。如果块中没有足够的空闲空间来添加 ITL，则这可能无法实现。
可能原因 (B)
相对于并行事务数量，initrans 数设置得太低
解决方案 (B)
从 AWR 报告中查找具有高 ITL 等待的段，或者使用以下 SQL：
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM V$SEGMENT_STATISTICS
WHERE STATISTIC_NAME = 'ITL waits' AND VALUE > 0
ORDER BY VALUE;
增加出现高 ITL 等待的段的 initrans值。
症状 (C)
AWR 报告中显示，在独占模式 (6) 下，“enq: TX - row lock contention”上有大量等待。
背景(C)
会话等待由其他会话持有的行级别锁定时，等待“enq: TX - row lock contention”。当某个用户更新或删除了行但尚未提交或回滚，而另一个会话希望更新或删除相同行时，会出现这种情况。
解决方案 (C)
这是应用程序问题，需要由应用程序开发人员参与查找涉及的 SQL 语句。下面的文档对进一步研究该问题可能会有所帮助：
Document 102925.1 - Tracing sessions: waiting on an enqueue
Document 179582.1 - How to Find TX Enqueue Contention in RAC or OPS
Document 1020008.6 - SCRIPT: FULLY DECODED LOCKING
Document 62354.1 - TX Transaction locks - Example wait scenarios
Document 224305.1 -Autonomous Transaction can cause locking
问题 5：高 CPU 和内存开销
A. 高 CPU 占用率
B. 高内存使用率
症状 (A)
I. TOP、prstat、vmstat 等操作系统工具显示用户 CPU 占用率非常高，而占用 cpu 最高的进程是 Oracle 进程或后台进程。
II. AWR 报告中显示最高等待数为以下一项或多项：
latch free
cursor pin S wait on X 或 cursor pin S wait 或 library cache mutex X
latch: cache buffers chains
resmgr: cpu quantum
enq: RO - fast object reuse
DFS lock handle
III. AWR 报告（SQLs ordered by buffer gets ）中显示一些 SQL 语句每次执行都读取很多buffer并消耗很多 cpu 时间。
IV. 高 cpu 开销进程的堆栈信息显示该进程在不断自旋（spinning）。
可能原因 (A)
I. Oracle bugs:
Bug 12431716 - Mutex waits may cause higher CPU usage in 11.2.0.2.2 PSU / GI PSU
Bug 8199533 - NUMA enabled by default can cause high CPU
Bug 9226905 - Excessive CPU usage / OERI:kkfdPaPrm from Parallel Query / High Version
count on PX_MISMATCH
Bug 7385253 - Slow Truncate / DBWR uses high CPU / CKPT blocks on RO enqueue
Bug 10326338 - High "resmgr:cpu quantum" but CPU has idle time
Bug 6455161 - Higher CPU / Higher "cache buffer chains" latch gets / Higher "consistent gets"
after truncate/Rebuild
II. Linux x86-64 平台上配置了非常大的 SGA，但是没有实施 Hugepages。
III. 高开销的 SQL 语句，使用了未优化的执行计划。
IV. 无法找到的进程。
解决方案 (A)
I. 对所遇到的 Bug 应用修复。获取所有这些修复的最有效方法是应用最新 PSU patch（补丁程序）。Document 756671.1 提供了有关最新 PSU 的详细信息。
II. 实施 hugepages。有关更多说明，请参阅
Document 361670.1 - Slow Performance with High CPU Usage on 64-bit Linux with Large SGA。
III. 优化导致过多 buffer gets和消耗高 cpu 时间的 SQL。有关详细信息，请参阅
Document 404991.1 - How to Tune Queries with High CPU Usage。
症状 (B)
I. ps、pmap 等操作系统工具显示内存中的进程在不断增长。pmap 显示进程的 heap（堆）和/或 stack（堆栈）内存不断增加。例如，
#pmap -x 26677
Address Kbytes RSS Anon Locked Mode Mapped File
00010000 496 480 - - r-x-- bash
0009A000 80 80 24 - rwx-- bash
000AE000 160 160 40 - rwx-- [ heap ]
FF100000 688 688 - - r-x-- libc.so.1
II. Oracle 进程的 session uga memory 和/或 session pga memory 在不断增长。
select se.sid,n.name,max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3;
III. 会话/进程 的游标数不断增长。
可能原因 (B)
I. Oracle bugs:
Bug 9919654 - High resource / memory use optimizing SQL with UNION/set functions with
many branches
Bug 10042937 HIGH MEMORY GROUP IN GES_CACHE_RESS AND ORA-4031 ERRORS
Bug 7429070 BIG PGA MEM ALLOC DURING PARSE TIME - KXS-HEAP-C
Bug 8031768 - ORA-04031 SHARED POOL "KKJ JOBQ WOR"
Bug 10220046 - OCI memory leak using non-blocking connection for fetches
Bug 6356566 - Memory leak / high CPU selecting from V$SQL_PLAN
Bug 7199645 - Long parse time / high memory use from query rewrite
Bug 10636231 - High version count for INSERT .. RETURNING statements with
reason INST_DRTLD_MISMATCH
Bug 9412660 - PLSQL cursor leak / ORA-600[kglLockOwnersListDelete]
Bug 6051972 - Memory leak if connection to database is frequently opened and closed
Bug 4690571 - Memory leak using BULK COLLECT within cursor opened using native
dynamic sql
II. 应用程序未显式关闭游标导致游标泄漏。
III. 具有异常大的 hash（散列）联接和/或排序操作的SQL语句。
可能解决方案 (B)
I. 应用适用 Bug 的修复。获取这些修复的最有效方法是应用最新 PSU patch（补丁程序）。Document 756671.1 提供了有关最新 PSU 的详细信息。
II. 确保应用程序显式关闭了游标。
III. 避免非常大的 hash（散列）联接和/或排序操作。
References
NOTE:756671.1 - Oracle Recommended Patches -- Oracle DatabaseNOTE:786507.1 - How to Determine the Blocking Session for Event: 'cursor: pin S wait on X'
NOTE:873243.1 - Troubleshooting 'enq: TX - index contention' Waits in a RAC Environment.
BUG:4690571 - MEMORY LEAK USING BULK COLLECT WITHIN CURSOR OPENED USING NATIVE DYNAMIC SQL
BUG:6051972 - MEMORY LEAK IF CONNECTION TO DATABASE SERVER IS FREQUENTLY OPENED AND CLOSED
NOTE:62354.1 - Waits for 'Enq: Tx - Row Lock Contention' - Wait Scenario Examples
NOTE:857576.1 - How to Minimise Waits for 'Log File Sync'
BUG:10411618 - ADD DIFFERENT WAIT SCHEMES FOR MUTEX WAITS
BUG:10636231 - HIGH VERSION COUNT FOR INSERT STATEMENTS WITH REASON INST_DRTLD_MISMATCH
BUG:6356566 - STATSPACK.SNAP COSUMES ALMOST 100% CPU
BUG:6455161 - HIGHER "CONSISTENT GETS" AFTER TRUNCATE
BUG:9591812 - INCORRECT WAIT EVENTS IN 11.2 ("CURSOR: MUTEX S" INSTEAD OF "CURSOR: MUTEX X")
NOTE:1511700.1 - ORA-00060 Single-Resource Deadlock Occurs on Autonomous Transaction
NOTE:1298015.1 - WAITEVENT: "cursor: pin S wait on X" Reference Note
NOTE:1298471.1 - H$PSEUDO_CURSOR
NOTE:1310764.1 - WAITEVENT: "cursor: pin S" Reference Note
NOTE:1349387.1 - Troubleshooting 'cursor: pin S wait on X' waits.
NOTE:1020008.6 - SCRIPT: FULLY DECODED LOCKING
NOTE:102925.1 - Tracing sessions: waiting on an enqueue
NOTE:1064487.1 - Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)
NOTE:1356828.1 - FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events
NOTE:1357946.1 - Troubleshooting 'library cache: mutex X' waits.
NOTE:164768.1 - Troubleshooting: High CPU Utilization
NOTE:179582.1 - How to Find TX Enqueue Contention in RAC or OPS
NOTE:361670.1 - Slow Performance with High CPU Usage on 64-bit Linux with Large SGA
NOTE:404991.1 - How to Tune Queries with High CPU Usage
NOTE:563566.1 - Troubleshooting gc block lost and Poor Network Performance in a RAC Environment
NOTE:34592.1 - WAITEVENT: "log file sync" Reference Note
NOTE:62143.1 - Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
NOTE:727400.1 - WAITEVENT: "library cache: mutex X"
BUG:7429070 - BIG PGA MEM ALLOC DURING PARSE TIME - KXS-HEAP-C
BUG:8031768 - ORA-04031 SHARED POOL "KKJ JOBQ WOR"
BUG:10042937 - HIGH MEMORY GROUP IN GES_CACHE_RESS AND ORA-4031 ERRORS
BUG:10220046 - MEMORY LEAK OCCURS WITH NON-BLOCKING APP IN THE LATER VERSION 10.2.0.4
BUG:7199645 - QUERY_REWRITE_ENABLED TAKE LONG PARSE TIME AND CONSUME ALL SERVER MEMORY
BUG:7385253 - DBWR IS CONSUMING HIGH CPU