Re: a explain plan question

As usual Wolfgang you are right. I didnt know you have to multiply the 30 *
6 million....

Thanks

Alex




On 1/30/07, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:


At 09:08 AM 1/30/2007, amonte wrote:
>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?

In the NL plan there is a flter predicate on TUH_NVPAGINA. It will
read TUH_NVPAGINA an estimated 30 times (rows from step 3) returning
~6M rows each time for an estimated 30*6M = 180M rows coming out of
the nested loop (step 2 rows = 182M)

In the HA plan it reads TUH_NVPAGINA  once, all 2163M rows (there is
no a access or filter predicate for step 5). Those 2163M rows are
than hashed in step 2 against the 30 rows out of step3 returning the
same estimated 182M rows.


>--------------------------------------------------------------------------------------------
>| 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))

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com



______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
--
http://www.freelists.org/webpage/oracle-l

Other related posts: