Using bind variables for multiple values
Date: Mon, 24 Nov 2008 06:47:32 -0800 (PST)
Message-ID: <6c7ef223-0934-4cf9-a44b-ad268937b86f@j32g2000yqn.googlegroups.com>
All,
a bit of background: we have a stored procedure that needs to lock several rows via SELECT ... FOR UPDATE. Currently this stored procedure has a single VARCHAR2 argument which contains a comma separated list of integers (ids) and consequently uses EXECUTE IMMEDIATE. Since the application has a lot duplicate SQL which would be reduced by using bind variables I did some exploration into how this could be solved via JDBC.
In the spirit of Richard, Jonathan and Tom I did some experimenting to find out how different approaches would work, especially from a performance point of view. For anyone interested here is the test case - and an question follows further down because there is one decision of the CBO I do not understand.
Kind regards
robert
SQL> set pagesize 100 linesize 200 SQL> set serverout on SQL> create table t1 (
2 id number(10) primary key,
3 dat varchar2(100) not null
4 )
5 /
Table created.
SQL> timing start insert
SQL> insert into t1
2 select level, rpad('dat ' || level, 90,'*')
3 from dual
4 connect by level <= 1000000
5 /
1000000 rows created.
SQL> commit
2 /
Commit complete.
SQL> timing stop
timing for: insert
Elapsed: 00:00:11.78
SQL> timing start "Gather stats"
SQL> begin
2 dbms_stats.gather_schema_stats(
3 user,
4 cascade => true,
5 estimate_percent => null,
6 method_opt => 'FOR ALL COLUMNS'
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> timing stop
timing for: Gather stats
Elapsed: 00:00:51.25
SQL> create or replace type iset as table of number(10)
2 /
Type created.
SQL> alter session set tracefile_identifier = 'nested_short' 2 /
Session altered.
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL> / Session altered.
SQL> set autotrace on SQL> timing start "Literals with IN clause" SQL> select length(dat) from t1 where id in (-1,2,3)2 /
LENGTH(DAT)
90
90
Execution Plan
Plan hash value: 47079033
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 3 | 285
| 6 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | |
| | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 285
| 6 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C007311 | 3 |
| 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
3 - access("ID"=(-1) OR "ID"=2 OR "ID"=3)
Statistics
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
363 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> timing stop
timing for: Literals with IN clause
Elapsed: 00:00:00.06
SQL> timing start "Subselect with IN clause"
SQL> select length(dat) from t1 where id in ( select column_value from
table(iset(-1,2,3)) )
2 /
LENGTH(DAT)
90
90
Execution Plan
Plan hash value: 949238496
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 255
| 24735 | 529 (1)| 00:00:03 |
| 1 | NESTED LOOPS | | 255
| 24735 | 529 (1)| 00:00:03 |
| 2 | SORT UNIQUE | |
| | | |
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH| |
| | | |
| 4 | TABLE ACCESS BY INDEX ROWID | T1 | 1
| 95 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C007311 | 1
| | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
5 - access("ID"=VALUE(KOKBF$))
Statistics
1 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
363 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> timing stop
timing for: Subselect with IN clause
Elapsed: 00:00:00.06
SQL> timing start "Join"
SQL> select length(dat) from t1 join table(iset(-1,2,3)) tt on
tt.column_value = t1.id
2 /
LENGTH(DAT)
90
90
Execution Plan
Plan hash value: 472081508
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 8168 |
773K| 2054 (5)| 00:00:10 |
|* 1 | HASH JOIN | | 8168 | 773K| 2054 (5)| 00:00:10 |90M| 2015 (4)| 00:00:10 |
| 2 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | |
| | |
| 3 | TABLE ACCESS FULL | T1 | 1000K|
Predicate Information (identified by operation id):
1 - access("T1"."ID"=VALUE(KOKBF$))
Statistics
1 recursive calls
0 db block gets
14192 consistent gets
0 physical reads
0 redo size
363 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> timing stop
timing for: Join
Elapsed: 00:00:00.23
SQL> drop type iset
2 /
Type dropped.
SQL> drop table t1
2 /
Table dropped.
SQL> exit
I wondered: why is it that the join version does a full table scan but the IN ( subselect ) version does not? I did a 10053 trace and found out that the same access strategy which leads to the INDEX UNIQUE SCAN in the case of IN ( subselect ) is costed much higher (~ factor of 30) in the JOIN case. BASE STATISTICS are identical in both cases and it seems the culprit is the join cardinality (255 vs. 8168) which seems derived from the outer table cardinality. The big myth to me is: where does the CBO get the cardinality of 255 from all of a sudden since it originally assumed 8168?
Base stats are identical, just tables are differently ordered. This is from the good case:
BASE STATISTICAL INFORMATION
Table Stats::
Table: T1 Alias: T1
#Rows: 1000000 #Blks: 14177 AvgRowLen: 95.00 Index Stats::
Index: SYS_C007309 Col#: 1
LVLS: 2 #LB: 1875 #DK: 1000000 LB/K: 1.00 DB/K: 1.00 CLUF: 14083.00
Table Stats::
Table: KOKBF$ Alias: KOKBF$ (NOT ANALYZED) #Rows: 8168 #Blks: 100 AvgRowLen: 100.00
SINGLE TABLE ACCESS PATH
BEGIN Single Table Cardinality Estimation
Table: KOKBF$ Alias: KOKBF$
Card: Original: 8168 Rounded: 8168 Computed: 8168.00 Non Adjusted: 8168.00
END Single Table Cardinality Estimation
Access Path: TableScan
Cost: 15.45 Resp: 15.45 Degree: 0
Cost_io: 15.00 Cost_cpu: 1937344
Resp_io: 15.00 Resp_cpu: 1937344
Best:: AccessPath: TableScan
Cost: 15.45 Degree: 1 Resp: 15.45 Card: 8168.00 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
BEGIN Single Table Cardinality Estimation
Table: T1 Alias: T1
Card: Original: 1000000 Rounded: 1000000 Computed: 1000000.00 Non Adjusted: 1000000.00
END Single Table Cardinality Estimation
Access Path: TableScan
Cost: 2014.78 Resp: 2014.78 Degree: 0
Cost_io: 1952.00 Cost_cpu: 270960655
Resp_io: 1952.00 Resp_cpu: 270960655
Best:: AccessPath: TableScan
Cost: 2014.78 Degree: 1 Resp: 2014.78 Card: 1000000.00
Bytes: 0
Good case: select length(dat) from t1 where id in ( select column_value from table(iset(-1,2,3)) )
Join order[2]: KOKBF$[KOKBF$]#1 T1[T1]#0
SORT resource Sort statistics
Sort width: 349 Area size: 307200 Max Area size:
61446144
Degree: 1
Blocks to Sort: 13 Row size: 13 Total
Rows: 8168
Initial runs: 1 Merge passes: 0 IO Cost /
pass: 0
Total IO sort cost: 0 Total CPU sort cost: 9098366
Total Temp space used: 0
Now joining: T1[T1]#0
NL Join
Outer table: Card: 255.25 Cost: 17.56 Resp: 17.56 Degree: 1 Bytes: 2
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 513378.14 Resp: 513378.14 Degree: 1
Cost_io: 497366.00 Cost_cpu: 69106002705
Resp_io: 497366.00 Resp_cpu: 69106002705
Access Path: index (UniqueScan)
Index: SYS_C007309
resc_io: 2.00 resc_cpu: 16313
ix_sel: 1.0000e-06 ix_sel_with_filters: 1.0000e-06
NL Join: Cost: 528.52 Resp: 528.52 Degree: 1
Cost_io: 525.00 Cost_cpu: 15195495
Resp_io: 525.00 Resp_cpu: 15195495
Access Path: index (AllEqUnique)
Index: SYS_C007309
resc_io: 2.00 resc_cpu: 16313
ix_sel: 1.0000e-06 ix_sel_with_filters: 1.0000e-06
NL Join: Cost: 528.52 Resp: 528.52 Degree: 1
Cost_io: 525.00 Cost_cpu: 15195495
Resp_io: 525.00 Resp_cpu: 15195495
Best NL cost: 528.52
resc: 528.52 resc_io: 525.00 resc_cpu: 15195495
resp: 528.52 resp_io: 525.00 resp_cpu: 15195495
Join Card: 255.25 = outer (255.25) * inner (1000000.00) * sel
(1.0000e-06)
Join Card - Rounded: 255 Computed: 255.25
Bad case: select length(dat) from t1 join table(iset(-1,2,3)) tt on tt.column_value = t1.id
Join order[1]: KOKBF$[KOKBF$]#0 T1[T1]#1
Now joining: T1[T1]#1
NL Join
Outer table: Card: 8168.00 Cost: 15.45 Resp: 15.45 Degree: 1 Bytes: 2
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 16443617.43 Resp: 16443617.43 Degree: 1
Cost_io: 15930808.00 Cost_cpu: 2213208566404
Resp_io: 15930808.00 Resp_cpu: 2213208566404
Access Path: index (UniqueScan)
Index: SYS_C007309
resc_io: 2.00 resc_cpu: 16313
ix_sel: 1.0000e-06 ix_sel_with_filters: 1.0000e-06
NL Join: Cost: 16382.32 Resp: 16382.32 Degree: 1
Cost_io: 16351.00 Cost_cpu: 135180948
Resp_io: 16351.00 Resp_cpu: 135180948
Access Path: index (AllEqUnique)
Index: SYS_C007309
resc_io: 2.00 resc_cpu: 16313
ix_sel: 1.0000e-06 ix_sel_with_filters: 1.0000e-06
NL Join: Cost: 16382.32 Resp: 16382.32 Degree: 1
Cost_io: 16351.00 Cost_cpu: 135180948
Resp_io: 16351.00 Resp_cpu: 135180948
Best NL cost: 16382.32
resc: 16382.32 resc_io: 16351.00 resc_cpu: 135180948
resp: 16382.32 resp_io: 16351.00 resp_cpu: 135180948
Join Card: 8168.00 = outer (8168.00) * inner (1000000.00) * sel
(1.0000e-06)
Join Card - Rounded: 8168 Computed: 8168.00 Received on Mon Nov 24 2008 - 08:47:32 CST