A comment on a recent post of mine pointed me to a question on the OTN SQL and PL/SQL Forum where someone had presented a well-written test case of an odd pattern of behaviour in ANSI SQL. I made a couple of brief comments on the thread, but thought it worth highlighting here as well. The scripts to create the required tables (plus a few extras) are all available on OTN. If you create only the four tables needed and all their indexes you will need about 1.3GB of space.
Kerry Osborne has a short post to point out that the last book I worked on has been translated into Chinese. The photo of the book cover looks pretty cool…if only I could read anything except for our names and a few straw English words
This book is a must-read for anyone that wants to cut past the hype an actually learn Exadata.
I mentioned the trip to Honduras at the end of my previous post. We landed in Tegucigalpa, Honduras with no dramas. A couple of guys from the University were there to meet us, which was a nice touch.
When getting foreign currency from an ATM, I would suggest you ignore anything Debra Lilley tells you to do. Her expert advice lead me to draw out the equivalent of $10 US, rather than the $100 US I planned to get. We couldn’t even pay for our food.
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…
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.
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…
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 as select * from all_objects ; 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;
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.
There’s no such thing as proactive tuning. Either you do it right before it goes into production (in which case it’s called “design”) or you react to the nasty surprises you get after it’s gone live.
Even if you do something about the problems before anyone has complained that’s still “reactive” not “proactive” – and the trick is to be reactive as soon as possible. For most companies the best strategy is probably to stick a big screen on the wall with the “Top Activity” page from OEM (or equivalent) so that the key players (DBAs and Developers) can keep a casual eye out for resource hogs and prioritise dealing with them as they appear.
We arrived at Trinidad & Tobago in the evening and it was hot and humid. I decided to wash some clothes in the hotel room sink, which was a mistake. It was so humid that even after about 36 hours my clothes were still damp and smelled a bit like wet dog when I packed them. Asking around it sounds like Guatemala will be just as humid, so I might have to use a hotel laundry service to get my clothes clean.