From oracle-l-bounce@freelists.org Thu Mar 25 14:14:31 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2PKEUO05924 for ; Thu, 25 Mar 2004 14:14:30 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2PKEQo05913 for ; Thu, 25 Mar 2004 14:14:26 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ADFCC392827; Thu, 25 Mar 2004 15:01:18 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 05545-59; Thu, 25 Mar 2004 15:01:18 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4491C393243; Thu, 25 Mar 2004 14:46:34 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 25 Mar 2004 14:44:56 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A0F76393536 for ; Thu, 25 Mar 2004 14:42:46 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01725-88 for ; Thu, 25 Mar 2004 14:42:46 -0500 (EST) Received: from floexmailfe2.ffci.com (webmail.ffdev.com [12.8.34.17]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 848E9393935 for ; Thu, 25 Mar 2004 14:34:00 -0500 (EST) Received: from floexmailbe2.ffci.com ([10.53.20.64]) by floexmailfe2.ffci.com with Microsoft SMTPSVC(5.0.2195.6713); Thu, 25 Mar 2004 14:41:14 -0500 Importance: normal Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: cache buffers chain latch contention Date: Thu, 25 Mar 2004 14:41:14 -0500 Message-ID: <840C139B79E7CC4496B2594E9E35E96703E7D518@floexmailbe2.ffci.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: cache buffers chain latch contention Thread-Index: AcQSmbdTFNu9YyevSWyP5JjhdM8sCgABos9Q From: "Koivu, Lisa" To: X-OriginalArrivalTime: 25 Mar 2004 19:41:14.0584 (UTC) FILETIME=[22304580:01C412A1] X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 1824 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Lisa.Koivu@Cendant-TRG.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hi Mladen, That is the problem. This data warehouse (yes it is a data warehouse) is based upon an idea called a data vault ( http://www.tdan.com/i027ht04.htm ). It is meant for compressed storage and the joins are nasty. However, people here are under the impression it should work like a mart through views. Even the people who visited from Oracle said joins like this will never scream. It's large, going to grow to extremely large and will probably continue to behave this way querying out of the dw. My first step out of the miserable abyss is a materialized view to resolve all of these joins. Another idea floating around is to hash partition the tables. I am not an advocate of it because of the added complexity for admin. I do not think it will solve the performance problems. Right now it is range partitioned and obviously, is not performing. Thanks for your response Lisa -----Original Message----- From: Mladen Gogala [mailto:mladen@wangtrading.com] Sent: Thursday, March 25, 2004 1:45 PM To: oracle-l@freelists.org Subject: Re: cache buffers chain latch contention Lisa, it looks like an ugly SOQ (son of a query) which would be best suited for the star transformation, which brings me to the purpose of life and database question: Is it a DW database or an OLTP database? If it is a DW one, you can add bitmap indexes, establish fact->detail relationships and, as Tim would say, "kimbalize" the query into a manageable beast. If it is an OLTP database, then I'm afraid that you're stuck with the alternatives ranging from bad to worse based on NL, sort/merge and hash. On 03/25/2004 01:23:39 PM, "Koivu, Lisa" wrote: > Thank you Jonathan for your response. > > My bad, I should have said... 9204 on Windows 2003, 2cpu box, degree 8. > Below is the explain plan for the select statement, and the statement is > below. > > The only "small" table is cntl_business_day. The rest of them are at > least medium sized, and some are large. This is of course out of > development. I can't run the statement in production for fear of > chewing up all resources. I will take your suggestion for the event and > try it in dev. > > >From your description of the small table scenario, if all the parallel > slaves are running with the same execution plan, then I could see how > this contention would arise in the index blocks. > > So Jonathan are you confirming that chasing down the exact problem and > rebuilding is the only real option? > > Again thank you > Lisa > > trdev-LISA>@XPLAN > > PLAN_TABLE_OUTPUT > ------------------------------------------------------------------------ > ---------------------------- > > ------------------------------------------------------------------------ > ---------------------------- > | Id | Operation | Name > | Rows | Bytes | Cost | Pstart| Pstop | TQ |IN-OUT| > ------------------------------------------------------------------------ > ---------------------------- > | 0 | SELECT STATEMENT | > | 1 | 874 | 17M| | | | | > |* 1 | TABLE ACCESS BY INDEX ROWID | > CNTL_BUSINESS_DAY | 1 | 33 | 2 | > | 2 | NESTED LOOPS | > | 1 | 874 | 17M| | | 95,01 | P->S | > | 3 | NESTED LOOPS | > | 91 | 76531 | 17M| | | 95,01 | PCWP | > | 4 | NESTED LOOPS | > | 28 | 22092 | 17M| | | 95,01 | PCWP | > | 5 | NESTED LOOPS | > | 3 | 2082 | 17M| | | 95,01 | PCWP > | 6 | NESTED LOOPS | > | 1 | 651 | 17M| | | 95,01 | PCWP > | 7 | NESTED LOOPS | > | 1 | 599 | 17M| | | 95,01 | PCW > | 8 | NESTED LOOPS | > | 1 | 567 | 17M| | | 95,01 | PCWP | > | 9 | NESTED LOOPS | > | 1 | 534 | 17M| | | 95,01 | PCWP | > | 10 | NESTED LOOPS | > | 1 | 472 | 17M| | | 95,01 | PCWP | > | 11 | NESTED LOOPS | > | 1 | 443 | 17M| | | 95,01 | PCWP | > | 12 | NESTED LOOPS | > | 1 | 353 | 17M| | | 95,01 | PCWP | > | 13 | NESTED LOOPS | > | 1 | 289 | 17M| | | 95,00 | S->P > | 14 | NESTED LOOPS | > | 1 | 258 | 17M| | | | > | 15 | NESTED LOOPS | > | 2 | 220 | 17M| | | | > | 16 | NESTED LOOPS | > | 5 | 500 | 17M| | | | | > | 17 | NESTED LOOPS | > | 4811K| 385M| 12M| | | | | > | 18 | NESTED LOOPS | > | 1550K| 79M| 1534K| | | | | > | 19 | PARTITION RANGE ALL | > | | | | 1 | 6 | | > | 20 | TABLE ACCESS FULL | > SAT_TSBFILE_BALANCE | 1533K| 46M| 866 | 1 | > | 21 | TABLE ACCESS BY INDEX ROWID | > HUB_CONTRACT | 1 | 22 | 1 | | > |* 22 | INDEX UNIQUE SCAN | > PK_HUB_CONTRACT_IDX | 1 | | | | > | 23 | PARTITION RANGE ALL | > | | | | 1 | 6 | | > | 24 | TABLE ACCESS BY LOCAL INDEX ROWID| > SAT_POFILE_SUPPRESSION | 3 | 90 | 7 > |* 25 | INDEX RANGE SCAN | > PK_SAT_POFILE_SUPPRESSION_IDX | 3 | | 6 | > |* 26 | INDEX UNIQUE SCAN | > PK_LNK_CONTRACT_OWNER_IDX | 1 | 16 | 1 | > |* 27 | INDEX UNIQUE SCAN | > PK_HUB_OWNER_POSITION_IDX | 1 | 10 | | | > | 28 | PARTITION RANGE ALL | > | | | | 1 | 6 | > | 29 | TABLE ACCESS BY LOCAL INDEX ROWID | SAT_POFILE > | 1 | 148 | 7 | 1 | > |* 30 | INDEX RANGE SCAN | > PK_SAT_POFILE_IDX | 1 | | 6 | 1 | 6 > | 31 | PARTITION RANGE ALL | > | | | | 1 | 6 | > | 32 | TABLE ACCESS BY LOCAL INDEX ROWID | > SAT_EQUIFAX | 1 | 31 | 7 | > |* 33 | INDEX RANGE SCAN | > PK_SAT_EQUIFAX_IDX | 1 | | 6 | 1 | 6 > | > | 34 | PARTITION RANGE ALL | > | | | | 1 | 6 | 95,01 | > | 35 | TABLE ACCESS BY LOCAL INDEX ROWID | > SAT_CRMAST_STATUS | 4 | 256 | > |* 36 | INDEX RANGE SCAN | > PK_SAT_CRMAST_STATUS_IDX | 4 | | 6 | > | 37 | PARTITION RANGE ALL | > | | | | 1 | 6 | 95,01 | > | 38 | TABLE ACCESS BY LOCAL INDEX ROWID | > SAT_CRMAST_SELLING_INFO | 5 | 450 > |* 39 | INDEX RANGE SCAN | > PK_SAT_CRMAST_SELLING_INFO_IDX | 5 | | 6 | > | 40 | PARTITION RANGE ALL | > | | | | 1 | 6 | 95,01 | > | 41 | TABLE ACCESS BY LOCAL INDEX ROWID | > SAT_CRMAST_LOAN_ADMIN | 8 | 232 | > |* 42 | INDEX RANGE SCAN | > PK_SAT_CRMAST_LOAN_ADMIN_IDX | 8 | | 6 | > | 43 | PARTITION RANGE ALL | > | | | | 1 | 6 | 95,01 | P > | 44 | TABLE ACCESS BY LOCAL INDEX ROWID | > SAT_CRMAST_INVENTORY | 7 | 434 | > |* 45 | INDEX RANGE SCAN | > PK_SAT_CRMAST_INVENTORY_IDX | 7 | | 6 | > | 46 | PARTITION RANGE ALL | > | | | | 1 | 6 | 95,01 | PC > | 47 | TABLE ACCESS BY LOCAL INDEX ROWID | > SAT_CRMAST_FICO | 2 | 66 | 7 | > |* 48 | INDEX RANGE SCAN | > PK_SAT_CRMAST_FICO_IDX | 2 | | 6 | 1 | > | 49 | PARTITION RANGE ALL | > | | | | 1 | 6 | 95,01 | PCW > | 50 | TABLE ACCESS BY LOCAL INDEX ROWID | > SAT_CRMAST_FAC | 4 | 128 | 7 | > |* 51 | INDEX RANGE SCAN | > PK_SAT_CRMAST_FAC_IDX | 4 | | 6 | 1 | > | 52 | PARTITION RANGE ALL | > | | | | 1 | 6 | 95,01 > | 53 | TABLE ACCESS BY LOCAL INDEX ROWID | > SAT_CRMAST_EQUITY_TRANSFER | 2 | 104 | > |* 54 | INDEX RANGE SCAN | > PK_SAT_CRMAST_EQUITY_TRANS_IDX | 2 | | 6 | 1 | > | 55 | PARTITION RANGE ALL | > | | | | 1 | 6 | 95,01 > | 56 | TABLE ACCESS BY LOCAL INDEX ROWID | > SAT_CRMAST_BALANCE_DAILY | 10 | 43 > |* 57 | INDEX RANGE SCAN | > PK_SAT_CRMAST_BALANCE_DAIL_IDX | 10 | | 6 | 1 | > | 58 | PARTITION RANGE ALL | > | | | | 1 | 6 | 95,01 | > | 59 | TABLE ACCESS BY LOCAL INDEX ROWID | > SAT_CRMAST_BALANCE | 9 | 855 | > |* 60 | INDEX RANGE SCAN | > PK_SAT_CRMAST_BALANCE_IDX | 9 | | 6 | > | 61 | PARTITION RANGE ALL | > | | | | 1 | 6 | 95,01 | > | 62 | TABLE ACCESS BY LOCAL INDEX ROWID | > SAT_CRMAST_AGING_DOLLAR | 3 | 156 > |* 63 | INDEX RANGE SCAN | > PK_SAT_CRMAST_AGING_DOLLAR_IDX | 3 | | 6 | > |* 64 | INDEX RANGE SCAN | > IE1_CNTL_BUSINESS_DAY_IDX | 1 | | 1 | > ------------------------------------------------------------------------ > ---------------------------- > > Predicate Information (identified by operation id): > --------------------------------------------------- > > 1 - filter("T"."REPORT_CATEGORY"='MONTHLY' AND > ("S1"."LOAD_END_DTS">"T"."LOAD_DTS" OR "S1"."LOAD_ > "S2"."LOAD_END_DTS" IS NULL) AND > ("S3"."LOAD_END_DTS">"T"."LOAD_DTS" OR "S3"."LOAD_END_DTS" I > IS NULL) AND ("S5"."LOAD_END_DTS">"T"."LOAD_DTS" OR > "S5"."LOAD_END_DTS" IS NULL) AND ("S6"."L > ("S7"."LOAD_END_DTS">"T"."LOAD_DTS" OR "S7"."LOAD_END_DTS" > IS NULL) AND ("S8"."LOAD_END_DTS"> > ("S9"."LOAD_END_DTS">"T"."LOAD_DTS" OR "S9"."LOAD_END_DTS" > IS NULL) AND ("S10"."LOAD_END_DTS" > ("S11"."LOAD_END_DTS">"T"."LOAD_DTS" OR > "S11"."LOAD_END_DTS" IS NULL) AND ("S12"."LOAD_END_DT > ("S13"."LOAD_END_DTS">"T"."LOAD_DTS" OR > "S13"."LOAD_END_DTS" IS NULL) AND ("S14"."LOAD_END_DT > ("S12"."LOAD_END_DTS">"T"."LOAD_DTS" OR > "S12"."LOAD_END_DTS" IS NULL) AND ("S13"."LOAD_END_DT > 22 - access("H1"."CONTRACT_ID"="S14"."CONTRACT_ID") > 25 - access("H1"."CONTRACT_ID"="S13"."CONTRACT_ID") > 26 - access("L1"."CONTRACT_ID"="S13"."CONTRACT_ID" AND > "L1"."OWNER_POSITION_ID"="S13"."OWNER_POSIT > filter("H1"."CONTRACT_ID"="L1"."CONTRACT_ID") > 27 - access("H2"."OWNER_POSITION_ID"="L1"."OWNER_POSITION_ID") > 30 - access("L1"."CONTRACT_ID"="S12"."CONTRACT_ID" AND > "L1"."OWNER_POSITION_ID"="S12"."OWNER_POSIT > filter("H1"."CONTRACT_ID"="S12"."CONTRACT_ID") > 33 - access("H1"."CONTRACT_ID"="S11"."CONTRACT_ID") > 36 - access("H1"."CONTRACT_ID"="S10"."CONTRACT_ID") > 39 - access("H1"."CONTRACT_ID"="S9"."CONTRACT_ID") > 42 - access("H1"."CONTRACT_ID"="S8"."CONTRACT_ID") > 45 - access("H1"."CONTRACT_ID"="S7"."CONTRACT_ID") > 48 - access("H1"."CONTRACT_ID"="S6"."CONTRACT_ID") > 51 - access("H1"."CONTRACT_ID"="S5"."CONTRACT_ID") > 54 - access("H1"."CONTRACT_ID"="S4"."CONTRACT_ID") > > PLAN_TABLE_OUTPUT > ------------------------------------------------------------------------ > ---------------------------- > 57 - access("H1"."CONTRACT_ID"="S3"."CONTRACT_ID") > 60 - access("H1"."CONTRACT_ID"="S2"."CONTRACT_ID") > 63 - access("H1"."CONTRACT_ID"="S1"."CONTRACT_ID") > 64 - access("T"."OCCURRENCE_NUM"=0 AND > "S13"."LOAD_DTS"<="T"."LOAD_DTS") > filter("S1"."LOAD_DTS"<="T"."LOAD_DTS" AND > "S2"."LOAD_DTS"<="T"."LOAD_DTS" AND "S3"."LOAD_DTS > "S5"."LOAD_DTS"<="T"."LOAD_DTS" AND > "S6"."LOAD_DTS"<="T"."LOAD_DTS" AND "S7"."LOAD_DTS"<="T". > "S9"."LOAD_DTS"<="T"."LOAD_DTS" AND > "S10"."LOAD_DTS"<="T"."LOAD_DTS" AND "S11"."LOAD_DTS"<="T > "S13"."LOAD_DTS"<="T"."LOAD_DTS" AND > "S14"."LOAD_DTS"<="T"."LOAD_DTS" AND "S12"."LOAD_DTS"<=" > > This is the statement > > select > S1.ROWID S1_ROWID, > S2.ROWID S2_ROWID, > S3.ROWID S3_ROWID, > S4.ROWID S4_ROWID, > S5.ROWID S5_ROWID, > S6.ROWID S6_ROWID, > S7.ROWID S7_ROWID, > S8.ROWID S8_ROWID, > S9.ROWID S9_ROWID, > S10.ROWID S10_ROWID, > S11.ROWID S11_ROWID, > S12.ROWID S12_ROWID, > S13.ROWID S13_ROWID, > S14.ROWID S14_ROWID, > T.ROWID T_ROWID, > H1.ROWID H1_ROWID, > H2.ROWID H2_ROWID, > L1.ROWID L1_ROWID, > T.BUSINESS_DT, > h1.service_entity_num, > H1.CONTRACT_NUM, > S1.CR_10_30, > S1.CR_31_60, > S1.CR_61_90, > S1.CR_91_120, > S1.CR_121_150, > S1.CR_151_180, > S1.CR_181_210, > S1.CR_211_240, > S1.CR_241_270, > S1.CR_OVER_270, > S2.CR_ACTIVITY_DATE, > S2.CR_HC_POST1, > S2.CR_HC_POST2, > S2.CR_BAL, > S2.CR_CL_INT_COL, > S2.CR_CL_PREM_BAL, > S2.CR_CYR_DEF, > S2.CR_LAST_PAY_DATE, > S2.CR_NEXT_PAY_DATE, > S2.CR_DEF_INT_BAL, > S2.CR_DEF_PRIN_BAL, > S2.CR_FILING_FEE_COLLECTED, > S2.CR_INT_PENALTY_BAL, > S2.CR_INT_PENALTY_BAL_RSV, > S2.CR_LATE_FEE_BAL, > S2.CR_LATE_FEE_BAL_RSV, > S2.CR_PAYS_LEFT, > S2.CR_PRIN, > S2.CR_REFUND, > S2.CR_TITLE_INS_COLLECTED, > S2.CR_TOTAL_DOWN, > S3.CR_ACCRUED_INT_BAL, > S3.CR_ACCRUED_INT_BAL_DEP, > S3.CR_ACCRUED_INT_BAL_RSV, > S3.CR_ACCRUED_PRIN_BAL, > S4.CR_EQT_IN, > S4.CR_EQT_IN_FROM_SVC_ENTY1, > S4.CR_EQT_IN_FROM_CONT_NO1, > S4.CR_EQT_IN_FROM_SVC_ENTY2, > S4.CR_EQT_IN_FROM_CONT_NO2, > S4.CR_EQT_OUT_SVC_ENTY1, > S4.CR_EQT_OUT_CNUM1, > S4.CR_EQT_OUT_SVC_ENTY2, > S4.CR_EQT_OUT_CNUM2, > S4.CR_EQT_OUT_AMT1, > S4.CR_EQT_OUT_AMT2, > S4.CR_EQT_TRAN_INVOLVED, > S4.CR_TRADE_ALLOW, > S5.CR_FAC_CD, > S5.CR_PREV_FAC_CD, > S6.CR_FICO_DATE, > S6.CR_FICO_SCORE, > S7.CR_TS_ASSOC, > S7.CR_ALT_YEAR, > S7.CR_DEED_DATE, > S7.CR_DATE_REC_IN_DEEDING, > S7.CR_FPS_OWNER, > S7.CR_FPS_PHASE_NO, > S7.CR_FPS_POINTS_OWNED, > S7.CR_TS_LOCATION, > S8.CR_LA_NUM, > S9.CR_ADJ_RATE_CODE, > S9.CR_CREDIT_LIFE_TYPE, > S9.CR_CREDIT_APPROVAL_CODE, > S9.CR_DATE_SOLD, > S9.CR_HC_AMT1, > S9.CR_HC_AMT2, > S9.CR_HC_DATE1, > S9.CR_HC_DATE2, > S9.CR_INTERNATIONAL, > S9.CR_INT_RATE, > S9.CR_PAY_AMT, > S9.CR_FREQ, > S9.CR_PURCH, > S9.CR_TERMS, > S9.CR_RESV_TYPE, > S9.CR_SITE, > S9.CR_CL_PREM_AMT, > S9.CR_DISCOUNT, > S9.CR_FILING_FEE_CHARGED, > S9.CR_ORIG_DOWN, > S9.CR_TITLE_INS_CHARGED, > S10.CR_CANCEL_REASON, > S10.CR_STATUS, > S10.CR_PREVIOUS_STATUS, > S10.CR_TYPE, > S10.CR_SUPP_7_DATE, > S10.CR_FIRST_PAY_DATE, > S10.CR_DATE_IN_LR, > S10.CR_DOCUMENT_STATUS, > S10.CR_PAC_FLAG, > S10.CR_QUAL_CODE, > S10.CR_QUAL_DATE, > S10.CR_WAS_PENDER_TYPE, > S11.FICO_SCORE_DT, > S11.FICO_SCORE_NUM, > S12.PO_ADDR1, > S12.PO_ADDR2, > S12.PO_ADDR3, > S12.PO_BIRTH_DATE, > S12.PO_CITY, > S12.PO_COUNTRY_CD, > S12.PO_COUNTY, > S12.PO_FNAME, > S12.PO_HOME_PHONE, > S12.PO_LNAME, > S12.PO_FPS_MEMBER, > S12.PO_MNAME, > S12.PO_MOBILE_PHONE, > S12.PO_POSTAL_CODE, > S12.PO_STATE_PROVINCE, > S12.PO_WORK_PHONE, > S13.PO_SUPPRESS_STMT, > S14.TS_BAL_DUE_RECOGNIZED, > S14.TS_LATE_FEE_RECOGNIZED, > S14.TS_MAINT_FEE_AMT, > S14.TS_YTD_MAINT_FEE_COLLECTED > from CNTL_BUSINESS_DAY T, > HUB_CONTRACT H1, > HUB_OWNER_POSITION H2, > LNK_CONTRACT_OWNER L1, > SAT_CRMAST_AGING_DOLLAR S1, > SAT_CRMAST_BALANCE S2, > SAT_CRMAST_BALANCE_DAILY S3, > SAT_CRMAST_EQUITY_TRANSFER S4, > SAT_CRMAST_FAC S5, > SAT_CRMAST_FICO S6, > SAT_CRMAST_INVENTORY S7, > SAT_CRMAST_LOAN_ADMIN S8, > SAT_CRMAST_SELLING_INFO S9, > SAT_CRMAST_STATUS S10, > SAT_EQUIFAX S11, > SAT_POFILE S12, > SAT_POFILE_SUPPRESSION S13, > SAT_TSBFILE_BALANCE S14 > where T.OCCURRENCE_NUM = 0 > AND T.REPORT_CATEGORY = 'MONTHLY' > and H2.OWNER_POSITION_ID = L1.OWNER_POSITION_ID > and H1.CONTRACT_ID = L1.CONTRACT_ID > and H1.CONTRACT_ID = S1.CONTRACT_ID > and S1.LOAD_DTS <= T.LOAD_DTS > and ( S1.LOAD_END_DTS > T.LOAD_DTS > or S1.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S2.CONTRACT_ID > and S2.LOAD_DTS <= T.LOAD_DTS > and ( S2.LOAD_END_DTS > T.LOAD_DTS > or S2.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S3.CONTRACT_ID > and S3.LOAD_DTS <= T.LOAD_DTS > and ( S3.LOAD_END_DTS > T.LOAD_DTS > or S3.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S4.CONTRACT_ID > and S4.LOAD_DTS <= T.LOAD_DTS > and ( S4.LOAD_END_DTS > T.LOAD_DTS > or S4.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S5.CONTRACT_ID > and S5.LOAD_DTS <= T.LOAD_DTS > and ( S5.LOAD_END_DTS > T.LOAD_DTS > or S5.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S6.CONTRACT_ID > and S6.LOAD_DTS <= T.LOAD_DTS > and ( S6.LOAD_END_DTS > T.LOAD_DTS > or S6.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S7.CONTRACT_ID > and S7.LOAD_DTS <= T.LOAD_DTS > and ( S7.LOAD_END_DTS > T.LOAD_DTS > or S7.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S8.CONTRACT_ID > and S8.LOAD_DTS <= T.LOAD_DTS > and ( S8.LOAD_END_DTS > T.LOAD_DTS > or S8.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S9.CONTRACT_ID > and S9.LOAD_DTS <= T.LOAD_DTS > and ( S9.LOAD_END_DTS > T.LOAD_DTS > or S9.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S10.CONTRACT_ID > and S10.LOAD_DTS <= T.LOAD_DTS > and ( S10.LOAD_END_DTS > T.LOAD_DTS > or S10.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S11.CONTRACT_ID > and S11.LOAD_DTS <= T.LOAD_DTS > and ( S11.LOAD_END_DTS > T.LOAD_DTS > or S11.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S12.CONTRACT_ID > and S12.LOAD_DTS <= T.LOAD_DTS > and ( S12.LOAD_END_DTS > T.LOAD_DTS > or S12.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S13.CONTRACT_ID > and S13.LOAD_DTS <= T.LOAD_DTS > and ( S13.LOAD_END_DTS > T.LOAD_DTS > or S13.LOAD_END_DTS is null) > and H1.CONTRACT_ID = S14.CONTRACT_ID > and S14.LOAD_DTS <= T.LOAD_DTS > and ( S14.LOAD_END_DTS > T.LOAD_DTS > or S14.LOAD_END_DTS is null) > and L1.OWNER_POSITION_ID = S12.OWNER_POSITION_ID > and L1.CONTRACT_ID = S12.CONTRACT_ID > and S12.LOAD_DTS <= T.LOAD_DTS > and ( S12.LOAD_END_DTS > T.LOAD_DTS > or S12.LOAD_END_DTS is null) > and L1.OWNER_POSITION_ID = S13.OWNER_POSITION_ID > and L1.CONTRACT_ID = S13.CONTRACT_ID > and S13.LOAD_DTS <= T.LOAD_DTS > and ( S13.LOAD_END_DTS > T.LOAD_DTS > or S13.LOAD_END_DTS is null) > / > > > > > -----Original Message----- > From: Jonathan Lewis [mailto:jonathan@jlcomp.demon.co.uk] > Sent: Thursday, March 25, 2004 11:58 AM > To: oracle-l@freelists.org > Subject: Re: cache buffers chain latch contention > > > Which version of Oracle ? > > How parallel ? > > Would you send the full execution plans for > the parallel and serial create statements. > > > > Contention for cache buffers chains latches > only means that multiple processes want to > find blocks that are protected by the same > latch - this could be caused by almost any > type of activity. > > In the case of a complex parallel CTAS, > it could be that you have N slaves which > are scanning one table and indexing into > another (very small) table, and therefore > continually hitting the same couple of blocks > in the small table concurrently. If this is the > case, then perhaps identifying that small table > and rebuilding it with one row per block > would be sufficient to solve the problem. > But you do need to know very specifically > where the problem is before you start rebuilding > objects. > > Since the process is stuck anyway, why not > run it with 10046 set at level 8 - this should > propagate to the slaves, and you can see which > of the decomposed pieces of SQL they are > getting stuck on, and this might help pinpoint > the problem. > > > (In general, a db block doesn't hold rows from > multiple tables - only a block belonging to a > cluster has that option). > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > April 2004 Iceland http://www.index.is/oracleday.php > June 2004 UK - Optimising Oracle Seminar > > > ----- Original Message ----- > From: "Koivu, Lisa" > To: > Sent: Thursday, March 25, 2004 4:10 PM > Subject: 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 > > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > > "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@freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- "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@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------