At 09:08 AM 1/30/2007, amonte wrote:
This is puzzling mea 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 Corporationwww.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