Oracle Tips and Tricks — David Fitzjarrell

December 10, 2009

In A Pickle

Filed under: General — dfitzjarrell @ 14:52

It’s entirely possible that a query execution plan includes the following peculiar entry:


COLLECTION ITERATOR (PICKLER FETCH)

Intriguing, at the very least. What, exactly, IS a pickler fetch? To answer that we must answer another question:


What is pickled data?

so let’s start there and see where that leads us.

Packed object data cannot be serialized directly, it requires some manipulation. This manipulation involves converting an object-oriented data structure into a byte stream so it can be navigated by iterative methods. Whew. What that means is that by ‘pickling’ we can achieve a more ‘palatable’ data stream presented to the receiving process or interface. As an example let’s return the output from the dbms_xplan.display function without any additional ‘magic’:

SQL> select dbms_XPLAN.DISPLAY FROM DUAL;

DISPLAY(PLAN_TABLE_OUTPUT)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DBMS_XPLAN_TYPE_TABLE(DBMS_XPLAN_TYPE(' '), DBMS_XPLAN_TYPE('-------------------------------------------------------------------------------'), DBMS_XPLAN_TYPE('| Id  | Operation
|  Name        | Rows  | Bytes | Cost  |'), DBMS_XPLAN_TYPE('-------------------------------------------------------------------------------'), DBMS_XPLAN_TYPE('|   0 | SELECT STATEMENT
|              |  8169 |  1412K|   102 |'), DBMS_XPLAN_TYPE('|   1 |  VIEW                          | DBA_OBJECTS  |  8169 |  1412K|   102 |'), DBMS_XPLAN_TYPE('|   2 |   UNION-ALL
|              |       |       |       |'), DBMS_XPLAN_TYPE('|*  3 |    FILTER                      |              |       |       |       |'), DBMS_XPLAN_TYPE('|   4 |     NESTED LOOPS
|              |     1 |   235 |    18 |'), DBMS_XPLAN_TYPE('|*  5 |      TABLE ACCESS FULL         | OBJ$         |     1 |   205 |    17 |'), DBMS_XPLAN_TYPE('|   6 |      TABLE ACCESS CLUSTER
| USER$        |     1 |    30 |     1 |'), DBMS_XPLAN_TYPE('|*  7 |       INDEX UNIQUE SCAN        | I_USER#      |     1 |       |       |'), DBMS_XPLAN_TYPE('|*  8 |     TABLE ACCESS BY INDEX ROWID
| IND$         |     1 |    26 |     2 |'), DBMS_XPLAN_TYPE('|*  9 |      INDEX UNIQUE SCAN         | I_IND1       |     1 |       |     1 |'), DBMS_XPLAN_TYPE('|  10 |    NESTED LOOPS
|              |  8168 |   941K|    84 |'), DBMS_XPLAN_TYPE('|  11 |     TABLE ACCESS FULL          | LINK$        |    82 |  7216 |     2 |'), DBMS_XPLAN_TYPE('|  12 |     TABLE ACCESS CLUSTER
| USER$        |   100 |  3000 |     1 |'), DBMS_XPLAN_TYPE('|* 13 |      INDEX UNIQUE SCAN         | I_USER#      |     1 |       |       |'), DBMS_XPLAN_TYPE('---------------------------------------
----------------------------------------'), DBMS_XPLAN_TYPE(' '), DBMS_XPLAN_TYPE('Predicate Information (identified by operation id):'), DBMS_XPLAN_TYPE('---------------------------------------------
------'), DBMS_XPLAN_TYPE(' '), DBMS_XPLAN_TYPE('   3 - filter("SYS_ALIAS_1"."TYPE#"1 AND "SYS_ALIAS_1"."TYPE#"10 OR '), DBMS_XPLAN_TYPE('              "SYS_ALIAS_1"."TYPE#"=1 AND  (SELECT /*+ */
1 FROM "SYS"."IND$" "I" WHERE '), DBMS_XPLAN_TYPE('              "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR '), DBMS_XPLAN_TYPE('              "I"."TYPE#"=4 OR "I"."TYPE#"=
6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)'), DBMS_XPLAN_TYPE('   5 - filter("SYS_ALIAS_1"."LINKNAME" IS NULL AND '), DBMS_XPLAN_TYPE('              "SYS_ALIAS_1"."NAME"''_NEXT_OBJECT'' AND '), DBMS_X
PLAN_TYPE('              "SYS_ALIAS_1"."NAME"''_default_auditing_options_'')'), DBMS_XPLAN_TYPE('   7 - access("SYS_ALIAS_1"."OWNER#"="U"."USER#")'), DBMS_XPLAN_TYPE('   8 - filter("I"."TYPE#"=1 OR
"I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 '), DBMS_XPLAN_TYPE('              OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)'), DBMS_XPLAN_TYPE('   9 - access("I"."OBJ#"=:B1)'), DBMS_XPLAN_T
YPE('  13 - access("L"."OWNER#"="U"."USER#")'), DBMS_XPLAN_TYPE(' '), DBMS_XPLAN_TYPE('Note: cpu costing is off'))


SQL>

We can read the result, but it doesn’t look much like the usually formatted PLAN_TABLE output. It’s just … there … in all of its unformatted glory and liberally sprinkled with “DBMS_XPLAN_TYPE” strings, which makes it rather confusing to interpret. Let’s use the TABLE() function and see if it provides something more recognizable:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------
| Id  | Operation                      |  Name        | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |  8169 |  1412K|   102 |
|   1 |  VIEW                          | DBA_OBJECTS  |  8169 |  1412K|   102 |
|   2 |   UNION-ALL                    |              |       |       |       |
|*  3 |    FILTER                      |              |       |       |       |
|   4 |     NESTED LOOPS               |              |     1 |   235 |    18 |
|*  5 |      TABLE ACCESS FULL         | OBJ$         |     1 |   205 |    17 |
|   6 |      TABLE ACCESS CLUSTER      | USER$        |     1 |    30 |     1 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|*  7 |       INDEX UNIQUE SCAN        | I_USER#      |     1 |       |       |
|*  8 |     TABLE ACCESS BY INDEX ROWID| IND$         |     1 |    26 |     2 |
|*  9 |      INDEX UNIQUE SCAN         | I_IND1       |     1 |       |     1 |
|  10 |    NESTED LOOPS                |              |  8168 |   941K|    84 |
|  11 |     TABLE ACCESS FULL          | LINK$        |    82 |  7216 |     2 |
|  12 |     TABLE ACCESS CLUSTER       | USER$        |   100 |  3000 |     1 |
|* 13 |      INDEX UNIQUE SCAN         | I_USER#      |     1 |       |       |
-------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

   3 - filter("SYS_ALIAS_1"."TYPE#"1 AND "SYS_ALIAS_1"."TYPE#"10 OR
              "SYS_ALIAS_1"."TYPE#"=1 AND  (SELECT /*+ */ 1 FROM "SYS"."IND$" "I" WHERE
              "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR
              "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
   5 - filter("SYS_ALIAS_1"."LINKNAME" IS NULL AND
              "SYS_ALIAS_1"."NAME"'_NEXT_OBJECT' AND
              "SYS_ALIAS_1"."NAME"'_default_auditing_options_')
   7 - access("SYS_ALIAS_1"."OWNER#"="U"."USER#")
   8 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4
              OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
   9 - access("I"."OBJ#"=:B1)
  13 - access("L"."OWNER#"="U"."USER#")

Note: cpu costing is off

37 rows selected.

SQL>

Wow, it does. The TABLE() function “pickles”, or converts, the object data into a byte stream and formats the output. The TABLE() function introduces the “COLLECTION ITERATOR (PICKLER FETCH)” step as shown in the second execution plan (for the “select * from table(dbms_xplan.display)” query) generated by autotrace:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------
| Id  | Operation                      |  Name        | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |  8169 |  1412K|   102 |
|   1 |  VIEW                          | DBA_OBJECTS  |  8169 |  1412K|   102 |
|   2 |   UNION-ALL                    |              |       |       |       |
|*  3 |    FILTER                      |              |       |       |       |
|   4 |     NESTED LOOPS               |              |     1 |   235 |    18 |
|*  5 |      TABLE ACCESS FULL         | OBJ$         |     1 |   205 |    17 |
|   6 |      TABLE ACCESS CLUSTER      | USER$        |     1 |    30 |     1 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
|*  7 |       INDEX UNIQUE SCAN        | I_USER#      |     1 |       |       |
|*  8 |     TABLE ACCESS BY INDEX ROWID| IND$         |     1 |    26 |     2 |
|*  9 |      INDEX UNIQUE SCAN         | I_IND1       |     1 |       |     1 |
|  10 |    NESTED LOOPS                |              |  8168 |   941K|    84 |
|  11 |     TABLE ACCESS FULL          | LINK$        |    82 |  7216 |     2 |
|  12 |     TABLE ACCESS CLUSTER       | USER$        |   100 |  3000 |     1 |
|* 13 |      INDEX UNIQUE SCAN         | I_USER#      |     1 |       |       |
-------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------

   3 - filter("SYS_ALIAS_1"."TYPE#"1 AND "SYS_ALIAS_1"."TYPE#"10 OR
              "SYS_ALIAS_1"."TYPE#"=1 AND  (SELECT /*+ */ 1 FROM "SYS"."IND$" "I" WHERE
              "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR
              "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
   5 - filter("SYS_ALIAS_1"."LINKNAME" IS NULL AND
              "SYS_ALIAS_1"."NAME"'_NEXT_OBJECT' AND
              "SYS_ALIAS_1"."NAME"'_default_auditing_options_')
   7 - access("SYS_ALIAS_1"."OWNER#"="U"."USER#")
   8 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4
              OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------
   9 - access("I"."OBJ#"=:B1)
  13 - access("L"."OWNER#"="U"."USER#")

Note: cpu costing is off

37 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=17 Card=8168 Bytes=16336)
   1    0   COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'




Statistics
----------------------------------------------------------
         23  recursive calls
          0  db block gets
         51  consistent gets
          0  physical reads
          0  redo size
       3384  bytes sent via SQL*Net to client
        678  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
         37  rows processed

SQL>

SQL>

Selecting from a function usually involves a picker fetch (introduced by the TABLE() operator) to format the output into a ‘user-friendly’ form, and, usually, it doesn’t introduce massive amounts of overhead with respect to query processing (the conversion and iteration are both efficient processes). I have rarely seen such a step add significantly to the response time (both queries shown above ran in about the same time). Yes, there may be situations where each added step degrades the response time but if that happens investigate the situation fully for you may find your assumptions in error.

It’s not so bad to be ‘in a pickle’ when it comes to queries.

Create a free website or blog at WordPress.com.