Re: a explain plan question
- From: amonte <ax.mount@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Tue, 30 Jan 2007 19:46:57 +0100
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
______________________________________________________________________
--
//www.freelists.org/webpage/oracle-l
Other related posts:
