(I)-Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part1 (II)-Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part2
In this Blog, we will demonstrate the performance difference of MEMBER and IN Comparison Conditions, and explore their complexity.
In next Blog: (II)-Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part2, we will demonstrate the performance difference of MEMBER and IN Comparison Conditions for nested table column.
Note: Tested in Oracle 19.27
1. MEMBER vs. IN Performance Difference
We create two nested tables of number, and run query on them with MEMBER and IN Conditions respectively:
---------------- test Number MEMBER vs. IN Comparison Conditions Performance ----------------
drop type t_num_tab;
create or replace noneditionable type t_num_tab is table of number;
/
create or replace procedure test_NUM_MEMBER_vs_IN_Conditions (p_size_1 number, p_size_2 number, p_sel varchar2 := 'A') as
l_t_num_tab_1 t_num_tab := t_num_tab();
l_t_num_tab_2 t_num_tab := t_num_tab();
l_start_time number;
l_ret_cnt number;
begin
select level bulk collect into l_t_num_tab_1 from dual connect by level <= p_size_1;
select level bulk collect into l_t_num_tab_2 from dual connect by level <= p_size_2;
dbms_output.put_line('l_t_num_tab_1.count = '||cardinality(l_t_num_tab_1));
dbms_output.put_line('l_t_num_tab_2.count = '||l_t_num_tab_2.count);
if p_sel in ('A', 'M') then
--================= MEMBER Comparison =================
l_start_time := dbms_utility.get_time;
select /*+ Test_NUM_MEMBER */ count(*) into l_ret_cnt
from table(cast (l_t_num_tab_1 as t_num_tab)) t1
where t1.column_value member of cast(l_t_num_tab_2 as t_num_tab);
dbms_output.put_line('=============== MEMBER Condition ===============');
dbms_output.put_line('Elapsed(centi) = '||(dbms_utility.get_time - l_start_time));
dbms_output.put_line('l_ret_cnt = '||l_ret_cnt);
end if;
if p_sel in ('A', 'I') then
--================= IN Comparison =================
l_start_time := dbms_utility.get_time;
select /*+ Test_NUM_IN */ count(*) into l_ret_cnt
from table(cast (l_t_num_tab_1 as t_num_tab)) t1
where t1.column_value in (select column_value from table(cast(l_t_num_tab_2 as t_num_tab)));
dbms_output.put_line('=============== IN Condition ===============');
dbms_output.put_line('Elapsed(centi) = '||(dbms_utility.get_time - l_start_time));
dbms_output.put_line('l_ret_cnt = '||l_ret_cnt);
end if;
end;
/
SQL > exec test_NUM_MEMBER_vs_IN_Conditions(100000, 1000);
l_t_num_tab_1.count = 100000
l_t_num_tab_2.count = 1000
=============== MEMBER Condition ===============
Elapsed(centi) = 226
l_ret_cnt = 1000
=============== IN Condition ===============
Elapsed(centi) = 2
l_ret_cnt = 1000
SELECT /*+ Test_NUM_MEMBER */ COUNT(*) FROM TABLE(CAST (:B1 AS T_NUM_TAB)) T1 WHERE T1.COLUMN_VALUE MEMBER OF CAST(:B2 AS T_NUM_TAB);
SQL_ID 9v3d6bbpjs0x2, child number 0 Plan hash value: 3309076612
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | COLLECTION ITERATOR PICKLER FETCH| | 50 | 100 | 30 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(VALUE(KOKBF$)MEMBER OFCAST(:B2 AS "T_NUM_TAB") )
SELECT /*+ Test_NUM_IN */ COUNT(*) FROM TABLE(CAST (:B1 AS T_NUM_TAB)) T1 WHERE T1.COLUMN_VALUE IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:B2 AS T_NUM_TAB)));
SQL_ID 2z2buk4wrs0gs, child number 0 Plan hash value: 4146508332
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 60 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | HASH JOIN RIGHT SEMI | | 10 | 40 | 60 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| | 300 | 600 | 30 (0)| 00:00:01 |
| 4 | COLLECTION ITERATOR PICKLER FETCH| | 1000 | 2000 | 30 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(VALUE(KOKBF$)=VALUE(KOKBF$))
For MEMBER, count() = 10,094,950 is about (l_t_obj_tab_1.count * l_t_obj_tab_2.count), which is quadratic performance.
For IN, count() = 100102 is about (l_t_obj_tab_1.count + l_t_obj_tab_2.count), which is linear performance.
--------------------- MEMBER Comparison ----------------------
SQL > exec test_NUM_MEMBE,R_vs_IN_Conditions(100000, 100, 'M');
l_t_num_tab_1.count = 100000
l_t_num_tab_2.count = 100
=============== MEMBER Condition ===============
Elapsed(centi) = 1199
l_ret_cnt = 100
$ > bpftrace -e 'uprobe:/orabin/app/oracle/product/19.27.0.0.250415-212/bin/oracle:kopi2csaccess+2 /pid == 1129837/ {@[ustack(2)] = count();}'
@[
kopi2csaccess+2
qerocFetch+201
]: 100001
@[
kopi2csaccess+2
expeal+59
]: 10094950
--------------------- IN Comparison --------------------------
SQL > exec test_NUM_MEMBER_vs_IN_Conditions(100000, 100, 'I');
l_t_num_tab_1.count = 100000
l_t_num_tab_2.count = 100
=============== IN Condition ===============
Elapsed(centi) = 13
l_ret_cnt = 100
$ > bpftrace -e 'uprobe:/orabin/app/oracle/product/19.27.0.0.250415-212/bin/oracle:kopi2csaccess+2 /pid == 1129837/ {@[ustack(2)] = count();}'
@[
kopi2csaccess+2
qerocFetch+201
]: 100102
2. MEMBER vs. IN complexity
We create nested table of objects with map member function to record number of Comparisons in MEMBER and IN queries in order to evaluate the complexity of applied algorithm, similar to the approaches of previous Blog:
Performance of Oracle Object Collection Comparisons - Part1
Performance of Oracle Object Collection Comparisons - Part2
---------------- test Object MEMBER vs. IN Comparison Conditions Performance ----------------
create or replace package cmp_counter as
p_cnt number := 0;
end;
/
create or replace package body helper as
procedure format(p_name varchar2, p_value number) as
begin
dbms_output.put_line(rpad(p_name, 40) ||' = '|| lpad(p_value, 10));
end format;
end helper;
/
drop type t_obj_tab force;
drop type t_obj force;
create or replace type t_obj as object (
p_num number,
map member function comp return integer);
/
create or replace type body t_obj as
map member function comp return integer is
begin
cmp_counter.p_cnt := cmp_counter.p_cnt + 1;
return p_num;
end;
end;
/
create or replace type t_obj_tab as table of t_obj;
/
create or replace procedure test_OBJ_MEMBER_vs_IN_Conditions (p_size_1 number, p_size_2 number, p_sel varchar2 := 'A') as
l_t_obj_tab_1 t_obj_tab := t_obj_tab();
l_t_obj_tab_2 t_obj_tab := t_obj_tab();
l_start_time number;
l_ret_cnt number;
begin
select cast(collect(t_obj(level)) as t_obj_tab) into l_t_obj_tab_1 from dual connect by level <= p_size_1;
select cast(collect(t_obj(level)) as t_obj_tab) into l_t_obj_tab_2 from dual connect by level <= p_size_2;
dbms_output.put_line('l_t_obj_tab_1.count = '||cardinality(l_t_obj_tab_1));
dbms_output.put_line('l_t_obj_tab_2.count = '||l_t_obj_tab_2.count);
if p_sel in ('A', 'M') then
--================= MEMBER Comparison =================
cmp_counter.p_cnt := 0;
l_start_time := dbms_utility.get_time;
select /*+ Test_OBJ_MEMBER */ count(*) into l_ret_cnt
from table(cast (l_t_obj_tab_1 as t_obj_tab)) t1
where t_obj(t1.p_num) member cast(l_t_obj_tab_2 as t_obj_tab);
dbms_output.put_line('=============== MEMBER Condition ===============');
dbms_output.put_line('Elapsed(centi) = '||(dbms_utility.get_time - l_start_time));
dbms_output.put_line('l_ret_cnt = '||l_ret_cnt);
dbms_output.put_line('Number of Comparisons = '||cmp_counter.p_cnt);
end if;
if p_sel in ('A', 'I') then
--================= IN Operator =================
cmp_counter.p_cnt := 0;
l_start_time := dbms_utility.get_time;
select /*+ Test_OBJ_IN */ count(*) into l_ret_cnt
from table(cast (l_t_obj_tab_1 as t_obj_tab)) t1
where t_obj(t1.p_num) in (select t_obj(t2.p_num) from table(cast(l_t_obj_tab_2 as t_obj_tab)) t2);
dbms_output.put_line('=============== IN Condition ===============');
dbms_output.put_line('Elapsed(centi) = '||(dbms_utility.get_time - l_start_time));
dbms_output.put_line('l_ret_cnt = '||l_ret_cnt);
dbms_output.put_line('Number of Comparisons = '||cmp_counter.p_cnt);
end if;
end;
/
SQL> exec test_OBJ_MEMBER_vs_IN_Conditions(10000, 1000);
l_t_obj_tab_1.count = 10000
l_t_obj_tab_2.count = 1000
=============== MEMBER Condition ===============
Elapsed(centi) = 4503
l_ret_cnt = 1000
Number of Comparisons = 19001000
=============== IN Condition ===============
Elapsed(centi) = 4
l_ret_cnt = 1000
Number of Comparisons = 11000
For MEMBER, the Number of Comparisons is about (l_t_obj_tab_1.count * l_t_obj_tab_2.count), which is quadratic performance.
For IN, the Number of Comparisons is about (l_t_obj_tab_1.count + l_t_obj_tab_2.count), which is linear performance.
Here both xplan:
SELECT /*+ Test_NUM_MEMBER */ COUNT(*) FROM TABLE(CAST (:B1 AS T_NUM_TAB)) T1 WHERE T1.COLUMN_VALUE MEMBER OF CAST(:B2 AS T_NUM_TAB);
SQL_ID 9v3d6bbpjs0x2, child number 0 Plan hash value: 3309076612
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | COLLECTION ITERATOR PICKLER FETCH| | 50 | 100 | 30 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(VALUE(KOKBF$)MEMBER OFCAST(:B2 AS "T_NUM_TAB") )
SELECT /*+ Test_NUM_IN */ COUNT(*) FROM TABLE(CAST (:B1 AS T_NUM_TAB)) T1 WHERE T1.COLUMN_VALUE IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:B2 AS T_NUM_TAB)));
SQL_ID 2z2buk4wrs0gs, child number 0 Plan hash value: 4146508332
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 60 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | HASH JOIN RIGHT SEMI | | 10 | 40 | 60 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| | 300 | 600 | 30 (0)| 00:00:01 |
| 4 | COLLECTION ITERATOR PICKLER FETCH| | 1000 | 2000 | 30 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(VALUE(KOKBF$)=VALUE(KOKBF$))
3. bpftrace on PICKLER FETCH Calls
The Oracle subroutine for PICKLER FETCH is implemented by "kodpunp" (kernel objects data manager (pickler) unpickle an image into an object). We can use bpftrace to count the number of such calls.
3.1 MEMBER Comparison
The bpftrace count on kodpunp (from kgmtimob): 19001000 matches Sqlplus(pid == 1128134) output: Number of Comparisons = 19001000.
SQL > exec test_OBJ_MEMBER_vs_IN_Conditions(10000, 1000, 'M');
l_t_obj_tab_1.count = 10000
l_t_obj_tab_2.count = 1000
=============== MEMBER Condition ===============
Elapsed(centi) = 7526
l_ret_cnt = 1000
Number of Comparisons = 19001000
Elapsed: 00:01:15.28
$ > bpftrace -e 'uprobe:/orabin/app/oracle/product/19.27.0.0.250415-212/bin/oracle:kodpunp+2 /pid == 1128134/ {@[ustack(2)] = count();}'
@[
kodpunp+2
kpcocaup+599
]: 2
@[
kodpunp+2
kopp2uattr+7010
]: 11000
@[
kodpunp+2
kgmtimob+1002
]: 19001000
3.2 IN Comparison
The bpftrace count on kodpunp (from kgmtimob): 11000 matches Sqlplus(pid == 1128134) output: Number of Comparisons = 11000.
SQL > exec test_OBJ_MEMBER_vs_IN_Conditions(10000, 1000, 'I');
l_t_obj_tab_1.count = 10000
l_t_obj_tab_2.count = 1000
=============== IN Condition ===============
Elapsed(centi) = 5
l_ret_cnt = 1000
Number of Comparisons = 11000
Elapsed: 00:00:00.09
$ > bpftrace -e 'uprobe:/orabin/app/oracle/product/19.27.0.0.250415-212/bin/oracle:kodpunp+2 /pid == 1128134/ {@[ustack(2)] = count();}'
@[
kodpunp+2
kpcocaup+599
]: 2
@[
kodpunp+2
kgmtimob+1002
]: 11000
@[
kodpunp+2
kopp2uattr+7010
]: 11000
#0 kodpunp ()
#1 kokoupkl ()
#2 kpcocaup ()
#3 kprcdt ()
#4 kprccu ()
#5 opifcr ()
#6 qergsFetch ()
#7 opifch2 ()
#8 opiefn0 ()
#9 opipls ()
#10 opiodr ()
#11 rpidrus ()
#12 skgmstack ()
#13 rpidru ()
#14 rpiswu2 ()
#15 rpidrv ()
#16 psddr0 ()
#17 psdnal ()
#18 pevm_EXECC ()
#19 pfrinstr_EXECC ()
#20 pfrrun_no_tool ()
#21 pfrrun ()
#22 plsql_run ()