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