A collection of tutorials, code and tools to help you get better using Oracle
06 June 2011
Oracle can be scary to a new developer. It has become a massive piece of software with thousands of features. Even skilled Oracle professionals who have worked with it daily for a decade or more claim they learn something new about Oracle every single day. I wouldn't go as far as saying that, but I am surprised, annoyed, delighted and re-educated by Oracle many times a week when working on a new project.
I have dabbled in other database technologies, but I have only delivered serious projects using Oracle. So when I claim that Oracle is the best relational database that exists, I am probably biased. Oracle Corporation certainly claims their database is the best, and more importantly the most scalable, but then obviously they would!
Whether Oracle is the best or most scalable database known to man is largely irrelevant for most projects. However, if you work somewhere that has paid the Oracle license fees, then Oracle is going to be your database tool of choice. Like most software, there is always more than one way to do something, and each way has its own set of compromises and limitations. Oracle is also expensive, and how expensive depends on how much hardware you need to run your application, so picking the most efficient solution can delight both your end users and your accountant.
So how do you know which method to use? You benchmark of course, which leads to the point of this introduction and upcoming topics.
As with all mature technologies, Oracle has tools to help you. Some are built into the database, and some written by other smart people, such as:
This list of tools is about the minimum set of tuning tools a database developer should be familiar with. There are many more tuning tools available, but most of them are targeted at database level tuning, traditionally carried out by DBA and Database Performance tuning experts. Tools you may hear mentioned are AWR, Statspack, ASH, the Oracle Wait Interface and so on. This tutorial is not targeted at those tools. You cannot learn everything at once, and I believe that if each developer knows how to best code his part of the application, the database will get much further before a database tuning expert is required.
Capturing runtime statistics using the tools, and knowing how to make things better are two totally different things. Tuning database performance is almost an open ended topic, so I won't explore much of it in this section. However, once you know the tools, you can start to capture information. This will allow you decide if one approach is better than another and as you gain experience you can make a judgment call as to whether or not the results look good. Until then, feel free to ask questions and read plenty and you will slowly but surely figure things out.
I assume you know what SQLPLUS is, and how to logon to a database. I also assume you have a database available - getting your first Oracle instance installed and running is a topic for another tutorial.
You should know some basic SQL and have worked with Oracle a little before.
Most importantly I assume you are curious. I cannot teach you everything, but I can point you in the correct direction, and the more you know, the more it enables you to learn. It's like a positive feed back loop.
In this tutorial, I am going to use the command line interface to Oracle, SQLPLUS, to demonstrate everything. There are GUI tools available, but not everywhere you work may have them, and SQLPLUS is always there. So lets get started ...