Execution plans

Jonathan Lewis's picture

Parallel Execution – 1

When you read an execution plan you’re probably trying to identify the steps that Oracle went through to acquire the final result set so that you can decide whether or not there is a more efficient way of getting the same result.

Jonathan Lewis's picture

Virtual Stats

Or – to be more accurate – real statistics on a virtual column.

This is one of the “10 top tips” that I came up with for my session with Maria Colgan at OOW13. A method of giving more information that might improve execution plans when you can change the code. I’ll start with a small data set including a virtual column (running 11.1.0.7), and a couple of problem queries:

Jonathan Lewis's picture

Autotrace trick

(… as in “trick or treat”)

Here’s an important point I learned from Maria Colgan’s “10 tips” presentation on Tuesday of Open World. It comes in two steps – the bit that most people know, and an unexpected consequence:

  1. autotrace can give misleading execution plans for queries that use bind variables – because autotrace doesn’t peek
  2. if you run a query after testing it with autotrace, the plan generated by autotrace can be shared by the later execution

Here’s a simple script I’ll be using to demonstrate the behaviour:

Jonathan Lewis's picture

Quiz Night

Here’s a little quiz about Bloom filtering. There seem to be at least three different classes of query where Bloom filters can come into play – all involving hash joins: partition elimination, aggregate reduction on non-mergeable aggregate views, and parallelism.

This quiz is about parallel queries – and all you have to do is work out how many Bloom filters were used in the following two execution plans (produced by 11.2.0.2), and where they were used.

I’ve got 4 tables, 3 very small dimensions and one large fact. I’ve joined the three dimensions to the fact on their primary key, and filtered on each dimension. Stripping out the eighteen hints that I inserted to get the plans I wanted the queries both looked like this:

Jonathan Lewis's picture

Hash Joins

I’ve written notes about the different joins in the past – but such things are always worth revisiting, so here’s an accumulated bundle of comments about hash joins.

Jonathan Lewis's picture

NVL()

Here’s a little detail that I hadn’t noticed before (and it goes back to at least 8i). This is running on 11.2.0.3, and table t1 is just all_objects where rownum <= 20000:

Jonathan Lewis's picture

Bloom Filter

I’ve posted this note as a quick way of passing on an example prompted by a twitter conversation with Timur and Maria about Bloom filters:

The Bloom filter (capital B because it’s named after a person) is not supposed to appear in Oracle plans unless the query is executing in parallel but here’s an example which seems to use a serial Bloom filter.  Running in 11.2.0.3 and 12.1.0.1 (the results shown are the latter – the numbers are slightly different between versions):

Jonathan Lewis's picture

Parallel to Serial

Here’s a little problem that came up on the Oracle-L listserver today:

I’m trying to write a query which reads the corresponding partition of the fact, extracts the list of join keys, materialises this result set, and finally joins the necessary dimensions. The key thing I’m trying to do is to run the initial query on the fact in parallel and then the rest of the query serially.

Jonathan Lewis's picture

Illogical Tuning

The title is a bit of a joke, really. It’s mirroring a title I used a little over a year ago “Logical Tuning” and reflects my surprise that a silly little trick that I tried actually worked.

If you don’t want to read the original article, here’s a quick précis – I started with the first query, which the optimizer executed as a filter subquery, and rewrote it as the second query, which the optimizer executed as two anti-joins (reducing the execution time from 95 seconds to 27 seconds):

Jonathan Lewis's picture

Same Plan

An interesting little problem appeared on the Oracle-L mailing list earlier on this week – a query ran fairly quickly when statistics hadn’t been collected on the tables, but then ran rather slowly after stats collection even though the plan hadn’t changed, and the tkprof results were there to prove the point. Here are the two outputs (edited slightly for width – the original showed three sets of row stats, the 1st, avg and max, but since the query had only been run once the three columns showed the same results in each case):

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