Thursday, 19 April 2012

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 this as sys.
 Then, for this demo, I want to clean out all the statistics on the tables I want to look at.
 Now, here's my initial query, getting total and mean salary grouped by departments.
When we then run the tuning advisor, a new tab appears on the worksheet which has the main results from the tuning sesstion.  This tab has four main sections to it. These are the statistics which the advior found on the objects in the query, changes to the profile which is in use, any indexes which need to be added.  Finally, if needed, there is a restructuring tab which may have some sql to help you restructure your query.

Finally, on the right hand side, we can see the details of tuning job.  The SQL_TUNE package generate text which we organise into the tabs.

We can see from the output above that the statistics are not available and the tool is recommending refreshing statistics on the objects in the original query.

We can then go and analyze the tables to see if that helps.

We can then check that the stats are fresh and at the time of posting, this is current.

Now, going back the tuning advisor and running it again, shows some different stats

Heres the final look at what the Tuning advisor tells us at the end of the second run.  This is the standard text output that comes from the tuning package

Tuning Task Name   : staName14054
Tuning Task Owner  : HRDEMO
Tuning Task ID     : 9295
Workload Type      : Single SQL Statement
Execution Count    : 1
Current Execution  : EXEC_9255
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 04/19/2012 07:33:50
Completed at       : 04/19/2012 07:33:50

Schema Name: HRDEMO
SQL ID     : 028hrurkuc6ah
SQL Text   : SELECT SUM(E.Salary),
             FROM Departments D,
               Employees E
             GROUP BY E.Department_Id
             ORDER BY E.Department_Id


1- Restructure SQL finding (see plan 1 in explain plans section)
  An expensive cartesian product operation was found at line ID 2 of the
  execution plan.

  - Consider removing the disconnected table or view from this statement or
    add a join condition which refers to it.


1- Original
Plan hash value: 2187233893

| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT        |             |    11 |    77 |    35   (3)| 00:00:01 |
|   1 |  SORT GROUP BY          |             |    11 |    77 |    35   (3)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN  |             |  2889 | 20223 |    34   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL    | EMPLOYEES   |   107 |   749 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT          |             |    27 |       |    32   (4)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| DEPT_ID_PKX |    27 |       |     0   (0)| 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   5 - SEL$1 / D@SEL$1
Column Projection Information (identified by operation id):
   1 - (#keys=1) "E"."DEPARTMENT_ID"[NUMBER,22], COUNT(*)[22], 
       COUNT("E"."SALARY")[22], SUM("E"."SALARY")[22]
   2 - (#keys=0) "E"."SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
   4 - (#keys=0) 


For doing this without SQL Developer, there are several things which you need to do. I have a little graphic which looks at each of the steps which need to be taken to create a tuning job in the normal SQL*Plus interface.  The main steps are creating task tuning tasks, and then interpreting the output.
Finally, this functionality is part of the SQL Worksheet in SQLDeveloper, which together with trace file editing, explain plan and autotrace, hierarchical profiler and monitoring sessions adds more tools to the toolbox for trying to find issues with you code.

Wednesday, 18 April 2012

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 in it to allow us to do something.  I have also created two procedures, one of which calls the other so we have some nested dependencies.

drop table hier_demo;
create table hier_demo (id number, name varchar2(200));
insert into hier_demo values (1, 'Barry');
insert into hier_demo values (2, 'Lisa');
insert into hier_demo values (3, 'Rebecca');
insert into hier_demo values (4, 'Katie-Ellen');

  CURSOR hiercur
    SELECT * FROM hier_demo;
  -- hierrec hiercur%type;
  -- type  hiertab is table of hierrec%TYPE;
  FOR myrec IN hiercur

Running the main procedure normally gives us some nice and simple out put.

When we click on the profile button in the plsql editor, SQL Developer will check that you have the proper permissions and the associated table to use the profiler.

When you hit the profiler button , it first comes up with the run dialog to set the parameters for the stored procedure.  Hitting ok on this diualog will run the stored procedure and any issues it has will also pop up while you are profiling.  

As this happens, the profiler  first checks to see if the there is a profiler log directory.  and if there is not one, it will prompt you to create one and get the appropriate permissions.

Hitting ok on this makes the tool then set up the directory for the profile.  To do this, it needs to run some SQL as sys to do it.

If the user agrees with all this, then he is prompted for SYS passwd to actually create the tables for the profiler statistics in the local user, in this case, hrdemo.
Finally, when they agree, the tool asks if it can setup a local set of tables for the profiler,  We'll agree to this too and make sure the profile is captured.

 Now, when we look at the profile tab of the PLSQL editor, we should have a new editor with the results of the profile.

This shows us a breakdown of the how the procedure actually executed all the way down to the actual fetch which returned the rows.  A very slight change to the procedure, in this case adding another procedure as a dependency which we also described above, we can show the nesting in the profile going further down.
create or replace
  CURSOR hiercur
    SELECT * FROM hier_demo;
  -- hierrec hiercur%type;
  -- type  hiertab is table of hierrec%TYPE;
  FOR myrec IN hiercur

This now shows us that we have another profile in the set and clicking on it gives us the hierarchy of calls in the stored procedures execution.  The main point here is that we can now see the further level of indirection through the printer procedure.

So thats all of this profiler for now,  If you want to see how to do this with your own tables, the easiest thing to do is to download the Oracle Developer Days VM from OTN.  This particular blog will make an appearance as part of a bigger set later which we will discuss Tuning in general..

Thursday, 12 April 2012

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);
 select first_name||' '||last_name into name from employees
 where employee_id = ID;
  RETURN name;

We can compile this for debug in SQLDeveloper as normal.  Now, for remote debugging, we want to go to another session and run this function there.  For clarity, we can do it in SQL*Plus.  Before that however, we need to switch on the remote debugger listener so we can attach to a session.  So, firstly, right click on your connection and choose remote debug, which will pop up a little window

For our purposes, on localhost, we dont need to add any other information, but if you are connecting to another database on another machine, add the host name to the local address field and choose an appropriate port.  When you click ok on this, the Run manager is shown with the listener details on there as shown above.

Now, here we are with SQL*Plus, fire it up with our demo user and make sure to execute the command

 execute DBMS_DEBUG_JDWP.CONNECT_TCP('',4000);

and then we can run our function as described above.

[oracle@localhost ~]$ sqlplus hrdemo/hrdemo

SQL*Plus: Release Production on Thu Apr 12 19:16:37 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HRDEMO@ORCL> set serveroutput on

PL/SQL procedure successfully completed.

  2  dbms_output.put_line(get_emp_name(100));
  3  end;
  4  /

Once we run the anonymous bock, the remote debugger kicks in and we stop at the appropriate breakpoint in the code.

On a last note, this works well in Application Express too so when you make a call to a function which you have remote debug switched on for, the debugger will break on the line as long as you have debug switched on in the developer toolbar.

Wednesday, 11 April 2012

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.
[oracle@localhost ~]$ ls /usr/lib/httpd/modules/|grep svn
[oracle@localhost ~]$ 
For this we need to make sure we have the proper SVN modules installed for apache.  We can do this with Yum. (The default repositories should be enough)
[oracle@localhost /]$ sudo yum install mod_dav_svn
Loaded plugins: security
Setting up Install Process
Resolving Dependencies
There are unfinished transactions remaining. You might consider running yum-complete-transaction first to finish them.
The program yum-complete-transaction is found in the yum-utils package.
--> Running transaction check
---> Package mod_dav_svn.i386 0:1.6.11-7.el5_6.4 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

 Package            Arch        Version                 Repository         Size
 mod_dav_svn        i386        1.6.11-7.el5_6.4        el5_latest         78 k

Transaction Summary
Install       1 Package(s)
Upgrade       0 Package(s)

Total download size: 78 k
Is this ok [y/N]: y
Downloading Packages:
mod_dav_svn-1.6.11-7.el5_6.4.i386.rpm                    |  78 kB     00:00     
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : mod_dav_svn                                              1/1 

  mod_dav_svn.i386 0:1.6.11-7.el5_6.4                                           

[oracle@localhost /]$ cd /usr/lib/httpd/modules/
[oracle@localhost modules]$ ls *svn*

Now, we can create a repository somewhere to store some code which we'll save in here later.  I've added two repositories, just to show you can :).

[oracle@localhost svn]$ svnadmin create /home/oracle/svn/repo1
[oracle@localhost svn]$ svnadmin create /home/oracle/svn/repo2
[oracle@localhost svn]$ ls repo1
conf  db  format  hooks  locks  README.txt
[oracle@localhost svn]$ cat repo1/README.txt 
This is a Subversion repository; use the 'svnadmin' tool to examine
it.  Do not add, delete, or modify files here unless you know how
to avoid corrupting the repository.

Visit for more information.
[oracle@localhost svn]$ 

Now since we're going to access this over apache, lets change the permissions

[oracle@localhost svn]$ sudo chown -R apache:apache /home/oracle/svn/repo1/
[sudo] password for oracle: 
[oracle@localhost svn]$ ls -al
total 16
drwxrwxr-x  3 oracle oracle 4096 Apr  9 21:53 .
drwxr-xr-x 45 oracle oracle 4096 Apr  9 21:51 ..
drwxrwxr-x  6 apache apache 4096 Apr  9 21:53 repo1
drwxrwxr-x  6 apache apache 4096 Apr  9 21:54 repo2
[oracle@localhost svn]$ 

We want to be able to secure svn, and for now, lets use basic svn authentication.  In order to do that, we need to configure subversion to allow users to connect.  To do that, go to your new repo, identify your svnserve.conf file and uncomment the following 2 lines

auth-access = write
password-db = passwd
[oracle@localhost svn]$ cd repo1
[oracle@localhost repo1]$ ls
conf  db  format  hooks  locks  README.txt
[oracle@localhost repo1]$ cd conf/
[oracle@localhost conf]$ vi svnserve.conf 
[oracle@localhost conf]$ sudo vi svnserve.conf 

Now setting up webdav for svn is relatively easy too.  Since we have the dav svn installed, we just need to edit the httpd.conf in /etc/httpd/conf/httpd.conf.
The first we need to do is to modify the file and add the mod dav svn modules to apache.
 In the same file, we also need to define a location for the our URLs to point to.    I have set this initial setup with the most basic setup which needs no authorization yet.  We'll amend that later.
 Lastly, save the file, and we need to restart the httpd daemon.  This is in the /etc/init.d directory.
 Now, we can check out if we can see this with a browser? Lets see.  We know that we set up the httpd.conf with a port number of 9999, so we can use the 'repos' location as the uri, and then we can specify the repository.  So, the url would be http://localhost:9999/repos/repo1 (or repo2)
 Going back to our image, we now have several port forwarding rules.  We add another for apache svn which pushes calls to port 9999 through to the guest.

 And now we can use our host to see the new repo.

After all this, we can add a user to svn so we can use it.  Primarily, me :)

