Tuesday, July 25, 2006

Ask Tom

In keeping with the theme of my most recent post, I just couldn't stop laughing at Tom's most recent response to a question about recursive sql.

Wednesday, July 19, 2006

Finding Information

QUESTION

Hi Robert,

I don't understand the meaning of any of the items on the query plan.

Is there some document that would explain what they are? If there is, where can I get it?

Thanks.

ANSWER

That's the best kind of question. Instead of sending me a query plan and asking me to interpret it for you, you're asking me for the means to do it yourself. Which is good because there is so much I don't understand about Oracle that I would have to study up to help you anyway.

Oracle Documentation

I love Oracle documentation. I can usually find what I want in there, complete with examples and explanations. So let's start there.

Once you have navigated to Oracle's documentation page, you'll want to select "View Library" and then go thorugh the searching page.

Choosing the right phrase can be tricky. In this case, try TKPROF, Query Plan, Execution Plan.



Searching with any of these three phrases points rather conclusively at the Oracle Performance Tuning Guide and Reference. Taking a look through it, it seems to have lots of information to get you started on how to understand the execution plans you see in the TKPROF output.

Ask Tom

The second thing I like to do is Ask Tom. He has answered 33,000 questions, meaning that in any case, my question was probably already asked. Sometimes his answers include links to other information.



Searching our three chosen phrases points to one Ask Tom article in particular that might be helpful.

Google

Sometimes I can google around to find articles on a topic. Normally I favour sources I trust, like Jonathan Lewis or something. In this case, googling found me this article:

Use EXPLAIN PLAN and TKRPOF To Tune Your Applications, by Roger Schrag

Still Stuck?

If you've gone through these sources and you still haven't found the information you're looking for, it's time for a degree of intrepidity.

I would certainly want to contact my fellow Oracle specialists. But rather than contact one individually with an open-ended question, I would post a message to a forum or message board, making sure to highlight what I had already looked for, what I had already found, and specifically how it came up short.

It is possible that the information I'm looking for has never been written. If so, that would be a great opportunity for me to do some research and make my own, new contribution to the Oracle community.

Tuesday, July 04, 2006

Oracle and Java

Last December I wrote about how to create a simple Perl application that connected to your Oracle database, it is high time that I showed you how to do the same in Java.

What You'll Need

1. An Oracle database. Make sure you can tnsping your ORACLE_SID. Better yet, make sure you can connect with sqlplus.

2. Java. It's a free download from Sun. I'm using 1.5, but I've tested this on several versions.

3. You don't need the client installed because we're using the thin client. If you want to use OCI but don't want to install the full Oracle client, download the instant client.

Set your PATH

As part of your Java installation, you must have set the path to your jdk\bin. Make sure that's there and add it if not.

Set your CLASSPATH

If you're using Java 1.4 or greater, then ojdbc14.jar contains the classes you need. You'll find it in ORACLE_HOME/jdbc/lib.

And more information on this? You can unzip the jar file and look at the files inside with some Java tools, search the Internet or, best yet, check out ORACLE_HOME/jdbc/doc/javadoc.tar. That's a complete API: all the classes and their parameters.

If you're using an older version, you'll want something like classes12.jar instead. Follow the installation instructions. But do NOT include this if you're on 1.4 or greater. You want one or the other, not both.

There is also one thing that seems to always get me when I start a java project. I always forget to add the local directory to the CLASSPATH. Save yourself the 20 wasted minutes and put it first.

C:\temp>echo %CLASSPATH%
.;c:\oracle\product\10.1.0\db\jdbc\lib\ojdbc14.jar

Include Your Classes

Though you may want others, here are the main classes you'll need to import. The former should be part of your Java SDK, the latter is in the aforementioned ojdbc14.jar.

import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;

Create an OracleDataSource

Once you've created an OracleDataSource, you'll be free to write your SQL calls. My example below shows the syntax for a default installation, you'll need to modify that second line as appropriate.

OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:scott/tiger@localhost:1521:ORCL");
Connection conn = ods.getConnection();

Check the documentation, there are other ways of doing this.

Query Away!

There are many ways to use your connection to query your database. Here is one quick sample I found.

Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select 'Hello World' from dual");
while (rset.next())
System.out.println(rset.getString(1));

Compile and Execute

You'll need to compile your application before you execute.

javac JdbcVersion.java
java JdbcVersion

If you get this error:

Exception in thread "main" java.lang.NoClassDefFoundError

Then check your CLASSPATH very carefully. You can run java with the -verbose option to see if you get more information about what's missing.

That's it!

For more information, review the documentation to which the README file points you:

1. The new technical white paper

2. The online JDBC doc for the most updated information

3. The revised JDBC FAQ

4. The JDBC Developer's Guide and Reference

This page is powered by Blogger. Isn't yours?