Barry McGillin

Subscribe to Barry McGillin feed
Oracle SQL Developer and Database Migration, with lots of core Oracle tech as well!Barry McGillin
Updated: 5 hours 44 min ago

Add a new hard drive to your Oracle Developer Days VM

Wed, 2012-05-09 06:59
For those of you who end up using the Oracle Developers Day VM for more that just demo's but playing with other things too, will find that at some point, you'll need more space. (Like I did)   Today's post is about just that.  We're going to add a new VMDK drive to our virtual machine and configure it so its available to you in the machine.

First thing we want to do is to have a list of the devices in your linux box.  This will save you searching for it once you add it later.

[oracle@localhost ~]$ cd /dev
[oracle@localhost dev]$ ls -al hd*
brw-r----- 1 root disk 3, 0 May 4 05:50 hda
brw-r----- 1 root disk 3, 1 May 4 05:51 hda1
brw-r----- 1 root disk 3, 2 May 4 05:50 hda2
brw-r----- 1 root disk 3, 64 May 4 05:50 hdb
brw-r----- 1 root disk 3, 65 May 4 05:51 hdb1
[oracle@localhost dev]$

Now we can power down the machine and add the drive.

You need to make sure your VM is powered down so we can make changes to the server.

Checking the storage frame of this VM, we can see that there is only two drives connected.  Double clicking on the storage frame pops up the storage window where we can add the drive.

Clicking on add drive, asks us if we want to add an already built drive or add a new one.  We want to add a new one.

We then choose a VMDK to use.  There are other types, but we're using this one for now.

On the next page of the wizard, we choose dynamically allocated. which will size the disk to just the be the size of the data that is in it.  So if there is no data on the drive, this file will be tiny.

Next we give it a name and size.

Clicking ok to finish the wizard show the new drive added.  One las thing we do is to change the type of the hard drive to be a secondary slave.

And there we have it, one file added.  This is useless to us though until we go in and format configure it in linux, then bring it online so its of use to us.

Ok, Now we have a drive attached to our virtual machine.  All that remains is for us to configure it in in the machine so it is formatted and mounted.

Firing up the VM as normal, we want to SU as root for the next phase.  The first thing we need to do is to format the disk.

[oracle@localhost ~]$ cd /dev
[oracle@localhost dev]$ ls -al hd*
brw-r----- 1 root disk 3, 0 May 4 05:50 hda
brw-r----- 1 root disk 3, 1 May 4 05:51 hda1
brw-r----- 1 root disk 3, 2 May 4 05:50 hda2
brw-r----- 1 root disk 3, 64 May 4 05:50 hdb
brw-r----- 1 root disk 3, 65 May 4 05:51 hdb1
brw-r----- 1 root disk 3, 65 May 4 05:53 hdd
[oracle@localhost dev]$

Now looking at the top device listing versus this one we can see that the new device that has been add is /dev/hdd

This disk that we've added is blank and raw so the first thing we need to do is to set up partitions and then format the disk.

[oracle@localhost dev]$ sudo fdisk /dev/sdd

Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)

Command (m for help):

Choose 'n' to create a new partition and then choose 'e' and then pick the defaults through that option.

Command (m for help): n
Command action
e extended
p primary partition (1-4)

Finally, when this comes back, choose the 'w' to write the partition table back to disk.

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[oracle@localhost dev]$

Now we can build the file system on the disk we have partitioned with mkfs.

[oracle@localhost dev]$  sudo mkfs -t ext3 /dev/sdd
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
6111232 inodes, 12211400 blocks
610570 blocks (5.00%) reserved for the super user
First data block=0
373 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

[oracle@localhost dev]$

Now you have a drive, but its not mounted anywhere so you still cant see it.    You will now need to create a mount point for your drive in the root file system.

[oracle@localhost ~]$ sudo mkdir -p /newdrive
[sudo] password for oracle:
[oracle@localhost ~]$

 And lastly you can issue the mount command to mount the drive to that mount point.

[oracle@localhost ~]$ sudo mount -t ext3 /dev/hdd /newdrive
[oracle@localhost ~]$

Now you can list your drive with 'ls -al /newdrive' and it is listed and usable.  However, the next time, the machine is rebooted, you will not have this drive mounted.  We need to add a line to the file /etc/fstab to allow it to be mounted automatically.

LABEL=/                 /                       ext3    defaults        1 1
LABEL=/home /home ext3 defaults 1 2
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
LABEL=SWAP-hda2 swap swap defaults 0 0
http://localhost:80 /home/oracle/dav davfs noauto,users 0 0
/dev/hdd /newdrive ext3 defaults 1 2

Adding the line above to this file will allow the drive to be mounted each time the machine reboots.  

Now you have a drive which you can use for data or install other Oracle software on, like Oracle Golden Gate  to help synchronise data between databases.  Find out more about the Oracle developer day VM on OTN

SQL Tuning Advisor - 101

Thu, 2012-04-19 02:53
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
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.

Using Hierarchical Profiler in SQL Developer

Wed, 2012-04-18 08:30
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..

Remote Debugging with SQL Developer revisited.

Thu, 2012-04-12 16:41
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.