Re: a explain plan question

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 30 Jan 2007 08:22:28 -0700
Message-Id: <20070130152321.80C105C4722@turing.freelists.org>

At 07:43 AM 1/30/2007, amonte wrote:
>Hi
>
>I have this query

SNIP
>and this plan
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1022392 Card=934
> Bytes=49502)
> 1 0 SORT (GROUP BY) (Cost=1022392 Card=934 Bytes=49502)
> 2 1 NESTED LOOPS (Cost=814767 Card=182275095 Bytes=9660580035)
> 3 2 TABLE ACCESS (FULL) OF 'TUD_FEDIA' (Cost=3 Card=30 Bytes=480)
> 4 2 PARTITION RANGE (ITERATOR)
> 5 4 TABLE ACCESS (FULL) OF 'TUH_NVPAGINA' (Cost=27159
> Card=5992606 Bytes=221726422)
>
>--------------------------------------------------------------------------------------------
>| 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 |
>--------------------------------------------------------------------------------------------

SNIP
>I was wondering how to read this plan, the order of steps. From old
>set autotrace trace exp it seems to me that step 5 is the first step?

Unless I'm completely mistaken, the first step is to access the driving table of the nested loop join, i.e. step 3. Besides, if it didn't it wouldn't have the information for the filter predicate for step 5

An interesting side-observation (for me at least) was that the M stands for MB as in 1,048,576=1024*1024, not Million as in 1,000,000. I wouldn't intuitively count rows in multiples of 1024.

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

Received on Tue Jan 30 2007 - 09:22:28 CST