執行計畫出現COLLECTION ITERATOR PICKLER FETCH
今天在使用explain plan for查看sql執行計畫時，出現了如下奇怪的執行計畫
- SQL> explain plan for select * from test where object_id = 100;
- Explained.
- SQL> select * from table(dbms_xplan.display());
- 14 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
- 12 db block gets
- 55 consistent gets
- 0 physical reads
- 0 redo size
- 1414 bytes sent via SQL*Net to client
- 519 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 14 rows processed
經過排查，發現是在使用explain plan for之前執行過set autotrace on，關閉autotrace後執行計畫就顯示正常，一點小知識，記錄以供參考。
