SQL Tuning Advisor – 101

The DBMS_SQLTUNE package is the interface for tuning SQL on demand. Its Doc pages are here.  Have a look.  There is a lot of stuff to do to set a tuning task, run it, report on it and then get it to do something useful.  We've wrapped all that into our SQL Tuning Advisor function which means you dont need to start writing plsql API calls to make this work.  Stick in your dodgy query, click the advisor button and visualize the results.

Here's a look at how to do this.  Firstly, we need to grant a few permissions to our user. I'm doing (more...)

Using Hierarchical Profiler in SQL Developer

One of the features exposed since SQL Developer 1.5 is the hierarchical profiler.  There have been several blogs and things about this but none I think that really get into the detail of what you are seeing and how to do it.

The hierarchical profiler allows you to see what happens when your piece of PL/SQL is running.  More specifically, it allows you to see where it is spending most of its time, which means you can concentrate on hammering that down, rather than wondering where to start.

For today, I'm using a really basic reference table with a few rows (more...)

Remote Debugging with SQL Developer revisited.

As part of the development process, we all have to work out the bugs in our code.  For all of us who use SQLDeveloper , we know how to debug with SQL Developer. Compile for Debug, breakpoint and go.  However, People still get confused by what remote debugging is and how it works.  At its most basic, it allows us to run a procedure in a session and debug if from another.

So, Lets say we have a simple procedure on employees table like this.

create or replace
name varchar2(100);

Setting up SVN for SQL Development with Oracle Developer Days VM

We've done a number of posts on using the Oracle Developer Day VM's and this is an addition to it, showing how we can set up a subversion repository using apache web dav for access.    I'll keep this really simple so the steps should doable, straight one after the other.

On the Oracle Developer Day image, we have installed SVN so we can use it as our source control system.  Lets find our svn.
[oracle@localhost ~]$ which svn
[oracle@localhost ~]$

We can check if we have the right modules installed for apache, which in this case is mod_dav_svn.

Continuous Integration for SQL Tasks

One of my favourite integration tools is hudson.   Today, we're going to show you how to setup hudson on the Oracle Developer Day image.  Since the image is built on Enterprise Linux, we'll need to either add a yum repository from which to install, or, even easier, just download the RPM from the hudson site
Clicking on the Oracle Linux link, we'll download hudson-redhat-2.2.0.rpm.
When its downloaded, you can install it on your linux image.
Now, its installed, we need to configure it.  Since we put in the RPM, there are a couple of standard directories (more...)