Top 60 Oracle Blogs

Recent comments

July 2012

Virtualization Presentation…

Last year I wrote a post about how I was being driven crazy by silly things people try to do with virtualization. That spawned the idea to do an “introduction to virtualization” style presentation.

When I posted my abstracts for the OTN Tour of Central America I accidentally included the abstract for this presentation. It wasn’t until I was already on the road and reading through a conference agenda that I noticed this and had one of those “oh sh*t!” moments. :) Since I had already put down a bunch of ideas for the presentation, I decided to write it, rather than ask for a change to the agenda. I’ve now presented this session a few times…


Some features in this post require a Diagnostics Pack license.

Just a small tip that could make things a little easier for you one day when you are trying to work out the underlying cause of a SQL execution plan change that leads to degraded performance, after the problem has occurred.

There are plenty of more technical blog posts out there referring to the contents of the OTHER_XML column in DBA_HIST_SQL_PLAN and other dictionary views. For example, this Jonathan Lewis post and the follow-up comments focus on the peeked values of bind variables.

Recursive SQL

A question came up on the OTN database forum a few days ago asking why a very simple query against a hash partitioned table was doing so much physical I/O to get a very small amount of data from a single partition:

I have table hash-partitioned on an indexed column “id” which is non-unique and part of my primary key. Inside each partition, rows with the same id are located close to each other which is done by dbms_redefinition reorg using orderby_cols. The intention is to reduce the amount of physical reads since there are no queries that don’t filter on the id column.

OTN Tour of Latin America: Guatemala (Continued)…

We arrived in Guatemala at night, went straight to the airport and parked ourselves in the hotel bar for a few minutes before bed. Seeing armed guards in front of the hotel restaurant was more than a little unnerving. On one hand, having the guards should make you feel safer, but on the other hand, the fact they may be necessary is a little scary…

Red Gate Webinars

Thanks to anyone who attended last week's webinar on OEM Performance Pages, organised by Red Gate Software. For those of you who couldn't make it and might be interested, a video is available here.

There were a number of questions after the webinar that I didn't feel there was sufficient time to answer properly so if anyone who asked those questions wants to post them in the comments below, maybe I can attempt to give more detailed answers here or in a seperate post.

Expert Oracle Exadata Translated into Chinese

Last year at Oracle Open World I was introduced to a guy named Zhang Leyi (Kamus). He said he had been hired to translate our Expert Exadata book into Chinese. There were a couple of other guys on the team as well – Kaya Huang 黄凯耀 and Jacky Zhang 张瑞. Well they have apparently finished their work and the book is due to be released this month. Kamus sent us a copy of the cover (see below). It looks very sexy don’t you think?

PK Problem

I have been saying for a very long time (probably since some time in the last century) that if you want to add a primary key (or unique) constraint to a table where there might be some duplicate data then one of the best strategies for doing so might be to create a non-unique index (with the online option), then add the constraint in the state enable novalidate, and then validate the constraint. For example:

create table t1

create index t1_i1 on t1(object_id) online;
-- collect stats
alter table t1 add constraint t1_pk primary key(object_id) enable novalidate;
alter table t1 modify constraint t1_pk validate;

OTN Tour of Latin America: Guatemala…

The Guatemala event is over. I will write a proper post about it on the plane tomorrow, but I just wanted to share three photos from the speakers room.

The linux ‘perf’ utility with EL 6 – part 2

In my previous post about perf I showed how it can be used to determine the function which is used the most systemwide.

One thing it helped me with, is determining the impact of swapping on a system. The testcase I build for that is kind of a no-brainer (setting up a 3GB SGA with 2GB of physical memory in a test VM), but think about a few things: I regularly encounter systems who have an amount of swap allocated. This can mean memory is simply plain over allocated, but can also be older pages are just swapped out by Linux “just in case”. This probably will happen more with the new oracle recommendation for “swappiness” (the eagerness of Linux to swap) to set it to 100 (used to be 60). The amount of swap transfers can be measured, but the regular tools cannot measure the impact. With perf, this is possible. - Dynamic Sampling (III) Part II

Just again a short note that the third installment's final part of the series on Dynamic Sampling has been published on

I show there how indexes can be used to get Dynamic Sampling working with rare / clustered values, and expand on further ideas how to deal with tricky situations for the optimizer.