I just finished a 4-day training course entitled Optimizing Oracle SQL, imparted by Karen Morton from Hotsos. I must say I was really impressed with the course, as it gives you a very detailed account of how Oracle works, thus enabling you to analyze what’s going on with your queries and, if needed, rewrite them (or even your database schema, if possible) to take maximum advantage of Oracle.

Being mostly a Java developer, one tends to view sql queries as simply a means to get data out and into the database. And this is logical, in a sense, since you can’t possibly learn about all the different technologies there are. You try to at least be aware of how your sql statement will work, but your focus is in the other layers.

I, for instance, sometimes use sub-queries to get complex results from multiple tables. Now, this isn’t bad per-se, but the real interesting part is that you don’t really know (or care) what Oracle is really doing to your query and the sub-queries involved. You see (in case you weren’t aware), Oracle has an optimization engine that processes every query you send to it, and then tries to determine which path is the best one to fulfill your query (should I use an index, should I load the whole table, should I loop over the results, should I do a join, etc). And that sometimes means rewriting your query internally, so that for example, instead of the sub-queries you have on your query, Oracle uses table joins. And all of this is being done "under the covers". I’m sure some developers aren’t aware this is happening, or if they are, they trust that Oracle will do what’s best. The problem is that this isn’t sometimes the case.

So, the course really goes into explaining how Oracle does this, and most important, how you can see what Oracle is doing so you can verify that what Oracle is doing is really what you want it to do. The course also lets you watch your query to see what resources are being used, so that you understand why your query takes so long to run, or why adding rows can affect the performance drastically, or why some indexes might be better than other.

The course also talks about what different combinations to the database structure, table configurations, database parameters, etc. can affect the overall performance, and how you can analyze the information Oracle gives you so that you can change your database (and your queries) to improve performance and/or stability.

Now, some of the topics from the course seemed more geared towards a Database Administrator (mainly because only a DBA would have permissions to do that), but they’re also important to a developer to know, so he can figure out why his/her queries are not behaving as expected.

I would recommend to any developer (and of course, any DBA) that works with Oracle databases to attend this course, as it really changes the way you view your queries, and how Oracle works.