Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted December 11, 2014

Do You Really Need Oracle Optimizer_index_cost_adj Set?

By David Fitzjarrell

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 relesaes 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 releases 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 two 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.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM