Today, while tuning a fairly complex query experiencing wrong cardinality estimates, I noticed something I was not aware of. Hence, I thought to write this short post to illustrate how to reproduce the problem I experienced… Create the test table (notice the correlation between the data of the two columns): SQL> CREATE TABLE t 2 […]
Adaptive Plans in Active Session History
Yesterday, during the talk “The Query Optimizer in Oracle Database 12c – What’s New?” at OakTable World UK 2013, an attendee asked me an interesting question that can be summarized as follows: does ASH show the switch of execution plan that takes place for an adaptive plan? Since I didn’t know the answer, here are […]
System Statistics Gathered in Exadata Mode – When Are They Relevant?
The aim of this post isn’t to explain what the “exadata mode” is. Hence, if you don’t know what it is, before continuing reading have a look to this post published on Kerry Osborne’s blog. The only thing I would like to add is that the “exadata mode” is available as of 11.2.0.4 or when […]
The Broken Statistics: "parse count (total)" and "session cursor cache h...
The values provided by the “parse count (total)” and “session cursor cache hits” statistics are subject to several bugs. And, what’s worse, for years Oracle didn’t care to fix it. This is my impression, at least. Then, when few weeks ago I read in the Oracle Support note 13837105.8 (Bug 13837105 – statistics “parse count […]
Scripts to Download Oracle Database 12c Release 1 Documentation
In the past (here and here) I already shared with you the scripts I used to download the documentation related to Oracle Database. The aim of this short post is to provide the script I just wrote for the latest version: 12c Release 1. download121.sh Happy download as well as happy reading! PS: it is […]
Invisible Indexes and Locks
Invisible indexes are useful to temporarily hide an index from the query optimizer. In this post, instead of explaining what invisible indexes are, I would like to show whether the database engine uses invisible indexes to avoid false contention caused by unindexed foreign key. Let’s start by creating two test tables with a relation (foreign […]
Long Parses and SQL Plan Baselines
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 […]