Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, sarayu.

Asked: October 18, 2007 - 8:06 am UTC

Answered by: Tom Kyte - Last updated: July 06, 2020 - 2:30 pm UTC

Category: Database - Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I am using DBMS_XPLAN.DISPLAY to display the execution plan of a query.
After using EXPLAIN PLAN SET STATEMENT ID ....

I am giving the following query:

SET PAGESIZE 0
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','SAM','ALL'));
Execution Plan
----------------------------------------------------------
Plan hash value: 3013799171

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         | 32744 | 65488 |    49   (3)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |            |          |
---------------------------------------------------------------------------------------------


can you please help me to know how to avoid this "COLLECTION ITERATOR PICKLER FETCH" and get the proper explain plan?

Thank you very much

Sarayu K.S.

and we said...

that is the correct plan.

you are selecting from a plsql function, it returns "pickled" (packed, formatted data) data.

Now, it would seem the query you explained was the one you just ran!

Two ways to do this, first, just empty your plan table and just use display:

12 rows deleted.

ops$tkyte%ORA10GR2> explain plan for select * from all_users;

Explained.

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 3588110293

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |    33 |  1254 |    40   (3)| 00:00:01 |
|*  1 |  HASH JOIN          |       |    33 |  1254 |    40   (3)| 00:00:01 |
|*  2 |   HASH JOIN         |       |    33 |  1155 |    22   (5)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| USER$ |    33 |  1056 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TS$   |    48 |   144 |    18   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | TS$   |    48 |   144 |    18   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("U"."TEMPTS#"="TTS"."TS#")
   2 - access("U"."DATATS#"="DTS"."TS#")
   3 - filter("U"."TYPE#"=1)

19 rows selected.


alternatively, use set statement_id to name your query and just display that one:

ops$tkyte%ORA10GR2> explain plan set statement_id 'FOO' for select * from scott.emp;

Explained.

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display('PLAN_TABLE', 'FOO'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |   540 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    15 |   540 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

and you rated our response

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

I still love this name: PICKLER FETCH

October 18, 2007 - 12:07 pm UTC

Reviewer: Duke Ganote from Amelia, OH USA

interesting: "COLLECTION ITERATOR PICKLER FETCH" is a fixpoint

October 19, 2007 - 4:27 am UTC

Reviewer: A reader

sql > select * from dual;
X
sql > select * from table(dbms_xplan.display_cursor);
SQL_ID  a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

sql > select * from table(dbms_xplan.display_cursor);
SQL_ID  dt0hqkzcdq5ny, child number 0
-------------------------------------
select * from table(dbms_xplan.display_cursor)

Plan hash value: 3713220770

--------------------------------------------------------------------
| Id  | Operation                         | Name           | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |     2 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |       |
--------------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

sql > select * from table(dbms_xplan.display_cursor);
....

COLLECTION ITERATOR PICKLER FETCH

November 29, 2007 - 2:40 am UTC

Reviewer: Nikhil from India

Hi,

From the below mentioned plan we can see that the table function is causing "COLLECTION ITERATOR PICKLER FETCH " & our DBA's are saying it is causing severe performance overhead. But when i look at the explain plan nothing seems to be wrong & entire program gets over in 2mins& 30secs (processed 20000 records approx.).

I am unable to draw any firm conclusion on , how to get rid of this "COLLECTION ITERATOR PICKLER FETCH " & it would be great if you can put some light on this. Kindly find below query & it execution plan.

SELECT DISTINCT DECODE(CH.TRX_TYPE, 'INV',CH.ORDER_NUMBER,NVL(CH.ORDER_NUMBER,
CH.COMMISSION_HEADER_ID)) ORDER_NUMBER , CH.TRX_TYPE TRX_TYPE
FROM
CN_COMMISSION_HEADERS CH , (( SELECT HIER_MV.PRY_MANAGER_FLAG
PRY_MANAGER_FLAG , HIER_MV.PRY_OVERLAY_FLAG PRY_OVERLAY_FLAG ,
HIER_MV.START_DATE_ACTIVE START_DATE_ACTIVE , HIER_MV.END_DATE_ACTIVE
END_DATE_ACTIVE , HIER_MV.SECY_SALESREP_ID SECY_SALESREP_ID , LEVEL
HIER_LEVEL FROM ( SELECT DHMV.PRY_SALESREP_ID , DHMV.PRY_MANAGER_FLAG ,
DHMV.PRY_OVERLAY_FLAG , DHMV.START_DATE_ACTIVE , DHMV.END_DATE_ACTIVE ,
DHMV.SECY_SALESREP_ID FROM DSCN_HIERARCHY_MV DHMV WHERE ((
DHMV.PRY_MEMBER_FLAG = 'Y' AND DHMV.PRY_OVERLAY_FLAG = 'Y' AND
DHMV.HIERARCHY_TYPE = 'TEAM_MEMBER' AND
DHMV.PRY_SALESREP_ID<>DHMV.SECY_SALESREP_ID) ) AND (
NVL(DHMV.END_DATE_ACTIVE, :B2 ) >= (:B3 - 90) AND DHMV.START_DATE_ACTIVE <=
:B2 ) AND DHMV.PRY_SALESREP_ID = :B1 ) HIER_MV START WITH PRY_SALESREP_ID =
:B1 CONNECT BY PRIOR SECY_SALESREP_ID = PRY_SALESREP_ID ) ) DHM WHERE
CH.PROCESSED_PERIOD_ID = :B7 AND DECODE(DHM.PRY_MANAGER_FLAG, 'Y',
CH.BOOKED_DATE, DECODE(DHM.PRY_OVERLAY_FLAG,'Y',CH.BOOKED_DATE,
GREATEST(DHM.START_DATE_ACTIVE, CH.PROCESSED_DATE - 90))) BETWEEN
GREATEST(DHM.START_DATE_ACTIVE,CH.PROCESSED_DATE - 90) AND
NVL(DHM.END_DATE_ACTIVE, :B2 ) AND CH.PROCESSED_DATE BETWEEN :B6 AND :B5
AND CH.DIRECT_SALESREP_ID = DHM.SECY_SALESREP_ID AND CH.TRX_TYPE IN ('INV',
'MAN') AND CH.ATTRIBUTE3 = :B4 MINUS SELECT ORD_DTLS.ORDER_NUMBER,
ORD_DTLS.TRX_TYPE FROM TABLE
(CAST(DSCN_RANKING_AND_QUARTILE_NEW.DSCN_BSD_ORDER_FETCH(:B8 )AS
DSCN_BSD_ORDER_DTL_TAB) ) ORD_DTLS


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 114 1.16 1.30 6 84 0 0
Fetch 602 12.17 122.84 45960 164447 0 52106
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 717 13.33 124.15 45966 164531 0 52106

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
52106 MINUS (cr=164993 pr=45988 pw=0 time=123367169 us)
80982 SORT UNIQUE (cr=164447 pr=45960 pw=0 time=117552135 us)
80984 FILTER (cr=164447 pr=45960 pw=0 time=204840413 us)
80984 TABLE ACCESS BY INDEX ROWID CN_COMMISSION_HEADERS_ALL (cr=164447 pr=45960 pw=0 time=204222389 us)
358548 NESTED LOOPS (cr=7738 pr=2309 pw=0 time=8503336 us)
742 VIEW (cr=3026 pr=886 pw=0 time=5255202 us)
742 CONNECT BY WITH FILTERING (cr=3026 pr=886 pw=0 time=5251304 us)
742 MAT_VIEW ACCESS BY INDEX ROWID DSCN_HIERARCHY_MV (cr=718 pr=199 pw=0 time=524742 us)
4886 INDEX RANGE SCAN DSCN_HIER_PRIM_SALESREP_IDX (cr=386 pr=0 pw=0 time=22369 us)(object id 732198)
0 NESTED LOOPS (cr=2308 pr=687 pw=0 time=4398039 us)
632 BUFFER SORT (cr=0 pr=0 pw=0 time=5954 us)
632 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=2564 us)
0 MAT_VIEW ACCESS BY INDEX ROWID DSCN_HIERARCHY_MV (cr=2308 pr=687 pw=0 time=4389231 us)
0 INDEX RANGE SCAN DSCN_HIER_PRIM_SALESREP_IDX (cr=2308 pr=687 pw=0 time=4383712 us)(object id 732198)
0 MAT_VIEW ACCESS BY INDEX ROWID DSCN_HIERARCHY_MV (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN DSCN_HIER_PRIM_SALESREP_IDX (cr=0 pr=0 pw=0 time=0 us)(object id 732198)
358548 INDEX RANGE SCAN CN_COMMISSION_HEADERS_N1 (cr=4712 pr=1423 pw=0 time=5140088 us)(object id 570298)
461040 SORT UNIQUE (cr=546 pr=28 pw=0 time=6533420 us)
461154 COLLECTION ITERATOR PICKLER FETCH DSCN_BSD_ORDER_FETCH (cr=546 pr=28 pw=0 time=7399290 us)

Appreciate ypur inputs in advance.

Regards
Nikhil B.
Tom Kyte

Followup  

November 29, 2007 - 8:50 am UTC

you are selecting

SELECT ORD_DTLS.ORDER_NUMBER,
ORD_DTLS.TRX_TYPE FROM TABLE
(CAST(DSCN_RANKING_AND_QUARTILE_NEW.DSCN_BSD_ORDER_FETCH(:B8 )AS
DSCN_BSD_ORDER_DTL_TAB) ) ORD_DTLS


that is selecting from a plsql function.

the only way to DO THAT is the collection iterator pickler fetch

and the only way to not do that is to NOT select from a plsql function.

catch 22

COLLECTION ITERATOR PICKLER FETCH

November 29, 2007 - 11:56 am UTC

Reviewer: Nikhil from India

So are we saying it gonna cause performance overhead ?.. Not sure , but a bit confused from your i/p.

Hope i am not bothering you too much.


Tom Kyte

Followup  

November 29, 2007 - 7:14 pm UTC

you either

a) need to select from a plsql function

or

b) you do not need to select from a plsql function


if you need A), then you need A)

it would be like saying:

select * from T;

plan: FULL SCAN TABLE T

reaction: full scans are expensive, get rid of it.

my reaction: well, apparently you need to do it - or if you don't, the only way to get rid of it is:

<this space left blank intentionally>

to not do it.


Ask DBAs if

a) they can quantify this severe performance overhead

or

b) they are just guessing because they never saw it before and they are assuming it must be something slow.

What is i/p?

November 30, 2007 - 1:24 am UTC

Reviewer: Not going to look it up from CT, USA

Sorry, but what did the last reviewer mean by your i/p?
Tom Kyte

Followup  

November 30, 2007 - 12:57 pm UTC

i believe they meant input.

annoying isn't it. I despise 'text speak' more and more every day.

tuning issue caused by table function

January 23, 2008 - 5:11 pm UTC

Reviewer: Scot Krause from Atlanta, GA USA

I read another thread where you said "use hints as a last resort". I 100% agree with that philosophy. However, I ran into a tuning issue that required a hint to solve and want to know if you think I could possibly solve this without a hint.

The issue:
- I have a jsp.
- The jsp calls a packaged procedure and passes in a set of id's in one concatenated varchar2 parameter called p_concat_id. The output parameter is a ref cursor.
- The procedure parses p_concat_id and loads the individual id's into an associate array (table of type number).
- The procedure executes a query that looks like this:
select <columns>
from x
where x.pk in (select * from table(parse_function(p_concat_id)));
- The pk column for table x has a unique index.
- Table x contains 3.2 million rows.
- The number of id's passed into the procedure is usually < 10. Hence, the unique index would be the best way to service the query.

The problem - the cost of the full table scan is lower than the cost of a unique scan, so the CBO goes with the full table scan. Hence, performance is very slow.

QUERY_PLAN - generated from the query above - 30-60 seconds to run
---------------------------------------------------
SELECT STATEMENT Cost = 13301
2.1 HASH JOIN SEMI
3.1 TABLE ACCESS FULL X
3.2 COLLECTION ITERATOR PICKLER FETCH PARSE_FUNCTION

QUERY_PLAN - query above with an index hint on the unique index - .25 seconds to run
-----------------------------------------------------------
SELECT STATEMENT Cost = 16412
2.1 NESTED LOOPS
3.1 SORT UNIQUE
4.1 COLLECTION ITERATOR PICKLER FETCH PARSE_FUNCTION
3.2 TABLE ACCESS BY INDEX ROWID X
4.1 INDEX UNIQUE SCAN X_U1 UNIQUE

The higher cost query with the unique index runs much faster! That makes sense, since usually < 10 id's are passed into the procedure.

I believe the reason the second explain plan has such a high cost is because the CBO wrongly assumed that the table function would return a large number of rows.

Thus, I rewrote the query with the cardinality hint to reflect the usual number of id's passed from the jsp:
select /*+ cardinality(tf 10 ) */ <columns>
from x, (select * from table(parse_function(p_concat_id))) tf
where x.pk = tf.column_value;

QUERY_PLAN - the cost is what I expected, the plan is correct, and performance is excellent
-------------------------------------------------------------
SELECT STATEMENT Cost = 37
2.1 NESTED LOOPS
3.1 COLLECTION ITERATOR PICKLER FETCH PARSE_FUNCTION
3.2 TABLE ACCESS BY INDEX ROWID X
4.1 INDEX UNIQUE SCAN X_U1 UNIQUE

Why did the CBO assume that the table function will return a large number of rows? Can I fix this problem without using either the index hint or the cardinality hint?

Thanks!

Tom Kyte

Followup  

January 23, 2008 - 8:28 pm UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7038986332061#14621915372791

the optimizer defaults table sources without statistics, a plsql function hasn't any - the default number of rows is a function of your default block size. 8,000 plus would be the default for an 8k block.

The PICKLER FETCH problem on the Explain Plan

August 12, 2013 - 4:47 am UTC

Reviewer: Shauna Johnson from Vancouver, WA

Hi Ask Tom Folks,

I've been a student of a program for Master's of Database Technologies who is now graduating. I've often looked to your site to answer challenging questions, and it is an invaluable resource so thank you!

I was searching how to solve the same problem that one of your other readers encountered on the PICKLER FETCH problem with Explain Plans.

They asked "can you please help me to know how to avoid this "COLLECTION ITERATOR PICKLER FETCH" and get the proper explain plan?

I looked through the threads and I'm sure the postings are relevant, but I felt it never answered the root of the issue. We have learned over the course of our program that if PICKLER FETCH comes up, autotrace is enabled. When I did a show autot, I found that it was set to autotrace on the explain plan. It was enabled still after previous executions of it. Once I turned it off again, the PICKLER FETCH error disappeared and the explain plan worked perfectly. In the labs I'm doing, it's one or the other - either use a trace or an Explain Plan.
I know this is an old post, but I found my way here by researching the issue so I thought I'd add this in case it comes up again for someone.

Thanks,
Shauna
Tom Kyte

Followup  

August 12, 2013 - 1:50 pm UTC

"They asked "can you please help me to know how to avoid this "COLLECTION
ITERATOR PICKLER FETCH" and get the proper explain plan? "


i don't understand that question at all???????

what is the "proper" plan they are requestings?????


a collection iterator pickler fetch is the NECESSARY and unavoidable row source you'll get when you query from a plsql function.


... We have learned over the course of
our program that if PICKLER FETCH comes up, autotrace is enabled. ...


false.



the pickler fetch rowsource comes from the fact that you are looking at the plan of a query that fetched from a plsql rowsource.


for example in the original question - the poster had autotrace on and then ran the query :

select * from table( DBMS_XPLAN.DISPLAY('plan_table','sam','all') );


the *explain plan* for that query that poster just ran necessarily includes a pickler fetch - since that is the only way for the dbms_xplan call to be performaned in SQL!!!!!



