OraFAQ Forum: SQL & PL/SQL » COLLECTION ITERATOR PICKLER FETCH

Home » SQL & PL/SQL » SQL & PL/SQL » COLLECTION ITERATOR PICKLER FETCH (Oracle 11.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

COLLECTION ITERATOR PICKLER FETCH [message #512270]

Fri, 17 June 2011 14:37

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

SQL> create table m001 as
  2    select to_char(level) l
  3    from   dual
  4    connect by level <= 10000
  5    union all
  6    select level
  7           ||chr(level)
  8    from   dual
  9    connect by level <= 5000;

Table created.

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM M001 WHERE L=2;

Explained.

SQL> 
SQL> 
SQL> create index t_idx on m001(l);

Index created.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM M001 WHERE L=2;

Explained.

SQL> 
SQL> 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

why COLLECTION ITERATOR PICKLER FETCH| DISPLAY instead of TABLE ACCESS FULL as Oracle need to skip index as it needs to use implicite conversation.

Regards
Ved

Report message to a moderator

Re: COLLECTION ITERATOR PICKLER FETCH [message #512273 is a reply to message #512270]

Fri, 17 June 2011 14:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://wiki.oracle.com/page/HOW+TO+trace+the+CBO+working+out+the+execution+path+%28event+10053%29

CBO works best when is has accurate statistics; which posted thread is lacking.

Report message to a moderator

Re: COLLECTION ITERATOR PICKLER FETCH [message #512274 is a reply to message #512273]

Fri, 17 June 2011 14:54

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

Session altered.

SQL> SELECT * FROM M001 WHERE L=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 185927211

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    23 |   552 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| M001 |    23 |   552 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("L")=2)

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

Session altered.

SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';

Session altered.

SQL> SELECT * FROM M001 WHERE L=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 185927211

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    23 |   552 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| M001 |    23 |   552 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("L")=2)

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

Session altered.

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM M001 WHERE L=2;

Explained.

SQL> 
SQL> 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Yes, thats what the plan I expected when used event 10053. Wondering why it is COLLECTION ITERATOR PICKLER FETCH when I simply use EXPLAIN PLAN FOR.

Regards
Ved

[Updated on: Fri, 17 June 2011 14:54]

Report message to a moderator

Re: COLLECTION ITERATOR PICKLER FETCH [message #512275 is a reply to message #512274]

Fri, 17 June 2011 14:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Wondering why it is COLLECTION ITERATOR PICKLER FETCH when I simply use EXPLAIN PLAN FOR.
File a Service Request with MOS.

Report message to a moderator

Re: COLLECTION ITERATOR PICKLER FETCH [message #512276 is a reply to message #512274]

Fri, 17 June 2011 15:03

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> SELECT * FROM M001 WHERE L=2;
ERROR:
ORA-01722: invalid number



no rows selected

Does this give you a hint?
Also see my post at the bottom of http://www.orafaq.com/forum/t/172127/102589/ topic.

Regards
Michel

Report message to a moderator

Re: COLLECTION ITERATOR PICKLER FETCH [message #512284 is a reply to message #512276]

Fri, 17 June 2011 21:37

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

Ved,

What you have posted is labeled "Execution Plan" like you get from autotrace, not "PLAN_TABLE_OUTPUT" like you get from dbms_xplan.display. Also, look at what is in the name column and you will see "DISPLAY", not "M001". What you are looking at is the execution plan for the query on table(dbms_xplan.display), not the plan table output for the query on table m001 that you ran. Since it uses the table function, it does not have statistics, so it uses a pickler fetch. Please see the demonstration below.

SCOTT@orcl_11gR2> create table m001 as
  2  select to_char(level) l
  3  from   dual
  4  connect by level <= 10000
  5  union all
  6  select level
  7  	    ||chr(level)
  8  from   dual
  9  connect by level <= 5000;

Table created.

SCOTT@orcl_11gR2> create index t_idx on m001(l);

Index created.

SCOTT@orcl_11gR2> exec dbms_stats.gather_table_stats (USER, 'M001')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> EXPLAIN PLAN FOR
  2  SELECT * FROM M001 WHERE L=2;

Explained.

SCOTT@orcl_11gR2> set autotrace on explain
SCOTT@orcl_11gR2> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- This is the plan_table_output for the explain plan for the query on table m001:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 185927211

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| M001 |     1 |     5 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("L")=2)

13 rows selected.

-- This is the execution plan from autotrace for the query on table(dbms_xplan.display):

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

SCOTT@orcl_11gR2>

[Updated on: Fri, 17 June 2011 21:46]

Report message to a moderator

Previous Topic:	Update a field
Next Topic:	calling APIs

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 30 15:08:39 CDT 2025