Who's online

There are currently 0 users and 37 guests online.

Recent comments


Null Quiz

Here’s an example I saw a few months ago of the confusion caused by NULL. As the owner of the problem put it: the first query, run from SQL*Plus for testing purposes, takes no time to complete; but when “put into a pl/sql cursor” (as shown in the second query) it takes ages to complete.

What’s going on ?

    select id
         , description
         , inventory
    from the_table
    where category =  nvl(null, category);


    open c_results for
        select id
             , description
             , inventory
        from the_table
        where category =  nvl(p_user_category, category);
    fetch c_results into v_id, v_description, v_inventory;
    close c_results;

After pondering the problem for a while the OP managed to get the required level performance by changing the predicate to this:

New Version Of XPLAN_ASH Utility

A new version of the XPLAN_ASH tool (detailed analysis of a single SQL statement execution) is available for download. The previous post includes links to video tutorials explaining what the tool is about.

As usual the latest version can be downloaded here.

The new version comes with numerous improvements and new features. The most important ones are:

  • Real-Time SQL Monitoring info included
  • Complete coverage including recursive SQL
  • Improved performance
  • 12c compatible
  • Simplified usage

Index Sorting

From time to time I’ve mentioned the fact that the optimizer will sort indexes alphabetically by name as the last tie-breaker when two plans have the same cost. Thanks to an email that arrived a couple of days ago I’ve been alerted to event 10089 (which has been around since at least with the description: “CBO Disable index sorting”.

Setting this event will disable the index sorting (which may result in some tied execution plans changing, of course) and may reduce contention on the row cache objects latches if you’re running or later and doing a lot of hard parsing.

Wrong Index 2

A couple of days ago I wrote an article about Oracle picking the “wrong index” after an index rebuild, and I mentioned that the sample data I had generated looked a little odd because it came from a script I had been using to investigate a completely different problem. This note describes that other problem, which appeared on the Oracle-L mailing list last month.

12c I/O debug

By my count there are 109 new v$ and gv$ dynamic performance views in 12c (so far) – and Glen Fawcett has posted a short note on a group that may be of particular benefit to anyone who finds they really have to delve into esoteric I/O problems from time to time. For the less exotic, there’s v$io_outliers and v$lgwrio_outliers which give details about any very slow I/Os – for the more exotic there’s v$kernel_io_outliers – which is the really fascinating one.

Here’s a short session capture focused on v$io_outliers:

12c Determinism

Following a comment from Marcin Przepiorowski on my last post, it crossed my mind to check whether “with” functions can be deterministic – the answer seems to be “not yet”. Here’s a simple script that you can run from end to end to check current and future releases – it compares inline (with) and standalone functions when the “deterministic” keyword has been used.

12c Funny

Here’s a quirky little thing I discovered about 5 minutes after installing 12c Beta 1 (cut-n-pasted from SQL*Plus):

create or replace view v$my_stats
        v$mystat        ms,
        v$statname      sn
        sn.statistic# = ms.statistic#
 14  ;
create or replace view v$my_stats
ERROR at line 1:
ORA-00999: invalid view name

You can’t create views with names that start with V$ or GV$ in the sys schema. Presumably to eliminate the risk of someone’s clever view definition from overwriting and disabling one of the distributed dynamic performance views by accident.


I thought I’d try to spend some of today catching up on old comments – first the easier ones, then the outstanding questions on Oracle Core.
The very first one I looked at was about pushing predicates, and the specific comment prompted me to jot down this little note about the 10053 trace file (the CBO trace).

CUR Gets

Here’s an interesting anomaly from the OTN database forum – PL/SQL track. It’s a single row delete from a table that does a huge number of db block gets and (in the first example shown) physical reads. Here’s the supplied output with autotrace enabled:

Speaking at E4 2013! … and some Exadata Patents good stuff

I’ll be co-speaking with Tyler Muth at E4  and we will be talking about Performance Visualization…
some of you may already know that I’m really a big fan of Tableau and just to give you an example on what the tool can do I’ve consolidated all the different viz that I’ve done during my sizing & performance gigs here and Tyler has also been doing a lot of Exadata performance & benchmarking gigs lately and there were a couple of times where we collaborated on specific viz (him doing it on R) just to validate each other’s work. In short, we are passionate about this stuff. And we are going to be awesome :)