Top 60 Oracle Blogs

Recent comments

August 2011

Super 8…

I can see Super 8 getting really mixed reviews because it feels a little like The Goonies meet Cloverfield.

Personally, I liked it and could see past the odd cheesy bits, but I can also see why some would think it falls short. There is a very E.T. childlike feel to the film, which is hardly surprising as the lead characters are kids. There is also the Stand By Me coming of age aspect. All this is wrapped up with a bow made of some fantastic visual effects.

Cool film, but difficult to pinpoint the target audience they had in mind (in my opinion). I’m sure over the next few years I will watch and enjoy it several times, but I don’t think I will be buying it.



Multi-Column Joins, Expressions and 11g


I've already outlined in one of my previous posts that getting a reasonable cardinality estimate for multi-column joins can be tricky, in particular when dealing with correlated column values in the join columns.

Since Oracle 10g several "Multi-Column Join Cardinality" sanity checks have been introduced that prevent a multi-column join from producing too low join cardinalities - this is controlled via the "_optimizer_join_sel_sanity_check" internal parameter that defaults to true from 10g on.

I Do Believe In One-Size-Fits-All Solutions! Humor?

My recent posts about certain technical marketing motions about certain information technology has kept me awake at night. However, my readers span all time zones so I need to remain vigilant in handling email and blog comments. I need a one-size-fits-all solution. I think I’ll pop a couple of these:

Filed under: oracle

Something free and interesting sounding...

Tanel Poder is doing another 'secret' free webinar. I've known Tanel for quite a few years now and he is a pretty smart guy. The topics he is presenting on this time center around scanning of data - and all of them look very interesting. Some are Exadata related but most of them would be useful for anyone.


Statistics don’t lie: a survey of browser users.

But people do

On 2nd August the BBC were reporting the survey described at the top as news – and I had a URL for their report, and their on 3rd of how it was a hoax. Both URLs now point to the same thing – rather reducing the impact of the posting.

Interview with Kyle Hailey in

Interview appears in


1- Why did you choose to specialize in Oracle databases?

I wanted to live in San Francisco and Oracle was the only company in 1989 recruiting on campus at Cornell from San Francisco. Living in San Francisco was my goal and Oracle got me there. At the time I thought databases were boring having majored in physics and mathematics. I had wanted to work on numerical models of trading systems and arbitrage calculations.

2- What made you specialize in Oracle Performance Tuning?

I like finding patterns in quantitative data whether it’s the stock market or databases. Oracle performance tuning has given me this opportunity.

3- Do you still remember the first day in your first job?

Not really. I do remember though starting in support and thinking how crazy it was answering customer phone calls acting like I was an expert and I’d never used Oracle!

4- Is there anybody you have regarded as role model at the beginning of your career?

Funny enough I went to high school with Micheal Dell, but I’d hardly say he was an inspiration. I was also in the computer science building at Cornell when Robert Morris released the first major internet worm in 1988, but that wasn’t much of an inspiration either, though we did go over his computer attacks in my operating system class the next day!

I’ve found several people inspirational. I met two of my biggest inspiration at the same time and place which was the Europecar Oracle 7.0 parallel server site in 1994. The two people were Roger Saunders from Oracle and Jan Simon Pendry from Sequent.
Roger had written and was using code to attach to the SGA and collect information much similar to active session history and this was almost a decade before Oracle did it officially. Roger was years ahead of the curve and his work blew me away and inspired me.
Jan Simon had never worked with Oracle before Europecar but with in a week he’d figured out the Oracle trace files, how to parse them and had created a stress test benchmarking system based on the trace files. On top of that, he wrote the first graphic monitoring tool I’d ever seen which led to my beginning to write graphic performance tools. Both Roger and Jan Simon changed my career.

5- What are the resources that you consult regularly to keep up-to-date with Oracle topics?

Oaktable email list and the Oaktable blog roll.

6- 3 Books that you would recommend reading to every Oracle professional?

