Pickler Fetch

SQL> create user rdba identified by rdba;

User created.

SQL> grant dba to rdba;

Grant succeeded.

SQL>

SQL> connect rdba/rdba
Connected.
SQL>

1 create table big_all_objects
2 as
3 select * from all_objects
4 union all
5 select * from all_objects
6 union all
7 select * from all_objects
8 union all
9 select * from all_objects
10 union all
11 select * from all_objects
12 union all
13* select * from all_objects
SQL> /

Table created.

Elapsed: 00:01:04.67
SQL> select count(1) from big_all_objects;

COUNT(1)
----------
141828

Elapsed: 00:00:00.50
SQL>

1* select /* Without Pickler fetch */ object_id from big_all_objects where object_id in (3,30,50,100,150,200)

OBJECT_ID
----------
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150

30 rows selected.

Elapsed: 00:00:00.88

Let's prepare for pickler fetch:
SQL> create or replace type inListTable as table of number
2 /

Type created.

SQL> create or replace function str2tbl( p_str in varchar2 ) return inListTable
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data inListTable := inListTable();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15 end;
16 /


Function created.

SQL> SQL>

1 select /* with pickler fetch */ object_id
2 from big_all_objects
3 WHERE object_id IN (select *
4* from table ( cast ( str2tbl('3,30,50,100,150,200') as inListTable)))
SQL> /

OBJECT_ID
----------
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150

30 rows selected.

Elapsed: 00:00:00.81
SQL>


Both of these take about the same time in running in the 2nd, 3rd iteration onwards.

Let's see what v$sqlarea has to say about these two.

First time after running query with pickler fetch..

EXECUTIONS HASH_VALUE SQL_TEXT                       SORTS      CPU_TIME   BUFFER_GETS DISK_READS PARSE_CALLS LOADS
---------- ---------- ------------------------------ ---------- ---------- ----------- ---------- ----------- ----------
     1 3492981496 with pickler fetch                      0     1370000    11953        9852      1           4

First time after running query after pickler fetch.

EXECUTIONS HASH_VALUE SQL_TEXT                       SORTS      CPU_TIME   BUFFER_GETS DISK_READS PARSE_CALLS LOADS
---------- ---------- ------------------------------ ---------- ---------- ----------- ---------- ----------- ----------
     1 3532977385 without pickler fetch                   0     530000     9174        9562       1           2

Here are the results after running the above queries 10 times each.

EXECUTIONS   HASH_VALUE SQL_TEXT                        SORTS       CPU_TIME  BUFFER_GETS DISK_READS PARSE_CALLS         LOADS
----------   ---------- ------------------------------  ----------  --------  ----------- ---------- -----------         ----------
10           1169982563 without pf                      0          4970000    90745       90610      10                  1

10           582411935 with pickler fetch               0          5270000    93657       90610      10                  4

From the above analysis we can conclude that after the procedure stays in shared pool and/or pinned, the execution time and resources used to run the SQL is the same. The big benefit we get is to have a non-fragmented shared pool. This is applicable when the number of arguments in an in list is random.