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.

Ansible installation on RedHat RHEL server with RPM packages

Ansible is a great tool for DevOps automation. At AdroitLogic, we recently initiated a drive to create Ansible based installation scripts for all of our enterprise product versions. Now an UltraESB cluster can be setup in a matter of minutes by configuring the inventory for the different components that makes up the system. A post on this will come shortly!

However, today we addressed the issue of installing Ansible on a RHEL machine without Internet access. This is a requirement for one of our customers. Unfortunately the Ansible installation instructions does not include steps on how to install the product with raw RPMs on a hardened system without Internet access.

1. Install the YUM download plugin

[root@emw01 ~]# yum install yum-plugin-downloadonly

2. Make a directory to "capture" the packages

[root@emw01 ~]# mkdir /tmp/ansible


3. Proceed with YUM install with following options

[root@emw01 ~]# yum install --downloadonly --downloaddir=/tmp/ansible/ ansible

4. The above step will download all of the required libraries and components to the directory /tmp/ansible. You can now copy these files onto your target system, and install each of the packages as listed below. Since some of the components may depend on others, you may need to tweak the order of components if you are installing a different version of Ansible on a RHEL version other than 6.6

rpm -ivh libyaml-0.1.3-4.el6_6.x86_64.rpm
rpm -ivh PyYAML-3.10-3.1.el6.x86_64.rpm
rpm -ivh python-babel-0.9.4-5.1.el6.noarch.rpm
rpm -ivh python-crypto-2.0.1-22.el6.x86_64.rpm
rpm -ivh python-crypto2.6-2.6.1-2.el6.x86_64.rpm
rpm -ivh python-pyasn1-0.0.12a-1.el6.noarch.rpm
rpm -ivh python-paramiko-1.7.5-2.1.el6.noarch.rpm
rpm -ivh python-setuptools-0.6.10-3.el6.noarch.rpm
rpm -ivh python-simplejson-2.0.9-3.1.el6.x86_64.rpm
rpm -ivh python-jinja2-2.2.1-2.el6_5.x86_64.rpm
rpm -ivh python-httplib2-0.7.7-1.el6.noarch.rpm
rpm -ivh python-keyczar-0.71c-1.el6.noarch.rpm
rpm -ivh ansible-1.9.2-1.el6.noarch.rpm

Connecting to local Coherence instance with WKA instead of Multicast

I have been trying to setup a test environment for a local Coherence cache implementation for sometime, and the information found via Google was not that helpful in understanding why the client application (in this case the UltraESB) was not connecting to the already created Coherence cluster using WKA, and was trying to create its own cluster with Multicast.

The System properties I passed to the JVM included the following, but the issue was not necessarily on these as I found out with my limited knowledge of Coherence.

The key lesson was - the System properties being correct was not enough for WKA to function properly!

wrapper.java.additional.13=-Dtangosol.coherence.cluster=adrt-cluster
wrapper.java.additional.14=-Dtangosol.coherence.management=all
wrapper.java.additional.15=-Dtangosol.coherence.cacheconfig=coherence/xxxx-cache-config.xml
wrapper.java.additional.16=-Dtangosol.coherence.localhost=127.0.0.1
wrapper.java.additional.17=-Dtangosol.coherence.localport=15000
wrapper.java.additional.18=-Dtangosol.coherence.wka.address=127.0.0.1
wrapper.java.additional.19=-Dtangosol.coherence.wka.port=15000
wrapper.java.additional.20=-Dtangosol.coherence.ttl=0
wrapper.java.additional.21=-Dtangosol.coherence.distributed.localstorage=false

The solution was to create a new file "tangosol-coherence-override.xml" and place it in the classpath (make sure its at the start to get picked up before any others - if any)

<?xml version='1.0'?>
<coherence  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
            xmlns="http://xmlns.oracle.com/coherence/coherence-operational-config"
            xsi:schemaLocation="http://xmlns.oracle.com/coherence/coherence-operational-config coherence-operational-config.xsd">    

  <cluster-config>
    <unicast-listener>
            <well-known-addresses>
                <socket-address id="1">
                        <address>127.0.0.1</address>
                        <port>15000</port>
                </socket-address>
        </well-known-addresses>
        <address>127.0.0.1</address>
        <port>15000</port>
    </unicast-listener>
  </cluster-config>

  <logging-config>
    <severity-level system-property="tangosol.coherence.log.level">9</severity-level>
    <character-limit system-property="tangosol.coherence.log.limit">0</character-limit>
  </logging-config>
</coherence>

If your client "connects" to the WKA cluster you will see the following, and you can notice that it connected to an already existing cluster with a different process

WellKnownAddressList(Size=1,
  WKA{Address=127.0.0.1, Port=15000}
  )
MasterMemberSet(
  ThisMember=Member(Id=3, Timestamp=2015-08-18 12:24:32.153, Address=127.0.0.1:15002, MachineId=60314, Location=site:,machine:localhost,process:5634)
  OldestMember=Member(Id=1, Timestamp=2015-08-18 12:24:07.652, Address=127.0.0.1:15000, MachineId=60314, Location=site:,machine:localhost,process:5366, Role=CoherenceServer)

And if it didn't connect with WKA, and created its own Multicast group you would see the following, where you can also notice that the client you just started is the only member of that new cluster

Group{Address=224.12.1.0, Port=12100, TTL=0}
MasterMemberSet(
  ThisMember=Member(Id=1, Timestamp=2015-08-18 10:25:08.389, Address=127.0.0.1:15002, MachineId=60314, Location=site:,machine:localhost,process:4278)
  OldestMember=Member(Id=1, Timestamp=2015-08-18 10:25:08.389, Address=127.0.0.1:15002, MachineId=60314, Location=site:,machine:localhost,process:4278)

How the UltraESB and AdroitLogic was born..

The UltraESB and AdroitLogic was born 5 years ago today! In my personal blog, I have captured some of the history behind starting this up!



The "personal" blog of Asankha Perera: How the UltraESB and AdroitLogic was born..