| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> cache buffers chain latch contention
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."
 |
 |