[sudo] password for oracle: 
[oracle@localhost conf]$ htpasswd -c /home/oracle/svn/repo1/
conf/       db/         format      hooks/      locks/      README.txt
[oracle@localhost conf]$ htpasswd -c /home/oracle/svn/repo1/conf/
authz          passwd         svnserve.conf  
[oracle@localhost conf]$ sudo htpasswd -c /home/oracle/svn/repo1/conf/passwd bamcgill
New password: 
Re-type new password: 
Adding password for user bamcgill
[oracle@localhost conf]$ 

Now we have users we can use svn. However, we need to let apache know that we want to use it.  Rememeber the tags we filled out earlier.  Change the location tags to have the following now.

<Location /repos>
  DAV svn
  SVNParentPath /home/oracle/svn
  AuthType Basic
  AuthName "Subversion Repository"
  AuthUserFile /home/oracle/svn/repo1/conf/passwd
  Require valid-user

Now, we restart the httpd daemon again

cd /etc/init.d
sudo ./httpd stop
sudo ./httpd start

And now we have authentication, albeit basic, but there are other blows to allow ldap and ypmaster access.

Need to check in something from the directory, which we will look at in another post.    Today, we set up subversion on the Oracle Developer Day Virtual Machine.  In later posts, we'll use this to check in some code and use it with Hudson to automate some integration tasks.

Tuesday, 10 April 2012

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 to check.  Firstly, we have an init.d script for starting and stopping hudson
So, to configure hudson, the actual configuration file is under /etc/sysconfig.
At this point, the main thing we want to do is to configure the port that hudson will operate on.  We'll change ours to 8888, since the image has several other ports doing different things.

Now, once thats done, come back to /etc/init.d and run hudson start.

So now, fireup firefox on the image and punch in localhost:8888

Which will give us this.

 Tada!.  now we have hudson up and running.  Lets run a dumb job to see what happens,  I'll do something really simple so you can try this immediately.
ls -altr
touch barry.txt
echo "Something $BUILD_NUMBER" >> barry.txt
cat barry.txt

creating a new job on hudson is easy.  Click on the new job icon and enter the name and description of your job.

Scroll down and choose a build option of Shell script.
And now add our little script.

and click save at the bottom.  Job done, so to speak.
Now run the job and see what happens.  It will queue it up and run it and when its finished, will show an icon as to whether there is thunder coming or the sun is still shining.
Drilling into the job, you can click on the console output to see how the job actually ran.  
There it is running.  One last step.  We need to add a port forwarding rule to the image so we can check this out from outside the image. and we're done. 

 I've just noticed too, that apex seems to be configured with port 8888, so we could get a clash later.  I will change that on this image.  Anyways, have fun with this for now.  I'll come back to this when we get subversion setup and linked to this so we can checkout the sql/plsql and run tests using this hudson install.