In this Document
Applies to:
Oracle Server- Enterprise Edition - Version 10.2.0.1 to 10.2.0.4 [Release 10.2]Information in this document applies to any platform.
***Checked for relevance on 30-Apr-2012***
Goal
LOB performance issues have multiple sources: This note is not designed to be a One-fix for all HWM issues:
One of the most prevalent problems seen with LOB performance is when the High WaterMark (HWM) enqueue has notable contention.
- This Document will use examples from the Automatic Workload Repository (AWR) and Active Session History (ASH) reports to confirm if you are encountering a problem with HWM contention.
- We will provide one of the possible fixes when you see this HWM enqueue contention on LOBs if using ASSM
- We will then provide you with examples of AWR and ASH reports showing the HWM enqueue before and after the application of the Fix
- Last: we will provide Tips and Techniques on how to diagnose other LOB performance problems using the AWR and ASH reports
COMMENT: There are SEVERAL conditions that can lead to HWM + LOBs during updates or delete/inserts. IF you are on RAC you should also review Bug 6413373 LOB HW enqueue contention in RAC environments with ASSM space management
Setting the LOB to use Pctversion 100% may be one of the quickest methods to check if the contention can easily be reduced for the LOB
Reducing the segment size is perhaps the strongest recommendation when attempting to improve LOB performance with BASICFILEs. This is usually best accomplished by creating Multiple partitions (powers of 2) to reduce LOB index size and contention. Freepools are also important for RAC configurations.
Securefiles which are available as of 11.2 will relieve most performance issues found with Basicfiles.
Fix
We will focus on Bug 6376915 "HW enqueue contention for ASSM LOB segments" and applying the fix:
“…HW enqueue contention can occur for LOB segments which are ASSM managed as space allocation only acquires one block at a time. With this fix ASSM lobs get a minimum number of chunks based on the value of event 44951 (up to 1024) which should help reduce or eliminate HWM enqueue contention.
This is the most important LOB HWM Enqueue bug encountered with the following prerequisites
a) Automatic Segment Space Mangement (ASSM) is used with the tablespace storing the LOB
b) Inserts and updates are slow (vs read)
c) High Load and/or concurrency exists
d) HW: enqueue is seen in the top 5 wait events of the AWR report
e) Not using RAC or Securefiles
FIX
1) The fix is already included in 11.1.0.7 and 10.2.0.4/10.2.0.5 but not implemented until the event is enabled
- for 10.2.0.3 Download and apply the patch for BUG 6376915:
2) Set EVENT 44951 up to 1024 (which changes Chunks allocated from 1 on ASSM to 1024)
NOTE: The fix for this problem introduces a *mandatory event* which must be set in order for the ASSM architectural change leading to more efficient allocation of LOB chunks during high load and transaction contention when Extent allocation is needed. The event is *not* set even after you apply the patch.
ALTER SYSTEM SET EVENT='44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024' scope=spfile;
- or set directly in the pfile/init.ora -
EVENT="44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024"
3) If using the 10.2.0.3 patch then Shutdown and Restart the database
COMMENT: MONITOR the impact of this event as it may introduce performance degradation in some cases: As the event is dynamic once the fix is in place you can enable at various levels or disable using level 0. Common levels include 128,512 or 1024.
USING AWR REPORTS to :
- Confirm you are hitting the LOB + enq: HWM - Contention Problem
- Confirm if the Patch + EVENT set resolved the problem
- Confirm if the problem did not help HWM contention
The following three Symptoms will help you identify if you have encountered the problem
Symptom #1
AWR REPORT
* "enq: HW - contention" will be in the Top 5 Wait events
** "SQL*Net more data from client" is usually in the Top 5 Wait events also
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
AVG
wait Total Call Wait
Event Waits Time(s) (ms) Time% Class
------------------------------ ------------ ----------- ------ ------ ---------
db file sequential read 2,580,474 35,544 14 77.4 User I/O
SQL*Net more data from client 659,140 5,513 8 ** 12.0 Network
CPU time 4,540 9.9
enq: HW - contention 88,910 2,890 33 * 6.3 Configuration
log file sync 777,146 1,688 2 3.7 Commit
Symptom #2
Review the TOP Enqueue Activity:
- The HWM enqueue will be among the top 5
- Transaction Contention may also be in the top few enqueues
- High Total and Average Wait times for Segment High Water Mark and TX row-lock contention
- High Transaction Waits in fractional or complete seconds (user timeouts or long commits
Enqueue Activity Snaps: 1234-1235
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc
Enqueue Type Requests Succ Gets Failed Gets Waits Wt.Time(s) Avg Wt Time(ms)
---------------------------------------------- -------- ---------- ----- --------- ----------------
HW-Segment High Water Mark 93,860 93,862 0 88,226 2,961 33.56 *
TX-Transaction(row lock contention) 272 272 0 209 570 2,729.44 **
TX-Transaction (index contention) 4,564 4,564 0 4,144 34 8.16
TX-Transaction 793,989 794,042 0 97 0 4.08
-- enq: TX - row lock contention hit 2.73 seconds Per Transaction !
Symptom #3
AWR WAIT EVENTS: More evidence
- enq: HW - contentionSQL
- Net more data from client **
- enq: TX - row lock contention ***
- High Average Wait in ms (20ms or higher) *
- Transaction Time in fractional (or complete) seconds *
-> s -second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
%Time TotalWait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
db file sequential read 2,580,474 .0 35,544 14 3.3
SQL*Net more data from client ** 659,140 .0 5,513 8 0.9
enq: HW - contention 88,910 .0 2,890 33* 0.1
log file sync 777,146 .0 1,688 2 1.0
read by other session 103,140 .0 929 9 0.1
SQL*Net break/reset to client 114,782 .0 813 7 0.1
enq: TX - row lock contention *** 380 43.4*** 557 1466* 0.0
log file parallel write 552,663 .0 394 1 0.7
latch: cache buffers chains 55,203 .0 382 7 0.1
...
-- enq: TX - row lock contention hit 1.46 seconds Per Transaction !
POST PATCH; EVENT; BOUNCE and AWR
TOP 5 TIMED EVENTS : you should notice the following changes
- Enq HWM - contention -- this event is usually missing from the top 5 post patch or should show as less than ~6ms avg/read if this bug is the HWM problem!
- SQL*Net more data from client -- this events time will usually decrease dramatically or drop from the top 5
------ ------ ---------
TOP ENQUEUE ACTIVITY: you should notice the following changes
- The HWM enqueue will be move far down the list and may drop off measurable enqueue activity
- Transaction Contention should no longer be in fractional or higher seconds
- Total and Average Wait times for Segment High Water Mark and TX row-lock contention should be a fraction of previous assessed times
- AWR WAIT EVENTS to Monitor after the PATCH has been applied, the EVENT is set and the Instance has been bounced include:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
RAC example
Total Wait Avg Wait Total%
Event Waits Time(s) Time(ms) Time Class
------------------------------ ------------ ----------- ------ ------ ---------
db file sequential read 140,990 3,529 25 19.1 User I/O
log file sync 62,033 3,313 53 17.9 Commit
gc buffer busy 44,012 2,442 55 13.2 Cluster
CPU time 2,268 12.3
log file parallel write 66,681 1,993 30 10.8 System I/O
AWR WAIT EVENTS to Monitor after the Patch, Event and Bounce has occurred:
Missing or notably decreased from the WAIT EVENTS section of the AWR report after the patch:
enq HWM HWM Contention Drops substantially down the Enqueue List
enq HWM Average HWM wait times will drop from 20-30+ ms to below ~6 ms
SQL*Net more data from client Total Time will usually decrease by more than 75%
enq: TX - row lock contention Will decrease during LOB load/updates (barring application issues)
CONFIRMING THE EVENT IS SET
The fix for BUG 6376915 requires:
- Patch (already built into 10.2.0.4,10.2.0.5 and 11.1.0.7 or higher)
- EVENT 44951 set in the spfile of init.ora
- LEVEL for the event: this value can be set up to 1024
e.g. EVENT=44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024
Many times you may believe that this event is already set in the database or for the instance: However, experience has shown that this is not always the case.
The easiest method to determine if the event has been set is to look at the most recent startup in the ALERT.LOG which reports Non-Default values.
ALERT.LOG
...
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Fri Dec 11 12:46:27 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as D:\oracle\product\10.2.0\db_1\RDBMS
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
processes = 150
event = 44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024
^^^ < this event will show as a non-default values in the ALERT.LOG
__shared_pool_size = 104857600
__large_pool_size = 4194304
__java_pool_size = 4194304
...
...
LAST:
There are several different sources for performance issues with LOBs. This event is only one of many potential problem source/solutions.
Under some circumstances this event will increase HWM contention so it is important to qualify and test the fix. The AWR reports can be used to provide feedback on any changes made.
References
BUG:6376915 - ENQ: HW - CONTENTION WITH LOB SEGMENTSNOTE:9801919.8 - Bug 9801919 - "enq: HW - contention" against segments that add an extent frequently during high concurrency
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17252115/viewspace-773460/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17252115/viewspace-773460/
2652
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
