Oracle Tips and Tricks — David Fitzjarrell

December 18, 2014

The Best Laid Plans

Filed under: General — dfitzjarrell @ 10:54

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, with 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.

Let’s see if dynamic sampling produces different results:


SQL> 
SQL> --
SQL> -- Create sample tables
SQL> --
SQL> -- Create them parallel, necessary
SQL> -- to get a Smart Scan on these 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> --
SQL> -- Run query on indexed column
SQL> -- and use index
SQL> --
SQL> -- Generate execution plan
SQL> --
SQL> -- Also report query execution time
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                                                                            
...
   1973629 Fnarm1973629                                   1000 999-99-9999                                                                            
   1934429 Fnarm1934429                                   1000 999-99-9999                                                                            
   1934499 Fnarm1934499                                   1000 999-99-9999                                                                            
   1934569 Fnarm1934569                                   1000 999-99-9999                                                                            

28571 rows selected.


Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 3668658578                                                                                                                           
                                                                                                                                                      
--------------------------------------------------------------------------------------------------                                                    
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |                                                    
--------------------------------------------------------------------------------------------------                                                    
|   0 | SELECT STATEMENT                    |            | 15472 |   846K|     1   (0)| 00:00:01 |                                                    
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP        | 15472 |   846K|     1   (0)| 00:00:01 |                                                    
|*  2 |   INDEX RANGE SCAN                  | EMPSAL_IDX |  6189 |       |     1   (0)| 00:00:01 |                                                    
--------------------------------------------------------------------------------------------------                                                    
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   2 - access("EMPSAL"=TO_NUMBER(:ESAL))                                                                                                              
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=2)                                                                                              


Statistics
----------------------------------------------------------                                                                                            
         17  recursive calls                                                                                                                          
          0  db block gets                                                                                                                            
      27718  consistent gets                                                                                                                          
       6720  physical reads                                                                                                                           
     901140  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: 3668658578                                                                                                                           
                                                                                                                                                      
--------------------------------------------------------------------------------------------------                                                    
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |                                                    
--------------------------------------------------------------------------------------------------                                                    
|   0 | SELECT STATEMENT                    |            | 15472 |   846K|     1   (0)| 00:00:01 |                                                    
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP        | 15472 |   846K|     1   (0)| 00:00:01 |                                                    
|*  2 |   INDEX RANGE SCAN                  | EMPSAL_IDX |  6189 |       |     1   (0)| 00:00:01 |                                                    
--------------------------------------------------------------------------------------------------                                                    
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   2 - access("EMPSAL"=TO_NUMBER(:ESAL))                                                                                                              
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=2)                                                                                              


Statistics
----------------------------------------------------------                                                                                            
         10  recursive calls                                                                                                                          
          0  db block gets                                                                                                                            
      86407  consistent gets                                                                                                                          
       7293  physical reads                                                                                                                           
        856  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> 

Doesn’t look good; maybe if the conditions are reversed:


SQL> 
SQL> --
SQL> -- Create sample tables
SQL> --
SQL> -- Create them parallel, necessary
SQL> -- to get a Smart Scan on these 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> --
SQL> -- Run query on indexed column
SQL> -- and not use index
SQL> --
SQL> -- Generate execution plan
SQL> --
SQL> -- Also report query execution time
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                                                                            
...
   1937557 Fnarm1937557                                   7000 777-77-7777                                                                            

1142857 rows selected.


Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 3668658578                                                                                                                           
                                                                                                                                                      
--------------------------------------------------------------------------------------------------                                                    
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |                                                    
--------------------------------------------------------------------------------------------------                                                    
|   0 | SELECT STATEMENT                    |            | 15237 |   833K|     1   (0)| 00:00:01 |                                                    
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP        | 15237 |   833K|     1   (0)| 00:00:01 |                                                    
|*  2 |   INDEX RANGE SCAN                  | EMPSAL_IDX |  6095 |       |     1   (0)| 00:00:01 |                                                    
--------------------------------------------------------------------------------------------------                                                    
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   2 - access("EMPSAL"=TO_NUMBER(:ESAL))                                                                                                              
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=2)                                                                                              


Statistics
----------------------------------------------------------                                                                                            
         16  recursive calls                                                                                                                          
          0  db block gets                                                                                                                            
      96812  consistent gets                                                                                                                          
       6017  physical reads                                                                                                                           
     841416  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 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                                                                            
...
   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: 3668658578                                                                                                                           
                                                                                                                                                      
--------------------------------------------------------------------------------------------------                                                    
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |                                                    
--------------------------------------------------------------------------------------------------                                                    
|   0 | SELECT STATEMENT                    |            | 15237 |   833K|     1   (0)| 00:00:01 |                                                    
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP        | 15237 |   833K|     1   (0)| 00:00:01 |                                                    
|*  2 |   INDEX RANGE SCAN                  | EMPSAL_IDX |  6095 |       |     1   (0)| 00:00:01 |                                                    
--------------------------------------------------------------------------------------------------                                                    
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   2 - access("EMPSAL"=TO_NUMBER(:ESAL))                                                                                                              
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=2)                                                                                              


Statistics
----------------------------------------------------------                                                                                            
         85  recursive calls                                                                                                                          
          0  db block gets                                                                                                                            
      17374  consistent gets                                                                                                                          
       7311  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> 

This is even worse, as no FULL TABLE SCAN plan is used, even when it would be the most efficient.

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.

Seems mice and men do better, sometimes.

December 8, 2014

You Can Get There Making All Right-Hand Turns But …

Filed under: General — dfitzjarrell @ 08:12

It would appear that some DBAs are still using the optimizer_index_cost_adj parameter to make index access paths more ‘desirable’ to the optimizer. In decades past this might have been a good strategy however with the improvement in statistics gathering in recent releases of Oracle this might not be the case. Let’s look at an example to see why this might do more ‘harm’ than good.

The optimizer_index_cost_adj parameter was first provided in Oracle 9i as a way to ‘gently’ influence the Cost-Based Optimizer to favor index scans over full table scans. It did that rather efficiently and it still does, which brings us to the inherent problem of using it: it does its job all TOO well sometimes. For efficiency and reduced physical I/O sometimes a full table scan is better than using an index, a fact that’s been known in Oracle circles for years now. Still, some sites are still setting optimizer_index_cost_adj to a non-default value, possibly through migrations to newer relesaeas and failing to modify the init.ora file configured for the older version. Using 11.2.0.4 here’s an example of what can occur when this parameter is altered to what might seem to be a ‘reasonable’ value. We start by disabling the automatic statistics gathering with the CREATE INDEX statement:


SQL> 
SQL> alter session set "_optimizer_compute_index_stats"=false;

Session altered.

SQL> 

Now, create the index on the EMP table:


SQL> create index emp_idx on emp(job);

Index created.

Let’s set optimizer_index_cost_adj to 10 and see what plans we get for two queries, one that should generate a full table scan and one that should use the index:


SQL> 
SQL> set autotrace on
SQL> 
SQL> alter session set optimizer_index_cost_adj=10;

Session altered.

SQL> 
SQL> select empno, ename, job, sal
  2  from emp
  3  where job = 'CLERK';

     EMPNO ENAME      JOB              SAL                                                                                          
---------- ---------- --------- ----------                                                                                          
      7369 SMITH      CLERK            800                                                                                          
      7876 ADAMS      CLERK           1100                                                                                          
      7900 JAMES      CLERK            950                                                                                          
      7934 MILLER     CLERK           1300                                                                                          
      7369 SMITH      CLERK            800                                                                                          
...
      7934 MILLER     CLERK           1300                                                                                          

1024 rows selected.


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 1472992808                                                                                                         
                                                                                                                                    
---------------------------------------------------------------------------------------                                             
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                             
---------------------------------------------------------------------------------------                                             
|   0 | SELECT STATEMENT            |         |  1024 | 39936 |     6   (0)| 00:00:01 |                                             
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |  1024 | 39936 |     6   (0)| 00:00:01 |                                             
|*  2 |   INDEX RANGE SCAN          | EMP_IDX |  1024 |       |     1   (0)| 00:00:01 |                                             
---------------------------------------------------------------------------------------                                             
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - access("JOB"='CLERK')                                                                                                        
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=2)                                                                             


Statistics
----------------------------------------------------------                                                                          
          9  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        204  consistent gets                                                                                                        
          4  physical reads                                                                                                         
          0  redo size                                                                                                              
      31902  bytes sent via SQL*Net to client                                                                                       
       1247  bytes received via SQL*Net from client                                                                                 
         70  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
       1024  rows processed                                                                                                         

SQL> 
SQL> select empno, ename, job, sal
  2  from emp
  3  where job = 'PRESIDENT';

     EMPNO ENAME      JOB              SAL                                                                                          
---------- ---------- --------- ----------                                                                                          
      7839 KING       PRESIDENT       5000                                                                                          
      7869 JACK       PRESIDENT       5000                                                                                          


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 1472992808                                                                                                         
                                                                                                                                    
---------------------------------------------------------------------------------------                                             
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                             
---------------------------------------------------------------------------------------                                             
|   0 | SELECT STATEMENT            |         |     2 |    78 |     1   (0)| 00:00:01 |                                             
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     2 |    78 |     1   (0)| 00:00:01 |                                             
|*  2 |   INDEX RANGE SCAN          | EMP_IDX |     2 |       |     1   (0)| 00:00:01 |                                             
---------------------------------------------------------------------------------------                                             
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - access("JOB"='PRESIDENT')                                                                                                    
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=2)                                                                             


Statistics
----------------------------------------------------------                                                                          
          8  recursive calls                                                                                                        
          0  db block gets                                                                                                          
         40  consistent gets                                                                                                        
          1  physical reads                                                                                                         
          0  redo size                                                                                                              
        812  bytes sent via SQL*Net to client                                                                                       
        499  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 

Notice that both queries used the index. This is because the index cost was artificially lowered by the optimizer_index_cost_adj setting in force. This was a fairly small table (4098 rows) and that 25% of the total row count was returned by the first query. Let’s now adjust the parameter setting back to its default and generate statistics on the schema:


SQL> alter session set optimizer_index_cost_adj=100;

Session altered.

SQL> 
SQL> exec dbms_stats.gather_schema_stats('BING')

PL/SQL procedure successfully completed.

SQL> 
SQL> select empno, ename, job, sal
  2  from emp
  3  where job = 'CLERK';

     EMPNO ENAME      JOB              SAL                                                                                          
---------- ---------- --------- ----------                                                                                          
      7369 SMITH      CLERK            800                                                                                          
      7876 ADAMS      CLERK           1100                                                                                          
      7900 JAMES      CLERK            950                                                                                          
      7934 MILLER     CLERK           1300                                                                                          
      7369 SMITH      CLERK            800                                                                                          
...
      7934 MILLER     CLERK           1300                                                                                          

1024 rows selected.


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3956160932                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |  1024 | 23552 |     9   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| EMP  |  1024 | 23552 |     9   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("JOB"='CLERK')                                                                                                        


Statistics
----------------------------------------------------------                                                                          
          1  recursive calls                                                                                                        
          0  db block gets                                                                                                          
         98  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
      31902  bytes sent via SQL*Net to client                                                                                       
       1247  bytes received via SQL*Net from client                                                                                 
         70  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
       1024  rows processed                                                                                                         

SQL> 
SQL> select empno, ename, job, sal
  2  from emp
  3  where job = 'PRESIDENT';

     EMPNO ENAME      JOB              SAL                                                                                          
---------- ---------- --------- ----------                                                                                          
      7839 KING       PRESIDENT       5000                                                                                          
      7869 JACK       PRESIDENT       5000                                                                                          


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 1472992808                                                                                                         
                                                                                                                                    
---------------------------------------------------------------------------------------                                             
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                             
---------------------------------------------------------------------------------------                                             
|   0 | SELECT STATEMENT            |         |     2 |    46 |     2   (0)| 00:00:01 |                                             
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     2 |    46 |     2   (0)| 00:00:01 |                                             
|*  2 |   INDEX RANGE SCAN          | EMP_IDX |     2 |       |     1   (0)| 00:00:01 |                                             
---------------------------------------------------------------------------------------                                             
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - access("JOB"='PRESIDENT')                                                                                                    


Statistics
----------------------------------------------------------                                                                          
          1  recursive calls                                                                                                        
          0  db block gets                                                                                                          
          5  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        812  bytes sent via SQL*Net to client                                                                                       
        499  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 

Now we get the ‘correct’ execution plans as the query returning 25% of the total rows uses a full table scan to reduce I/O. This is because the index access cost has not been artificially modified to favor such scans. We still get an index scan for the second query that returns 2 rows, which is as it should be.

What was good in earlier releases of Oracle may no longer provide benefit since changes in the CBO and statistics gathering could make such settings detrimental to perfrmance, as the above example illustrates. This example was also executed against an EMP table containing around 1.5 million rows with the same results; it isn’t difficult to realize that doubling the I/O arbitrarily isn’t a good idea. Sometimes the status quo shouldn’t be maintained.

It’s easy at times to pass through settings that once provided benefit in older releases of Oracle. Such settings should be examined and tested before passing them on to production unaltered as they may increase the work Oracle does to retrieve data. Testing is key; yes, that can prolong completing a migration to a newer release but it could prove invaluable in reducing I/O and improving performance, as illustrated here. And it may prevent the DBA from hunting down a performance problem that could have been avoided.

Sometimes it’s better to make a left-hand turn now and then.

Create a free website or blog at WordPress.com.