The 11th and 12th of December and, therefore, the CBO Days in Zurich are approaching. Given the high quality of the speakers that accepted my invitation and the number of people that decided to attend the event, in my opinion it will be a blast! By the way, if you are interested in attending the [.....

The company I work for, Trivadis, is very pleased to organize, the 11th and 12th of December, an outstanding seminar with top guest speakers in Zurich. This year’s focus will be on the Oracle query optimizer, also known as a cost-based optimizer (CBO). The query optimizer is not only one of the most...

Most execution plans can be interpreted by following few basic rules (in TOP, Chapter 6, I provide such a list of rules). Nevertheless, there are some special cases. One of them is when an index scan, in addition to the access predicate, has a filter predicate applying a subquery. The following execution...

The 31st of January and 1st of February 2012 I will present a 2-day seminar about the Oracle query optimizer in Ballerup (DK). The event is organized by Miracle A/S. The content, which is based on the chapters 2, 4, 5, 6, 9 and 10 of my book, is the following: Chapter 1 describes the life [...]

Challenges and Chances of the 11g Query Optimizer is the name of a presentation I gave at several events (e.g. Trivadis Performance Days, Oracle OpenWorld, DOAG Konferenz, UKOUG Conference) throughout 2011. Its abstract is the following: With every new release, the query optimizer is enhanced. Oracle...

The STATISTICS_LEVEL parameter controls a bunch of features. In addition to the documentation, also the V$STATISTICS_LEVEL view provides a list of the ones it controls. SQL> SELECT statistics_name, description, activation_level 2 FROM v$statistics_level 3 ORDER BY 3 DESC, 1; STATISTICS_NAME [...

At page 189 of TOP I wrote the following piece of text: In summary, with the initialization parameter optimizer_secure_view_merging set to TRUE, the query optimizer checks whether view merging could lead to security issues. If this is the case, no view merging will be performed, and performance could...

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...

As of 11.2.0.2 a new package, DBMS_AUTO_SQLTUNE, is available for accessing and configuring Automatic SQL Tuning. The package provides three features: Execution of the Automatic SQL Tuning task (EXECUTE_AUTO_TUNING_TASK) Generation of a report showing the output generated by the Automatic SQL Tuning...

When I started writing the series of posts about Exadata Storage Server and the query optimizer, I didn’t expect to write more than three posts (part 1, part 2, part 3). Of course, things change. Hence, here is part 4 to cover a couple of things that I learned in the next couple of months. In [...]

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...

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...

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...

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...

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...

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....

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...

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...

Zero-size unusable indexes and index partions is a small but useful feature of Oracle Database 11g Release 2. Simply put, its aim is to save space in the database by immediately releasing the segment associated to unusable indexes or index partitions. To illustrate this, let’s have a look to an...

An execution plan describes the operations carried out by the SQL engine to execute a SQL statement. Every time you have to analyze a performance problem related to a SQL statement, or simply question the decisions taken by the query optimizer, you must know the execution plan. Whenever you deal with...

Yesterday, while reading a swiss italian dialect tranlation of Le petit prince (entitled Ul principe pinin), I noticed a very interesting discussion between the little prince and the king (the inhabitant of the first planet visited by the little prince…). Here is the most important part of that...

In the section Evolving SQL Plan Baselines of TOP (pages 299-300) I explain what an evolution is and how to manually do it. What I do not cover is the automatic evolution. In fact, I wrote only the following text about it: “In addition to the manual evolution just explained, automatic evolution...