Posted by
ErmanArslansOracleBlog on
Dec 29, 2021; 7:53am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/row-cache-lock-tp10531p10544.html
That trace doesn't help.. It just shows us that, drop table operation completed in 171 secs due to row cache lock.. So it doesn't crash, it doesn't hang and it doesn't create a deadlock situation.. It just takes some time to complete..
You need to reproduce the issue and while you are waiting for that drop operation; you should identify the cache that is being waited by your session.
While the drop operation is waiting; you should run the following;
select p1text,p1,p2text,p2,p3text,p3 from v$session where event='row cache lock';
select parameter,count,gets,getmisses,modifications from v$rowcache where cache#=<p1_from_above>
According to the value returned from query above, here are the suggestions;
DC_SEQUENCES
For DC_SEQUENCES, consider caching sequences using the cache option.
DC_OBJECTS
Look for any object compilation activity which might require an exclusive lock, blocking other activities
DC_SEGMENTS
Contention here is most likely to be due to segment allocation. Investigate what segments are being created at the time.
DC_USERS
This may occur if a session issues a GRANT to a user and that user is in the process of logging on to the database. Investigate why grants are being made while the users are active.
DC_TABLESPACES
The most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.