Understading Oracle Query Performance Tuning and using Indexes for optimizations

Sometime back we had a suspected issue with the performance of an Oracle query. Although the issue was found to be unrelated to the suspicion, we did investigate the issue and was able to further improve the performance based on the analysis performed.

To list indexes already existing on a table, you can login as SYSDBA and execute the following. From a Linux workstation, this would mean:

[user@host ~]$ sudo su - oracle
-bash-4.1$ sqlplus / as sysdba

From within SQLPlus, execute:
set pages 999
set lin 999

break on table_name skip 2

column table_name  format a25
column index_name  format a25
column column_name format a25

select
   table_name,
   index_name,
   column_name
from
   dba_ind_columns
where
   table_owner='APS_ESB'
order by
   table_name,
   column_position;


The output will list each table and the indexes

TABLE_NAME          INDEX_NAME            COLUMN_NAME
------------------- --------------------- -------------------------
...
DELIVERY_INFO       DELIVERY_INFO_PK      MSG_REF
...

By default, Oracle will only create an index for the primary key of a table.




To understand how a query is analyzed and performed by Oracle, use the "EXPLAIN PLAN FOR " prefix before your SQL query to analyze. Then after SQLPlus outputs "Explained", issue the query "SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());" as shown below

SQL> EXPLAIN PLAN FOR SELECT MSG_REF FROM SCHEMA.PERF_DELIVERY_INFO WHERE ST_STAGE IN (4, 6) AND ((ST_SUBSYS0_STATE  < 2 AND (ST_SUBSYS0_UPDATE_TIME  IS NULL OR ST_SUBSYS0_UPDATE_TIME  < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS1_STATE  < 2 AND (ST_SUBSYS1_UPDATE_TIME  IS NULL OR ST_SUBSYS1_UPDATE_TIME  < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS2_STATE  < 2 AND (ST_SUBSYS2_UPDATE_TIME  IS NULL OR ST_SUBSYS2_UPDATE_TIME  < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS3_STATE  < 2 AND (ST_SUBSYS3_UPDATE_TIME  IS NULL OR ST_SUBSYS3_UPDATE_TIME  < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS4_STATE  < 2 AND (ST_SUBSYS4_UPDATE_TIME  IS NULL OR ST_SUBSYS4_UPDATE_TIME  < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS8_STATE   < 2 AND (ST_SUBSYS8_UPDATE_TIME   IS NULL OR ST_SUBSYS8_UPDATE_TIME   < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS9_STATE   < 2 AND (ST_SUBSYS9_UPDATE_TIME   IS NULL OR ST_SUBSYS9_UPDATE_TIME   < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS10_STATE   < 2 AND (ST_SUBSYS10_UPDATE_TIME   IS NULL OR ST_SUBSYS10_UPDATE_TIME   < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS11_STATE   < 2 AND (ST_SUBSYS11_UPDATE_TIME   IS NULL OR ST_SUBSYS11_UPDATE_TIME   < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS5_STATE < 2 AND (ST_SUBSYS5_UPDATE_TIME IS NULL OR ST_SUBSYS5_UPDATE_TIME < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS6_STATE < 2 AND (ST_SUBSYS6_UPDATE_TIME IS NULL OR ST_SUBSYS6_UPDATE_TIME < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS7_STATE  < 2 AND (ST_SUBSYS7_UPDATE_TIME  IS NULL OR ST_SUBSYS7_UPDATE_TIME  < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')))) AND ROWNUM < 101 ORDER BY LAST_RECV_TIME;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1365086006

--------------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   100 |  6400 |     |  2622   (2)| 00:00:32 |
|   1 |  SORT ORDER BY        |             |   100 |  6400 |   968K|  2622   (2)| 00:00:32 |
|*  2 |   COUNT STOPKEY     |             |     |     |     |          |      |
|*  3 |    TABLE ACCESS FULL| PERF_DELIVERY_INFO |  7763 |   485K|     |  2499   (2)| 00:00:30 |
--------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<101)
   3 - filter(("ST_STAGE"=4 OR "ST_STAGE"=6) AND ("ST_SUBSYS0_STATE"<2 AND
          ("ST_SUBSYS0_UPDATE_TIME" IS NULL OR "ST_SUBSYS0_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS1_STATE"<2 AND
          ("ST_SUBSYS1_UPDATE_TIME" IS NULL OR "ST_SUBSYS1_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS2_STATE"<2 AND
          ("ST_SUBSYS2_UPDATE_TIME" IS NULL OR "ST_SUBSYS2_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS3_STATE"<2 AND
          ("ST_SUBSYS3_UPDATE_TIME" IS NULL OR "ST_SUBSYS3_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS4_STATE"<2 AND
          ("ST_SUBSYS4_UPDATE_TIME" IS NULL OR "ST_SUBSYS4_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS8_STATE"<2 AND
          ("ST_SUBSYS8_UPDATE_TIME" IS NULL OR "ST_SUBSYS8_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS9_STATE"<2 AND
          ("ST_SUBSYS9_UPDATE_TIME" IS NULL OR "ST_SUBSYS9_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS10_STATE"<2 AND
          ("ST_SUBSYS10_UPDATE_TIME" IS NULL OR "ST_SUBSYS10_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS11_STATE"<2 AND
          ("ST_SUBSYS11_UPDATE_TIME" IS NULL OR "ST_SUBSYS11_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS5_STATE"<2 AND
          ("ST_SUBSYS5_UPDATE_TIME" IS NULL OR "ST_SUBSYS5_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS6_STATE"<2 AND
          ("ST_SUBSYS6_UPDATE_TIME" IS NULL OR "ST_SUBSYS6_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS7_STATE"<2 AND
          ("ST_SUBSYS7_UPDATE_TIME" IS NULL OR "ST_SUBSYS7_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF'))))


As we can see above, a full table scan will occur if we do not have any indexes over the columns over which any rows will be filtered. After creating an index over the columns of significance, we can now analyze the performance again with "EXPLAIN PLAN FOR .." and the same query now executes with:

-------------------------------------------------------------------------------------------
| Id  | Operation     | Name           | Rows  | Bytes | Cost (%CPU)| Time      |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |   100 |  6400 |    10   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |              |      |      |           |      |
|*  2 |   INDEX FULL SCAN| PERF_DELIVERY_INFO_IDX |  7763 |   485K|    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

As we can see, a simple index created to correctly support a query can increase the performance many folds. In the example above, the table scan was replaced with a scan only over the index, which is much more efficient in CPU usage and time.