RE: Tuning By cardinality Estimates

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 06 Mar 2009 05:27:52 -0700
Message-Id: <200903061227.n26CRt6Z027365_at_mail97c0.megamailservers.com>

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

Received on Fri Mar 06 2009 - 06:27:52 CST