Improving Application Performance Using Oracle Database 12c

Version 2
Created by Laura Ramsey-Oracle on Aug 23, 2016 8:43 PM. Last modified by Laura Ramsey-Oracle on Aug 30, 2016 7:55 PM.

    Improving Application Performance Using Oracle Database 12c

     

    By Paul Guerin, (Top Gun)

     

    Oracle Database 12c features lead to the highest levels of Oracle Database Application Performance

     

    While improving application performance is possible from tuning database and operating system configurations, the biggest application performance gains are often from index optimization. The SQL statements will determine what index keys should be used. Index attributes such as cluster factor can be important, but often the most significant performance gains come from aligning the index key and column order to the SQL statement.

     

    When deciding the order of the index columns, generally a good starting point is to specify the most frequently used columns first. This means an index with key columns of x, y, and z, may benefit SQL statements with equality predicates that use columns:

     

    -x.

    -x, and y.

    -x, y, and z.

    -x, y, z, and others.

     

     

    A common access path for SQL statements with equality predicates is an Index Range Scan. In special circumstances an Index Skip Scan can be used for indexes that don't have a matching leading column for any SQL statement predicate.

     

    It's not guaranteed that an SQL statement will benefit from an index, but Oracle database 12c introduces powerful new features that can be used to analyze and improve index efficiency. Let's examine these new features, and how they can benefit application performance.

     

    Identify unused indexes in Oracle database 12c

     

    There may be indexes that had an efficient key and column order in the past, but due to the profile of the data changing, over time an index may no longer be used. Index usage monitoring can confirm which indexes are not being used. It's not enabled by default, so to enable this feature use the MONITORING USAGE clause.

     

    -- enable monitoring on an index suspected to be of poor efficiency
    ALTER INDEX &index_name MONITORING USAGE;

     

    The legacy view to view the index usage is the v$object_usage dynamic view. However while v$object_usage is available, it's deprecated in Oracle Database 12c. The major limitation of v$object_usage is that you must be logged in as the index owner in order to show a result set. For example, if the session is not from the owner of the index, then v$object_usage dynamic view doesn't show anything:

     

    show user

     

    USER is "SYS"

     

    SELECT index_name, used, monitoring,

        TO_CHAR(TO_DATE(start_monitoring,'mm/dd/yyyy hh24:mi:ss'),'ddMonyyyy hh24:mi:ss') start_monitor,

        TO_CHAR(TO_DATE(end_monitoring,'mm/dd/yyyy hh24:mi:ss'),'ddMonyyyy hh24:mi:ss') finish_monitor

    FROM v$object_usage

    WHERE table_name=UPPER('&table_name');

     

    no rows selected

     

     

    So the replacement view in Oracle Database 12c is the dba_object_usage dictionary view, which has the ability to display index usage regardless of session owner. The dba_object_usage dictionary view can be used from any account granted with the SELECT ANY DICTIONARY privilege.

     

    show user
     
    USER is "SYS"
     
    col owner format a20
    col index_name format a30
    SELECT index_name, used, monitoring, 
        TO_CHAR(TO_DATE(start_monitoring,'mm/dd/yyyy hh24:mi:ss'),'ddMonyyyy hh24:mi:ss') start_monitor,
        TO_CHAR(TO_DATE(end_monitoring,'mm/dd/yyyy hh24:mi:ss'),'ddMonyyyy hh24:mi:ss') finish_monitor
    FROM dba_object_usage 
    WHERE table_name=UPPER('&table_name');
     
    INDEX_NAME                     USE MON START_MONITOR               FINISH_MONITOR
    ------------------------------ --- --- --------------------------- ---------------------------
    INDEX_TEST$N                   NO  YES 19Jul2016 21:29:48

     

    If for any reason you want to disable the usage monitoring then you can use the NOMONITORING USAGE clause similar to the following command:

     

    -- disable monitoring
    ALTER INDEX &index_name NOMONITORING USAGE;

     

    After an appropriate period to confirm the index is not being used, then this object is a candidate for dropping. The reduction of indexes associated with a table may also improve DML performance due to lower overhead.

     

    Enhance an already efficient key in Oracle Database 12c

     

    If an index is being used by the cost-based optimizer, then it's regarded as being more efficient than a full table scan for that SQL statement. There may be an opportunity to find a more efficient index for an SQL statement. However, if a more efficient index can't be found, then a performance gain can still result from index compression.

     

    Before Oracle Database 12c, there was only a single method to compress indexes, but now there are two:

     

    • index prefix compression
    • advanced index compression (new for Oracle Database 12c)

     

    Either method may reduce the size of an index, and also result in performance improvements if a query returns a large result set. This is especially true for execution plans containing Index Full Scans, and Index Fast Full Scans as the entire index is accessed.

     

    Advanced index compression is new for the Oracle Database 12c release, and is available with the Oracle Advanced Compression option. However before advanced index compression, the only way to compress an index was to use index prefix compression. Index prefix compression required an assessment to determine the most optimum key prefix. The assessment involved analyzing the existing index to find the optimum prefix for the key.

     

    ANALYZE INDEX &index_name VALIDATE STRUCTURE;

     

    Unfortunately, by default the table is locked during the analysis, so DML will be halted until completion. The good news is that the DML lock doesn't affect querying, so SELECT statements against the table are not impeded. The result of the analysis is displayed via the dictionary views INDEX_STATS, and INDEX_HISTOGRAM.

     

    The INDEX_STATS dictionary view shows the optimum column prefix and the associated saving from compression. There the two columns of interest in the INDEX_STATS dictionary view.

     

    opt_cmpr_count: the number of prefix non-compressed columns that give the optimum compression.

    opt_cmpr_pctsave: the estimated saving % compared with non-compressed when specifying the optimum compression (ie opt_cmpr_count).

     

    SELECT name, partition_name, blocks, opt_cmpr_count, opt_cmpr_pctsave FROM index_stats;

     

    NAME                PARTITION_NAME         BLOCKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
    ------------------- ---------------------- ---------- -------------- ----------------
    INDEX_TEST$N1                                2688          2           38

     

    From the example above, the analysis shows the index will be reduced by 38%, when using index prefix compression with a prefix of two columns. The dictionary view also shows the analyzed index has 2688 blocks.

     

    Also use DBA_INDEXES dictionary view to show the status and configuration of the index.

     

    col index_name format a40
    SELECT index_name, index_type, status, compression, prefix_length, visibility, leaf_blocks
    FROM dba_indexes 
    WHERE index_name = UPPER('&index_name');
     
    INDEX_NAME                               INDEX_TYPE                  STATUS   COMPRESSION   PREFIX_LENGTH VISIBILIT LEAF_BLOCKS
    ---------------------------------------- --------------------------- -------- ------------- ------------- --------- -----------
    INDEX_TEST$N                             NORMAL                      VALID    DISABLED                    VISIBLE          2505
     

     

    Compressing this index is as simple is rebuilding with the COMPRESS clause, and using the prefix found previously.

     

    ALTER INDEX &index_name REBUILD COMPRESS 2;

    ANALYZE INDEX &index_name VALIDATE STRUCTURE;

    SELECT blocks FROM index_stats;

     

    BLOCKS

    ------

      1664

     

     

    The INDEX_STATS dictionary view originally reported a size of 2688 blocks, and now there are 1664 blocks, which is a (1-1664/2688)*100 = 38% reduction in total blocks. The DBA_INDEXES dictionary view shows the new status and configuration of the index.

     

    INDEX_NAME                           INDEX_TYPE              STATUS   COMPRESSION   PREFIX_LENGTH VISIBILIT LEAF_BLOCKS
    ---------------------------------------- --------------------------- -------- ------------- ------------- --------- -----------
    INDEX_TEST$N                         NORMAL                  VALIDENABLED               2 VISIBLE      1538

     

    The DBA_INDEXES dictionary view also shows the index is compressed with a prefix of 2.

     

    Now if we use the advanced compression feature of Oracle Database 12c, the index compression is even easier than for index prefix compression. Advanced index compression can be used with b-tree indexes by rebuilding using the COMPRESS ADVANCED LOW clause.

     

    • New in Oracle Database 12c:

         ALTER INDEX &index_name REBUILD COMPRESS ADVANCED LOW;

     

    col index_name format a40

    SELECT index_name, index_type, status, compression, prefix_length, visibility, leaf_blocks

    FROM dba_indexes

    WHERE index_name = UPPER('&index_name');

     

    INDEX_NAME                               INDEX_TYPE                  STATUS   COMPRESSION   PREFIX_LENGTH VISIBILIT LEAF_BLOCKS

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

    INDEX_TEST$N                             NORMAL                      VALID    ADVANCED LOW                VISIBLE          1538

     

     

    An obvious advantage of advanced index compression is that no analysis is required to find the prefix for optimum compression.

     

    As a bonus, advanced index compression can employ other techniques to compress more than just index prefix compression on it's own.

     

    These other techniques are:

     

    • shared intra-column level prefixes
    • duplicate key elimination
    • rowid compression

     

     

    Consequently, when using advanced index compression, there is an upside over and above index prefix compression.

     

     

    Switch to alternate index types in Oracle database 12c

     

    In previous releases it was invalid for multiple indexes with the same key and column order, to exist at the same time. For example, if there is an initial index on a table, and you attempt to create a partitioned index with the same key and column order, then the following error will result:

     

    CREATE INDEX &table_name$n10000p ON &table_name(n10000, dy)

    COMPRESS ADVANCED LOW

    GLOBAL PARTITION BY RANGE(n10000)

    (PARTITION n10 VALUES LESS THAN (10),

    PARTITION nmax VALUES LESS THAN (MAXVALUE)

    );

     

    ERROR at line 1:

    ORA-01408: such column list already indexed

     

     

     

    However now in Oracle Database 12c, multiple indexes with the same key and column order are permitted with conditions:

     

    1. Only one index can be visible to the cost-based optimizer at a time.
    2. All the existing indexes differ by type such as:
      • B-tree versus bitmap.
      • Different partitioning strategies.
      • Unique versus non-unique.

     

     

    So in Oracle Database 12c, if a non-partitioned index of the same key and column order already exists, then I just need to make all the conflicting indexes invisible to the cost-based optimizer as follows:

     

    • New in Oracle Database 12c:
      ALTER INDEX &index_name INVISIBLE;

     

    Index altered.

     

    Now when I attempt to create the partitioned index again with the same key and column order as before, the index creation is successful as shown:

     

    CREATE INDEX &table_name$n10000p ON &table_name(n10000, dy)

    COMPRESS ADVANCED LOW

    GLOBAL PARTITION BY RANGE(n10000)

    (PARTITION n10 VALUES LESS THAN (10),

    PARTITION nmax VALUES LESS THAN (MAXVALUE)

    );

     

     

    Index created.

     

     

    Querying the indexes against a table, and their keys is easy using the LISTAGG analytic function:

     

    col c format a60
    SELECT index_name,
        '(' || LISTAGG(lower(column_name)||' '||descend, ', ') WITHIN GROUP (ORDER BY column_position) || ')' key
    FROM dba_ind_columns
    WHERE table_name = UPPER('&table_name')
    GROUP BY index_name;
     
    INDEX_NAME                               key
    ---------------------------------------- ---------------------------------------
    INDEX_TEST$N                             (n10000 ASC, dy ASC)
    INDEX_TEST$N10000P                       (n10000 ASC, dy ASC)

     

    So there are two indexes with the same key and column order, and the DBA_INDEXES dictionary view confirms the two indexes are of different types:

     

    col index_name format a40

    SELECT index_name, index_type, compression, prefix_length, visibility, leaf_blocks

    FROM dba_indexes

    WHERE table_name = UPPER('&table_name');

     

    INDEX_NAME                               INDEX_TYPE                  COMPRESSION   PREFIX_LENGTH VISIBILIT LEAF_BLOCKS

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

    INDEX_TEST$N                             NORMAL                      DISABLED                    INVISIBLE        2505

    INDEX_TEST$N10000P                       NORMAL                      ADVANCED LOW                VISIBLE          1538

     

     

    Only one index is visible to the cost-based optimizer, while any others are invisible. So it's a simple matter to test the performance of these indexes by just switching the visibility status as required.

     

    ALTER INDEX &index_name INVISIBLE;

     

    Index altered.

     

    ALTER INDEX &index_name VISIBLE;

     

    Index altered.

     

    The DBA_INDEXES dictionary view shows the visibility of the indexes has been swapped, so now the other index will be available for selection by the cost-based optimizer.

     

    col index_name format a40

    SELECT index_name, index_type, compression, prefix_length, visibility, leaf_blocks

    FROM dba_indexes

    WHERE table_name = UPPER('&table_name');

     

    INDEX_NAME                               INDEX_TYPE                  COMPRESSION   PREFIX_LENGTH VISIBILIT LEAF_BLOCKS

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

    INDEX_TEST$N                             NORMAL                      DISABLED                    VISIBLE          2505

    INDEX_TEST$N10000P                       NORMAL                      ADVANCED LOW                INVISIBLE        1538

     

     

    Once it's determined which index gives the greatest benefit, then the invisible index can be dropped.

     

    Compressed indexes can result in higher performance for Index Full Scans, and Index Fast Full Scans, leading to the highest levels of Oracle database application performance.

     

    See also

     

    Database SQL Language Reference

    http://docs.oracle.com/database/121/SQLRF/statements_5013.htm#SQLRF01209

     

    Database Administrator's Guide

    http://docs.oracle.com/database/121/ADMIN/indexes.htm#ADMIN016

     

    Database Concepts

    http://docs.oracle.com/database/121/CNCPT/indexiot.htm#CNCPT721

     

    Database VLDB and Partitioning Guide

    http://docs.oracle.com/database/121/VLDBG/GUID-48E980C5-AC1D-4140-AE6A-531BE38108E0.htm#VLDBG14200

     

    About the Author

     

    Paul Guerin is an international consultant that specializes on Oracle database performance. Paul has been the principle consultant for many blue-chip companies from Australia, France, Hong Kong, and the United States. Moreover he has presented at some of the world's leading Oracle conferences, including Oracle Open World 2013. He is based from a global delivery center in South East Asia, and is a participant in the Oracle ACE program.