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 -> How can I further reduce physical read for this query short of throwing memory at it?

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

From: Jining Han <jining.han_at_gmail.com>
Date: 13 Feb 2006 09:09:08 -0800
Message-ID: <1139850548.770454.145430@g14g2000cwa.googlegroups.com>


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 Received on Mon Feb 13 2006 - 11:09:08 CST

Original text of this message

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