This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,967 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Categories

performance view v$rowcache

Robeen
Robeen Member Posts: 2,261 Silver Badge
in Performance & Availability Discussions

Oracle DB 12.1.0.2

Hello Team,

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

Thanks,

Roshan

· Share on TwitterShare on Facebook

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?

    · Share on TwitterShare on Facebook
  • Robeen
    Robeen Member Posts: 2,261 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

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • Robeen
    Robeen Member Posts: 2,261 Silver Badge

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

    · Share on TwitterShare on Facebook