Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Support Stumped -- sql stuck on nam='latch: row cache objects'

Daryl E.Jan 10 2011 — edited Jan 18 2011
We have a new 11gr1 PSU5 db that's sole purpose in life is one query. One query, that's it. We hit it hard but its 100+ cpus. and 20-30 sessions per second.
Every few minutes/hours some sessions will go in a spin mode where all they do is sit there on a wait. 'latch: row cache objects'. Normally the query finishes in under 1s, some times it can go 10 minutes or more sitting there. If I put on a 10046 trace, thats what I get row cache objects.

We have tried many things - oracle hasnt admitted a bug, but I thought I would throw it out there to the experts for some hidden tricks/advice.

SGA 15GB, host 256G, huge pages
cursor sharing exact and tried force
session cached cursors increased
It would appear it is waiting on various different objects not always the same object.

The query is a bit complex involving context but not that bad (ie. expected run times is < 1s and somewhere around 30-70K block reads)

This is a logical dataguard instance - so that's how the data gets into the db.

Just throwing it out there to see if others have any ideas.. We've been at sev 1 for 2 weeks now.

=====
WAIT #11: nam='latch: row cache objects' ela= 2000 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346876910
WAIT #11: nam='latch: row cache objects' ela= 3999 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346881909
WAIT #11: nam='latch: row cache objects' ela= 3000 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346885909
WAIT #11: nam='latch: row cache objects' ela= 2999 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346888908
WAIT #11: nam='latch: row cache objects' ela= 2000 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346891908
WAIT #11: nam='latch: row cache objects' ela= 2999 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346895907
WAIT #11: nam='latch: row cache objects' ela= 0 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346895907
WAIT #11: nam='latch: row cache objects' ela= 0 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346895907
WAIT #11: nam='latch: row cache objects' ela= 0 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346896907
WAIT #11: nam='latch: row cache objects' ela= 1999 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346899906
WAIT #11: nam='latch: row cache objects' ela= 3000 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346902906
WAIT #11: nam='latch: row cache objects' ela= 0 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346902906
WAIT #11: nam='latch: row cache objects' ela= 2999 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346906905
WAIT #11: nam='latch: row cache objects' ela= 3000 address=16592233480 number=257 tries=0 obj#=81484 tim=1294690346909905
======
Daryl
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 15 2011
Added on Jan 10 2011
78 comments
4,741 views