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 Oct 18, 1:02*pm, geos <g... (AT) nowhere (DOT) invalid> wrote:
sys is special, you should not gather statistics for objects in it
(the system statistics gathering is a whole 'nuther story), and you
definitely should not be running any user data there.

To find out exactly what the optimizer is doing under the covers you
need to run a trace (google 10053).

But really, google the bind-peeking problem - you ought to be seeing
the opposite problem, the first process executing the query should get
the plan - so if you ran your query as scott, referring to emp, then
as sys, referring to scott.emp, those are two different querys. This
topic can get deep, especially in the latest versions with all sorts
of child cursors and such. Just doing an explain plan can be
misleading. You should at least look at the predicates and allstats.
See http://oracle-randolf.blogspot.com/2...rformance.html
, and Jonathan Lewis has posted some basic plan analysis on his blog,
and his book goes through what the optimizer does in detail.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...tter-trickery/

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

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,'advance d'))

Plan hash value: 3713220770

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

t (%CPU)| Time |

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


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

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

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: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.

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

On Oct 18, 1:02*pm, geos <g... (AT) nowhere (DOT) invalid> wrote:
A pickler fetch isn't what's executing against EMP, as Mladen pointed
out. You might want to read here:

http://oratips-ddf.blogspot.com/2009/12/in-pickle.html


David Fitzjarrell

geos wrote:
joel, ddf, Deadly Dirk, Mladen Gogala - thanks for pointing out my
mistake and for these article references!

cheers,
geos