Trouble Shooting Oracle Performance – Cristian Antognini
- best overall Oracle performance book
Oracle Wait Interface – Richmond Shee, Kirtikumar Deshpande and K. Gopalakrishnan
- best book on Oracle wait interface which is crucial to Oracle performance tuning
Practical Oracle 8i- Jonathan Lewis
- old book but one of the best for understanding how Oracle works, what the important features are and most importantly for understanding how to boundary test new features to see if the will meet your needs or break before investing time and becoming dependent on a feature that might not be sufficient.

7- What was the biggest technical challenge in your career so far?

By far my biggest challenge has been and still is selling my ideas to people. My ideas have been successful but there has been a lot missed opportunities. I offered my ideas for performance tuning to Quest who turned them down, but Oracle picked them up in OEM 10g and it was a great success. At Oracle I had many more ideas that were turned down but then Embarcadero picked them up and they were successful in DB Optimizer. At Embarcadero I had more ideas that were turned down and now Delphix is picking them up. I wish I could just create a Steve Jobs reality distortion field and enroll people into my ideas. It would be more fun, efficient and stable!

8- How do you disconnect from work in your spare time?

That’s tough. I think about work too much. Connecting with my little boy is the best way.

9- What advise would you give to somebody who just started studying computer science?

Tough to say. I wish I had majored in computer science but that was 25 years ago. Now I’m not sure I’d still want to major in computer science. Computer usage is becoming ubiquitous. I’d focus more on current problems such as how to mine data and visualize data. Actually if I was in school now I’d be more interested entrepreneurship or possibly biotech and nanotech.


An introduction to collectl

Some of you may have seen on twitter that I was working on understanding collectl. So why did I start with this? First of all, I was after a tool that records a lot of information on a Linux box. It can also play information back, but this is out of scope of this introduction.

In the past I have used nmon to do similar things, and still love it for what it does. Especially in conjunction with the nmon-analyzer, an Excel plug in it can create very impressive reports. How does collectl compare?

Getting collectl

Getting collectl is quite easy-get it from sourceforge:

The project website including very good documentation is available from sourceforge as well, but uses a slightly different URL:

I suggest you get the archive-independent RPM and install it on your system. This is all you need to get started! The impatient could type “collectl” at the command prompt now to get some information. Let’s have a look at the output:

$ collectl
waiting for 1 second sample...
#cpu sys inter  ctxsw KBRead  Reads KBWrit Writes   KBIn  PktIn  KBOut  PktOut
1   0  1163  10496    113     14     18      4      8     55      5      19
0   0  1046  10544      0      0      2      3    164    195     30      60
0   0  1279  10603    144      9    746    148     20     67     11      19
3   0  1168  10615    144      9    414     69     14     69      5      20
1   0  1121  10416    362     28    225     19     11     71      8      35

The “ouch” has been caused by my CTRL-c to stop the execution.

Collectl is organised to work by subsystems, the standard option is to print CPU, disk and network subsystem, aggregated.

If you don’t know what information you are after, you could use the –all flag to display aggregated information across all subsystems. Be warned that you need a large screen for all that output! For even more output, add the –verbose flag to the –all option and you need a 22” screen at least. The verbose flag prints more output, as the name suggests. For the disk subsystem you can view the difference:

$ collectl -sd -i 5 --verbose
waiting for 5 second sample...

#KBRead RMerged  Reads SizeKB  KBWrite WMerged Writes SizeKB
162     136     10     15      187      30     19      9
109      24      9     11      566     118     23     24
$ collectl -sd -i 5
waiting for 5 second sample...
#KBRead  Reads KBWrit Writes
9865     73    190     23

Each subsystem can be queried individually, the default monitoring interval is 1 second. The man page for collectl lists the following subsystems:


b - buddy info (memory fragmentation)
c - CPU
d - Disk
f - NFS V3 Data
i - Inode and File System
j - Interrupts
l - Lustre
m - Memory
n - Networks
s - Sockets
t - TCP
x - Interconnect
y - Slabs (system object caches)

As the name suggests, these sub systems provide summary information. Summaries are ok for a first overview, but don’t forget that information is aggregated and detail is lost.

From an Oracle point of view I’d probably be most interested in the CPU, disk and memory usage. If you are using RAC, network usage can also be interesting.

Detailed subsystem information is available for these (again taken from the excellen manual page):

D - Disk
E - Environmental data (fan, power, temp),  via ipmitool
F - NFS Data
J - Interrupts
L - Lustre OST detail OR client Filesystem detail
N - Networks
T - 65 TCP counters only available in plot format
X - Interconnect
Y - Slabs (system object caches)
Z - Processes

You can combine subsystems, and you can combine detail and summary information. Bear in mind though that this becomes a lot of information for a putty session of gnome-terminal!

In interactive mode, you might want to consider the –home flag, which does a top-like refresh and prints real time information without scrolling: very neat!

But even with the –-home option, digesting all that information visually can be a bit daunting, which leads me to my next section.

Generating graphical output

While all the textual information is all nice and good, it is difficult to visualise. Collectl can help you with that as well. All you need to do is generate a file in tab format, which is as simple as adding the –P and –f options. Since you can’t be overwhelmed with the information gathered in a file (unlike on standard out), you could use the detail switches. If you have the luxury, create the file with the information in a directory expored via samba and analyse it with Excel or other utilities. It’s possible to use gnuplot as well, but I found that a bit lacking for interactive use. The collectl-utils provide a CGI script to analyse collectl files on the host which can be convenient. Here is an example for measuring CPU, memory and all disks with a monitoring interval of 15 seconds. The file will be in “Plot” format (-P) and goes to /export/collectl/plotfiles:

$ collectl -sdmn -i 15 -P -f /export/collectl/plotfiles

Note that you can’t use the verbose flag here, and you also shouldn’t use a file name with the –f switch!

The resulting file is called After renaming it to hostname-yyyymmdd.txt it can quite easily be imported using your favourite spreadsheet application. Imagine all the graphs you could produce with it! Also the header contains interesting information:

# Collectl:   V3.5.1-1  HiRes: 1  Options: -sdmn -i 15 -P -f /export/collectl/plotfiles
# Host:       node1 DaemonOpts:
# Distro:     Red Hat Enterprise Linux Server release 5.5 (Tikanga)  Platform:
# Date:       20110805-142647  Secs: 1312550807 TZ: +0100
# SubSys:     dmn Options: z Interval: 1 NumCPUs: 16  NumBud: 0 Flags: i
# Filters:    NfsFilt:  EnvFilt:
# HZ:         100  Arch: x86_64-linux-thread-multi PageSize: 4096
# Cpu:        AuthenticAMD Speed(MHz): 2210.190 Cores: 4  Siblings: 4
# Kernel:     2.6.18-194.el5  Memory: 65990460 kB  Swap: 16809976 kB
# NumDisks:   173 DiskNames: c0d0 sda sdb sdc sdd sde sdf sdg sdh sdi sdj sdk sdl sdm sdn sdo sdp sdq sdr sds sdt sdu sdv sdw sdx sdy sdz sdaa sdab sdac sdad sdae sdaf sdag sdah sdai sdaj sdak sdal sdam sdan sdao sdap sdaq sdar sdas sdat sdau sdav sdaw sdax sday sdaz sdba sdbb sdbc sdbd sdbe sdbf sdbg sdbh sdbi sdbj sdbk sdbl sdbm sdbn sdbo sdbp sdbq sdbr sdbs sdbt sdbu sdbv sdbw sdbx sdby sdbz sdca sdcb sdcc sdcd sdce sdcf sdcg sdch sdci sdcj sdck sdcl sdcm sdcn sdco sdcp sdcq sdcr sdcs sdct sdcu sdcv sdcw sdcx sdcy sdcz sdda sddb sddc sddd sdde sddf sddg dm-0 dm-1 dm-2 dm-3 dm-4 dm-5 dm-6 dm-7 dm-8 dm-9 dm-10 dm-11 dm-12 dm-13 dm-14 dm-15 dm-16 dm-17 dm-18 dm-19 dm-20 dm-21 dm-22 dm-23 dm-24 dm-25 dm-26 dm-27 dm-28 dm-29 dm-30 dm-31 dm-32 dm-33 dm-34 dm-35 dm-36 dm-37 dm-38 dm-39 dm-40 dm-41 dm-42 dm-43 dm-44 dm-45 dm-46 dm-47 dm-48 dm-49 dm-50 dm-51 dm-52 dm-53 dm-54 dm-55 dm-56 dm-57 dm-58 dm-59 dm-60
# NumNets:    8 NetNames: lo: eth0: eth1: eth2: eth3: sit0: bond0: bond1:
# SCSI:       DA:0:00:00: ... DA:2:00:00:00

