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
Smart way of Technology 