Get COLLECTION ITERATOR PICKLER FETCH during execution plan in Oracle

Get COLLECTION ITERATOR PICKLER FETCH during execution plan in Oracle

When i tried to fetch the execution plan for query. I got the execution plan having output “COLLECTION ITERATOR PICKLER FETCH”
When i checked it showing my real execution plan above it.
Explaination: A pickler fetch, the dbms_pickler package is called to convert the array data type to a byte stream that can be displayed.

Cause:
This execution plan produces when we SET AUTOTRACE ON or some other command in the same session for fetching the execution plan of sql query.

Example of showing the Collection Iterator Pickler fetch

-- Set autotrace on
SQL>SET AUTOTRACE ON

-- then decide to fetch plan with explain plan
SQL> explain plan for select * from hr.departments;
Explained.

--Execute the following for getting plan
SQL> @?\rdbms\admin\utlxpls.sql

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 4167016233
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 567 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 567 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
8 rows selected.

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 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1159 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8 rows processed

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.