RE: Cost/Time Anomaly
- From: "Richard J. Goulet" <rgoulet@xxxxxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Jan 2007 12:32:06 -0500
This stinks like a star schema of one sort or another, therefore is
star_join enabled in this database?
Dick Goulet, Senior Oracle DBA
45 Bartlett St Marlborough, Ma 01752, USA
Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795
RGoulet@xxxxxxxxxx
: POWERING TRANSFORMATION
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of blr_dba
Sent: Friday, December 29, 2006 2:03 PM
To: 'ORACLE-L'
Subject: Cost/Time Anomaly
Hi Gurus,
Am stuck in a tuning problem and need your expertise to get rid of the
issue.
I have 3 huge tables(~20M rows each) and many small look up tables
joined in a query as follows...
Assume:
Big tables : BT1, BT2, BT3
Small tables: ST1, ST2, ST3, ST4, ST5, ST6
select * from BT1, BT2, BT3, ST1, ST2, ST3, ST4, ST5, ST6
where
BT1.id=BT2.id(+) and
BT1.id=BT3.id(+) and
BT1.id=ST1.id(+) and
BT1.id=ST2.id(+) and
BT1.id=ST3.id(+) and
BT1.id=ST4.id(+) and
BT1.id=ST5.id(+) and
BT1.id=ST6.id(+);
The CBO is using hash joins and the cost is too high (400K) and we are
having a lots of "direct path write waits".
I tried to remove the outer joins for the small lookup tables by using
sclar sub-queries. The cost reduced drastically (10K) but the overall
execution time got increased.
Badly need your expertise to get rid of this issue.
Also would like to know even if the CBO cost is less in the second case,
why the overall execution time is more. Is n't the cost inversly
proportional to the time taken to execute the query?
Other related posts:
