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

--
FUT: comp.databases.oracle.misc

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

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

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

[mgogala@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

--------------------------------------------------------------------------------
----------

Time |

--------------------------------------------------------------------------------
----------

00:00:01 |

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

--------------------------------------------------------------------------------
--------------------

t (%CPU)| Time |

--------------------------------------------------------------------------------
--------------------


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