Re: a explain plan question

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@xxxxxxxxx> 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

Other related posts: