Re: execution plans
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.comReceived on Tue Oct 18 2011 - 18:29:18 CDT