RE: cache buffers chain latch contention

  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 25 Mar 2004 14:34:52 -0500

Hi Waleed, 

I'm sorry, I meant total of 8, 4 per cpu.  I still don't see how that
will solve the underlying problem though. 

Thanks
Lisa

-----Original Message-----
From: Khedr, Waleed [mailto:Waleed.Khedr@xxxxxxx] 
Sent: Thursday, March 25, 2004 2:09 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: cache buffers chain latch contention

Degree of 8 means 16 workers on 2 CPUs = means magnifying any small
contention for CPU resources (that will never show up on another system
if you have more CPU's).

Reduce the degree to 2 and see how it works for you.

Waleed

-----Original Message-----
From: Koivu, Lisa [mailto:Lisa.Koivu@xxxxxxxxxxxxxxx]
Sent: Thursday, March 25, 2004 1:24 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: cache buffers chain latch contention


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@xxxxxxxxxxxxxxxxxx] 
Sent: Thursday, March 25, 2004 11:58 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@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
-----------------------------------------------------------------

"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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------

"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: