row cache lock

From: New DBA <new_dba_on_the_block_at_yahoo.com>
Date: Sat, 26 Feb 2005 05:58:39 -0800 (PST)
Message-ID: <20050226135839.82206.qmail@web53308.mail.yahoo.com>

Hi,

First let me express my sincere thanks to everyone in this list who has answered my questions in the past and help me learn and understand oracle better.

So here is my problem (9.2.0.5 on HP-UX):

While investigating long running requests, I found out that most of them were waiting on "row cache lock" since a long time.

P1 was 21, and I checked v$rowcache and it returned 2 rows:

dc_table_scns
dc_partition_scns

The current SQL for most of these sessions was COMMIT.

Please explain me what do these 2 row caches mean and how to find out who is currently holding them so that I can investigate further. I searched metalink but couldn't find any information.

Moreover, I saw one odd thing while looking at v$session_wait

I saw there were many rows with STATE=WAITED KNOWN TIME and seconds_in_wait a very big value (>10000). Moreover, seconds_in_wait kept increasing.

Meanwhile, when I looked at the process for these sessions at the O/S level, they were consuming 100% CPU. Does this situation mean that last wait was seconds_in_wait back and ever since then it has not waited on anything?

Please help me understand the above 2 problems.

Regards
New DBA

Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Feb 26 2005 - 09:01:42 CST