RAC 환경에서 sequence 관련 장애가 있어 분석중에 찾은 내용 (don't forget!)
sequence cache의 크기를 늘리고, ORDER 보다 NOORDER 로 하는 것이 경합을 줄일 수 있다.
(NOCACHE 금지)
Generally,
sequences can be cached with cache values as high as 200 in RAC. This is much
higher than for a regular Oracle
instance. If there is insufficient caching, contention can result and will show up as
an increase in service times. If there are performance problems due to sequences,
the row cache locks statistics in the v$system_event
view should be examined to determine whether the problem is due to the use
of Oracle sequences. This is discussed in the following points: n A problem with sequences is indicated
in a v$system_event as an extended average wait time for row cache locks in
the range of a few hundred milliseconds. The proportion of time waiting
for row cache locks to the total time
waiting for non-idle events will be relatively high. n In the v$rowcache view, for the dc_sequences parameter, the ratio of dlm_conflicts to dlm_requests
will be high. If this ratio exceeds 10 to 15% and the row cache lock wait time is a significant portion of the total wait time, it is likely that the
service time deterioration is due to insufficiently cached
sequences.
If the
application has to create objects frequently, performance degradation to the RAC
environment will occur. This is due to the fact that object creation requires inter-instance
coordination. A large ratio of dlm_conflicts to dlm_requests on the dc_object_ids
row cache in v$rowcache, the same SELECT as was used for sequences will
work here as well, along with excessive wait times for the row cache lock event
in v$system_event,
is indicative that multiple instances in the cluster are issuing excessive amounts of concurrent DDL
statements. http://media.techtarget.com/searchOracle/downloads/AUG-grid_excerpt_1.pdf
