Debugging Oracle 11g Applications - New Features Overview for Oracle 11g Invisible Index

Call (800) 766-1884 for Oracle support & training

Donald K. Burleson
Advanced Oracle Tips and Tricks for the Oracle DBA

Debugging Oracle 11g Applications
by Ben Prusinski

Welcome to Debugging Oracle 11g Applications! Our journey to the world of troubleshooting and debugging for Oracle 10g and 11g applications and databases will be quite an adventure. Oracle 11g Release 1 came out on July 2007 for public general release on Linux and Windows platforms. The focus of this book will be on how to leverage undocumented and hidden utilities, tools and features with Oracle 11g that previously either have scant documentation or have been exclusively in the realm of Oracle internal support engineers. We will also touch upon some of the most useful new features within Oracle 11g as they pertain to our discussion of Oracle 11g database tools and undocumented features when relevant. As such, the book will not be simply about new features for Oracle 11g. In this chapter, we will survey the new features for Oracle 11g.

New Features Overview for Oracle 11g Release

With the Oracle 11g database new release, many new features have been added since Oracle 10g. In summary, among these new features include the following:

New index type with Invisible Indexes
New partitioning features for Data Warehousing
Oracle 11g Active Data Guard
Monitoring features with the ADR utility
New features for backup and recovery with RMAN
Automatic Storage Management (ASM) new features
Automatic Memory Management
Database Replay and Rapid Application Testing new features

New Feature for Oracle 11g: Invisible Index

Oracle 11g introduces a new index type called the invisible index. The invisible index provides added functionality in that it can be used for testing performance scenarios and affects how the Oracle Cost Based Optimizer (CBO) behaves. For example, when the invisible index is used the CBO ignores the index as if the index did not exist. If the Oracle 11g initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES is set to TRUE then the CBO will see the index. By default the parameter is set to FALSE so that the CBO ignores the invisible index when using execution plans. The syntax to create a new invisible index with Oracle 11g is similar to other CREATE INDEX statements as shown in the example:

SQL> CREATE INDEX emp_inv_idx ON scott.emp(ename)
2 TABLESPACE USERS
3 INVISIBLE;

Index created.

SQL> show parameter optimizer_use_invisible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE

SQL>

Figure 1.1: Invisible Index Creation with Oracle 11g

Now we can check to find the index has been created and is an invisible index with the following query.

SQL> SELECT INDEX_NAME, INDEX_TYPE, VISIBILITY
2 FROM ALL_INDEXES
3 WHERE INDEX_NAME LIKE 'EMP%';

INDEX_NAME                     INDEX_TYPE                  VISIBILITY
------------------------------ --------------------------- ----------
EMP_LOWER_IDX                  FUNCTION-BASED NORMAL       VISIBLE
EMP_INV_IDX                    NORMAL                      INVISIBLE
EMP_DEPARTMENT_IX              NORMAL                      VISIBLE
EMP_EMAIL_UK                   NORMAL                      VISIBLE
EMP_EMP_ID_PK                  NORMAL                      VISIBLE
EMP_JOB_IX                     NORMAL                      VISIBLE
EMP_MANAGER_IX                 NORMAL                      VISIBLE
EMP_NAME_IX                    NORMAL                      VISIBLE

8 rows selected.

Figure 1.2: Query to check Index Type for Oracle 11g

The key difference is use of the INVISIBLE statement in the syntax to create the invisible index. Now to see the affect of the invisible index on the optimizer behavior, we can use autotrace to see the execution plan.

Notice in the following example that the query performs a full table scan against the SCOTT.EMP table since the index is invisible.

SQL> select empno, ename, sal
2 from scott.emp
3 order by ename;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7876 ADAMS            1100
      7499 ALLEN            1600
      7698 BLAKE            2850
      7782 CLARK            2450
      7902 FORD             3000
      7900 JAMES             950
      7566 JONES            2975
      7839 KING             5000
      7654 MARTIN           1250
      7934 MILLER           1300
      7788 SCOTT            3000

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7844 TURNER          1500
      7521 WARD             1250

14 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 150391907

|   0 | SELECT STATEMENT   |      |    14 |   196 |     4 (25)| 00:00:01 |
|   1 | SORT ORDER BY     |      |    14 |   196 |     4 (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP |    14 |   196 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------

          0 recursive calls
          0 db block gets
          7 consistent gets
          0 physical reads
          0 redo size
        803 bytes sent via SQL*Net to client
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
         14 rows processed

Figure 1.3: Oracle 11g CBO Behavior with Invisible Index

Now we can test the impact on the cost based optimizer if we make the invisible index into a normal and visible index with the ALTER INDEX… VISIBLE command as shown in the query:

SQL> ALTER INDEX EMP_INV_IDX VISIBLE;

Index altered.

Now when we re-run the query against the SCOTT.EMP table the results should access the new index as shown in the following example query:

SQL> set timing on
SQL> set autotrace on
SQL> select ename, empno, sal
2 from
3 scott.emp
4 order by empno;

ENAME           EMPNO        SAL
---------- ---------- ----------
SMITH            7369        800
ALLEN            7499       1600
WARD             7521       1250
JONES            7566       2975
MARTIN           7654       1250
BLAKE            7698       2850
CLARK            7782       2450
SCOTT            7788       3000
KING             7839       5000
TURNER           7844       1500
ADAMS            7876       1100

ENAME           EMPNO        SAL
---------- ---------- ----------
JAMES            7900        950
FORD             7902       3000
MILLER           7934       1300

14 rows selected.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------

Plan hash value: 4170700152

| 0 | SELECT STATEMENT | | 14 | 196 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 196 | 2 (0)| 00:00:01 |

| 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        406 recursive calls
          0 db block gets
         81 consistent gets
          7 physical reads
          0 redo size
        803 bytes sent via SQL*Net to client
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
         11 sorts (memory)
          0 sorts (disk)
         14 rows processed

Figure 1.4: Oracle 11g CBO Behavior without Invisible Index

We can toggle the index type back to invisible using the ALTER INDEX… INVISIBLE command on the index as well.

SQL> ALTER INDEX emp_inv_idx INVISIBLE;

Index altered.

We also test by making the primary key index for PK_EMP invisible:

SQL> alter index scott.pk_emp invisible;

Index altered.

SQL> set autotrace on
SQL> select ename, empno, sal
2 from scott.emp
3 order by empno;

ENAME           EMPNO        SAL
---------- ---------- ----------
SMITH            7369        800
ALLEN            7499       1600
WARD             7521       1250
JONES            7566       2975
MARTIN           7654       1250
BLAKE            7698       2850
CLARK            7782       2450
SCOTT            7788       3000
KING             7839       5000
TURNER           7844      1500
ADAMS            7876       1100

ENAME           EMPNO        SAL
---------- ---------- ----------
JAMES            7900        950
FORD             7902       3000
MILLER           7934       1300

14 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 150391907

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   196 |     4 (25)| 00:00:01 |
|   1 | SORT ORDER BY     |      |    14 |   196 |     4 (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP |    14 |   196 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        240 recursive calls
          0 db block gets
         55 consistent gets
          5 physical reads
          0 redo size
        803 bytes sent via SQL*Net to client
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          7 sorts (memory)
          0 sorts (disk)
         14 rows processed

SQL>

Figure 1.5: Oracle 11g CBO Behavior- Invisible Index on Primary Key

The beauty of using the invisible index feature for Oracle 11g is that it allows the developer and DBA staff to test performance for the index without the risk of dropping an index which can be time consuming and a potential risk to the production environment.

Oracle Consulting

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books

Oracle® is the registered trademark of Oracle Corporation.