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.

Transactions spanning multiple resources - an Example with JMS, JDBC and File systems with JTA

We've written a sample that shows use of multiple JTA transactions spanning JMS, JDBC and File system resources. The sample is a solution in reply to a real user question as presented here.

The solution is presented using the Atomikos JTA libraries that allows the execution of the sample without deploying the UltraESB on a JEE application server such as JBoss. The original post requested for a sample using WebSphere MQ, and Oracle as the database, on top of JBoss.

The sample we've implemented uses an "embedded" ActiveMQ JMS server - started with just a couple of lines of Spring configuration within the UltraESB. The complete mediation is just a few lines of extremely powerful code - using the robust Spring framework JDBCTemplates. This coupled with the use of Java allows one to write powerful integration scripts, with the full power of the Java programming language or any other JSR 223 scripting language such as Ruby, Groovy, Javascript etc., without learning vendor or product specific configuration languages based on XML.

The sample shows the happy day scenarios as well as rollback of transactions, using the SOA ToolBox to fire sample JMS messages for testing.

The ESB Performance Testing Framework & Results of Round 4

I developed the original ESB Performance Test Framework - in June 2007 while actively working on the Apache Synapse and the WSO2 ESBs. Since then, we've run three rounds of testing, comparing both Proprietary and Open Source ESBs - including Mule, Apache ServiceMix, Apache Synapse/WSO2 ESB, a leading proprietary ESB and the proprietary version of an open source ESB.

It was interesting to see other vendors such as Mule and BEA picking up this test framework in addition to WSO2, in publishing results. However, due to different hardware configurations being used, and other advanced tuning or optimizations performed by vendors - the results were left a bit questionable and could not be compared in a fair manner.

This latest round - Round 4 - takes this framework to the Amazon EC2 - and is designed to let end-users run the tests on an EC2 node for less than $2 of computing time! This will allow differences in hardware to not make any difference, and allow the users to see how exactly the tests have been configured, tuned and run.

I hope Mule, ServiceMix, JBoss, OpenESB, Petals, BEA/Oracle, IBM, WSO2 and any other ESB vendors I've missed will make use of this opportunity and publicly share the necessary configurations so that we could all rely on an accurate and fair benchmark - which can be verified independently on demand by any end-user!

Hence - unlike in the past, I will refrain from naming any competitor - unless its vendor or the open source project team requests that it be included and the results publicly shared. Thus it is now left to the users of ESBs - to demand the configurations necessary for them to run these tests from vendors - and themselves decide which one to select after executing the test on Amazon EC2. I hope to make this process even simpler at the next round - with a custom AMI and more automation

The Round 4 results includes the three test cases conducted earlier (i.e. Direct Proxy, Content Based Routing [CBR] Proxy, and XSLT Proxy), and adds a new scenario for WS-Security processing.

http://adroitlogic.org/samples-articles-and-tutorials/15-tutorials/48-esb-performance.html