RE: a explain plan question

From: Pabba, Chandra <Chandra.Pabba_at_fmr.com>
Date: Tue, 30 Jan 2007 08:53:02 -0600
Message-ID: <6D2375ACD32D9B48A9B433AA0527B084054A497F@MSGDCCCLA2WIN.DMN1.FMR.COM>

Hi,  

The rightmost or the most indented and the uppermost operation is the first operation that is executed. In your case, step 5.  

HTH Thanks
Chandra Pabba

---

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of amonte

	Sent: Tuesday, January 30, 2007 8:44 AM
	To: oracle-l_at_freelists.org
	Subject: a explain plan question
	
	
	Hi
	
	I have this query
	
	SELECT
	   TUH_NVPAGINA.ID_SECCION AS ID_SECCION,
	   TUD_FEDIA.ID_TIPO_DIA AS ID_TIPO_DIA,
	   TUD_FEDIA.ID_MES AS ID_MES,
	   count(distinct TUH_NVPAGINA.TX_COOKIE_SESION) CNT
	FROM
	   TUH_NVPAGINA,
	   (SELECT FE_DIA, ID_MES, ID_TIPO_DIA
	      FROM TUD_FEDIA
	     WHERE ID_MES = :p_f_inicio) TUD_FEDIA
	WHERE TUH_NVPAGINA.FE_DIA = TUD_FEDIA.FE_DIA
	GROUP BY
	   TUH_NVPAGINA.ID_SECCION, 
	   TUD_FEDIA.ID_TIPO_DIA,
	   TUD_FEDIA.ID_MES
	
	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 |         

---

---

        

	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")
	
	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?         

           5 4 TABLE ACCESS (FULL) OF 'TUH_NVPAGINA' (Cost=27159 Card=5992606 Bytes=221726422)         

        Thanks         

        Alex                  

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 30 2007 - 08:53:02 CST