Forum Stats

  • 3,824,984 Users
  • 2,260,449 Discussions
  • 7,896,372 Comments

Discussions

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

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    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
    Robeen Member Posts: 2,179 Silver Badge

    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
    EdStevens Member Posts: 28,778 Gold Crown

    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.