Re: a explain plan question

From: amonte <ax.mount_at_gmail.com>
Date: Tue, 30 Jan 2007 17:08:45 +0100
Message-ID: <85c1fb130701300808k1de38227m221914126204d070@mail.gmail.com>

This is puzzling me

a change from NL to HASH (by using USE_HASH) then plan changed which is obvious but also the stats? TUH_NVPAGINA which has over 2000 million of rows now shows correct estimation under HASH but showed a miserable 6 million using NL. What is causing this?

---

| Id  | Operation                  |  Name         | Rows  | Bytes | Cost  |

Pstart| Pstop |

---

|   0 | SELECT STATEMENT           |               |   934 | 49502 |
1022K|       |       |
|   1 |  SORT GROUP BY             |               |   934 | 49502 |
1022K|       |       |
|   2 |   NESTED LOOPS             |               |   182M|  9213M|
814K|       |       |
|*  3 |    TABLE ACCESS FULL       | TUD_FEDIA     |    30 |   480 |     3
|       |       |
|   4 |    PARTITION RANGE ITERATOR|               |       |       |
|   KEY |   KEY |
|*  5 |     TABLE ACCESS FULL      | TUH_NVPAGINA  |  5992K|   211M| 27159

| KEY | KEY |

---

Predicate Information (identified by operation id):

---

   3 - filter("TUD_FEDIA"."ID_MES"=TO_NUMBER(:Z))    5 - filter("TUH_NVPAGINA"."FE_DIA"="TUD_FEDIA"."FE_DIA")

---

| Id  | Operation                  |  Name         | Rows  | Bytes | Cost  |

Pstart| Pstop |

---

|   0 | SELECT STATEMENT           |               |   934 | 49502 |
1555K|       |       |
|   1 |  SORT GROUP BY             |               |   934 | 49502 |
1555K|       |       |
|*  2 |   HASH JOIN                |               |   182M|  9213M|
1347K|       |       |
|*  3 |    TABLE ACCESS FULL       | TUD_FEDIA     |    30 |   480 |     3
|       |       |
|   4 |    PARTITION RANGE ITERATOR|               |       |       |
|   KEY |   KEY |
|   5 |     TABLE ACCESS FULL      | TUH_NVPAGINA  |  2163M|    74G|

1330K| KEY | KEY |

---

Predicate Information (identified by operation id):

---

   2 - access("TUH_NVPAGINA"."FE_DIA"="TUD_FEDIA"."FE_DIA")    3 - filter("TUD_FEDIA"."ID_MES"=TO_NUMBER(:Z))

On 1/30/07, Dennis Williams <oracledba.williams_at_gmail.com> wrote:
>
> Alex,
>
> For the basics of understanding Oracle explain plans, there are some
> pretty good resources on the web. A quick Google found the following, which
> on first glance provides a good start.
>
> http://www.akadia.com/services/ora_interpreting_explain_plan.html
>
> Once you get the basics under your belt, there are some experts on this
> list that can help answer your more specific questions.
>
> Dennis Williams
>

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

Received on Tue Jan 30 2007 - 10:08:45 CST