Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How can I further reduce physical read for this query short of throwing memory at it?

Re: How can I further reduce physical read for this query short of throwing memory at it?

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Mon, 13 Feb 2006 20:13:47 +0100
Message-ID: <dsql2e$knp$1@news1.zwoll1.ov.home.nl>


Jining Han wrote:
> The database is 10.1.0.4 RAC on HP-UX Itanium.
>
> I am having a hard time with this query. The best I have done is
> about 5 minutes with a 2GB db_cache_size (the box is running short on
> memory if I add more to the cache), and I have played with
> pga_aggregate_target without seeing obvious gains. My users want to
> bring this down to <= 2 min.
>
> The only thing I can think of trying now is to create some large block
> tablespaces (database is currently 8k blocks) since all analysis
> (statspack, traces, etc) indicates sequence read being the bottleneck.
>
> Any help/suggestions/hints are greatly appreciated
>
> Thanks
> JH
> ================== Query Info ========================
>
> Tables and # of records: ORG: 72393, SALES: 45787748
> # of records meeting SELECT criteria: 296771
>
> Query:
>
> select
> D.ORG_NBR ,
> D.BRNC_ID ,
> L.PROD_CD,
> sum(L.SALE_AMT),
> COUNT(DISTINCT(L.SALE_ID)),
> sum(L.DISCOUNT_AMT),
> COUNT(DISTINCT(L.CUST_ID))
> FROM
> ORG O,
> SALES L
> WHERE
> (L.DEPT_ID = D.ORG_ID
> AND ( L.SRC_SYS_CD IN ('EU', 'US') ))
> AND (
> D.ORG_NBR = '100000' and
> D.BRNC_ID = '5000'
> )
> GROUP BY
> D.ORG_NBR ,
> D.BRNC_ID ,
> L.PROD_CD;
>
>

What does table alias 'D' stand for?
Are D.ORG_NBR and D.BRNC_ID really character fields? Why is the ORG table parallel? 70k rows might be better off non-parallel.
Apart from that, I'd expect indexes on:
sales.DEPT_ID, D.ORG_ID, sales.SRC_SYS, D.ORG_NBR, D.BRNC_ID and SALES.PROD_CD

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
Received on Mon Feb 13 2006 - 13:13:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US