Oracle: Can Adaptive Cursor Sharing Plans Depend On Execution Order?

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.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles