Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How can I further reduce physical read for this query short of throwing memory at it?
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