To make easier the interpretation of execution plans, in chapter 6 of TOP I defined three types of operations: standalone operations, unrelated-combine operations, and related-combine operations. For combine operations I also added a list of all operations of each type. Since in 11.2 a new related-combine operation is available, I decided to write this short […]
During an evolution the database engine compares the performance of two execution plans. The aim is to find out which one provides the better performance. For that purpose it has to run the SQL statement on which the SQL plan baseline is based and compare some execution statistics. The following output of the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function […]
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, […]
In the first and second post of this series I shared with you some basics about smart scan and gave some details about projection and restriction. The aim of this post is to cover the third basic technique: join filtering. Join filtering is not something specific to the Exadata Storage Server. In fact, it is […]
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 […]
In the first post of this series I shared with you some basics about smart scan and gave some details about projection. The aim of this post is to cover the second basic technique: restriction. Simply put, the aim of this technique is to offload to a cell the processing of predicates found in the […]
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) […]
Even though the utilization of an Exadata Storage Server should be transparent for the query optimizer, when you look at execution plans generated in an environment using it you might notice slight differences. The purpose of this series of post is to summarize the differences I was able to observe. Disclaimer: I do not have […]
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 […]
In some specific situations the query optimizer is able to completely avoid executing a join even if a SQL statement explicitly calls for it. Two are the cases currently covered by this optimization technique, which is called join elimination. The first one was introduced in Oracle Database 10g Release 2, the second one in Oracle […]