Row cache lock

14 Jul

A rather odd performance issue happened at work a few days ago. All of a sudden, one of the databases started to work very slowly, and a quick look in ASH data showed that it was spending over 70% of its time waiting on “row cache lock” (not to be confused with “latch: row cache lock”).

It was a test database (11.2.0.3) on an overloaded server with non-standard configuratioin, so my initial reaction was to bounce the database and hope that the symptoms would go away, but it didn’t work. So there was no other choice but to investigate the root cause. The next simplest thing to “turning it off and then back on” was finding the blocking session in ASH (the V$ACTIVE_SESSION_HISTORY view has columns BLOCKING_SESSION and BLOCKING_SESSION_SERIAL#). Most of the entries for this wait event had NULLs in these columns, but among those NULLs there were sid’s and serial#’s of two sessions. Unfortunately, both sessions were from background processes, MMON and SMON, so killing them obviously wasn’t a tempting choice. MMON is responsible for collecting AWR data and such, so the instance could probably survive if it gets killed, but SMON must always be running or the instace would crash.

So I looked at parameters of this event. The first parameter, P1, was always 3. According to the documentation, that parameter means the cache id, and the name of the cache can be found in V$ROWCACHE. It turned out to be DC_ROLLBACK_SEGMENTS. With this information at hand, I searched MOS for bugs with matching symptoms, but all matches found were for other Oracle versions. I took the system state dump trying to find additional information, but all I got was same session id’s that I saw in ASH. I tried a few other things, but to no avail.

A colleague suggested to re-create rollback segments. At first, I didn’t consider this approach promising, but since things I deemed more promising failed to work, I decided to try it as well. Of course it was simpler to just re-create the UNDO tablespace rather deal with individual rollback segments, so I created a new UNDO tablespace, updated UNDO_TABLESPACE parameter, and tried to drop the old one. The DROP command failed because there was still an active rollback segment. I bounced the database, and tried to do the same. The rollback segment was still active (and row cache lock events were still eating up most of database time). I used _offline_rollback_segments parameter and bounced the database again. The old UNDO tablespace still refused to drop, and row cache lock waits continued to rampage. I checked the rollback segments associated with the old UNDO tablespaces and their statuses, and found that the problem rollback segment was in NEEDS RECOVERY state. Then I tried to drop the rollback segment manually, and that worked, although only at the second attempt (the first attempt ended up in an ORA-30025 error)! The row cache lock waits disappeared as suddenly as they came, and a few minutes later the DROP command for the old UNDO tablespace finally completed.

About these ads

2 Responses to “Row cache lock”

  1. Lonion July 19, 2014 at 3:32 am #

    So the root cause is that some of rollback segments need recovery? If this, why?

    • savvinov July 21, 2014 at 12:34 pm #

      Hi Lonion,

      thanks for visiting my blog and for your question.

      I don’t know the exact mechanism that lead a rollback segment needing recovery to (almost) entire database hung up on “row cache lock” waits. This being a test database, I don’t have enough resources to dig into internals of this issue.

      I can only assume that a background process that was performing the recovery of this rollback segment somehow deadlock either itself or another session accessing the same rollback segment. I’ve seen some dead-lock row cache lock bugs in MOS database. Their versions didn’t match so they’re probably not relevant, but there could be other unpublished bugs of the same nature.

      Best regards,
      Nikolay

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 344 other followers

%d bloggers like this: