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_at_wangtrading.com]
Sent: Thursday, March 25, 2004 1:45 PM
To: oracle-l_at_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_at_jlcomp.demon.co.uk]
> Sent: Thursday, March 25, 2004 11:58 AM
> To: oracle-l_at_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" <Lisa.Koivu_at_Cendant-TRG.com>
> To: <oracle-l_at_freelists.org>
> 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_at_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_at_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_at_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_at_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
-----------------------------------------------------------------
Received on Thu Mar 25 2004 - 14:14:31 CST
