Re: a explain plan question
- From: amonte <ax.mount@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 30 Jan 2007 17:08:45 +0100
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:
