Oracle Tips and Tricks — David Fitzjarrell

April 17, 2008

To "b" or not to "b"

Filed under: Indexes,Performance — dfitzjarrell @ 13:12

With the plethora of database-centric applications available today, and with the performance problems they can generate it can be a worthy effort to determine which vendor-created indexes are and are not being used. (This is especially helpful if you’re working closely with the application vendor to improve their product.) Of course one way to do this is to set event 10046 at level 8 or 12 and let the trace files fly so they can be analyzed later for which indexes are being used by the application queries. And that could be a long and tedious process. One would think there is a better way to accomplish this.

There is.

Oh, I suppose you’d like to know this better way … it’s really rather simple:

Let Oracle do the work for you.

So let’s see how we tell Oracle to do this task for us so our job is much easier.

Oracle has provided a mechanism (since at least Oracle 8.1.6) to monitor an index for usage using

alter index <index_name> monitoring usage;

The results of that monitoring are found in the V$OBJECT_USAGE view,in a column, strangely enough, named USED. This isn’t a long, boring thesis on how, when, where, who and why the index in question was used, only that it either is or is not used. The ‘window’ spans the time period starting with the execution of the above-listed command and ends when the following is issued:

alter index <index_name> nomonitoring usage;

The data remains in the V$OBJECT_USAGE view until another monitoring ‘window’ is started.

So, let’s see an example of how this works. We’ll use the EMP table from the SCOTT/TIGER demonstration schema:

SQL>
SQL> --
SQL> -- Create an index on the EMPNO column
SQL> -- of the EMP table
SQL> --
SQL> create index emp_eno_idx
  2  on emp(empno);

Index created.

SQL>
SQL> --
SQL> -- Let's monitor the index to see if
SQL> -- it's being used
SQL> --
SQL> alter index emp_eno_idx monitoring usage;

Index altered.

SQL>
SQL> --
SQL> -- Now, let's run some queries
SQL> --
SQL> -- First, let's get everything from the
SQL> -- EMP table
SQL> --
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>
SQL> --
SQL> -- Obviously the index hasn't yet been
SQL> -- used
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            NO

1 row selected.

SQL>
SQL> --
SQL> -- So let's run a qualified query and
SQL> -- see if things change
SQL> --
SQL> -- Since the DEPTNO column isn't indexed
SQL> -- the monitored index still shouldn't be
SQL> -- used
SQL> --
SQL> select * from emp where deptno = 30;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

6 rows selected.

SQL>
SQL> --
SQL> -- And we see it isn't
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            NO

1 row selected.

SQL>
SQL> --
SQL> -- Yet another qualified query, this time
SQL> -- using the indexed column
SQL> --
SQL> select * from emp where empno  --
SQL> -- We see the index is now being used, or at
SQL> -- least it was for that last query
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL>
SQL> --
SQL> -- We'll try another query using that column
SQL> --
SQL> -- Let's set autotrace on to see if the index
SQL> -- is being used in this example
SQL> --
SQL> set autotrace on
SQL> select * From emp where empno is null;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3712041407

---------------------------------------------------------------------------
 Id   Operation           Name  Rows   Bytes  Cost (%CPU) Time 
---------------------------------------------------------------------------
   0  SELECT STATEMENT              1     87      0   (0)      
*  1   FILTER                                                  
   2    TABLE ACCESS FULL EMP      14   1218      3   (0) 00:00:01
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement


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

SQL> set autotrace off
SQL>
SQL> --
SQL> -- Since the index has been marked as used
SQL> -- it remains in the USED state for this
SQL> -- monitoring window even though the last
SQL> -- query didn't use the index at all
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL>
SQL> --
SQL> -- Turn off the usage monitoring
SQL> --
SQL> alter index emp_eno_idx nomonitoring usage;

Index altered.

SQL>
SQL> --
SQL> -- And the last generated data remains
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL> 

Letting Oracle monitor index usage is much easier than traipsing through piles of event 10046 trace files looking for index scans. I’m happy they’ve provided such a tool. But, you may run across an index which is used but won’t be marked as such in V$OBJECT_USAGE. How can this be? Oracle can use the statistics from the index in determining the best query plan, and when those statistics are gone (as when the index has been dropped) performance can suffer; the optimizer generates a decision tree when each query is hard parsed, and missing index statistics may direct the optimizer down a path it might not have taken when the statistics existed. Oracle, in one respect,is correct in that the index in question hasn’t been read however it did use the statistics to perform path elimination. So, before heading straight for the ‘drop index’ command it would be prudent to verify the index in question really isn’t being used in any way — this is why we have test systems, correct? Dropping the index on a test database then verifying that no performance degradation occurs is, in my mind, a good idea. If, after the tests indicate an index may truly be unused, performance problems arise because that index is missing it can be recreated to restore the application to its original lustre.

Some suggest that simply setting an index to UNUSABLE would provide the same conditions as dropping it, but disabling an index in that fashion doesn’t remove the statistics generated on that index and if a query or process is using those statistics but is not actually accessing the index the same conditions don’t exist and one could be led into a false sense of security that the index in question is truly unused. Yes, actual access to the index is not allowed but since the index wasn’t being read to begin with (only the statistics were used by the CBO for cost calculations) I can’t accept that the same run-time conditions exist. Eventually the statistics will be outdated and no longer will be used but it could take a week, a month or longer for this to occur (depending upon system activity). For those DBAs in a hurry (and, face it, sometimes management IS in a hurry for results) setting an index to UNUSABLE may not be a valid course of action to discover whether it’s actually used or not.

There is a way in Oracle 11.2 and later releases: set the index to INVISIBLE. This will prevent the optimizer from using the index and the associated statistics without dropping it:


SQL> create index emp_empno_idx
  2  on emp(empno)
  3  invisible;

Index created.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3956160932                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("EMPNO"<7400)                                                                                                         
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=2)                                                                             


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

SQL> 
SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> set autotrace off
SQL> select LOADED_VERSIONS,executions,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='axr7gct6p1g3y';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED                                                                             
--------------- ---------- ------------- --------------                                                                             
              1          1             2              1                                                                             
              1          1             1              1                                                                             

SQL> set autotrace on
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


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


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

SQL> 
SQL> alter session set optimizer_use_invisible_indexes=false;

Session altered.

SQL> set autotrace off
SQL> select LOADED_VERSIONS,EXECUTIONS,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='axr7gct6p1g3y';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED                                                                             
--------------- ---------- ------------- --------------                                                                             
              1          1             2              1                                                                             
              1          1             1              1                                                                             

SQL> set autotrace on 
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3956160932                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("EMPNO"<7400)                                                                                                         
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=2)                                                                             


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

SQL> 
SQL> alter index emp_empno_idx visible;

Index altered.

SQL> set autotrace off
SQL> select LOADED_VERSIONS,EXECUTIONS,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='axr7gct6p1g3y';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED                                                                             
--------------- ---------- ------------- --------------                                                                             
              1          2             3              2                                                                             
              1          1             2              1                                                                             


SQL> set autotrace on
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


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


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

SQL> 

Note the change in LOADED_VERSIONS and INVALIDATIONS when the index is made visible or invisible. There is also a setting, optimizer_use_invisible_indexes, which can be set at the session level. It’s FALSE by default; setting it to TRUE will show the same results as the example shown above without making the index visible to every user who has access the objects in the affected schema, thus making the effect local to the current session. By setting the index to INVISIBLE you can assess whether that index is truly used or not, regardless of what Oracle’s index monitoring tells you. You will then be better prepared to assess whether the index can be safely dropped.

Of course database administration cannot be ruled by rash acts, and relying upon a sole source of information (such as V$OBJECT_USAGE) can result in problems down the line. So, careful attention to detail is necessary, especially when managing the existence (or not) of an index [or table, or view, or …]. I like to follow a few simple rules:

1 — Test, test, test.
2 — Keep testing.
3 — Never do anything you can’t undo.

Keeping to that methodology usually ensures I’m not in trouble later. And, it keeps the end-users happier.

I like happy end-users.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: