How to check what indexes are used in query for Oracle

Here I'm going to describe how to get execution plan result for specific query. For example we have a table with test data:

CREATE TABLE indexed_data (
  id NUMBER(19,0) NOT NULL CONSTRAINT indexed_data_pk PRIMARY KEY,
  data NUMBER(19,0) NOT NULL
);
CREATE INDEX indexed_data_data_inx ON indexed_data (
  data
) NOLOGGING;

insert into indexed_data(id, data) values (1, 1);
insert into indexed_data(id, data) values (2, 2);
insert into indexed_data(id, data) values (3, 3);

And want to know is indexed_data_data_inx index used in query:

SELECT * from indexed_data WHERE data = 0;

This two queries will helps you:

EXPLAIN PLAN
SET statement_id = 'indexed_data_ex_plan' FOR
SELECT * from indexed_data WHERE data = 0;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'indexed_data_ex_plan','BASIC'));

As result you will get:

Plan hash value: 1246571312

-------------------------------------------------------------
| Id  | Operation                                                | Name                                           |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                                                      |
|   1 |  TABLE ACCESS BY INDEX ROWID | INDEXED_DATA                      |
|   2 |   INDEX RANGE SCAN                        | INDEXED_DATA_DATA_INX |
-------------------------------------------------------------

String comparison in java

Comparison is the second common operation with strings in java. Let's find out which of this four functions are faster than other:

1. String.intern()
2. String.equals()
3. String.equalsIgnoreCase()
4. String.compareTo()

I decide to use in my benchmark array of 1000 random strings and compare elements from first path (0..499) with elements from second path (500..999):

  @Setup
    public void prepare() {
        testStringsPool = new String[1000];
        for (int i = 0; i < testStringsPool.length; i++) {
            int customLength = rnd.nextInt();
            if (customLength < 0) {
                customLength *= -1;
            }
            testStringsPool[i] = randomString(customLength % 20 + 10);
        }
    }

Here are my benchmark functions without annotations:

    public void intern_() {
        for (int i = 0; i < testStringsPool.length / 2; i++) {
            if (testStringsPool[i].intern() ==
                testStringsPool[testStringsPool.length - i – 1].intern());
        }
    }

    public void equals_() {
        for (int i = 0; i < testStringsPool.length / 2; i++) {
            if (testStringsPool[i].equals(
                testStringsPool[testStringsPool.length - i – 1]));
        }
    }

    public void compareTo_() {
        for (int i = 0; i < testStringsPool.length / 2; i++) {
            if (testStringsPool[i].compareTo(
                   testStringsPool[testStringsPool.length - i - 1]) == 0);
        }
    }

    public void equalsIgnoreCase_() {
        for (int i = 0; i < testStringsPool.length / 2; i++) {
            If(testStringsPool[i].equalsIgnoreCase(
                 testStringsPool[testStringsPool.length - i - 1]));
        }
    }

When we run those benchmark tests, we get something similar like this:

# Run complete. Total time: 00:00:25

Benchmark                                    Mode  Samples    Score   Error  Units
t.StringOpts.compareTo_                avgt        1          0.270 ±   NaN  us/op
t.StringOpts.equalsIgnoreCase_    avgt        1          1.323 ±   NaN  us/op
t.StringOpts.equals_                       avgt        1          0.348 ±   NaN  us/op
t.StringOpts.intern_                        avgt        1       148.612 ±   NaN  us/op


The winner is String.compareTo function

Conclusion for string comparison:

1. compareTo – is the fastest because it operates with parameter of String class without additional checking for type safety
2. equals – a bit slower by checking input parameter for the same type (String)
3. equalsIgnoreCase – more slower because all the characters are converted to uppercase in both strings
4. intern – the slowest. But when you need to work with many identical strings it can help you to reduce memory usage.

String concatenation in Java

What is the most commonly used class in java projects?

We use jmap tool from jdk to find the answer.

jmap -histo | head -n 14

Where - java application process id (I have used pid of running tomcat server)

The output of this command is:

num     #instances         #bytes  class name

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

   1:         43101       18737736  [B

   2:         61736       10054912  [C

   3:         11598        5165024  [I

   4:         58901        1413624  java.lang.String

   5:          8514         749232  java.lang.reflect.Method

   6:         21313         682016  java.util.HashMap$Node

   7:          5286         549960  java.lang.Class

   8:          7914         483944  [Ljava.lang.Object;

   9:          9323         372920  java.util.HashMap$ValueIterator

  10:          1814         320184  [Ljava.util.HashMap$Node;

  11:          7862         314480  java.lang.ref.Finalizer

As we can see String is one of the common used classes in java projects and takes a lot of memory. The most common operations performed with strings is concatenation.

There are three variants of string concatenation:

1. String a = “Hello ” + “world”; 

2. String b = new StringBuffer();
    b.append(“Hello ”);
    b.append(“world”).toString(); 

3. String c = “Hello ”.concat(“world”);

Let's look to them closer and try to compare. There are two common possibilities to concatenate strings using '+' sign:

1. String s1 = “STRING_VAL1” + “STRING_VAL2”;

2. String s2 = “STRING_VAL1” + STRING_VARIABLE;

We need to view generated bytecode by javap from jdk to compare them:

javap -c SomeJava.class

For the first expression it looks like this. 

Source:

String q = "Hello " + "world";

Bytecode:

  public static void main(java.lang.String[]) throws java.lang.Exception;

    Code:

       0: ldc           #3                  // String Hello world

       2: astore_1

       3: return

For the second expression.

Source:

String hello = "Hello ";

String result = hello + "world";

Bytecode:

  public static void main(java.lang.String[]) throws java.lang.Exception;

    Code:

       0: ldc           #3                  // String Hello

       2: astore_1

       3: new           #4                  // class java/lang/StringBuilder

       6: dup

       7: invokespecial #5                  // Method java/lang/StringBuilder."":()V

      10: aload_1

      11: invokevirtual #6                  // Method java/lang/StringBuilder.append

      14: ldc           #7                  // String world

      16: invokevirtual #6                  // Method java/lang/StringBuilder.append

      19: invokevirtual #8                  // Method java/lang/StringBuilder.toString

      22: astore_2

      23: return 

If you try to concatenate two or more constant strings then java compiler do this operation in compile time:

String s = “Hello ” + “world” + “...”;

In other cases will be used explicitly or implicitly StringBuilder and String.concat() method. StringBuffer is also possible for multithreading applications, but we examine only first two in bold. Let's decide which of them better

We will create two microbenchmark tests to find out which is better StringBuilder or String.concat:

@State(Scope.Thread)

@OutputTimeUnit(TimeUnit.NANOSECONDS)

public class StringOpts {

    @Benchmark

    @BenchmarkMode({Mode.AverageTime})

    @OutputTimeUnit(TimeUnit.MICROSECONDS)

    public void concat_() {

        String s1 = "Hello";

        String s2 = s1.concat(" world");

    }

    @Benchmark

    @BenchmarkMode({Mode.AverageTime})

    @OutputTimeUnit(TimeUnit.MICROSECONDS)

    public void append_() {

        StringBuilder s1 = new StringBuilder("Hello");

        String s2 = s1.append(" world").toString();

    }

}

When benchmark had been finished we got such result:

# Run complete. Total time: 00:00:12

Benchmark               Mode  Samples  Score   Error  Units

t.StringOpts.append_    avgt        1  0.007 ±   NaN  us/op

t.StringOpts.concat_    avgt        1  0.010 ±   NaN  us/op

Where you can see that StringBuilder.append is faster than String.concat function. When you need concatenate only two strings in some cases you can prefer String.concat because this generates less objects than when you use StringBuilder.

JMS (from my old report)

You can see original slides in http://www.slideshare.net/mybbslides/jms-14767911

General concepts

Queues

Topics

Message types

JMS specification provides standard implementations of javax.jms.Message:

1. javax.jms.BytesMessage - array of bytes

2. javax.jms.MapMessage - key-value pairs

3. javax.jms.ObjectMessage - serialized Java object

4. javax.jms.StreamMessage - stream of Java primitive values

5. javax.jms.TextMessage - Java String object (used for XML)

JMS working algorithm

1. Obtain a javax.jms.ConnectionFactory using JNDI lookup. (ConnectionFactory
names are different depending on JMS provider.)
2. Obtain a javax.jms.Connection from the ConnectionFactory.
3. Obtain a javax.jms.Session from the Connection.
4. Create a javax.jms.MessageProducer or javax.jms.MessageConsumer from the Session.
5. Send a message for the MessageProducer, or receive a message for the MessageConsumer
(synchronous) or set a message listener (asynchronous).
6. Start the Connection to start message delivery.
7. Finally close the Connection.

Synchronous messaging

while(true) {
    Message message = consumer.receive();
    ....
}

Asynchronous messaging (listener class)

class AsyncConsumerMessageListener implements MessageListener {
    public void onMessage(Message message) {
        TextMessage msg = (TextMessage) message;
        ....
    }
}

Asynchronous messaging (listener class usage)

AsyncConsumerMessageListener asyncConsumer = new AsyncConsumerMessageListener();
MessageConsumer consumer = session.createConsumer(destination);
consumer.setMessageListener(asyncConsumer);

Spring amqConnectionFactory

<bean class="org.apache.activemq.ActiveMQConnectionFactory" id="amqConnectionFactory">
    <property name="brokerURL" value="${jms.url}"/>
</bean>


1) jms.url=tcp://localhost:61616
2) jms.url=vm://localhost

Spring jmsQueueConnectionFactory

<bean id="jmsQueueConnectionFactory" class="org.springframework.jms.connection.SingleConnectionFactory">
    <property name="targetConnectionFactory" ref="amqConnectionFactory"/>
</bean>

Spring listener-container

<jms:listener-container connection-factory="jmsQueueConnectionFactory" destination-type="queue" container-type="default">
    <jms:listener destination="TESTQUEUE" method="onMessage" ref="webConsumer"/>
</jms:listener-container>

Spring jmsTemplate

<bean class="org.springframework.jms.core.JmsTemplate" id="jmsTemplate">
    <constructor-arg ref="jmsQueueConnectionFactory"/>
</bean>

Spring message listener

@Component
public class WebConsumer implements MessageListener
{
    public void onMessage( final Message message ) {...}
}

Spring producer

@Component
public class WebProducer
{
    @Autowired
    private JmsTemplate jmsTemplate;

    public void send(String message) throws JMSException
    {
        jmsTemplate.convertAndSend("TESTQUEUE", message);
    }
}

Sources and useful links

1. Official JMS tutorial book
2. http://www.thirdeyeconsulting.com/indyjug/jms/jms.html
3. http://www2.sys-con.com/itsg/virtualcd/Java/archives/0604/chappell/index.html

Design patterns in Spring framework

 1. Creational Patterns

Singleton – this is the most common and simple design pattern. Pattern consists  with one class that is responsible for creation one instance of itself. Beans are defined in spring config xml files. Method getBean(beanName) returns one instance of predefined bean object only. Spring framework implements main idea of this pattern only. In singleton pattern class must control that only one instance of it will be created unlike in spring framework it implemented as part of bean scoping. By default all beans in spring are singletons. This can be changed by using scope attribute.

Example:
<bean class="com.blogspot.mvnblogbuild.SingletonBeanExample" id="singletonBeanExample" scope="singleton"></bean>

Prototype – duplicate is created of current object in this pattern. In spring framework this pattern implements as part of bean scoping like singleton pattern.

Example:
<bean class="com.blogspot.mvnblogbuild.PrototypeBeanExample" id="prototypeBeanExample" scope="prototype"></bean>

Factory – this pattern creates set of objects with a common parent class or interface without sharing creation logic  to the client code. Spring uses factory pattern to create objects of beans using BeanFactory child classes.

XmlWebApplicationContext and ClassPathXmlApplicationContext classes are implement ApplicationContext which is extends BeanFactory. Both those classes are factories that return user defined beans.


Builder - builder design pattern is allow to create instances of comlex class by specifying interface for creating parts of product object. Spring provides programmatic means of constructing BeanDefinitions using the builder pattern through Class "BeanDefinitionBuilder".

2. Behavioural Patterns

Template method – is used extensively to deal with boilerplate repeated code. For example JdbcTemplate, JmsTemplate, JpaTemplate.

This pattern is about defining the skeleton of the algorithm in which some of the steps of the algorithm are deferred to the sub-classes or to the data member objects.

Mediator – to reduce communications between many objects this pattern provide one class which handle all this communications. Spring framework provide DispatcherServlet class.

Strategy - in this pattern behavior of program can be changed in runtime by choosing one of the algoritms

Observer - this pattern is used when you need to notify set of objects in one to many relationship. Where observer objects waiting for events from observable object.

3. Structural Patterns

Proxy – This pattern is useful to control the access to an object and expose the same interface as that of that object. Spring framework implements this pattern in its AOP support.

4. J2EE Patterns

Model View Controller . The advantage with Spring MVC is that your controllers are POJOs as opposed to being servlets. This makes for easier testing of controllers. One thing to note is that the controller is only required to return a logical view name, and the view selection is left to a separate ViewResolver. This makes it easier to reuse controllers for different view technologies.

Front Controller. Spring framework implements this pattern by DispatcherServlet to ensure an incoming request gets dispatched to your controllers.

View Helper - Spring has a number of custom JSP tags, and velocity macros, to assist in separating code from presentation in views.

  

Oracle database tips&tricks

Here I am going to collect useful queries for Oracle database.

If you use many database users and need to check which of them are locked:

select username, ACCOUNT_STATUS from dba_users where username in ('DB_USER1', 'DB_USER2', 'DB_USER3', ....);

User with EXPIRED or LOCKED status can reactivated back to OPEN status by:

alter user DB_USER1 account unlock;

To prevent account expiration in future - you can set unlimited password time frame for all users with default profile:

alter profile default limit password_life_time_unlimited;

My new app - WallPaperGirls - Corset and spring-like ads management

Image for more interest :)

This is my new pet project that I started to develop month ago. First version include nearly 50 images and viewer application. I described here http://mvnblogbuild.blogspot.com/2013/04/android-bitmap-and-outofmemoryerror.html how I  fought with android memory manager. Here I tell you about ads management in this application. On my regular work I use spring framework. That's why I decided to create the same mechanism for ad rotation in app.
For each ad network was created separate class. Settings was stored in class annotations. For example annotation for class is looks like this:

import java.lang.annotation.*;

@Target(value=ElementType.TYPE)

@Retention(value= RetentionPolicy.RUNTIME)

public @interface AdEntityMeta {

int runFrequency() default 0;

String name();

}

Sample ad class with AdEntityMeta:

@AdEntityMeta(name = "SampleAdNetworkName")

public class SampleAdNetwork {...}

 As you see we set the name of class to SampleAdNetworkName and leave runFrequency property to default 0 value. In manager class I use reflection for processing ad network classes. It iterate by classes and check annotation existence by calling:

Class adCl = adObj.getClass();

if(adCl.isAnnotationPresent(AdEntityMeta.class)) {...}

If annotation exists then we can get parameters from it:

 AdEntityMeta adEntityMeta = adCl.getAnnotation(AdEntityMeta.class);

Initialization method marked by AdEntityInitMeta annotation. To process it I use this code:

Method[] method = adCl.getMethods();

for(Method md: method) {

if(md.isAnnotationPresent(AdEntityInitMeta.class)) {

AdEntityInitMeta adEntityInitMeta = md.getAnnotation(AdEntityInitMeta.class);

if (adEntityInitMeta.initType() == AdEntityLifecycleType.APPLICATION_INIT) {

Class[] methodParamTypes = md.getParameterTypes();

Object[] methodArgs = new Object[methodParamTypes.length];

for (int i=0; i

if (methodParamTypes[i] == Activity.class) {

methodArgs[i] = this.baseActivity;

}

}

try {

md.invoke(adObj, methodArgs);

} catch (Exception e) {

e.printStackTrace();

}

}

}

}

Here program enumerates similarly all methods from class and checks method marked by AdEntityInitMeta annotation. All input method parameters are stored in methodArgs. In this code was allowed only parameter with type Activity or inherited from it.

If you are interested - here is my app: https://play.google.com/store/apps/details?id=com.blogspot.mvnblogbuild.wallpapergirls.corsets