Which one is better over the above? This question has been the one of the latest topic in our performance team when using a viable solution for varying In-List management.
Background:
We all know Oracle's shared pool is sensitive to the way sql is written - whitespace, uppercase/lowercase difference, number of bind variables in IN-Clause.
To avoid shared pool being bombarded with thousands of sqls of same type, we currently are using buckets for IN-clause.
Ex:
select * from emp where empno in (:B1,:B2,:B3,......,:B16) -- this is bucket size 16.
So, if user passes only one bind value, the rest of bind values will go as null.
And if user passes two bind values, the rest of bind values will go as null.
This way we control, the number of sqls which needs to be maintained in shared pool.
We implement different size of buckets: 16, 32, 64, 128, 256, 512, 768, 1000
Upon searching i came across Tomy kyte's solution mentioned here (http://tkyte.blogspot.com/2006/06/varying-in-lists.html).
Initially i used his 8i solution and results were somehow unexpected:
Here is the testcase:
SQL> create or replace type stringTableType as table of varchar2(4000);
2 /
Type created.
SQL> SQL> 2 3 4
5 v_str long default p_str || ',';
6
7 v_n number;
8
9 v_data stringTableType := stringTableType();
10
11 begin
12
13 loop
14
15 v_n := instr( v_str, ',' );
16
17 exit when (nvl(v_n,0) = 0);
18
19 v_data.extend;
20
21 v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
22
23 v_str := substr( v_str, v_n+1 );
24
25 end loop;
26
27 return v_data;
28
29 end;
30
31 /
Function created.
SQL> create table test as select rownum r from dual connect by level<=100000;
Table created.
SQL> create index test_ux on test(r);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true)
PL/SQL procedure successfully completed.
Now running a NORMAL in-list with 10 inlist values:
SQL> declare
a number;
begin
for i in 1..10000 loop
select count(*) into a from test where r in (1,2,3,4,5,6,7,8,9,10);
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.23
Note the "Elapsed" time. It is 1.5 secs.
Now using the Collection method:
SQL> SQL> declare
a number;
begin
for i in 1..10000 loop
select count(*) into a from test
where r in ( select /*+ cardinality(10) */ * from THE ( select cast( convert2Table( '1,2,3,4,5,6,7,8,9,10' ) as stringTableType ) from dual ) );
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:22.41
Note the "Elapsed" time is almost 15x more than normal inlist.
This was a bit surprising.
I did the same test under 10046 + tkprof eyes.
SELECT COUNT(*)
FROM
TEST WHERE R IN (1,2,3,4,5,6,7,8,9,10)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.46 0.42 0 0 0 0
Fetch 10000 1.99 2.00 0 200000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 2.46 2.43 0 200000 0 10000
Rows Row Source Operation
------- ---------------------------------------------------
10000 SORT AGGREGATE (cr=200000 pr=0 pw=0 time=2117708 us)
100000 INLIST ITERATOR (cr=200000 pr=0 pw=0 time=2271474 us)
100000 INDEX RANGE SCAN TEST_UX (cr=200000 pr=0 pw=0 time=1354629 us)(object id 60859168)
SELECT COUNT(*)
FROM
TEST WHERE R IN ( SELECT /*+ cardinality(10) */ * FROM THE ( SELECT CAST(
CONVERT2TABLE( '1,2,3,4,5,6,7,8,9,10' ) AS STRINGTABLETYPE ) FROM DUAL ) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 48 0 0
Execute 10000 5.40 5.42 0 0 0 0
Fetch 10000 9.57 9.65 0 120000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 14.99 15.09 0 120048 0 10000
Rows Row Source Operation
------- ---------------------------------------------------
10000 SORT AGGREGATE (cr=120000 pr=0 pw=0 time=13856299 us)
100000 NESTED LOOPS (cr=120000 pr=0 pw=0 time=16447839 us)
100000 VIEW VW_NSO_1 (cr=0 pr=0 pw=0 time=11596021 us)
100000 HASH UNIQUE (cr=0 pr=0 pw=0 time=11085381 us)
100000 COLLECTION ITERATOR PICKLER FETCH CONVERT2TABLE (cr=0 pr=0 pw=0 time=3527457 us)
20000 FAST DUAL (cr=0 pr=0 pw=0 time=151992 us)
100000 INDEX RANGE SCAN TEST_UX (cr=120000 pr=0 pw=0 time=2182846 us)(object id 60859168)
The difference between 2 is sea. Although the later did almost 1/2 LIO yet the total time spent on CPU was huge.
It seems to me that NL over INLIST ITERATOR is not a good choice ATLEAST for 10 values.
Now, I tried with 1000 values -- i didnt capture the screen shots. But here is what i saw.
- for loop that runs 1000 times -- the "R in (1,2,3...1000)" took 54 secs. And using the Tom Kyte's suggested methood - it took 16 minutes.
Am i missing something?
