Oracle Tips and Tricks — David Fitzjarrell

March 7, 2014

Hide And Seek (After A Fashion)

Filed under: General — dfitzjarrell @ 12:12

Indexes can be very useful tools to improve query performance, but it can be difficult to test indexes in an environment that exactly mirrors production. Although not the preferred method, Oracle offers in 11.2 and later releases the option of invisible indexes. What are invisible indexes? An invisible index can’t be ‘seen’ by any session by default, so it can be created and remain unavailable until testing is scheduled. This can be confusing since Oracle has never offered such a feature. Looking at an example might clear up any confusion.

Creating an invisible index is fairly straightforward:


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

Index created.

SQL>

We now have an invisible index on the EMPNO column of the ubiquitous EMP table. Will it be used? No, since the required parameter, optimizer_use_invisible_indexes, has not been altered from its default of FALSE. We prove this by executing the following query and examining the plan:


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
        861  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Notice the full table scan for a single row result set; even though we know the index exists the INVISIBLE keyword prevents Oracle from using it. Let’s fix that with the next attempt:


SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

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: 576302360
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1|    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1|    87 |     1   (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
        861  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

The current session can now see and use this invisible index even though no one else can. Since no DDL was issued the cursor is not invalidated. The parameter optimizer_use_invisible_indexes was set to TRUE at the session level, which now allows Oracle to know this index exists. If you’ve created more than one invisible index then all invisible indexes are visible from the session where this parameter is set to TRUE. This could make testing difficult if several invisible indexes are created using a common column especially if it’s the leading column. In the following example the intent was to test EMO_EMPNO_IDX but two similar invisible indexes were created:


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

Index created.

SQL>
SQL> create index emp_empdept_idx
  2  on emp(empno,deptno)
  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
        861  bytes sent via SQL*Net to client
        419  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>
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: 1632405565
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            |Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    1 |    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP             |    1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_EMPDEPT_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
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        861  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Notice that the intended index was not selected; the second of the two invisible indexes was favored for the query. It’s best if only one invisible index per table is created to avoid such occurrences. Of course it’s not hard to drop an invisible index so if the first attempt doesn’t function as intended the index can be dropped and a revised invisible index can replace it.

When should you use invisible indexes? Normally in a development or load test environment when performance issues need to be addressed. An invisible index can be created and then tested using the commands shown previously; the beauty of such a system is that only one session needs to see the invisible index so it can be tested. This prevents a new index from affecting existing processes so that other sessions on a heavily used test server won’t be disrupted by its presence. Test suite results can be invalidated by creating visible indexes without first verifying prior testing is complete (I’ve seen situations where indexes were created on load test systems because the person creating the indexes thought the system was ‘free’ and did not check that information first).

So you have an invisible index created and it provides the desired results; how does it become visible to all? The following command establishes its visibility:


SQL> alter index emp_empno_idx visible;

Index altered.

SQL>

A quick query against EMP from another session proves the index can be seen by all:


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 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1|    87 |     1   (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
----------------------------------------------------------
        210  recursive calls
          0  db block gets
         47  consistent gets
          0  physical reads
          0  redo size
        861  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 

Once you make an index visible the DDL invalidates the current cursor for queries against the table where the once-invisible index is built. Since the plan changes this should not be a surprising event. This does result in a hard parse, so you will likely see the hard to soft parse ratio increase after invisible indexes are made visible.

Invisible indexes are a very useful addition to an already world-class database, allowing performance tuning in a relatively isolated environment. Of course such tools must be used with discretion, forethought and care as the example with two similar invisible indexes illustrates. Keeping that in mind I find that invisible indexes can greatly improve the tuning process since the indexes can be tested and refined with a minimal impact on other sessions. Implementation then becomes less of a ‘hit-or-miss’ proposition in the finalized version of a product release or in a firefighting session geared toward immediate performance improvement since various forms of an index can be created and the most beneficial version can be selected. Remember, too, that there are no ‘silver bullets’ when it comes to Oracle performance.

About these ads

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 628 other followers

%d bloggers like this: