Re: execution plans
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