cache buffers chain latch contention
- From: "Koivu, Lisa" <Lisa.Koivu@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 25 Mar 2004 11:10:17 -0500
Hi everyone,
A couple of days ago I attempted a parallel mv-create statement in my
dev database. Instead of completing, or even showing any progress, it
sat there. When I didn't run it in parallel, I saw temp space being
chewed up, to me that indicates progress. When I run it in parallel,
nothing. It sat there.
Investigation of v$system_event indicated latch free wait. OK, fine, so
I look at v$latch and find I have enormous misses and sleeps on cache
buffers chains. I chased down the child latches and pinpointed the
affected objects. I had several hash partitioned indexes (4096
partitions, yes I went crazy, hey why not?) that were on the latches. I
rebuilt them range partitioned, tried the mv build again, same problem.
OK, fine, then what?
My impression of this latch, and what happens, is this (PLEASE correct
me if I am wrong): A db block can house records from multiple tables.
When I fired the build in parallel, the slaves began fighting over who
got the latch for what hot block in cache. It appears that I can begin
to alleviate this problem by rebuilding objects off of hot child
latches.
Am I wrong? Metalink doesn't say much other than "this is your
problem". Gaja states this is because of excessive i/o according to
Cary Millsap http://www.revealnet.com/newsletter-v3/0402_C.htm , and
Steve Adams theorizes that it could be due to an extreme number of free
extents, http://www.ixora.com.au/q+a/0104/13001720.htm and comes to the
same conclusion as Cary
http://www.ixora.com.au/newsletter/2000_11.htm#hash_latches in that it's
excessive i/o.
Well, yes, that's why I'm trying to build a mv to stop this nasty join
between 18 tables for queries that someone set the expectation of "it
should run in a few minutes" (don't ask, I'm tired of fighting). I need
to get through the create.
My question is, will reorging objects really help? IN the last few days
I have heard more than once "just separate data and indexes, temp and
redo on different luns" and don't worry too much about it. This also
came from an oracle "expert" that is coming next week to hold our hands
through a Windows 2003 RAC install. Seems to me that if moving objects
will help, this idea of just split it up simplistically and let it go is
out the window.
What am I missing? Since this is a dw, yes, it's going to have a ton of
i/o. There isn't much I can do about that - the reports for our first
user group are detailed, not summarized. They really do pull thousands
of records and look at them. The bottom line is I will need to turn PQO
on in this database, and right now I am convinced I'll have the same
lockup in prod because I see a large number of sleeps and misses on the
same latch.
I have no idea why my title is "senior" dba. Today I feel like I just
started learning Oracle yesterday.
I am interested in any comments... they sure would be appreciated...
thanks all
Lisa Koivu
Oracle Database Monkey
Orlando, FL, USA
"The sender believes that this E-Mail and any attachments were free of any
virus, worm, Trojan horse, and/or malicious code when sent. This message and
its attachments could have been infected during transmission. By reading the
message and opening any attachments, the recipient accepts full responsibility
for taking proactive and remedial action about viruses and other defects. The
sender's business entity is not liable for any loss or damage arising in any
way from this message or its attachments."
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
