performance view v$rowcache

Robeen

Oracle DB 12.1.0.2

Hello Team,

can you please advise how to use the v$rowcache to identify performance bottleneck?

Thanks,

Roshan

EdStevens

I wouldn't, unless other diagnostics pointed in that direction.

Honestly, I started working with oracle in 1995, and have never had cause to look at this view. In fact, I had to go to the reference manual to even see what it is. And when I googled "v$rowcache" to see what others were writing about it, the number one hit was a Don Burleson article where he started talking about cache hit ratios, which is a highly discredited metric.

so, what makes you think you should be looking at this view to identify a performance bottleneck?

Robeen

It has some important performance metrics

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

EdStevens

Maybe they are "important", maybe not.

Before delving into that , you mus step back and take a broader look at what you are doing.

Are you facing and actual, demonstrated performance issue? (Is some business process being negatively impacted by database or application performance?) If the answer is "no", then you are exhibiting symptoms of Compulsive Tuning Disorder.

If you are facing an existential performance issue, then what diagnostics have you done to see exactly where the time is being consumed? If "yes", what were the diagnostics and their results? If "no", then forget about v$rowcache and perform those diagnostics.

Robeen

You may have a look at https://aprakash.wordpress.com/2010/05/07/row-cache-lock-an-interesting-case/