RE: Tuning By cardinality Estimates
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: hrishys@xxxxxxxxxxx
- Date: Fri, 06 Mar 2009 05:27:52 -0700
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
--
//www.freelists.org/webpage/oracle-l
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
