| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query tuning stumper
one place to look would be your sort_area_size. your doing summing and group
bys... if this is too small your going to do that in a temp tablespace
instead of in memory.
your using bitmap indexes right? this implies that some of this data atleast is non-transactional correct? if its non-transactional it can be pre-computed with a materialized view. tihs could potentially save alot..
you are also using a hash join. look at your hash_area_size as well.
----- Original Message -----
From: "Mark Richard" <mrichard_at_transurban.com.au>
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Tuesday, July 15, 2003 8:49 PM
Subject: Re: Query tuning stumper
>
> I've only looked quickly at this query, but why do you think it should be
> using the shipto_key index? There doesn't appear to be any kind of
> filtering on the cust_shipto table so I don't know why an index would help
> since every record would match. Am I missing something?
>
> Also, what explain plan tool are you using to produce that plan with
> embedded SQL - it looks like it would be very useful to teach concepts to
> people using that plan?
>
> Regards,
> Mark.
>
>
>
>
> "Meng, Dennis"
> <Dennis.Meng_at_card To: Multiple
recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> inal.com> cc:
> Sent by: Subject: Query tuning
stumper
> ml-errors_at_fatcity
> .com
>
>
> 16/07/2003 06:49
> Please respond to
> ORACLE-L
>
>
>
>
>
>
> Hi all,
> I have been struggling with the follwing query for hours with no avail.
> This is oracle 8.1.7.4 on Tru64 unix with a data warehouse setup.
Invc_line
> is the fact table with about 267 mil records.
> There is a bitmap index on shipto_key(invc_line_bix04) and another bitmap
> index on BILLG_DT_KEY( invc_line_bix01). The optimal path should be a
> bitmap index merge of these two indexes, but instead it is ignoring the
> index on shipto_key. Can anybody shel some line on what gives here?
>
> SELECT SUM(INVC_LINE.EXTND_FI_COGS),
>
> SUM(INVC_LINE.EXTND_SD_COGS),
>
> MTL.RPTG_SEG_CD,
>
> MTL.RPTG_SEG_DESC,
>
> SUM(INVC_LINE.EXTND_SD_REBT_ADJD_GP),
>
> INVC_LINE.ITEM_CTGRY_DESC,
>
> INVC_LINE.ITEM_CTGRY_CD,
>
> SUM(INVC_LINE.EXTND_SD_REBT_ADJD_COGS),
>
> SUM(INVC_LINE.EXTND_FI_SALES_PRC),
>
> SUM(INVC_LINE.EXTND_SD_SALES_PRC),
>
> INVC_LINE.ORD_TYP_CD,
>
> CUST_SHIPTO.SALES_ORD_CUST_GRP_CD,
>
> CUST_SHIPTO.SALES_GRP_PRES_REG_DESC,
>
> SUM(INVC_LINE_ATTRB.SHP_DIRCT_CST),
>
> SUM(INVC_LINE.EXTND_REBT_PART_AMT)
>
> FROM CUST_SHIPTO,
>
> INVC_LINE,
>
> INVC_LINE_ATTRB,
>
> BILL_DT,
>
> MTL
>
> WHERE ( INVC_LINE.SHPTO_KEY=CUST_SHIPTO.SHPTO_KEY )
>
> AND ( INVC_LINE.BILLG_DT_KEY=BILL_DT.DAY_DT_KEY )
>
> AND ( MTL.MTL_KEY=INVC_LINE.MTL_KEY )
>
> AND ( INVC_LINE_ATTRB.BILLG_NUM=INVC_LINE.BILLG_NUM
>
> AND INVC_LINE_ATTRB.BILLING_SEQ_NUM=INVC_LINE.BILLG_SEQ_NUM )
>
> AND ( BILL_DT.DT BETWEEN '2003/06/01'
>
> AND '2003/06/30'
>
> AND INVC_LINE.SALES_CHNNL IN ('D',
>
> 'F',
>
> 'M',
>
> 'DPG') )
>
> GROUP BY MTL.RPTG_SEG_CD,
>
> MTL.RPTG_SEG_DESC,
>
> INVC_LINE.ITEM_CTGRY_DESC,
>
> INVC_LINE.ITEM_CTGRY_CD,
>
> INVC_LINE.ORD_TYP_CD,
>
> CUST_SHIPTO.SALES_ORD_CUST_GRP_CD,
>
> CUST_SHIPTO.SALES_GRP_PRES_REG_DESC
>
> The Plan
>
> Select Statement CHOOSE Rows: 164,290 Cost: 466,524
> Sort Group By Rows: 164,290 Cost: 466,524
> GROUP BY mtl.rptg_seg_cd, mtl.rptg_seg_desc,
> invc_line.item_ctgry_desc, invc_line.item_ctgry_cd, invc_line.ord_typ_cd,
> cust_shipto.sales_ord_cust_grp_cd, cust_shipto.sales_grp_pres_reg_desc
> Nested Loops Rows: 164,290 Cost: 462,150
> Hash Join Rows: 135,004 Cost: 57,138
> mtl.mtl_key = invc_line.mtl_key
> Hash Join Rows: 135,004 Cost: 53,334
> invc_line.shpto_key = cust_shipto.shpto_key
> Nested Loops Rows: 135,004 Cost: 45,127
> Unique Range Scan Dw.day_dt_idx1 [Analyzed] Keys:
> 0.000 Rows Per Key: 1.00 Rows: 1 Cost: 11
> bill_dt.dt >= '2003/06/01', bill_dt.dt
> <= '2003/06/03'
> Table Access By Index Rowid Dw.invc_line Rows:
> 271,897,386 Cost: 45,127
> invc_line.sales_chnnl = 'D', invc_line.sales_chnnl
> = 'F', invc_line.sales_chnnl = 'M', invc_line.sales_chnnl = 'DPG'
> Bitmap Conversion To Rowids
> Bitmap Index Single Value Dw.invc_line_bix01
> invc_line.billg_dt_key = bill_dt.day_dt_key
> Table Access Full Dw.cust_shipto Rows: 1,179,146
Cost:
> 3,636
> Unique Fast Full Scan Dw.mtl_ix2 [Analyzed] Keys:
> 0.000 Rows Per Key: 1.00 Rows: 812,615 Cost: 292
> Table Access By Index Rowid Dw.invc_line_attrb Rows:
> 272,311,352 Cost: 3
> Unique Unique Scan Dw.pk_invc_line_attrb [Analyzed]
Keys:
> 0.000 Rows Per Key: 1.00 Rows: 272,311,352 Cost: 2
> invc_line_attrb.billg_num = invc_line.billg_num,
> invc_line_attrb.billing_seq_num = invc_line.billg_seq_num
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: JApplewhite_at_austin.isd.tenet.edu
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Meng, Dennis
> INET: Dennis.Meng_at_cardinal.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 |
 |