Top 60 Oracle Blogs

Recent comments

May 2013

Parse Time

Dominic Brooks published a note recently about some very nasty SQL – originally thinking that it was displaying a run-time problem due to the extreme number of copies of the lnnvl() function the optimizer had produced. In fact it turned out to be a parse-time problem rather than a run-time problem, but when I first read Dominic’s note I was sufficiently surprised that I decided to try modelling the query.

Unfortunately the query had more than 1,000 predicates, (OR’ed together) and some of them included in-lists.  Clearly, writing this up by hand wasn’t going to be a good idea, so I wrote a script to generate both the data, and the query, as follows – first a table to query:


I’d like to dedicate this posting to fellow Oak Table member Richard Foote, for reasons that the readers we have in common will immediately recognise:

The singer is Canadian astronaut Commander Chris Hadfield who has been tweeting and posting pictures from space – be careful, you may get hooked:


When I posted the link to the video it had received 1.5M views; less than 24 hours later it’s up to roughly 7M. (And they weren’t all Richard Foote). Clearly the images have caught the imagination of a lot of people. If you have looked at the twitter stream it’s equally inspiring – and not just for the pictures.


Default null for collection parameter

I’ve got an existing package called DEMO as below

SQL> create or replace package demo is
  2     -- used to pass list of numbers
  5     PROCEDURE p(
  6       p_list1               IN     t_num_list
  7     );
  8  end;
  9  /

Package created.

but what I’d like to do is add another collection parameter to it, whilst keeping backward compatibility

I could use a overloaded version, or I can default that second parameter to null.  Lets explore the second option:

BGOUG Spring 2013…

On Thursday I’ll be flying out to Bulgaria for BGOUG Spring 2013. It’s been about 18 months since I’ve visited the people over there, so I’m really looking forward to getting stuck in.

2013-05-13 08.16.28


This will be my first conference of the year, so I’m feeling a little nervous at the moment. I’m sure the adrenalin rush will kick in and get me through. :)

Desktop SSD…

I wrote a couple of days ago about replacing my MacBook Pro hard drive with SSD. At the same time I bought a little SSD to use as the system drive for my desktop. I fitted that this morning, installed a fresh copy of Fedora 18 and mounted the original 1TB hard drive as a data drive.

Like the MacBook Pro, my desktop is a few years old, but still has plenty of grunt (Quad Core and 8G RAM) for what I need it for. I do run the odd VM on it, but any heavy stuff is run on my server, so there is no incentive to go out an buy the latest kit for what is essentially just a client PC.

Prize Winners : Oracle E-Business Suite R12 Integration and OA Framework Development and Extension Cookbook

A couple of weeks ago I started a competition to win 2 copies of Oracle E-Business Suite R12 Integration and OA Framework Development and Extension Cookbook by Andy Penver. Thanks to Packt for donating the prizes. The competition closed yesterday and the lucky winners are:

  • Arun
  • Ajay Sharma

I’ve sent your email addresses to my contact at Packt, who will contact you to deliver your e-book.



Colored Heat Maps in SQL*Plus

Screen Shot 2013-05-10 at 1.13.15 PM

The above is so cool.

The graphic shows the latency heatmap of “log file sync”. I was running a swingbench load and at the same time throttling I/O such that latencies started off good then got worse and then back to normal.

All I did was type

Hakan Factor

Here’s a quick and dirty script to create a procedure (in the SYS schema – so be careful) to check the Hakan Factor for an object. If you’re not familiar with the Hakan Factor, it’s the value that gets set when you use the command “alter table minimize records_per_block;”.

I was prompted to publish this note by an item on the OTN SQL forum describing a problem with partition exchange with a table when there were bitmap indexes in place and the table had been changed to have some extra columns added. (Problem as yet unresolved as I publish).

If you start playing with the Hakan Factor, you’ll find that there are some odd little bugs in what gets stored and how it gets used. (SQL updated to use bitand() to reflect comments below and Karsten Spang’s blog note; also edited following a comment on OTN to show the rest of the spare1 flag bits)

MacBook Pro Mid 2009 : Replacing hard drive with SSD…

I’ve had my 13″ MacBook Pro since the mid 2009 refresh and it’s been really reliable. Apart from one brief visit to Apple to replace a noisy fan, I’ve had no worries. A few years ago I upgraded from 4G  to 8G RAM, so I’m not stranger to taking the back off it.

Even though it’s quite old by computer geek standards, I really don’t have any performance problems. I do demos with a couple of Linux VMs running Oracle and it works OK. Despite this, I was bored the other night and decided to buy an SSD to replace the internal hard drive. It arrived yesterday, so during last nights insomnia, I decided to fit the hard drive, rather than stare at the ceiling.

The actual hard drive replacement is pretty simple. You can see an example of it here. It takes about 5 minutes.

Direct path read and fast full index scans

This is yet another blogpost on Oracle’s direct path read feature which was introduced for non-parallel query processes in Oracle version 11.

For full table scans, a direct path read is done (according to my tests and current knowledge) when:

- The segment is bigger than 5 * _small_table_threshold.
- Less than 50% of the blocks of the table is already in the buffercache.
- Less than 25% of the blocks in the buffercache are dirty.