In a forum I contribute to the following question was asked: “Can adaptive cursor sharing (ACS) depend on execution order?”
The issue described a relatively basic query that changed execution plans, apparently due to the order the query statements were run based on bind variable values. It’s an interesting issue that testing has verified. The tests are reproduced below, in abbreviated form, so let’s look at what was executed and what execution plans were used.
Adaptive Cursor Sharing, for those who aren’t familiar with the term, allows Oracle to bind peek every time a query is run to determine the actual value in the bind variable. Knowing this helps Oracle provide a reasonably performant execution plan and can generate several different plans for the same query text. Oracle then selects from the possible cursors and the associated plans to execute the bind variable query in, hopefully, the most efficient manner. A key to this is current statistics, including histograms, so the optimizer can ‘know’ the data distribution and can better assess an efficient access path. With 12.1.0.2 a bug exists, involving the use of histograms, that affects execution plans for bind variable queries. Examples are provided illustrating the problem relative to non-bind variable queries.
First let’s consider not using bind variables and see what plans are generated. Tables were created and loaded in a way to allow an index scan for one salary value and a full table scan for another salary value. The non-bind-variable results follow:
SQL>
SQL> --
SQL> -- Create sample tables
SQL> --
SQL> create table emp(
2 empid number,
3 empnm varchar2(40),
4 empsal number,
5 empssn varchar2(12),
6 constraint emp_pk primary key (empid)
7 ) ;
Table created.
SQL>
SQL> create index empsal_idx on emp(empsal) ;
Index created.
SQL>
SQL> create table emp_dept(
2 empid number,
3 empdept number,
4 emploc varchar2(60),
5 constraint emp_dept_pk primary key(empid)
6 ) ;
Table created.
SQL>
SQL> create table dept_info(
2 deptnum number,
3 deptnm varchar2(25),
4 constraint dept_info_pk primary key(deptnum)
5 ) ;
Table created.
SQL>
SQL> --
SQL> -- Load sample tables with data
SQL> --
SQL> begin
2 for i in 1..2000000 loop
3 insert into emp
4 values(i, 'Fnarm'||i, (mod(i, 7)+1)*1000, mod(i,10)||mod(i,10)||mod(i,10)||'-'||mod(i,10)||mod(i,10)||'-'||mod(i,10)||mod(i,10)||mod(i,10)||mod(i,10));
5 insert into emp_dept
6 values(i, (mod(i,8)+1)*10, 'Zanzwalla'||(mod(i,8)+1)*10);
7 commit;
8 end loop;
9 update emp
10 set empsal=7000 where empsal in (3000,4000,5000);
11 commit;
12 update emp set empsal=800 where empsal=1000 and empssn not like '9%';
13 commit;
14 insert into dept_info
15 select distinct empdept, case when empdept = 10 then 'SALES'
16 when empdept = 20 then 'PROCUREMENT'
17 when empdept = 30 then 'HR'
18 when empdept = 40 then 'RESEARCH'
19 when empdept = 50 then 'DEVELOPMENT'
20 when empdept = 60 then 'EMPLOYEE RELATIONS'
21 when empdept = 70 then 'FACILITIES'
22 when empdept = 80 then 'FINANCE' end
23 from emp_dept;
24
25 end;
26 /
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_stats.gather_schema_stats('BING')
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Run query on indexed column
SQL> -- and use index
SQL> --
SQL> -- Generate execution plan
SQL> --
SQL> set autotrace on
SQL>
SQL> select *
2 from emp
3 where empsal = 1000;
EMPID EMPNM EMPSAL EMPSSN
---------- ---------------------------------------- ---------- ------------
679 Fnarm679 1000 999-99-9999
749 Fnarm749 1000 999-99-9999
819 Fnarm819 1000 999-99-9999
889 Fnarm889 1000 999-99-9999
959 Fnarm959 1000 999-99-9999
...
1958719 Fnarm1958719 1000 999-99-9999
1210839 Fnarm1210839 1000 999-99-9999
1210699 Fnarm1210699 1000 999-99-9999
1210769 Fnarm1210769 1000 999-99-9999
28571 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3668658578
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28571 | 892K| 911 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 28571 | 892K| 911 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMPSAL_IDX | 28571 | | 96 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPSAL"=1000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17017 consistent gets
6790 physical reads
52960 redo size
1454131 bytes sent via SQL*Net to client
21496 bytes received via SQL*Net from client
1906 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28571 rows processed
SQL>
SQL> --
SQL> -- Now use indexed column and NOT use index
SQL> --
SQL> select *
2 from emp
3 where emPsal=7000;
EMPID EMPNM EMPSAL EMPSSN
---------- ---------------------------------------- ---------- ------------
667 Fnarm667 7000 777-77-7777
668 Fnarm668 7000 888-88-8888
669 Fnarm669 7000 999-99-9999
671 Fnarm671 7000 111-11-1111
674 Fnarm674 7000 444-44-4444
...
1958760 Fnarm1958760 7000 000-00-0000
1142857 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1142K| 34M| 1507 (8)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1142K| 34M| 1507 (8)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPSAL"=7000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
86286 consistent gets
6384 physical reads
0 redo size
54471009 bytes sent via SQL*Net to client
838642 bytes received via SQL*Net from client
76192 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1142857 rows processed
SQL>
The results are as expected; the query looking for salary value 1000 uses the index (the data volume is small enough to make this the most efficient path) and the query looking for the salary value 7000 uses a full table scan. Things get interesting when the literals are replaced by a bind variable:
SQL> --
SQL> -- Create sample tables
SQL> --
SQL> create table emp(
2 empid number,
3 empnm varchar2(40),
4 empsal number,
5 empssn varchar2(12),
6 constraint emp_pk primary key (empid)
7 ) ;
Table created.
SQL>
SQL> create index empsal_idx on emp(empsal) ;
Index created.
SQL>
SQL> create table emp_dept(
2 empid number,
3 empdept number,
4 emploc varchar2(60),
5 constraint emp_dept_pk primary key(empid)
6 ) ;
Table created.
SQL>
SQL> create table dept_info(
2 deptnum number,
3 deptnm varchar2(25),
4 constraint dept_info_pk primary key(deptnum)
5 ) ;
Table created.
SQL>
SQL> --
SQL> -- Load sample tables with data
SQL> --
SQL> begin
2 for i in 1..2000000 loop
3 insert into emp
4 values(i, 'Fnarm'||i, (mod(i, 7)+1)*1000, mod(i,10)||mod(i,10)||mod(i,10)||'-'||mod(i,10)||mod(i,10)||'-'||mod(i,10)||mod(i,10)||mod(i,10)||mod(i,10));
5 insert into emp_dept
6 values(i, (mod(i,8)+1)*10, 'Zanzwalla'||(mod(i,8)+1)*10);
7 commit;
8 end loop;
9 update emp
10 set empsal=7000 where empsal in (3000,4000,5000);
11 commit;
12 update emp set empsal=800 where empsal=1000 and empssn not like '9%';
13 commit;
14 insert into dept_info
15 select distinct empdept, case when empdept = 10 then 'SALES'
16 when empdept = 20 then 'PROCUREMENT'
17 when empdept = 30 then 'HR'
18 when empdept = 40 then 'RESEARCH'
19 when empdept = 50 then 'DEVELOPMENT'
20 when empdept = 60 then 'EMPLOYEE RELATIONS'
21 when empdept = 70 then 'FACILITIES'
22 when empdept = 80 then 'FINANCE' end
23 from emp_dept;
24
25 end;
26 /
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_stats.gather_schema_stats('BING')
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Run query on indexed column
SQL> -- and use index
SQL> --
SQL> -- Generate execution plan
SQL> --
SQL> variable esal number
SQL>
SQL> begin
2 :esal:=1000;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on
SQL>
SQL> select *
2 from emp
3 where empsal = :esal;
EMPID EMPNM EMPSAL EMPSSN
---------- ---------------------------------------- ---------- ------------
889 Fnarm889 1000 999-99-9999
959 Fnarm959 1000 999-99-9999
1029 Fnarm1029 1000 999-99-9999
1099 Fnarm1099 1000 999-99-9999
49 Fnarm49 1000 999-99-9999
119 Fnarm119 1000 999-99-9999
...
1934569 Fnarm1934569 1000 999-99-9999
1937439 Fnarm1937439 1000 999-99-9999
1937509 Fnarm1937509 1000 999-99-9999
28571 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400K| 12M| 1544 (10)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 400K| 12M| 1544 (10)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPSAL"=TO_NUMBER(:ESAL))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16841 consistent gets
6391 physical reads
47780 redo size
1454131 bytes sent via SQL*Net to client
21495 bytes received via SQL*Net from client
1906 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28571 rows processed
SQL>
SQL> --
SQL> -- Now use indexed column and NOT use index
SQL> --
SQL> begin
2 :esal:=7000;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> select *
2 from emp
3 where empsal=:esal;
EMPID EMPNM EMPSAL EMPSSN
---------- ---------------------------------------- ---------- ------------
888 Fnarm888 7000 888-88-8888
891 Fnarm891 7000 111-11-1111
892 Fnarm892 7000 222-22-2222
893 Fnarm893 7000 333-33-3333
...
1937557 Fnarm1937557 7000 777-77-7777
1142857 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400K| 12M| 1544 (10)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 400K| 12M| 1544 (10)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPSAL"=TO_NUMBER(:ESAL))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
86255 consistent gets
4358 physical reads
0 redo size
54468801 bytes sent via SQL*Net to client
838641 bytes received via SQL*Net from client
76192 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1142857 rows processed
SQL>
The index access path no longer exists, even though the bind value of 1000 should generate that plan. Let’s reverse the bind variable assignment so Oracle looks for the records having a salary of 7000 first; the plans generated are, again, not what would be expected:
SQL>
SQL> --
SQL> -- Create sample tables
SQL> --
SQL> create table emp(
2 empid number,
3 empnm varchar2(40),
4 empsal number,
5 empssn varchar2(12),
6 constraint emp_pk primary key (empid)
7 ) ;
Table created.
SQL>
SQL> create index empsal_idx on emp(empsal) ;
Index created.
SQL>
SQL> create table emp_dept(
2 empid number,
3 empdept number,
4 emploc varchar2(60),
5 constraint emp_dept_pk primary key(empid)
6 ) ;
Table created.
SQL>
SQL> create table dept_info(
2 deptnum number,
3 deptnm varchar2(25),
4 constraint dept_info_pk primary key(deptnum)
5 ) ;
Table created.
SQL>
SQL> --
SQL> -- Load sample tables with data
SQL> --
SQL> begin
2 for i in 1..2000000 loop
3 insert into emp
4 values(i, 'Fnarm'||i, (mod(i, 7)+1)*1000, mod(i,10)||mod(i,10)||mod(i,10)||'-'||mod(i,10)||mod(i,10)||'-'||mod(i,10)||mod(i,10)||mod(i,10)||mod(i,10));
5 insert into emp_dept
6 values(i, (mod(i,8)+1)*10, 'Zanzwalla'||(mod(i,8)+1)*10);
7 commit;
8 end loop;
9 update emp
10 set empsal=7000 where empsal in (3000,4000,5000);
11 update emp set empsal=800 where empsal=1000 and empssn not like '9%';
12 commit;
13 insert into dept_info
14 select distinct empdept, case when empdept = 10 then 'SALES'
15 when empdept = 20 then 'PROCUREMENT'
16 when empdept = 30 then 'HR'
17 when empdept = 40 then 'RESEARCH'
18 when empdept = 50 then 'DEVELOPMENT'
19 when empdept = 60 then 'EMPLOYEE RELATIONS'
20 when empdept = 70 then 'FACILITIES'
21 when empdept = 80 then 'FINANCE' end
22 from emp_dept;
23
24 end;
25 /
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_schema_stats('BING')
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Run query on indexed column
SQL> -- and do not use index
SQL> --
SQL> -- Generate execution plan
SQL> --
SQL> variable esal number
SQL>
SQL> begin
2 :esal:=7000;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on
SQL>
SQL> select *
2 from emp
3 where empsal = :esal;
EMPID EMPNM EMPSAL EMPSSN
---------- ---------------------------------------- ---------- ------------
888 Fnarm888 7000 888-88-8888
891 Fnarm891 7000 111-11-1111
892 Fnarm892 7000 222-22-2222
893 Fnarm893 7000 333-33-3333
...
1937557 Fnarm1937557 7000 777-77-7777
1142857 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400K| 12M| 1544 (10)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 400K| 12M| 1544 (10)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPSAL"=TO_NUMBER(:ESAL))
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
96810 consistent gets
4935 physical reads
843308 redo size
54468801 bytes sent via SQL*Net to client
838641 bytes received via SQL*Net from client
76192 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1142857 rows processed
SQL>
SQL> --
SQL> -- Now use indexed column and try to use index
SQL> --
SQL> begin
2 :esal:=1000;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> select *
2 from emp
3 where empsal=:esal;
EMPID EMPNM EMPSAL EMPSSN
---------- ---------------------------------------- ---------- ------------
889 Fnarm889 1000 999-99-9999
959 Fnarm959 1000 999-99-9999
1029 Fnarm1029 1000 999-99-9999
1099 Fnarm1099 1000 999-99-9999
...
1934499 Fnarm1934499 1000 999-99-9999
1934569 Fnarm1934569 1000 999-99-9999
1937439 Fnarm1937439 1000 999-99-9999
1937509 Fnarm1937509 1000 999-99-9999
28571 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400K| 12M| 1544 (10)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 400K| 12M| 1544 (10)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPSAL"=TO_NUMBER(:ESAL))
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
16934 consistent gets
5887 physical reads
56732 redo size
1454131 bytes sent via SQL*Net to client
21495 bytes received via SQL*Net from client
1906 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28571 rows processed
SQL>
It appears that regardless of the order Adaptive Cursor Sharing produces the same full table scan plan regardless of bind value, at least in 12.1.0.2. Statistics on the schema are current for both runs and using literal values in place of bind variables produces the expected execution plans so it appears that the statistics accurately reflect the data.
The person posting the query asked if this was a bug; if it’s related to histograms and cardinality then Bug 14565911 applies, and it would certainly seem to be the case given the evidence presented. Possibly putting more fuel on that fire let’s change how statistics are generated and see if that makes a difference:
SQL>
SQL> --
SQL> -- Create sample tables
SQL> --
SQL> create table emp(
2 empid number,
3 empnm varchar2(40),
4 empsal number,
5 empssn varchar2(12),
6 constraint emp_pk primary key (empid)
7 ) ;
Table created.
SQL>
SQL> create index empsal_idx on emp(empsal) ;
Index created.
SQL>
SQL> create table emp_dept(
2 empid number,
3 empdept number,
4 emploc varchar2(60),
5 constraint emp_dept_pk primary key(empid)
6 ) ;
Table created.
SQL>
SQL> create table dept_info(
2 deptnum number,
3 deptnm varchar2(25),
4 constraint dept_info_pk primary key(deptnum)
5 ) ;
Table created.
SQL>
SQL> --
SQL> -- Load sample tables with data
SQL> --
SQL> begin
2 for i in 1..2000000 loop
3 insert into emp
4 values(i, 'Fnarm'||i, (mod(i, 7)+1)*1000, mod(i,10)||mod(i,10)||mod(i,10)||'-'||mod(i,10)||mod(i,10)||'-'||mod(i,10)||mod(i,10)||mod(i,10)||mod(i,10));
5 insert into emp_dept
6 values(i, (mod(i,8)+1)*10, 'Zanzwalla'||(mod(i,8)+1)*10);
7 commit;
8 end loop;
9 update emp
10 set empsal=7000 where empsal in (3000,4000,5000);
11 update emp set empsal=800 where empsal=1000 and empssn not like '9%';
12 commit;
13 insert into dept_info
14 select distinct empdept, case when empdept = 10 then 'SALES'
15 when empdept = 20 then 'PROCUREMENT'
16 when empdept = 30 then 'HR'
17 when empdept = 40 then 'RESEARCH'
18 when empdept = 50 then 'DEVELOPMENT'
19 when empdept = 60 then 'EMPLOYEE RELATIONS'
20 when empdept = 70 then 'FACILITIES'
21 when empdept = 80 then 'FINANCE' end
22 from emp_dept;
23
24 end;
25 /
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_stats.gather_schema_stats('BING', method_opt => 'for all columns size skewonly')
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Run query on indexed column
SQL> -- and do not use index
SQL> --
SQL> -- Generate execution plan
SQL> --
SQL> variable esal number
SQL>
SQL> begin
2 :esal:=7000;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on
SQL>
SQL> select *
2 from emp
3 where empsal = :esal;
EMPID EMPNM EMPSAL EMPSSN
---------- ---------------------------------------- ---------- ------------
888 Fnarm888 7000 888-88-8888
891 Fnarm891 7000 111-11-1111
892 Fnarm892 7000 222-22-2222
893 Fnarm893 7000 333-33-3333
...
1955225 Fnarm1955225 7000 555-55-5555
1142857 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400K| 12M| 1544 (10)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 400K| 12M| 1544 (10)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPSAL"=TO_NUMBER(:ESAL))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
86250 consistent gets
7494 physical reads
0 redo size
54469617 bytes sent via SQL*Net to client
838641 bytes received via SQL*Net from client
76192 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1142857 rows processed
SQL>
SQL> --
SQL> -- Now use indexed column and try to use index
SQL> --
SQL> begin
2 :esal:=1000;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> select *
2 from emp
3 where empsal=:esal;
EMPID EMPNM EMPSAL EMPSSN
---------- ---------------------------------------- ---------- ------------
889 Fnarm889 1000 999-99-9999
959 Fnarm959 1000 999-99-9999
1029 Fnarm1029 1000 999-99-9999
1099 Fnarm1099 1000 999-99-9999
...
1952209 Fnarm1952209 1000 999-99-9999
1955079 Fnarm1955079 1000 999-99-9999
1955149 Fnarm1955149 1000 999-99-9999
1955219 Fnarm1955219 1000 999-99-9999
28571 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400K| 12M| 1544 (10)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 400K| 12M| 1544 (10)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPSAL"=TO_NUMBER(:ESAL))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16705 consistent gets
7930 physical reads
0 redo size
1454131 bytes sent via SQL*Net to client
21495 bytes received via SQL*Net from client
1906 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28571 rows processed
SQL>
No change even when histograms are generated only for skewed column data. So this does seem to be affected by Bug 14565911, and no patch appears to be available. The bug is listed as fixed in release 12.2; it may be necessary to get a backport of this bug fix through My Oracle Support.
With a product as complex as Oracle, bugs and issues are bound to turn up; not every case can be tested prior to release. As the bug report states this is restricted to bind variable usage. Notice that for all of the execution plans the predicate information reports that the bind variable, although declared as a number and populated as such, is being processed by the TO_NUMBER function, which may influence the index usage. Let’s give this one more try, this time with a function-based index using the TO_NUMBER function:
SQL>
SQL> --
SQL> -- Create sample tables
SQL> --
SQL> create table emp(
2 empid number,
3 empnm varchar2(40),
4 empsal number,
5 empssn varchar2(12),
6 constraint emp_pk primary key (empid)
7 ) ;
Table created.
SQL>
SQL> create index empsal_idx on emp(empsal) ;
Index created.
SQL> create index empsal_fbi_idx on emp(to_number(empsal)) ;
Index created.
SQL>
SQL> create table emp_dept(
2 empid number,
3 empdept number,
4 emploc varchar2(60),
5 constraint emp_dept_pk primary key(empid)
6 ) ;
Table created.
SQL>
SQL> create table dept_info(
2 deptnum number,
3 deptnm varchar2(25),
4 constraint dept_info_pk primary key(deptnum)
5 ) ;
Table created.
SQL>
SQL> --
SQL> -- Load sample tables with data
SQL> --
SQL> begin
2 for i in 1..2000000 loop
3 insert into emp
4 values(i, 'Fnarm'||i, (mod(i, 7)+1)*1000, mod(i,10)||mod(i,10)||mod(i,10)||'-'||mod(i,10)||mod(i,10)||'-'||mod(i,10)||mod(i,10)||mod(i,10)||mod(i,10));
5 insert into emp_dept
6 values(i, (mod(i,8)+1)*10, 'Zanzwalla'||(mod(i,8)+1)*10);
7 commit;
8 end loop;
9 update emp
10 set empsal=7000 where empsal in (3000,4000,5000);
11 commit;
12 update emp set empsal=800 where empsal=1000 and empssn not like '9%';
13 commit;
14 insert into dept_info
15 select distinct empdept, case when empdept = 10 then 'SALES'
16 when empdept = 20 then 'PROCUREMENT'
17 when empdept = 30 then 'HR'
18 when empdept = 40 then 'RESEARCH'
19 when empdept = 50 then 'DEVELOPMENT'
20 when empdept = 60 then 'EMPLOYEE RELATIONS'
21 when empdept = 70 then 'FACILITIES'
22 when empdept = 80 then 'FINANCE' end
23 from emp_dept;
24
25 end;
26 /
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_stats.gather_schema_stats('BING')
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Run query on indexed column
SQL> -- and use index
SQL> --
SQL> -- Generate execution plan
SQL> --
SQL> variable esal number
SQL>
SQL> begin
2 :esal:=1000;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on
SQL>
SQL> select *
2 from emp
3 where empsal = :esal;
EMPID EMPNM EMPSAL EMPSSN
---------- ---------------------------------------- ---------- ------------
679 Fnarm679 1000 999-99-9999
749 Fnarm749 1000 999-99-9999
819 Fnarm819 1000 999-99-9999
889 Fnarm889 1000 999-99-9999
959 Fnarm959 1000 999-99-9999
...
1451849 Fnarm1451849 1000 999-99-9999
1451219 Fnarm1451219 1000 999-99-9999
1451289 Fnarm1451289 1000 999-99-9999
1451779 Fnarm1451779 1000 999-99-9999
28571 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400K| 12M| 1544 (10)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 400K| 12M| 1544 (10)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPSAL"=TO_NUMBER(:ESAL))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16774 consistent gets
8138 physical reads
0 redo size
1454131 bytes sent via SQL*Net to client
21496 bytes received via SQL*Net from client
1906 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28571 rows processed
SQL>
SQL> --
SQL> -- Now use indexed column and NOT use index
SQL> --
SQL> begin
2 :esal:=7000;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> select *
2 from emp
3 where empsal=:esal;
EMPID EMPNM EMPSAL EMPSSN
---------- ---------------------------------------- ---------- ------------
667 Fnarm667 7000 777-77-7777
668 Fnarm668 7000 888-88-8888
669 Fnarm669 7000 999-99-9999
...
1934580 Fnarm1934580 7000 000-00-0000
1142857 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400K| 12M| 1544 (10)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 400K| 12M| 1544 (10)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPSAL"=TO_NUMBER(:ESAL))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
86316 consistent gets
7524 physical reads
0 redo size
54462057 bytes sent via SQL*Net to client
838642 bytes received via SQL*Net from client
76192 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1142857 rows processed
SQL>
The function-based index made no difference, and it really shouldn’t have been expected to. The test was included to provide most of the possible scenarios for plan verification, and it seems that the bug isn’t mitigated by how the stats are gathered or which indexes are created. [Not every possible combination of calls to DBMS_STATS were tried as doing that could take several hours to generate and run every test case.] In 12.1.0.2 you could run into a situation where bad plans are being generated when bind variables are used. Knowing this may help explain some cases of poor performance where bind variables are used.
Adaptive Cursor Sharing has been very good at producing good execution plans when bind variables are used with varying values; in 12.1.0.2 this has been adversely affected by Bug 14565911. Hopefully Oracle will issue a patch to correct this in releases from 11.1 on so we won’t have to wait for 12.2 to provide the expected behavior for Adaptive Cursor Sharing.
