Query join with View - Oracle Performance

I had a slow running query that joined on two views, A and B. I had this wrong understanding that the query is slow as it prepares the whole view and then runs the query on the view. This was totally incorrect.

Consider view in a join as a sub-query. The optimizer would try to minimize the cost.In my case the join with view A performed better than the join with view B. Both had approximately same amount of data.

I went through the following articleshttps://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_1https://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_2https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle

And the explain plan proved it all.The join with view A was merged as index joins in the query with base table. The reason being view A had none of this (was a simple select with inner join)

• UNION ALL/UNION view

• Outer-joined view

• Anti-joined view

• Semi-joined view

• DISTINCT view

• GROUP-BY view

While View B had many outer joins and an outer join with another view, C. The outer joins on tables were moved to View pushed predicate.

The optimizer does a view merge for a view with outer join when it is possible to do so.

The join on view C was expensive as it was a HASH join.

Avro - schema change compatibility

I was under the impression that I can add an optional field to Avro schema and consumers with old schema would still be able to read the message whether or not the newly added optional filed is set or not in the record.

So I added an optional field to an Avro schema, generated an Avro from it and I was able to read it from consumers using the old and new schema (this might not work if the schema changes are not at the end).

But this is not how it should work as the Avro schema is not forward compatible. And that is what happened and one of the consumers started to fail.

So I was surprised why my code was working and the other code (using older schema) was throwing errors when reading the avro created out of new schema. This is where it was failing

    BinaryDecoder binaryDecoder = DecoderFactory.get().binaryDecoder(is, null);

    while (!binaryDecoder.isEnd()) {

      DatumReader<MyClass> datumReader = new SpecificDatumReader< MyClass >(MyClass.class);

      datumReader.read(null, binaryDecoder); // failure here

    }

The difference was, I didn't had the while loop in my code, assuming the input stream represented a single record, while the other consumer was handling a batch of requests.

So the above code would read part of the avro byte array as per the old schema in the first iteration and the remaining bytes for the new field (even though it is not set) in the second iteration. Since the left bytes was not a complete record it was reporting errors.

Map.get - KeyNotFound error

It is interesting how different languages approach towards a given problem.

Let's have a look at how different languages react while trying to read a key from dictionary

Java

map.get('key_that_does_not_exist')

=> null

Ruby

map['key_that_does_not_exist']

=> nil 

Python

>> x['key_that_does_not_exist'] Traceback (most recent call last):  File "<stdin>", line 1, in <module> KeyError: 'key_that_does_not_exist' >> x.get('key_that_does_not_exist) => None

Scala

val x = Map("a" -> "1”)

x("key_that_does_not_exist”) // Throws exception

x.get("key_that_does_not_exist”) // None

JDK in OSX

I wanted to figure out the Java installation on OSX so that I can easily switch between JDK 6,7 and 8.

I got my answer from this post http://stackoverflow.com/questions/15120745/need-help-understanding-oracles-java-on-mac

In summary

When we install Java using a dmg it goes here /Library/Java/JavaVirtualMachines/

This command is handy to figure this out

$ /usr/libexec/java_home 

/Library/Java/JavaVirtualMachines/jdk1.8.0_31.jdk/Contents/Home

java_home man
https://developer.apple.com/library/mac/documentation/Darwin/Reference/ManPages/man1/java_home.1.html

Let's see how this gets connected with java command.

$ java -version

java version "1.8.0_31"

Java(TM) SE Runtime Environment (build 1.8.0_31-b13)

Java HotSpot(TM) 64-Bit Server VM (build 25.31-b07, mixed mode)

$ which java

/usr/bin/java

$ ls -al /usr/bin/java

lrwxr-xr-x  1 root  wheel  74 Apr  2  2014 /usr/bin/java -> /System/Library/Frameworks/JavaVM.framework/Versions/Current/Commands/java

</pre >

/System/Library/Frameworks/JavaVM.framework/Versions/Current/Commands/java is actually a proxy to actual installation of java!

Let's see where does this proxy end up

$ sudo dtrace -n 'syscall::posix_spawn:entry { trace(copyinstr(arg1)); }' -c "java -version"

dtrace: description 'syscall::posix_spawn:entry ' matched 1 probe

dtrace: pid 19903 has exited

CPU     ID                    FUNCTION:NAME

  0    638                posix_spawn:entry   /Library/Java/JavaVirtualMachines/jdk1.8.0_31.jdk/Contents/Home/bin/java

from the post

A combination of factors are considered. JAVA_HOME is used if set (try JAVA_HOME=/tmp java). If JAVA_HOME is not set then the list of all virtual machines on the system is discovered. The JAVA_VERSION and JAVA_ARCH environment variables are used, if set, to filter the list of virtual machines to a particular version and supported architecture. The resulting list is then sorted by architecture (preferring 64-bit over 32-bit) and version (newer is better), and the best match is returned.

The proxy is intelligent enough to find the Java installation and java will work for you. But what if you want to switch between multiple JDKs. Simple, pass the instruction through JAVA_HOME. export JAVA_HOME=/Library/Java/JavaVirtualMachines/jdk1.7.0_71.jdk/Contents/Home

No need to add to the PATH :) 

I love this proxy concept. An Apple A Day, Keeps lot of worries Away

eclipse

Adding more support to your eclipse (say you have only Java support and you want to add web support)

1. Download eclipse with Web support
2. Or, add required module through Help - Install New Software - http://download.eclipse.org/releases/luna (your release)

Extract the downloaded zip/tar. Run ./eclipse or double click the Eclipse App from inside the downloaded folder. Eclipse needs java in path or JAVA_HOME set.

Plugins are stored inside the downloaded eclipse directory. eclipse.ini path <downloaded eclipse directory>/Eclipse.app/Contents/MacOS/eclipse.ini. So they work across workspaces.

The preferences are per workspace so they are installed inside workspace. e.g. Run configurations and editor preferences. There are ways to keep them common across workspaces.

Eclipse Java and Configurations

Update the eclipse.ini as per your need.

AspectJ Support

Add plugins from https://eclipse.org/ajdt/downloads/

AspectJ Compiler and AspectJ Development Tools

Maven Support

Add m2e connectors from m2e marketplace

Open pom file. Overview section would show error. Click on the error - Click discover new m2e connectors

Maven Integration for AJDT, m2 connector for build-helper-maven-plugin

eclipse and maven

I have become a fan of Maven (there is no surprise to it).

Download and install instructions (bottom of the page) on this page http://maven.apache.org/download.cgi. It is better to use the zip/gz and set the appropriate env than using the yum installation.

Default repository location - ~/.m2
Settings.xml - ls $M2_HOME/conf

Maven eclipse integration - http://maven.apache.org/eclipse-plugin.html
It comes with the eclipse j2ee installation (check if you have import maven project option. If not install the plugin)

Now let's configure Eclipse to use the maven we downloaded (so that we use same maven and same settings as used from console)

Preferences - Maven

1. Installations: Click Add and point to the extracted maven directory. Make it default. Eclipse comes with embedded maven and that will be used otherwise.
2. User Settings - Point to the settings.xml that you wish to into the Global and User Setting. You can point to the one that comes default with the maven package, use a company specific one or your custom one.
3. Make sure the Local Repository is same as you expect. (matches what you intended from the console)
You can look into other settings but with this we are good to go.

To import a maven project into eclipse. File - Import - Existing maven project. Now eclipse honors your project POM file for all project lifecycle options (build, deploy). Update to POM file automatically reflect in the project. To manually perform this task. Right click Project - Maven - Update project

Create a new Maven project is. New -> Maven project

Update the Java version for your Maven project

  <build>

    <plugins>

      <plugin>

        <groupId>org.apache.maven.plugins</groupId>

        <artifactId>maven-compiler-plugin</artifactId>

        <version>3.2</version>

        <configuration>

          <source>${java.version}</source>

          <target>${java.version}</target>

        </configuration>

      </plugin>

    </plugins>

  </build>

Define java.version property with the version you want. The connector automatically associates to the installed jres (in your eclipse) to the version you specified.

e.g. Say you have Jdk 1.7 and 1.8 installed and added to your eclipse.

java.version = 1.7 - associates jdk 1.7 to the project

java.version = 1.8 - associates jdk 1.8 to the project

java.version = 1.6 - associates jdk 1.7 to the project

we can have source and target at different version (source always at higher). Say we want to write code in 1.7 which will run on a 1.6 jre installation (you are under migration but your servers are still at 1.6). This will keep the JRE at 1.7 but set the compiler to 1.6.

Some of the important aspects to consider

1. Scope in a dependency - compile, runtime, provided, test, ..
2. Maven life cycles
3. Sub modules
4. Optional is a valuable keyword for dependency if you are creating a library. This will help users to ignore that dependency add their own. Say, a newer version of Logger.

'maven deploy' pushes your build artifact to the nexus repository (you have to specify repository in your maven file or it can be present in the setting.xml (top pom))

When you specify a dependency in your pom three things might happen (Maven dependencies has the references)

1. if the project is in the same workspace (uses the project directly)
2. If the project is not present it looks into the local repository (~/.m2/repository)
3. If not found in the local repository it will download from nexus and add to local repository

# My build does not pick changes from a dependent jar

The third one is a tricky one (there are configurations to play with how the dependency is resolved). Suppose you change API but have not updated the version of the artifact. When you build this project and deploy the nexus is updated with the latest changes. Now a dependent project does a maven build but still finds the old jar. This is due to the local repository already having the jar from previous build. So it is always recommended to update the artifact version.

Exception - How to manage different exceptions your code wants to throw?

Often I have seen multiple Exception types created to handle different types of exceptions. In Java they all become individual classes. They might all extend from a common Exception class defined by you. But as Java does not support inheritance of constructors we end up having multiple classes with similar code. Not if it was Ruby code :)

Anyways. I got the following thought from the way Http defines Status Codes.

http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html

Define broad level Exception classes.

Informational, Success, Bad Request, Server Error. You may or may not have Redirection. In each of these Exception classes have status code. If possible, try to keep the status codes matching to the http status codes.

Now you have limited set of classes and you can set specific status code based on a scenario.

class InformationalException {
  public static enum InformationalStatus {
     // you have avoid it by using directly http status from commons client
  }

  public InformationalException(InformationalStatus status, String message, Throwable th) {
  }
}

The good news is most of the clients understand the http codes and it is no longer a magic.

Less code is less pain.