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

Android Bitmap and OutOfMemoryError

In this post I am going to collect tips and tricks to help developer avoid OutOfMemoryError.

In code below I use this function to suspend current thread for some time:

public static void sleep(long time) {

  try {

    Thread.sleep(time);

  } catch (Exception e) {}

}

First of all let's talk about bitmap loading. Imagine you must load whole image with width 10x for imageview control with width x. Control don't need full size original image for good quality. Also if you load it as is then bitmap allocate too much memory. (If image configuration is ARGB_8888 then 4 bytes were allocated for every pixel). If we load scaled image with width x this will be enough. In first step we read only image size without loading content:

BitmapFactory.Options options=new BitmapFactory.Options();

options.inJustDecodeBounds = true;

BitmapFactory.decodeResource(context.getResources(),     R.drawable.sample_image, options);

Now options variable contain width and height of image. I scale image by control width. BitmapFactory.Options have inSampleSize property. This property used to return smaller image to save memory. The documentation mentions using values that are a power of 2. Result code looks like this:

int scale = 1;

BitmapFactory.Options options = new BitmapFactory.Options();

options.inJustDecodeBounds = true;

BitmapFactory.decodeResource(context.getResources(), resouceId, options);

int bmpWidth = options.outWidth;

if (bmpWidth > destWidth) {

  do {

    bmpWidth /= 2;

    if (bmpWidth > destWidth) {

      scale *= 2;

    }

  } while (bmpWidth > destWidth);

}

options.inJustDecodeBounds = false;

options.inSampleSize = scale;

Bitmap image = BitmapFactory.decodeResource(context.getResources(), resouceId, options);

Here we multiply scale variable by 2 and divide image width while it greater then destWidth. We get image with minimal size and good quality for display in control with destWidth width.

Let's consider a simple situation. We need to load bitmap, process it and destroy:

Bitmap image = ... // Load image

// Process image content

image.recycle();

sleep(100);

image = null;

System.gc();

sleep(100);

recycle function free up memoty associated with image. sleep function here take time for GC.


If you display large image in activity and need to free resources after activity was closed:

@Override

public void onCreate(Bundle savedInstanceState) {

  ...

  imageView = (ImageView) findViewById(R.id.full_image_view);

  imageView.setImageBitmap(...);

  ...

}

@Override

protected void onDestroy() {

  ((BitmapDrawable)imageView.getDrawable()).getBitmap().recycle();

  sleep(100);

  super.onDestroy();

  System.gc();

  sleep(100);

}

Upd: If image quality is not important to you:

BitmapFactory.Options options = new BitmapFactory.Options();

options.inPreferredConfig = Bitmap.Config.RGB_565;

This value provide normal image quality and any pixel is stored in 2 bytes. If inPreferredConfig is null and decoder try to pick image with ARGB_8888 then one pixel is stored in 4 bytes in memory. For large images this will increase memory usage.

One more way to avoid Access-Control-Allow-Origin restriction for cross domain ajax requests

Some time ago I developed the application based on WebKit engine. It allows user to process page elements by injecting javascript code into original third party site source. Let's call it js tool. First version of it interacted with my site by sending ajax reqests. Everything works fine before I try to test it on secure connection to the site which we call SecureSite.

When I loaded a page from this site - my tool was not working. WebKit web inspector showed 1 error:

XMLHttpRequest cannot load http://localhost:8080/wsapi/util/uploadpagesrc. Origin https://some.secure.domain is not allowed by Access-Control-Allow-Origin.

SecureSite in his response headers sended Access-Control-Allow-Origin which block ajax request to my localhost site. In forums I found solution to use jsonp request. Here is example:

$(document).ready(function() {
 $.getJSON('http://twitter.com/users/usejquery.json?callback=?', function(json) {
 $('#twitter_followers').text(json.followers_count);
 });
});

But I need to send post requests and ability to save large data. In my js tool is used websockets. When ajax requests throws errors websockets works fine. They have only one minus - message size limitation. For cometd it was 8192:

I have not configured websocket server to increase this limit. Maybe I will do this another time

Android. Problem in database access from service

Few months ago I worked on my first android application. I use AlarmManager for work in background and call from it my intents receiver. Here the code:

public static final int FIRST_RUN = 5000; // 5 seconds
public static final int INTERVAL = 30000; // 30 sec
private AlarmManager alarmManager = null;
....
Intent intent = new Intent(this, FingerNotesServiceReceiver.class);
PendingIntent pendingIntent = PendingIntent.getBroadcast(this, 0, intent, 0);
this.alarmManager = (AlarmManager) getSystemService(ALARM_SERVICE);
this.alarmManager.setRepeating(AlarmManager.ELAPSED_REALTIME_WAKEUP,
SystemClock.elapsedRealtime() + FIRST_RUN, INTERVAL,
pendingIntent);

FingerNotesServiceReceiver - this is my receiver class. It extends BroadcastReceiver class. Everything looks fine, but there are one problem. If you try to open connection to sqlite database file with write permissions from your receiver you will catch the error described below:

android.database.sqlite.DatabaseObjectNotClosedException: Application did not close the cursor or database object that was opened here

Sqlite don't allow you to create more than one write connections to db file. To prevent this in main process of application you can use static instance of SQLiteOpenHelper. But receiver still throws this error. This happens, because onReceive functions calls on separate process. When you try to gain access to database from receiver - application creates new static SQLiteOpenHelper object. Because existing object is staying in main process. To solve this I was add ContentProvider into application with needed functionality. Receiver interact with database through provider and code looks like I send queries to sqlite directly. As a result I got this scheme for application modules:

You can check my app on google play: FingerNotes

Git pocket tutorial

My first post about git repository.

At the beginning we must initialize out repo. Let's say it have tempo name. For now we guess that repo was created on git server and we only must create a local copy on our development machine. First of all we must install git client. Further i will talk about linux console version of client. On the next step we must initialize our local repo:

1. git init
2. git add <file1> <file2> ...
3. git commit -m "init commit"
4. git remote add origin git@remoterepo.host.addr:username/path/to/tempo.git
5. git push -u origin master

General format for git remote add(4) is:

git remote add remote_name remote_location

Also we have git clone with similar format:

git clone remote_location

You can ask, what the difference between "remote add" and "close" and i answer you:

1. remote add - creates an entry in your git config and you can either push changes to remote repo
2. clone - creates new repo (local copy from remote_location). It similar to this code:

git init

git remote add origin remote_location

git pull origin master

Now let's talk about work with branches. First of all you need to create one by command:

git branch

After that you can view branches:

git branch - show your local branches

git branch -a - showing all branches including remote ones

When our new branch was founded in list - we can switch to it:

git checkout <branchname>

After work on it - we can remove it

# delete local

git branch -d <branchname>

# remote

git push origin :<branchname>

We can push changes to remote branch after committing:

git push origin <branchname>:refs/heads/<branchname>

When work on branch was finished - it can pushed to master:

git checkout master

git merge <branchname> --no-ff

git push origin master - push master branch to remote repo

If you need update code from remote repo:

git remote update; - update all remote repos

git pull <remoterepo> <local>