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.
