Re: row cache lock

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 26 Feb 2005 14:07:50 -0000
Message-ID: <004601c51c0c$8ea41370$6702a8c0@Primary>

Have another look at these sessions with event = 'row cache lock'.

If the state is not 'WAITING' then they are not waiting, they are using (or trying to use) CPU. If the status is 'WAITED KNOWN TIME',
then (to within about 3 seconds) the amount of time they have been using CPU is

seconds_in_wait minus wait_time
(the latter is in hundredths of seconds, so divide by 100 before subtracting).

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Jan 21st 2005

Original Message ----- From: "New DBA" <new_dba_on_the_block_at_yahoo.com> To: <oracle-l_at_freelists.org> Sent: Saturday, February 26, 2005 1:58 PM Subject: row cache lock

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

--

http://www.freelists.org/webpage/oracle-l Received on Sat Feb 26 2005 - 09:10:50 CST