This should be enough to remind you of where you were running this test.

Run duration and interval

Use the –i flag to change the monitoring interval, this is the same as you’d do with SAR or iostat/vmstat and the like. You could then either use the –c option to count n samples, or alternatively use –R to run for n weeks, days, hours, minutes or seconds, each of which are abridged with their first letter. For example to run for 15 minutes with samples taken every 15 seconds, you’d say collectl –i 15 –R 15m.

Quick and dirty

If you need an interactive overview of what’s going on top-style, you can use the –top flag. This will print output very similar to the top command, but this time you have a lot more options to sort on. Use collectl –showtopops. This is so cool that I couldn’t help just listing the options here:

$ collectl --showtopopts
The following is a list of --top's sort types which apply to either
process or slab data.  In some cases you may be allowed to sort
by a field that is not part of the display if you so desire


vsz    virtual memory
rss    resident (physical) memory

syst   system time
usrt   user time
time   total time

rkb    KB read
wkb    KB written
iokb   total I/O KB

rkbc   KB read from pagecache
wkbc   KB written to pagecache
iokbc  total pagecacge I/O
ioall  total I/O KB (iokb+iokbc)

rsys   read system calls
wsys   write system calls
iosys  total system calls

iocncl Cancelled write bytes

Page Faults
majf   major page faults
minf   minor page faults
flt    total page faults

Miscellaneous (best when used with --procfilt)
cpu    cpu number
pid    process pid
thread total process threads (not counting main)


numobj    total number of slab objects
actobj    active slab objects
objsize   sizes of slab objects
numslab   number of slabs
objslab   number of objects in a slab
totsize   total memory sizes taken by slabs
totchg    change in memory sizes
totpct    percent change in memory sizes
name      slab names

Filtering information

Let’s say you are running multiple ASM disk groups in your system, but you are only interested in the performance of disk group DATA. The –sD flag will print all the information for all disks (LUNs) of the system. Collectl reports disks as the native devices and dm- devices. For multipathed devices you obviously want to look at the dm- device. You could use the multipath –ll command to map dm- device to WWIDs and your disks in the end. Let’s say you found out that the disks you need to look at are /dev/dm-{1,3,5,8} you could use the –dskfilt flag, which takes a perl regex. In my example, I could use the following command to check on those disks:

collectl -sD -c 1 --dskfilt "dm-(1\b|3\b|5\b|8\b)"
waiting for 1 second sample...

#          <---------reads---------><---------writes---------><--------averages--------> Pct
#Name       KBytes Merged  IOs Size  KBytes Merged  IOs Size  RWSize  QLen  Wait SvcTim Util
dm-1             0      0    0    0       0      0    0    0       0     0     0      0    0
dm-3             0      0    0    0       0      0    0    0       0     0     0      0    0
dm-5             0      0    0    0       0      0    1    1       0     0     0      0    0
dm-8             0      0    0    0       0      0    0    0       0     0     0      0    0

Note the “\b” boundary, which is my uneducated way to saying that the expression should match dm-1, but not 10, or anything else that extends beyond number one.

Additional filters you can apply can be found in the output of collectl –showsubopts as well as in section subsystem options in the manpage.


Used correctly, collectl is the swiss army knife for system monitoring, the level of detail which can be gathered is breathtaking. Thanks Mark Seger! And aplogies for all the good stuff I’ve been missing!

Nice one:

Nice one:

The New Order Oracle Coding Challenge 3 – Mind Boggle

August 5, 2011 (Modified August 7, 2011) (Back to the Previous Post in the Series) (Forward to the Next Post in the Series) In part 1 of this series the challenge was to simply reverse the order of digits in the numbers from 1 to 1,000,000 to find that cases where the numbers formed by the reverse ordered digits [...]