Oracle - Execution Plans , Explain Plans, AWR Reports, Tuning - A handy Guide

There is an inherent different between explain plans and execution plans , and I almost got confused that they were the same.

In order to capture an execution plan , we need to follow the steps:

The user who will be running the sql for which you want the execution plan , should have permissions to alter session in order to enable trace.(This is not mandatory , if you can run the sqls via the user who has permissions then that is also fine)

Grant permission:

grant alter session to ;
revoke alter session from ;

Enable SQL TRACING

To enable Trace run the following DCL:

ALTER SESSION SET sql_trace = true;

To specify trace file identifier which helps to locate specific trace files easily

ALTER SESSION SETtracefile_identifier =tracing_example;

To specify the trace destination other than the oracle default , we could perform below


ALTER SESSION SET user_dump_dest=

Default vaues for the various parameters above can also be found using TOAD->Database->Administer->Parameters

Explain Plan

Using toad , this can be achieved by following the steps below:

1.  Connect to the Oracle SID
2. Open a SQL editor, and write the SQL query for which the explain plan is required.
3. CTRL+E will produce the explain plan for the query - which basically means , this is the most likely path oracle will chose while executing the SQL. 
4. Analyze the cost of the query , and identify the areas which are causing the cost to grow high.
5. Mostly this happens when full table access is performed, or hashed joins are used , instead of full index scans and nested loops.
6. This is the fastest way to identify if a query you have written has some tuning gaps and can be rewritten to perform better in distributed and scalable high volume environments.

Enable SNAPSHOTS

By enabling snapshots we will be able to capture the Oracle activity between regular periods identified by snapshot ids. To view the current snapshot interval we can run the following:

select * from DBA_HIST_WR_CONTROL

To enable snapshots for every 15 minutes , we can run the following procedure. We can change the snapshot interval based on approximately how much time would a process(which we are interested in profiling / tuning ) takes to run .

BEGIN dbms_workload_repository.modify_snapshot_settings(interval => 15, retention => null); END;

Generate AWR Report

An AWR report captures all Oracle activity between given snapshots. This can be helpful to monitor the queries which are taking longer than expected and also identify tuning paramters.

To generate an AWR report we can perform the following steps:

1. Invoke @awrrpt.sql from ..\oracle\product\\dbhome_1\RDBMS\ADMIN
2. Choose report type as html
3. Provide number of days you want the report to span for snapshot ids
4. Provide the snapshots which we want to capture in our report. 
5. Provide a valid report name.
6. AWR report is generated in the ..\oracle\product\\dbhome_1\RDBMS\ADMIN folder with the name provided.

Many of the above options can be chosen as default as well.

Capturing SQL ID

If we do not have a AWR report , but still want to identify the the SQL id based on the query content the following can be used:

SELECT sql_id, child_number, substr(sql_text, 1, 300) sqltxt from v$sql WHERE sql_text LIKE %%' ;

Also the various execution plans used by oracle for this SQL can be generated using:

set linesize 150

set pagesize 2000

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('SQL_ID'));

Execution Plan

Execution plan on the other hand is the actual path /plan used by oracle to execute a query.

To extract an execution plan, you will have to perform a few more steps as below:

1. Enable snapshots for the SID which you will be executing the queries from.Set the snapshot interval based on your specific enviroment , sql process running
2. Execute the SQL procedure, process or DBMS transaction which you want to tune.
3. Generate AWR report for the snapsots we are interested in.
4. Browse the sql statistics to identify which queries are taking up the most time , in terms of execution , io waits , cpu etc
5. Each SQL will have a unique SQL_ID , capture that.
6. Invoke @awrsqrpt.sql from ..\oracle\product\\dbhome_1\RDBMS\ADMIN
7. Provide the sql id for which we want to generate the execution plan
8. Provide snapshot ids
9. Provide a name for the plan report
10. Execution plan is generated in the ..\oracle\product\\dbhome_1\RDBMS\ADMIN folder with the name provided.

This plan will provide the exact path , and can be used to identify whats causing the costs for SQL runtime to go high.

Tuning Options:

• Disable tracing
• Disable all SQL profilers
• Perform Database connections monitoring - if connections are being returned once transactions complete
• reduce redo logs
• add hints wherever costs of queries are more due to joins and full table scans
• add appropriate indexes 
• Check Init.ORA for Oracle settings ,and modify based on the needs
• The list if huge .......

Generating Java Heap Dumps on the go !

Ever come across a stituation where you need to take a look at the heap dumps , as to whats eating up the memory ?

Well depends which version of JDK u r on. The couple I know of are:

1. jmap - a very old command which is now un supported . If u r using old versions of JDK prior to 1.5 this command is handy.
2. jconsole - you can aquire the heap dump using the hotspotdiagnostics using the javax Mbeans. For jconsole to recognize the process , we should start the java process with the following :

-Dcom.sun.management.jmxremote.port=<PORT>   

-Dcom.sun.management.jmxremote.authenticate=false 

-Dcom.sun.management.jmxremote.ssl=false

1. jrmc - jrockit mission control - If you are on the latest versions of JDK from jrockit , this is an ultra cool utility provided to be able to diagnose and monitor and profile your jvm. Go to the Advanced ->Diagnose->heapdumpprof util and generate the heap dump at the desired location.
2. When you startup the jvm add the arguments  -XX:+|-HeapDumpOnOutOfMemoryError and                                 -XX:HeapDumpPath=<Location where the dump should be written to>
3. Alternatively if you are  using Jrockit you can provide the following startup arguments                                   -Djrockit.oomdiagnostics=true and  -Djrockit.oomdiagnostics.filename=d:\heapdump.log
4. You could also monitor the memory using visualvm provided in latest jdks which give you better insight on the young heap and the old heap.
5. Eclipse memory analyzer can also be used to generate a heap dump and also view the dum generated by any of the above processes. This is very helpful m when you want to identify memory leaks etc
6. 
   

        The dump thus generated usually can is a log file which you can read or a hprof file which can be analysed using a memory analyzer(eclipse plugin http://www.eclipse.org/mat/downloads.php).

       

Web Services - FAQs

Note: This is how I understand Web services. I have mostly worked on SOAP based implementations of Web services which use ESB to transport the messages back and forth between the provider and the consumer. The SOAP contract is available to understand how to make use of a web service. I have used XML Beans, AXIS, Castor in the process.

My knowledge on REST is limited at this stage.

Q: What exactly is a web service. ?
A: A web service is a service provided by an application , such that it can be used by applications(internal , external) with minimal coupling. An example could be rss feeds.
Where in a newspaper provides all its article in the RSS format , for other websites to include in their web apps.

Q: When should a functionality be exposed as a webservice ?
A: When a functionality could be useful to other applications , and the functionality needs to be available out of the box (language independent , platform independent) . With the only constraint being XML as a medium of communication.

Q: What ways can a webservice be consumed ?
A: Interestingly this depends , on how the service provider expects the request to be.

• RPC - tight coupling exists , message broker , stub , binding , registration, lookup etc are terms often used when we speak about RPC , similar to Java RPC/CORBA. More Service Oriented.
  
• SOAP - Communication based on XML written in specific formats as defined is service XSD's and invoke services as defined in the WSDL. Message Oriented.
  
• REST - Communication based on XML over Http but using specific set of functions like GET , POST etc.
  

Q:What exactly is the wsdl used for in a webservice ?
A: It basically is a document to describe which functionalities are exposed for consumption by the client.

A: What exactly is a contract in terms of webservice ?
A: Its is a document to describe how to invoke a web service , what are the inputs and the output formats applicable and also if a client is registered to use this service(public or private)

Q: What are the types Webservice implementations available in java?
A: CORBA, Restlets etc

Q:What are the various marshalling/ unmarshalling frameworks available in Java?
A: Castor, XMLBeans , JIBX etc

Q: What is an ESB ?
A: An enterprise service bus acts as a interface to many systems which communicate with each other. The systems communicate to the ESB with a message and the ESB will thereafter process the message (forward it to the correct service provider, wait for the reply , send the reply back to the client).

Wicket - Displaying a PDF onclick of a link or onSubmit of a Button

Very simple scenario , I wanted to display a PDF or an Excel on click of a link/ on submit of a button.

Since wicket offers flavors of ajaxbutton and ajax link one can easily make a mistake of the using them to render the pdf or excel.

I did the same. everything seemed to work unit the point where the response needed to be rendered and i would see a pdf or excel.

The problem was that using an ajax button or link we can only render a ajax response, but in this case i wanted to render a pdf or excel response (bytes).

It only clicked to me after a while of debugging that the browser had no way to identify how to render the ajax response which contained pdf bytes.


Lesson learnt , always use a Normal Button or a link to render the pdf as the web response.

an example would be like this:
Link excelLink = new Link("linForExcel"){
@Override
public void onClick() {
final OutputStream excelOutputStream = methodToReturnTheExcelOutputStream();
final ByteArrayResource byteArrayResource = new ByteArrayResource("application/vnd.ms-excel", ((ByteArrayOutputStream) excelOutputStream).toByteArray());
IResourceStream resourceStream = byteArrayResource.getResourceStream();
getRequestCycle().setRequestTarget(new ResourceStreamRequestTarget(resourceStream) {
public String getFileName() {
return "NameOfExcel.csv";
}
});
}

Wicket - Programattically Scroll a Div Horizontally

I came across a user case where I had to programatically scroll the contents of a div to the right. This was
to be performed on a click of an ajax link. This is how I achieved the same:

onSubmit(AjaxTarget....)
{
.... body of onsubmit , additional operations.
target.appendJavascript("var obj = document.getElementById('divid');obj.scrollLeft = 500;");
}


some additional methods which could be used on the window itself rather than a div are

scrollBy
scrollTo
moveBy

Wicket - Nested Modal Windows - Parent window not getting refreshed

Recently while trying to use nested modal windows I came across this wierd issue where the value being returned from the child modal window was not being refreshed and shown on the parent modal window.

My heirarchy was like this:
-><html>
--><form>
----><div wicket:id="parentModalWindow">
------><wicket:panel>
--------><form> -- Parent Modal Window Form
----------><div wicket:id="childModalWindow">
------------><wicket:panel>
--------------><form> --Child Modal window form
--------------></form>
------------></wicket:panel>
----------></div>
--------><form>
------><wicket:panel>
----></div>
--></form
-><html>

When using such a design of components the child modal window was always submitting a null value for the form components. And hence the form processing was not happening correctly.

I thereafter was suggested to use a web page instead of a panel for the child modal window as panel -> form - > panel - > form hierarchy didnt work well. Form within form was supported.

Hence I update my child modal window content to be created from a web page. And used a page creator to set content.

Now I went a step ahead as I could see the value being returned to the parent i submitted the child form and but the parent modal window components were not refreshed .


I then realised that I had to overide

childModalWindow.setCloseButtonCallback(new ModalWindow.CloseButtonCallback()
{
public boolean onCloseButtonClicked(AjaxRequestTarget target) {
target.addComponent(parentWindowComponent);

return true;
}
});

childModalWindow.setWindowClosedCallback(new ModalWindow.WindowClosedCallback()
{
public void onClose(AjaxRequestTarget target) {
target.addComponent(parentWindowComponent);

}
});


This fixed the final problem as well the container in the parent window also updated to show the new value submitted by the child modal window.

Wicket - Auto Complete inside a ModalWindow

While trying to include an autocomplete field inside a modalwindow I came across this issue where the ajax response being sent was rendered behind the modal window on IE. Windows XP, Wicket 1.3.7

In other words the lookup values were not visible as they were placed behind the modal window container. This however worked fine on firefox.

The same auto complete text field worked fine on a normal web page. Both on IE and Firefox.

Searching through forums I realised that the z-index of the autocomplete text field was much lower than the container hence it was always behind other containers and components.

The Wicket AutoComplete .js can be browsed through to see what exactly was happening.

function getOffsetParentZIndex(obj) {
obj=typeof obj=="string"?Wicket.$(obj):obj;
obj=obj.offsetParent;
var index="auto";
do {
var pos=getStyle(obj,"position");
if(pos=="relative"||pos=="absolute"||pos=="fixed") {
index=getStyle(obj,"z-index");
}
obj=obj.offsetParent;
} while (obj && index == "auto");
return index;
}


This method above always returned zero for the modal window container and hence the auto complete z index was set to 0 + 1 = 1 which was much lower than the other containers.

Also something noticeable was on IE all container pos came out as static is not being processed in the method and hence the method goes right upto the last parent to get the z-index.


As a workaround we came up with a solution where we added style="position:relative;z-index=99999;" to the element containing the text field , this could be the div, form , td etc in your case.

What this will do is to allow the above method process the container and send back 99999 as the z-index and your auto complete's z-index would then be 99999 +1 and hence it will render above the container.


Problem Solved.

Another work around would be change the modal window content to be a web page instead of the Panel.

Apache Commons Collections - Chaining Comparators

I came across this useful Util class provided by Apache Commons Collections.

ComparatorUtils

one of the useful methods here is chainedComparator which allows us to chain comparators to achieve sorting on multiple attributes of an object.

if the results from the first comparator result in 0 then the next comparator is invoked.

I remember writing a Util for myself for the same. But This class is much more robust and accessible.

Migrating From Junit 4 to TestNG

Testing next generation sounds good , for which recently I had migrated some of my unit tests written in JUnit 4 to Test NG.

Some useful features I have used and liked:

1. Annotations
2. Dependent Method
3. Groups
4. Printable and User Friendly Reports
5. CSS Configuration for reports
6. Tests runnable for various granularities like suites , groups, class , package etc
7. Eclipse plugin available
8. Data Provider
9. Spring compatibily
10. XML configuration
    
11. Many more which can be read from the Test NG documentation
    

Connecting to Oracle 8.1.7 from Weblogic10.3

This has been a nail biting issues for me where I had to connect to an older unsupported version of oracle from a newer version of Weblogic.

An upgrade document on weblogic 10.3 states that 8.1.7 is no longer supported by weblogic.
http://download.oracle.com/docs/cd/E15919_01/wlp.1032/e14253/upgrade_process.htm
This is true as we will not be able to simply create a datasource from weblogic 10.3 to oracle 8.1.7
coz the driver (ojdbc6.ja and ojdbc5.jar) as packaged with weblogic are not compatible with the same.

Hence the search began to find a suitable driver. The Drivers as mentioned by oracle for 8.1.7 can be viewed here:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc817.html.


Of course I started off with downloading classes12.jar

and I realised that though my standalone unit tests(JUNIT 4 using spring AbstractTransactionalDataSourceSpringContextTests) worked fine with some tweaks here and there.

However when the same spring config
<bean id="testDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName"><value>oracle.driver.jdbc.OracleDriver</value></property> <property name="url"><value>jdbc:oracle:thin:@host:port:databasename</value></property> <property name="username"><value>USERNAME</value></property> <property name="password"><value>PASSWORD</value></property> </bean>

was used within an app deployed on weblogic to connect to the database I got a weird exception:
java.lang.ArrayIndexOutOfBoundsException: 4 at oracle.jdbc.driver.T4C8TTIdty.marshal(T4C8TTIdty.java:465) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:329) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java: 490) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:202) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtensio n.java:33) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:474) at java.sql.DriverManager.getConnection(DriverManager.java:582) at java.sql.DriverManager.getConnection(DriverManager.java:185)

The problem was obvious Weblogic class loader was trying to return the Oracle Driver as present in ojdbc6.jar or ojdbc5.jar and completely ignored my classes12.jar which I had in the classpath.

Hence I had to start from scratch again. I instead added classes12.jar to the weblogic classpath and tried creating a datasource using the weblogic console , even then I gotthe

same exception as above. I wondered if upgrading to another jar would help. I hence downloaded ojdbc14.jar and added it to the weblogic classpath and tried creating a datasource

and it was successful.

However I wondered if there was away of not changing the weblogic classpath and instead using ojdbc14.jar from within my application to be loaded in preference as ooposed to

odjbc6.jar/ojdbc5.jar as present in weblogic classpath.

I later added

<container-descriptor>
<prefer-web-inf-classes>true</prefer-web-inf-classes>
</container-descriptor>

to my weblogic.xml so as to change the classloader preference.

Some forums suggested using the weblogic-application.xml to achieve the same by adding

<prefer-application-packages>
<package-name>oracle.jdbc.driver.*</package-name>
</prefer-application-packages>

Both will change the order for class loader preferences.

However for me the first option worked.

So now I am able to connect to oracle 8.1.7 from an application which has ojdbc14.jar in web-inf/lib and the weblogic.xml says to prefer the same.

The datasource is not a jndi datasource as can be done in weblogic but it is a spring configured datasource.

I guess this is a neater and an easier option for me as it saves me the task of upgrading to a newer oracle or downgrading to a older weblogic