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 example… Create a partitioned table, […]
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 an execution plan, you […]
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 discussion (you can read the whole text, […]
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 of SQL plan […]
Today I presented at the DOAG Conference in Nuremberg. As the title of this post suggests, I talked about the query optimizer features introduced in Oracle Database 11g. This is an evolution of the paper I already presented at the UKOUG Conference in Birmingham, at the Hotsos Symposium in Dallas, at the AOUG Expertentreff in […]
As of 11.1.0.7 in the package DBMS_XPLAN there is a new function: FUNCTION prepare_plan_xml_query(plan_query IN VARCHAR2) RETURN VARCHAR2;FUNCTION prepare_plan_xml_query(plan_query IN VARCHAR2) RETURN VARCHAR2; Simply put, the function takes as input a query that extract information from a plan table (e.g. PLAN_TABLE or V$SQL_PLAN) and builds a query based on SQLX functions that returns the output […]
In this post I would like to remove some misinformation about the utilization of hints with invisible indexes. Let’s start by providing you what two sources say about that topic: Oracle Database 11g: New Features for DBAs and Developers (by Sam R. Alapati and Charles Kim, page 132) “If you want the optimizer to take […]
Oracle Database, as of 10g Release 2, uses bloom filters in various situations. Unfortunately, no information about their usage is available in Oracle documentation. For this reason, I decided to write a paper to explain not only what bloom filters are, but also, and foremost, to describe how Oracle Database makes use of them. Specifically, […]
Few days ago, in the post entitled Long Parses, I pointed out that stored outlines can be used to shorten the parse time by restricting the number of execution plans considered by the query optimizer. As of Oracle Database 11g, stored outlines are deprecated in favor of SQL plan baselines. Therefore, someone might think that […]
From time to time I have to fight against long parses. In this post I would like to share with you what happened to a customer of mine while upgrading from 9.2.0.7 to 10.2.0.3 (10.2.0.4 was not yet available at that time). The parse of a complex SQL statement took 5 seconds in 9.2.0.7. Not […]