pickler fetch is *never an error*. It is a sign that the plan you are looking at included a plsql function as a row source.


I think you are misunderstanding something big time here.


give me the step by steps you are doing - as a cut and paste from sqlplus - and I'll explain what you are seeing, why you are seeing it, and why it is not an error but the expected, normal requested behavior.


pickler fetch and nested loops

May 30, 2014 - 5:01 pm UTC

Reviewer: Javier from Reston, VA

Hi Tom,

Just as the original post, I'm looking to avoid the pickler fetch in a MUCH DIFFERENT WAY. Based on our execution plans and trace files, we see this operation when querying access restricted tables. This, of course, is expected and is done as a hash join against the results returned by the function. The function populates a set of id's based on what roles the user has and pushes those predicates against object_1.

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5331 | 786K| 1358 (21)| 00:00:03 |
|* 1 | HASH JOIN RIGHT SEMI | | 5331 | 786K| 1358 (21)| 00:00:03 |
| 2 | COLLECTION ITERATOR PICKLER FETCH| GET_PERMISSIONS | 16360 | 32720 | 20 (10)| 00:00:01 |
| 3 | TABLE ACCESS FULL | OBJECT_1 | 634K| 90M| 1302 (19)| 00:00:03 |
-------------------------------------------------------------------------------------------------------

The problem we're having is with reports where the optimizer finds it is more efficient to perform a NESTED LOOP between one object and the access restricted object. The function is then called as many times as the nested loop operation runs. Where a report might take a couple seconds, it will take minutes to complete because the function is called thousands of times. These reports are very long and complex but below is a sample where I forced the nested loop (query takes too long without additional predicate).

SELECT /*+ USE_NL(obj1 obj2)*/
ob1.id
FROM object_1 ob1, object_2 ob2
WHERE ob1.obj2_id = ob2.id(+)AND obj1.obj_name IN ('SOME_VALUE');

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3620 | 190K| 137 (2)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 3620 | 190K| 137 (2)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | OBJECT_1 | 3620 | 123K| 135 (1)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | OBJECT1_FK_I | 3620 | | 22 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | OBJECT_2 | 1 | 19 | 1 (0)| 00:00:01 |
| 5 | NESTED LOOPS SEMI | | 1 | 19 | 21 (10)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | OBJECT_2 | 1 | 17 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | OBJECT_2_PK | 1 | | 1 (0)| 00:00:01 |
|* 8 | COLLECTION ITERATOR PICKLER FETCH| GET_PERMISSIONS | 137 | 274 | 20 (10)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

We have some reports where the function is called over 100,000 times and can take an additional 12 minutes to complete. I am not the developer of these reports but I am doing my best to help them tune it. I can force them to use hash joins and the query time is excellent. There are, unfortunately, thousands of these reports with varying degrees of performance degradation because of this function and developers are wondering if there are other alternatives. Is there a way to tell the optimizer to use hash joins when dealing with this funciton? Thoughts greatly appreciated. Thanks.

COLLECTION ITERATOR PICKLER FETCH

September 04, 2014 - 5:53 am UTC

Reviewer: Vikas Chopkar from India

Hi ,
Please guide me Whether table function will improve performance or degrade the performance. it should be used in OTLP or OLAP. As from some website i came to know that Table function is good for OLTP system and Push the data as soon as it get single row to Frontend.
Please help me in clearing the confusion.

Regards
Vikas Chopkar

Collections vs Global Temporary Table

July 06, 2020 - 10:51 am UTC

Reviewer: Kartheek Katragadda from India

I have a similar situation where my collection has 50K to 100K records in some of the scenarios and I have some sql like
select * from table(collection_name) where col1= :1;


When the collection data is few hundreds it is performing well but as collection size grows it is very slow. We have PGA target size is 10GB.

Can global temporary table and index on col1 performs better than collection?

Thanks
Chris Saxon

Followup  

July 06, 2020 - 2:30 pm UTC

Possibly - have you tried it?

More to Explore

DBMS_XPLAN

More on PL/SQL routine DBMS_XPLAN here