Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: a explain plan question

Re: a explain plan question

From: amonte <ax.mount_at_gmail.com>
Date: Tue, 30 Jan 2007 19:46:57 +0100
Message-ID: <85c1fb130701301046k5bc067aco67cf87562ad24b0f@mail.gmail.com>


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_at_centrexcc.com> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 30 2007 - 12:46:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US