Who's online

There are currently 0 users and 23 guests online.

Recent comments

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


dbms_dnfs and

If you are using to use dbms_dnfs to clone your database as described in MOS note 1210656.1 or on Tim's Hall blog - Direct NFS (DNFS) Clonedb in Oracle Database 11g Release 2 (Patchset and your database version is there is one additional step which is not covered by script and has to be done manually.

In it was enough to run crtdb.sql and dbren.sql scripts generated by but this is what happen when you will run dbren.sql on
SQL> !cat dbren.sql

Exadata Smart Scan Projection Limitation

Here is an interesting limitation to Exadata Smart Scans - if more than 254 columns from a table (not HCC compressed, more on that in moment) need to be projected, Smart Scans for that particular segment will be disabled and Exadata will fall back to conventional I/O.

HAVING Cardinality

When performing aggregate GROUP BY operations an additional filter on the aggregates can be applied using the HAVING clause.Usually aggregates are one of the last steps executed before the final result set is returned to the client.However there are various reasons, why a GROUP BY operation might be somewhere in the middle of the execution plan operation, for example it might be part of a view that cannot be merged (or was hinted not to be merged using the NO_MERGE hint), or in the more recent releases (11g+) the optimizer decided to use the GROUP BY PLACEMENT transformation that deliberately can move the GROUP BY operation to a different execution step of the plan.In such cases, when the GROUP BY operation will be input to some other operation, it becomes essential for the overall efficiency of the execution plan preferred by the optimizer that the cardinality estimates are in the right ballpark, as it will influe

Oracle 11gR2 RAC Installation on Oracle Linux 6

I spent today updating my Oracle 11gR2 RAC installation on OL6 article. The original article used an older version of VirtualBox , which meant some of the screen shots looked a little dated. It’s now updated to VirtualBox 4.2.6, so it should be a little less confusing for anyone who is new to VirtualBox.

I’ll probably update the OL5 RAC article some time this next week, since that article uses VirtualBox 3.2.8, which is pretty much ancient history now. :)



New Version Of XPLAN_ASH Utility

A new version 2.0 of the XPLAN_ASH utility introduced here is available for download.You can download the latest version here.The change log tracks the following changes:- Access check- Conditional compilation for different database versions- Additional activity summary- Concurrent activity information (what is/was going on at the same time)- Experimental stuff: Additional I/O summary- More pretty printing- Experimental stuff: I/O added to Average Active Session Graph (renamed to Activity Timeline)- Top Execution Plan Lines and Top Activities added to Activity Timeline- Activity Timeline is now also shown for serial execution when TIMELINE option is specified- From on: We get the ACTUAL DOP from the undocumented PX_FLAGS colu

Exchange Partition, Virtual Columns And Column Statistics

Here is an odd bug that can lead to some nasty side effects when using the EXCHANGE PARTITION technique. It is probably there for a very long time, simply because it depends on the usage of virtual columns, and the basic technique of virtual columns was introduced way back in the Oracle 8i times with the introduction of Function Based Indexes.

The problem isn't the exchange partition operation itself, but the accompanying swap of object statistics information, in particular the column statistics.

Look the following sequence of DDL and DML commands and pay then special attention to the output for the column statistics before and after the EXCHANGE PARTITION operation:

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

    Report Generators And Query Transformations

    Usually the Cost-Based Optimizer arrives at a reasonable execution plan if it gets the estimates regarding cardinality and data scattering / clustering right (if you want to learn more about that why not watch my Webinar available at ""?).

    Here is an example I've recently come across where this wasn't case - the optimizer obviously preferred plans with a significantly higher cost.

    The setup to reproduce the issue is simple:

    Coalesce Subquery Transformation - COALESCE_SQ

    Oracle 11.2 introduced a set of new Query Transformations, among others the ability to coalesce subqueries which means that multiple correlated subqueries can be merged into a number of less subqueries.

    Timur Akhmadeev already demonstrated the basic principles in a blog entry, but when I was recently involved into supporting a TPC-H benchmark for a particular storage vendor I saw a quite impressive application of this optimization that I would like to share here.