Re: execution plans

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Tue, 18 Oct 2011 23:29:18 +0000 (UTC)
Message-ID: <pan.2011.10.18.23.29.18_at_gmail.com>



On Tue, 18 Oct 2011 22:02:01 +0200, geos wrote:
> execution plan as scott:
> 0 SELECT STATEMENT, koszt 24
> 1  COLLECTION ITERATOR PICKLER FETCH

Geos, this is the execution plan for
"select * from table(dbms_xplan.display_cursor())"

You've made a mistake somewhere. Let me demonstrate:

[mgogala_at_medo ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 18 19:25:26 2011

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from emp where ename in ('KING','SMITH');

     EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO


      7839 KING       PRESIDENT 	   17-NOV-81	   5000
	10

      7369 SMITH      CLERK	      7902 17-DEC-80	    800
	20


Elapsed: 00:00:00.07
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT



SQL_ID gz5n0raq2znm5, child number 0

select * from emp where ename in ('KING','SMITH')

Plan hash value: 3225201695



| Id | Operation | Name | Rows | Bytes | Cost (% CPU)|
Time |



| 0 | SELECT STATEMENT | | | | 2 (100)|

         |

|   1 |  INLIST ITERATOR	     |		 |	 |	 
|	      |
	 |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP	 |     2 |    74 |     
2 (0)|
00:00:01 |

|* 3 | INDEX RANGE SCAN | EMP_ENAME | 2 | | 1 (0)|
00:00:01 |



Predicate Information (identified by operation id):


   3 - access(("ENAME"='KING' OR "ENAME"='SMITH'))

Note


  • SQL plan baseline SQL_PLAN_8dv7sf1yt16ycb03ea9f1 used for this statement

24 rows selected.

Elapsed: 00:00:01.25
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT



SQL_ID 738a0r0utp4wv, child number 0

select * from table(dbms_xplan.display_cursor())

Plan hash value: 3713220770



| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |



|   0 | SELECT STATEMENT		  |		   |	   
|	   
|
24 (100)|	   |

| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 8168 | 16336 |
24 (5)| 00:00:01 |



Note


  • SQL plan baseline SQL_PLAN_bgbyxyzjz83xz8df50001 used for this statement

17 rows selected.

Elapsed: 00:00:00.06
SQL> First, I executed your SQL and then executed dbms_xplan.display_cursor twice. Second time, it gave me the plan that seems to be the problem.

-- 
http://mgogala.byethost5.com
Received on Tue Oct 18 2011 - 18:29:18 CDT

Original text of this message