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 […]
ITL Waits – Changes in Recent Releases
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 […]
Scripts to Download Documentation
In this post I pointed out that I like to have a copy of the documentation in PDF format on my notebook. In the same post, and its comments, I also described how I generate the scripts I use to download the files. Recently I updated the scripts and, as a result, I thought to […]
IS NULL Conditions and B-tree Indexes
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 […]
Parallel Full Table Scans Do Not Always Perform Direct Reads
Even though in general parallel full table scans performs direct reads, some exceptions exist. The aim of this post is to show such an exception. For test purposes I build in my own schema a copy of the SH.SALES table (the one distributed by Oracle with the demo schemas…). On that table I build an […]
Partition-Wise Join of List-Partitioned Tables
When two tables are equi-partitioned on their join keys, the query optimizer is able to take advantage of partition-wise joins. To make sure that the tables are equi-partitioned, as of Oracle Database 11g reference partitioning can be used. In fact, per definition, with reference partitioning all “related” tables have exactly the same partitioning schema. If […]
Optimizer Mode Mismatch Does Not Prevent Sharing of Child Cursor!?!?
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, […]
Native Full Outer Join Officially Available in 10.2.0.5
Today I installed for the first time the patchset 10.2.0.5. While reading the README file, I noticed the following piece of information. To enable a new native full outer join implementation in the database, a user has to set the following underscore parameter: _optimizer_native_full_outer_join =force You can set this parameter for the system or for […]
Bind Variable Peeking: Bane or Boon?
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) […]
Inserts Experiencing an Increasing CPU Consumption
Last week I had to analyze a strange performance problem. Since the cause/solution was somehow surprising, at least for me, I thought to share it with you. Let me start by quickly describing the setup and what was done to reproduce the problem: Database version: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 (64-bit) Operating system: […]