CBO

Richard Foote's picture

Oracle 19c Automatic Indexing: Data Skew Part III (The Good Son)

  I’m going to expand just a tad on my previous posts on data skew and run a simple query that returns a few rows based on a column predicate AND another query on the same column that returns many rows. The following table has a CODE column as with previous posts with the data […]

Jonathan Lewis's picture

Bloom Upgrade

It’s a common pattern of Oracle features that they start with various restrictions or limitations that disappear over time. This note is about an enhancement to Bloom filter processing that appeared in the 18.1 optimizer and, for some people, may be a good enough reason for upgrading to a newer version of Oracle. This enhancement came to my attention through a question on the Oracle Developer forum asking how to get a Bloom filter pushed inside a UNION ALL view. The original requirement wasn’t a trivial one so I’ll demonstrate the problem with a very simple example – first the data set:

Richard Foote's picture

Oracle 19c Automatic Indexing: Poor Data Clustering With Autonomous Databases Part III (Star)

In Part I we looked at a scenario where an index was deemed to be too inefficient for Automatic Indexing to create a VALID index, because of the poor clustering of data within the table. In Part II we improved the data clustering but the previous SQLs could still not generate a new Automatic Index […]

Jonathan Lewis's picture

Recursive WITH upgrade

There’s a notable change in the way the optimizer does cost and cardinality calculations for recursive subquery factoring that may make some of your execution plans change – with a massive impact on performance – as you upgrade to any version of Oracle from 12.2.0.1 onwards. The problem appeared in a question on the Oracle Developer Community forum a little while ago, with a demonstration script to model the issue.

I’ve copied the script – with a little editing – and reproduced the change in execution plan described by the OP. Here’s my copy of the script, with the insert statements that generate the data (all 1,580 of them) removed.

Richard Foote's picture

Oracle 19c Automatic Indexing: Common Index Creation Trap (Rat Trap)

When I go to a customer site to resolve performance issues, one of the most common issues I encounter is in relation to inefficient SQL. And one of the most common causes for inefficient SQL I encounter is because of deficiencies the default manner by which the index Clustering Factor is calculated. When it comes […]

Jonathan Lewis's picture

Most Recent – 2

A question arrived in my email a few days ago with the following observations on a statement that was supposed to query the data dictionary for some information about a specified composite partitioned table. The query was wrapped in a little PL/SQL, similar to the following:

Jonathan Lewis's picture

Lower Cost Ignored

This is an update on a post I wrote nearly 10 years ago describing how the optimizer could choose to ignore a lower cost indexed access path and use a higher cost index if the lower cost were based on guesswork. The original article article used (select {constant} from dual) as a way of supplying an “unpeekable bind” in a predicate, but that mechanism stopped working 11gR2, hence the update.

The upate also goes into a little more detail about event 38036 which can be used to modify this behaviour by defining a “cut-off” percentage where Oracle will switch back to using the lower cost path.

We start with the code to generate the data – including, in this case – a table that I can query to supply “hidden constants” to the optimizer:

Richard Foote's picture

Oracle 19c Automatic Indexing: A More Complex Example (How Does The Grass Grow)

In this post I’m going to put together in a slightly more complex SQL example a number of the concepts I’ve covered thus far in relation to the current implementation of Oracle Automatic Indexing. I’ll begin by creating three tables, a larger TABLE1 and two smaller TABLE2 and TABLE3 lookup tables. Each table is created […]

Jonathan Lewis's picture

Analytic cost error

Here’s a surprising costing error that was raised on the Oracle Developer Forum a few days ago. There’s a glitch in the cost atributed to sorting when an analytic over() clause – with corresponding “window sort” operation – makes a “sort order by” operation redundant. Here’s a script to generate the data set I’ll use for a demonstration with a template for a few queries I’ll be running against the data.

Jonathan Lewis's picture

Order By

This is a brief note with an odd history – and the history is more significant than the note.

While searching my library for an example of an odd costing effect for the “order by” clause I discovered a script that looked as if I’d written for 11.1.0.6 in 2008 to demonstrate a redundant sort operation appearing in an execution plan; and then I discovered a second script written for 11.2.0.4 in 2014 demonstrating a variant of the same thing (presumably because I’d not found the original script in 2014) and the second script referenced a MOS bug number

Bug 18701129 : SORT ORDER BY ISN’T AVOIDED WHEN ROWID IS ADDED TO ORDER BY CLAUSE

To prevent automated spam submissions leave this field empty.
Syndicate content