Who's online

There are currently 0 users and 26 guests online.

Recent comments



warning: Invalid argument supplied for foreach() in /www/oaktable/sites/all/modules/cck/content.module on line 1284.

Friday Philosophy – Is the CBO becoming way too complex?

I was at the SIOUG annual conference in Slovenia this week (and a very good conference it was too) and I was watching a presentation by Christian Antognini about how the CBO learns by it’s mistakes. This was of course mostly about adaptive cursor sharing and cardinality feedback. Chris was also able to share a few tid-bits about 12c enhancements in this area. I can’t go into details, but basically it looks like the CBO is going to not only enhance those two features but there is a new one where the CBO can change the plan on the fly, as the same query progresses.


There’s never enough time to read everything that’s worth reading, so even though Guy Harrison’s blog is one of the ones worth reading I find that it’s often months since I last read it. Visiting it late last night, I found an interesting batch of articles spread over the last year about the performance of SSD – the conclusions may not be what you expect, but make sure you read all the articles or you might end up with a completely misleading impression:

OOW 2012 – And was there news on the XMLDB front…?

Just finished my Birds of a Feather XMLDB panel session in the Marriott Hotel and now looking back on an event full day. It all started of with a Keynote session with among others Mark Hurd. the most interesting bit, in my honest opinion, was the announcement of more details in Oracle 12c. I will …

Continue reading »

OakTable World 2012

This Monday Oct 1, 2:00-2:50PM I’ll be presenting at the OakTable World which used to be called as Oracle Closed World, this year it’s a full two-day conference with awesome speakers lineup and very interesting topics… you can just watch this video by Mogens Norgaard to know more about the roots of this very technical event.

My presentation title is “Where did my CPU go?” – monitoring & capacity planning adventures on a consolidated environment .. and If you love to hack the ASH and AWR data and curious about CPU capacity planning then you’re really going to enjoy this session.. it’s going to be all about CPU ;)


DBMS_APPLICATION_INFO for Instrumentation

I just wanted to put up a post about DBMS_APPLICATION_INFO. This is a fantastic little built-in PL/SQL package that Oracle has provided since Oracle 8 to allow you to instrument your code. i.e record what it is doing. I’m a big fan of DBMS_APPLICATION_INFO and have used it several times to help identify where in an application time is being spent and how that pattern of time has altered.

Some PL/SQL developers use it and some don’t. It seems to me that it’s use comes down to where you work, as most PL/SQL developers are aware of it – but not everyone uses it (a friend of mine made the comment recently that “all good PL/SQL developers use it“. I can understand his point but don’t 100% agree).

Exclusion of Unioned SQL in Views – Followup

Last week I put up a post about how Oracle can filter out sections of a union view..

Within the comments I put up another example where the CBO did not filter out all but one of the Union views despite my replicating the exact WHERE clause of one of the unioned statements. Jonathan Lewis posted a followup to say “Oracle can be very touchy about how it plays this game” and made a prediction of how the CBO would handle a slightly different scenario.

This was the extra scenario and I include brief details on creating the unioned view too. NB all on Oracle {non-Exadata :-) }

Exclusion of Unioned SQL in Views?

Question – you have a view definition of the following format:

select 1 as d_type, col_a,col_b,col_c
from TAB_X, TAB_Y, TAB_Z
where {your predicates}
select 2 as d_type, col_a,col_b,col_c
from TAB_P, TAB_Q, TAB_R
where {your predicates}
select 3 as d_type, col_a,col_b,col_c
from TAB_X X, TAB_Y, TAB_Z
where {your predicates}

You now select from the view and your code includes a filter predicate such as:


Parallel Execution Analysis Using ASH - The XPLAN_ASH Tool


Note: This blog post actually serves three purposes:

  1. It introduces and describes my latest contribution to the Oracle Community,  the "XPLAN_ASH" tool

  • It accompanies a future OTN article on Parallel Execution that will be published some time in the future

  • It is supposed to act as a teaser for my upcoming "Parallel Execution Masterclass" that will be organized by Oracle University and can be booked later this year
  • Table Of Contents


    Real-Time SQL Monitoring Overview

    Real-Time SQL Monitoring Shortcomings

    Adaptive STAT lines in SQL trace

    Lately I’ve been using SQL runtime execution statistics combined with SQL monitor for performance diagnostics and, honestly, almost forgot about SQL trace. So this note is not very useful to me but it might be to someone: along with ALL_EXECUTIONS option appeared in (I believe) 11gR2, there’s a new option starting with which can significantly decrease amount of data in the trace files for STAT lines compared to ALL_EXECUTIONS, still providing some of them from time to time.

    Here is a case to demonstrate: