Search

Top 60 Oracle Blogs

Recent comments

December 2011

Challenges and Chances of the 11g Query Optimizer

Challenges and Chances of the 11g Query Optimizer is the name of a presentation I gave at several events (e.g. Trivadis Performance Days, Oracle OpenWorld, DOAG Konferenz, UKOUG Conference) throughout 2011. Its abstract is the following:

With every new release, the query optimizer is enhanced. Oracle Database 11g Release 1 and Release 2 are no exception to the rule. Specifically, they introduce key improvements in the following areas: indexing, optimization techniques, object statistics and plan stability. The aim of this presentation is to review the new features from a practical point of view as well as to point out challenges related to them. In other words, to let you know what you can expect from the query optimizer when you upgrade to Oracle Database 11g.

UKOUG 2011 Summary

This year's UKOUG 2011 conference in Birmingham was so packed and multi-dimensional for me that I decided it's going to take a few very different posts to describe my experiences without sending any unfortunate readers into a deep stupor, so the overall summary first and then a couple of more focussed posts to follow.

Sunday

When I left the hotel in Edinburgh on Sunday morning and the snow was falling, I feared the worst after the horrible travel experiences almost exactly one year before but, in the end, a little de-icing and a small delay for incoming pandas weren't enough to hold me up too much. I had to walk round the block for a while when I got to the Hyatt to secure a smoking room but, with presentation preparation on my early agenda, it was worth it.

IOT Trap

In a recent question on OTN someone asked why Oracle had put some columns into the overflow segment of an IOT when they had specified that they should be in the main index section (the “IOT_TOP”) by using the including clause.

The answer is simple and devious; there’s a little trap hidden in the including clause. It tells Oracle which columns to include, but it gets applied only after Oracle has re-arranged the column ordering (internally) to put the primary key columns first. The OP had put the last column of the primary key AFTER the dozen columns in the table that he wanted in the index section, but Oracle moved that column to the fifth position in the internal table definition, so didn’t include the desired 10 extra columns.

RMOUG Training Days 2012

RMOUG Training Days 2012
Event date: 
Tue, 2012-02-14 - Thu, 2012-02-16

One of the largest Oracle user group conferences in the world, the "Training Days 2012" conference presented by the Rocky Mountain Oracle Users Group (http://www.RMOUG.org) is two days on Wednesday and Thursday (15-16 Feb), consisting of more than 140 presentations by more than 100 of the top speakers in the world.  Registration for this conference, from as low as US$265 - US$340 for early registration, is an incredible educational value.  Click here to register for this event.
 

Right Deep, Left Deep and Bushy Joins

At UKOUG someone asked me if DB Optimizer’s VST diagrams could deal with left deep verses right deep execution plans. What is right deep verses left deep? Good question. In join trees (not VST) the object on the left is acted upon first then the object on the right.  Below are left deep and right deep examples of the same query, showing

Cary Millsap : Mastering Oracle Trace Data…

I was ribbing Cary about missing his unconference session (due to migraine and alarm malfunction). Lenz Grimmer and I both tweeted him to see where he was and in return we received this message in reply.

Very generous indeed. Unfortunately Lenz was flying home so he couldn’t make it. I had a conversation with Cary about it saying I wanted to come, but felt weird about accepting a freebie, so I thanked him for the offer and left it at that.

UKOUG 2011: Days 1 – 3…

I went to lots of good presentations over the three days.

Day 1:

  • SQL Tuning – Kyle Hailey
  • Clonedb – Me (not counting myself in the “good” list, just placing myself in the timetable)
  • OakTable Talks
  • Emerging Trends in RAC – Julian Dyke
  • Upgrading your Cluster to 11gR2 – Martin Bach
  • Learning about life through business and software – Cary Millsap

Day 2:

  • NFS Tuning – Kyle Hailey
  • OakTable Talks
  • RAC Attack – Organized by Jeremy Schneider (I spent the rest of the day helping out here)

Day 3:

Recent SPARC T4-4 TPC-H Results Prove Oracle Can Do Better Than…Oracle! Part II.

My recent post entitled Recent SPARC T4-4 TPC-H Benchmark Results. Proving Bandwidth! But What Storage? provoked the following comment/question  from a reader:

Does this summarize your point(s)?

TPC-H produces a number which is a reflection of (hourly?!?) system throughput.

System throughput may not be indicative of system “performance” to its uses b/c users are typically most intersted in response time. Thus, TPC-H is a easily mis-used benchmark for comparing real world performance.

Christmas Explain Plan Patterns

Here’s a lovely Candy Striped pattern in an Explain Plan. Looks like the traditional Christmas candy canes. Just in time for the Holiday Season!

Test Data

The UKOUG conference is over for another year – but it has left me with plenty to do and lots of things to investigate. Here’s just one little point that I picked up during one of the 10 minute “Oak Talks” that members of the Oak Table Network were doing in the lunch breaks.

There is a fairly well-known strategy for generating a list of numbers by using a “select from dual … connect by …” query, but I hadn’t realised that there were two ways of using it. The code I’ve usually used is this:

select
        rownum id 
from
        dual 
connect by 
        rownum <= 4000
;

But it looks as if most people use it like this:

select
        rownum id 
from
        dual 
connect by 
        level <= 4000
;