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;
>
>
> AUTO STATS
> ----------------------------------------
> 402 recursive calls
> 8 db block gets
> 241874 consistent gets
> 240828 physical reads
> 4080 redo size
> 1152 bytes sent via SQL*Net to client
> 664 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 1 sorts (disk)
> 4 rows processed
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12218 Card=11 Byte
> s=682)
>
> 1 0 PX COORDINATOR
> 2 1 PX SEND* (QC (RANDOM)) OF ':TQ10002' (Cost=12218 Card=11
> :Q1002
> Bytes=682)
>
> 3 2 SORT* (GROUP BY) (Cost=12218 Card=11 Bytes=682)
> :Q1002
> 4 3 PX RECEIVE* (Cost=12218 Card=11 Bytes=682)
> :Q1002
> 5 4 PX SEND* (HASH) OF ':TQ10001' (Cost=12218 Card=11
> :Q1001
> Bytes=682)
>
> 6 5 SORT* (GROUP BY) (Cost=12218 Card=11 Bytes=682)
> :Q1001
> 7 6 PX RECEIVE* (Cost=12218 Card=11 Bytes=682)
> :Q1001
> 8 7 PX SEND* (HASH) OF ':TQ10000' (Cost=12218 Ca
> :Q1000
> rd=11 Bytes=682)
>
> 9 8 SORT* (GROUP BY) (Cost=12218 Card=11 Bytes
> :Q1000
> =682)
>
> 10 9 TABLE ACCESS* (BY GLOBAL INDEX ROWID) OF
> :Q1000
> 'SALES' (TABLE) (Cost=43569 Card=70335 Bytes=3024405)
>
> 11 10 NESTED LOOPS* (Cost=12217 Card=53481 B
> :Q1000
> ytes=3315822)
>
> 12 11 PX BLOCK* (ITERATOR)
> :Q1000
> 13 12 TABLE ACCESS* (FULL) OF 'ORG'
> :Q1000
> (TABLE) (Cost=114 Card=1 Bytes=19)
>
> 14 11 INDEX* (RANGE SCAN) OF
> 'FK_SALES$LNDR:Q1000
> ' (INDEX) (Cost=199 Card=70989)
>
>
> Thanks
> Jining

There are alternatives to using GROUP BY that might help you.

Go to Morgan's Library at www.psoug.org. Click on GROUP BY
Scroll down to: "GROUP BY SUBSTITUTE"

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)