Re: execution plans

From: joel garry <joel-garry_at_home.com>
Date: Tue, 18 Oct 2011 13:50:00 -0700 (PDT)
Message-ID: <27f34998-a4a5-4af0-8b04-d081ca79d85b_at_x16g2000prd.googlegroups.com>



On Oct 18, 1:02 pm, geos <g..._at_nowhere.invalid> 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
>
> --
> FUT: comp.databases.oracle.misc

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/2009/02/basic-sql-statement-performance.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

--
_at_home.com is bogus.
http://www.signonsandiego.com/news/2011/oct/18/the-gop-chairmans-twitter-trickery/
Received on Tue Oct 18 2011 - 15:50:00 CDT

Original text of this message