Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

ITL Waits – Changes in Recent Releases (script)

20 June 2011 Written by Christian Antognini

A reader of this blog, Paresh, asked me how I was able to find out the logic behind ITL waits without having access to Oracle code. My reply was: I wrote a test case that reproduce ITL waits and a piece of code that monitors them. Since other readers might be interested, here is the […]

Read More 8 Comments

ITL Waits – Changes in Recent Releases

13 April 2011 Written by Christian Antognini

In recent releases Oracle has silently changed the behavior of ITL waits. The aim of this post it to describe what has changed and why. But, first of all, let’s review some essential concepts about ITLs and ITL waits. Interested Transaction List The Oracle database engine locks the data modified by a transaction at the […]

Read More 6 Comments

IS NULL Conditions and B-tree Indexes

17 February 2011 Written by Christian Antognini

At page 383 of my book I wrote the following sentence (BTW, the same information is also provided by Table 9-3 at page 381): With B-tree indexes, IS NULL conditions can be applied only through composite B-tree indexes when several SQL conditions are applied and at least one of them is not based on IS […]

Read More 2 Comments

Optimizer Mode Mismatch Does Not Prevent Sharing of Child Cursor!?!?

3 June 2010 Written by Christian Antognini

The aim of this post is to describe a strange (buggy) situation that I observed recently. But before doing that, I shortly summarize what a parent cursor and a child cursor are as well as when they can be shared. By the way, I borrowed this description from the pages 20/21 of my book. Hence, […]

Read More 4 Comments

Bind Variable Peeking: Bane or Boon?

29 April 2010 Written by Christian Antognini

Almost one year ago Iggy Fernandez asked me to write a short text for the Ask the Oracles column of the NoCOUG Journal. The topic was “Bind Variable Peeking: Bane or Boon?”. My text along with the ones of Wolfgang Breitling, Dan Tow and Jonathan Lewis were published in the August issue. For some (unknown) […]

Read More Leave a Comment

Tracing VPD Predicates

28 February 2010 Written by Christian Antognini

Even though a number of articles and blog posts have already been written on this topic (e.g. on Pete Finnigan’s site I found references dating back from 2003), from time to time I’m still asked “How to trace predicates generated by VPD?”. Hence, here’s yet another blog post about this topic… Let’s setup the scene […]

Read More 2 Comments

Does the Query Optimizer Cost PX Distribution Methods?

26 January 2010 Written by Christian Antognini

The short answer to this question is “yes”, it does. Unfortunately, the distribution costs are not externalized through the execution plans and, as a result, this limitation (yes, it is really a limitation in the current implementation, not a bug) confuses everyone that carefully look at the information provided in an execution plan of a […]

Read More 13 Comments

Does CREATE INDEX Gather Global Statistics?

17 December 2009 Written by Christian Antognini

You can add the COMPUTE STATISTICS clause to the CREATE INDEX statement. It instructs the SQL statement to gather and store index statistics in the data dictionary, while creating the index. This is useful because the overhead associated with the gathering of statistics while executing this SQL statement is negligible. In Oracle9i, the gathering of […]

Read More 4 Comments

Hints for Direct-path Insert Statements

23 October 2009 Written by Christian Antognini

Up to Oracle Database 10g Release 2, direct-path inserts are supported only by INSERT INTO … SELECT … statements (including multitable inserts), MERGE statements (for the part inserting data), and applications using the OCI direct-path interface (for example, the SQL*Loader utility). At the statement level two methods are available to specify that a direct-path insert […]

Read More 13 Comments

A-Rows and DML Statements – Part 2

3 August 2009 Written by Christian Antognini

In the first post about this topic I wrote: “What I don’t like about the column “A-Rows” (or the underlying columns LAST_OUTPUT_ROWS in the V$ views), is that for the operations modifying a table 0 is shown. By the way, according to the documentation it is not a bug.” What I forgot to mention is […]

Read More Leave a Comment
Older Entries