RE: Tuning By cardinality Estimates

The cardinalities of the joins

------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 21489 |00:04:00.06 | 148K| 13345 | | 2 | NESTED LOOPS | | 1 | 218 | 21489 |00:04:00.04 | 148K| 13345 | | 3 | NESTED LOOPS OUTER | | 1 | 218 | 21489 |00:02:48.10 | 82575 | 9516 | | 4 | NESTED LOOPS | | 1 | 218 | 21489 |00:02:47.88 | 79708 | 9516 |

are off by a factor of about 100 ( 218 estimated vs 21489 actual ). Check if the join criteria between SC and SH are correlated.

At 04:34 AM 3/6/2009, hrishy wrote:

Hi Bhavik

Thanks for quick response,and correcting my understanding on the starts column.

Having understood the starts column and the cardinality calculation my question then would be why oracle is not using Hash Join and prefers a Nested Loop join It takes almost 8 minutes to complete the query with nested loops.If i use a use_hash hint it completes in 2 minutes

regards
Hrishy




--
http://www.freelists.org/webpage/oracle-l

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l


Other related posts: