I thought that’d grab your attention!
 

We know there are a lot of PL/SQL developers out there who are looking to make (or have already made) a break into application development using a more popular programming language like Java as well as many Java developers who use Toad to make access to Oracle easier.
 

According to a 2006 IOUG survey of Oracle sites, the 3^rd most widely used language after SQL and PL/SQL (77%) was Java (38%). http://www.ioug.org/Research.pdf 
 

So – what does Toad do for Java?
 

Well actually, quite a bit, and mostly it’s focussed around performance. Let’s face it, if you are a Java developer and you haven’t come from a SQL or PL/SQL background, accessing the Oracle database efficiently is going to be a struggle where you depend on writing SQL statements to query data.
 

In this blog, I want to highlight one of the main areas where Toad can assist you in the Java world to give you the confidence you may be lacking in Oracle – which is:
 

The answer is a definite yes. This is perhaps where Toad will probably be of most help to Java developers. The Toad Xpert edition includes our SQL tuning technology called SQL Optimizer.
 

Let’s assume I have a SQL statement which I’ve either written in the Editor or built using Toad’s Query Builder. You could add a Java wrapper and embed the SQL straight into your Java source, but first, you want to make a quick check that the SQL will perform well first.
 

Here’s a typical SQL statement, viewed in the Editor.

  

I can quickly get an impression of whether there’s a problem by performing an Explain Plan – which shows the expected execution path and steps which Oracle will take in order to execute the SQL as efficiently as possible. Press the  button on the Editor’s toolbar. The TABLE ACCESS FULL on the EMPLOYEE table indicates that Oracle has to search across every record in the table instead of using an Index. This is very inefficient. Let’s see if we can make this statement perform faster.
 

Press the SQL Optimizer button on the Editor’s toolbar  . A pop-up window will ask you into which window you wish to open the SQL Optimizer. Click OK to accept the default of “With the Tuning Lab”. To save time, you can also check the “Set as default” check box.

 

  

The Tuning Lab is typically used to tune ad-hoc SQL whereas the Batch Optimizer is typically used to bulk-tune multiple SQL statements, scripts, PL/SQL programs, etc. You could also use this if you had, say, hundreds of Java script files which contained embedded SQL statements.
 

Now, simply press the Optimize button () and let the SQL Optimizer do its thing!

 

You can see that the SQL Optimizer has found a number of possible re-write alternatives which may offer an improvement over the original. Although the Oracle Plan Cost, an indicator of the expected performance when the statement is executed, shows some statement re-writes with lower values than others, the only true way to perform a comparison is to go ahead and execute the statements. To save time, you can select which you want to execute or execute all of them.
 

 

In this case, I selected the top 10 and pressed the Execute Selected button. On the left of this screenshot, you’ll notice some Layout buttons. Press the Execution Statistics layout button to see the execution results compared graphically.
 

 

In the Scenario Explorer, the light blue bar shows the execution of the original statement. Light green bars indicate those re-writes where execution performance was improved. Dark blue indicates no change. Press the Resolution layout button and you can see that within a few seconds, I managed to achieve a performance improvement (elapsed time) of 14.83 times better than the original using alternative #7. Pretty impressive!

 

 

If you press the Compare Scenarios layout button, you can compare alternative #7 with the original SQL. This is what the SQL Optimizer did:
 

 

So now what I need to do is to lift the re-written SQL statement back into my Editor. Press the SQL Details layout button and select Alt #7 so it’s visible in the SQL Text window. Now press the  button to return this re-written SQL statement to the Editor.
 

Now use the “Make a non-SQL code statement” button  (Editor toolbar) to add the Java wrapper and presto!, you are ready to run your Java code with a far more efficient SQL statement.
 

Finally, I mentioned earlier that you may have Java files with embedded SQL statements and you want to assess them to see if there are any potentially poor performing SQL statements amongst them. You may, for example have Java code where the SQL has been generated using Hibernate (used for mapping Classes to database tables).

 

 

This is where the Quest SQL Optimizer’s SQL Scanner comes in useful. Press the SQL Scanner tab in the SQL Optimizer main screen and create a new Scanner Job. Click the Source Code link on the left and add all your Java files.
 

 

Now click Finish. Your Java code will be listed in the SQL Scanner window ready for scanning.
 

Push the  button to begin scanning the embedded SQL. The results will be displayed as below and any SQL statements ranked according to Problematic, Complex, Simple or Invalid. This ranking system quickly enables you to assess which SQL statements are the worst, so that you can proceed with the optimization.
 

 

Selecting the appropriate Optimization button will go ahead and begin optimizing the SQL as described above and allow you to return the optimized SQL into the Java source code.
 

 

The Quest SQL Optimizer is designed to be both effective and simple to use, without any prior knowledge of how to tune SQL and should be an extremely valuable tool for Java developers concerned about poor SQL performance in their application.
 

Quest Software also provides a full suite of Java tools too and one, in particular, which is the perfect complement to Toad Xpert with SQL Optimizer for SQL tuning, is JProbe.
 

JProbe is an enterprise-class Java profiler providing intelligent diagnostics on memory usage, performance and test coverage, allowing developers to quickly pinpoint and repair the root cause of application code performance and stability problems that obstruct component and integration integrity.
 

JProbe also offers an Eclipse plug-in that provides intelligent code performance analysis and problem resolution directly within the Eclipse Java IDE.
 

For more information on Toad for Oracle Xpert (with Quest SQL Optimizer) with a free 30-day trial, please click this link: http://www.quest.com/toad-for-oracle/features-benefits.aspx#3 
 
And for more information on JProbe, visit the JProbe user community.  Additionally, there are two download options for you to get your feet wet. JProbe offers either a free 10-day trial that is a fully functional product and the JProbe Freeware which is an Eclipse-based memory analysis tool that leverage’s JProbe’s superior data visualization and investigative tools to analyze and diagnose heap dump memory issues.
 
Here are the links to download each product: