Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

The only thing you should NOT ask Icelanders (and maybe Norwegians) to do...

I love to visit Iceland. Not because of the landscape (We Do Not Use landscapes), but because of the people there. Man, they're funny and good to talk to.

This time (last week, for two days only) I detected a deep, slow-burning anger in the folks up there, that I haven't felt before.

One of them - a very stylish, suit-dressed bartender in his 40's, who broke his principle about not talking politics with guests (hey, the bar was empty), said that he suspected there would be real riots in the fall of 2010 if the few guilty bastards were not punished for real in court.

The handful of real bastards have been driven out of Iceland, by the way: People simply spat on them when they met them on the street AND painted their houses and cars red at night. One of the bastards re-painted his house in its original color. Guess what happened the following night.

They all now live in the UK or Florida.

Fine and good. Now you know.

But this post is about the one thing you should never ask an Icelander to do: A list.

You see, back around year 900 several Norwegian vikings sailed to Iceland. They pretty much remembered everything: Clothes. Food. The ship. Oares. Live animals. Tools. Pen & Paper. You know - all the usual stuff for a 400 years voyage.

But whoever was in charge of The Norwegian Iceland Travel List forgot one thing, and whether they discovered it quickly (i.e. on the journey) or when they had settled in in their small mud huts, I do not know. But man, they must have told The List Guy a thing or two upon suddenly remembering what they had forgot:


Anyway, they apparently decided to do something about it, because DNA-tests of Icelandic women some years ago confirmed that they originate from the British isles.

The British scientist who found this out, and who was interviewed on Danish Radio, commented on the fact that Icelandic women look Pretty Damn Good by saying (rather drily): "They probably didn't take the ugly ones."

So now you know.

Cloning prior to patching/upgrading/CPU: what do you do?

In addition to my previous post about cloning, please tell me your experiences and practices. What do you do when altering your Oracle database software? I think a home name should include a FULL version number (so instead of 11.2.0) to indicate the version of the home. If you strictly clone the home before [...]

Oracle datafile IO latency – Part 1

On my post about observing the Exadata V1 I had an interesting comment posted by Mark Seger (author of collectl and collectl utilities) about the correlation of activities across a system, the sample and snap time, and seeing the state of the subsystem before and after

The comment made me curious about the effect of snap intervals on the performance numbers of the datafiles and block devices.. especially on the latency numbers.. so I made a few test cases and created some scripts that would give me 5 seconds, 10 minutes, and 60 minutes output of latency numbers on the database. Also running 5 seconds interval of OSWatcher to give me a view on the block devices.

As I was doing all of this, I had an interesting discovery about how the latency output of the datafiles are being computed and I was able to quantify by having the performance numbers how average could be misleading and mask the problem on the datafile IO latency.

I did a one-take (amateur </p />

    	  	<div class=

Cloning your Oracle database software installation

With the coming of the binary version of the installer repository (with Oracle 9 if my memory serves me well), some things changed which did not make everybody happy. The biggest disappointment of most people: it was not supported to tar (or zip, cpio, whatever) your Oracle installation and put it in another place. This [...]


A recent question on the OTN database forum asked: “What’s the difference between index rebuild unrecoverable and nologging?” The most important difference, of course, is that unrecoverable is a deprecated option so you shouldn’t be using it even though it still works. Another important difference is that unrecoverable tells you exactly the risk you are [...]

Oracle's SQL Tuning Pack - how well does it work?

Have you used Oracle's SQL Tuning Pack? What were your experiences like?
At my last consulting gig 2 out 3 statements I "tuned" with the SQL tuning pack actually ran significantly more slowly after applying the profiles recommended. I thought it was just my bad karma and luck of the draw until recently. Recently I've started asking other DBAs what their experiences were and generally finding less than enthusiastic responses. In the classes on Oracle 10g and OEM, I've always cautioned that profiles should not be blindly trusted and give an example using a Swingbench query where the SQL runs more slowly after the profile.If applying the profile, I recommend to jump immediately to the SQL performance page and to verify the effect of the profile on the load of the query.

Above is a page from OEM after the SQL Tuning Advisor has been run, a profile is suggested, and now I can implement the profile.

After the profile has been implemented, I'd recommend clicking on the SQL_ID to see go to the SQL details page and see what the new execution plan looks like on the load chart

In the above SQL details we can see the new execution plan hash 467377872 and that it's load is now higher. The number of executions by the application, swingbench remained the same, but the load by the SQL statement with the new profile is higher.
At this point I'd click on "Tuning Information" and back out the profile.

I thought the above example was a fluke along with the couple of bad experiences I had at a customer site but after asking a number of other DBAs who have used the SQL Tuning Advisor and who have responded less than enthusiastically, I'm now curious to poll a wider audience.

A couple of thoughts:
One, I think the reason the STA's profile created more load is because the application, swingbench, actually only uses one row from the query result set and ignores the rest of the result set. I'm guessing that the STA tuned the query for the entire result set.
Two, it seems to be a serious flaw that the STA doesn't actually run the query with the new profile to compare the before and after. At least this should be offered as an option.
It seems that Oracle 11 will address some of this issues in a round about way with SQL baselines and the evolve. See
From the above link, for example

Baseline Plan Test Plan Improv. Ratio
-------------- --------- -------------
Rows Processed: 0 0
Elapsed Time(ms): 5036 1033 4.88
CPU Time(ms): 254 700 .36
Buffer Gets: 1728 43945 .04
Disk Reads: 254 22 11.55
Direct Writes: 0 0
Fetches: 49 22 2.23
Executions: 1 1

Though I haven't investigated how the SQL Baselines and the evolve option are incorporated in OEM 11g.

Index branches

Some time ago I published a little script that showed you how to read an index treedump and summarise the leaf block usage in the logical order of the index leaf blocks – allowing you to see fairly easily if the index had any areas where the blocks were poorly filled. Here’s another  way of looking [...]

Do Oracle 11g features weaken security?

I did a session at the Logica Guru4Pro event a few weeks ago and posted the slides to my site on my Oracle security white papers page . I also talked about this in my blog in a post titled....[Read More]

Posted by Pete On 01/07/10 At 12:01 PM

Instrumentation Overhead

One of the nice things about conferences such as ODTUG is the networking and ideas exchange that they facilitate. One of the nice things about Twitter is that it allows people who aren’t there to catch up on these two conference aspects. Even @doug_conference who doesn’t use Twitter recognizes this. Anyway, @alexgorbachev commented on [...]

Getting Help

I hope people won’t take this as a suggestion that I want them to start using this blog like a forum – but I’d like to highlight a note written some time ago by Randolf Geist on the OTN DBA Forum: HOW TO: Post a SQL statement tuning request – template posting It’s worth following his link to [...]