Re: execution plans

From: Deadly Dirk <dirk_at_pfln.invalid>
Date: Tue, 18 Oct 2011 21:39:30 +0000 (UTC)
Message-ID: <pan.2011.10.18.21.39.30_at_pfln.invalid>



On Tue, 18 Oct 2011 22:02:01 +0200, geos wrote:

> there is a query which executes with two different plans when run on
> different accounts. this is generally understandable to me. but is there
> a way/algorithm to investigate the settings/configuration/permissions
> which have crucial influence on execution plans? for example:
>
> select * from emp where ename in ('KING','SMITH');
>
> execution plan as scott:
> 0 SELECT STATEMENT, koszt 24
> 1 COLLECTION ITERATOR PICKLER FETCH
>
> execution plan as sys:
> 0 SELECT STATEMENT, koszt 2
> 1 INLIST ITERATOR
> 2 TABLE ACCESS BY INDEX ROWID
> 3 INDEX RANGE SCAN
>
> is there anything in the above plans that suggests some parameter(s)
> which when set in sys session would give scott-like execution plan?
>
> thank you,
> geos

Geos, you're making a mistake. The "pickler" plan is from displaying cursor. Here is the complete session that reproduces your problem:

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT



SQL_ID fv0v1agrdrjkv, child number 0

select * from table(dbms_xplan.display_cursor(null,null,'advanced'))

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_ftkj82r3jcvxq8df50001 used for this statement

17 rows selected.

Elapsed: 00:00:00.11
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.00
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT



SQL_ID gz5n0raq2znm5, child number 1

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:00.06
SQL> show user
USER is "SCOTT"
SQL> The first attempt was by using the "display_cursor" with the "advanced" argument. I didn't like the output, so I re-run it without the arguments and got the "pickler plan" you mentioned. After that, I did it for the same statement as you and got the right plan, the one with the index scan, all of that without ever switching to "SYS". You simply made a mistake, you executed "display_cursor" twice in a row.

-- 
I don't think, therefore I am not.
Received on Tue Oct 18 2011 - 16:39:30 CDT

Original text of this message