Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
performance view v$rowcache

Oracle DB 12.1.0.2
Hello Team,
can you please advise how to use the v$rowcache to identify performance bottleneck?
Thanks,
Roshan
Answers
-
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?
-
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
-
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.
-
You may have a look at https://aprakash.wordpress.com/2010/05/07/row-cache-lock-an-interesting-case/