This is a reference note for a question that came up as a comment on a lengthy note I wrote about reading execution plans.

How do you interpret something like: from$_subquery$_001@SEL$1 in the Query Block Name / Object Alias section of an execution plan.

The simple answer is that if you’ve got an inline view in the FROM clause of a query and you haven’t given the inline view an alias the optimizer will have to invent one – and this is what they look like.

As a quick demo here’s a script to create a couple of tables and then run a query that joins two inline views (using “ANSI”-style SQL), with variations on which of the inline views are named:

Table Of Contents

1.0 Introduction
2.0 Overview
3.0 The Main Course
4.0 Simplify
5.0 Filling the Gaps
6.0 Looking at the numbers
7.0 Predicate Information
8.0 Resolution

How do you work out what hints you need to tweak an execution plan into the shape you want?

Here’s a “case study” that’s been playing out over a few weeks on the Oracle Developer Community (here and here) and most recently ended up (in one of its versions) as a comment on one of my blog notes. It looks like a long note, but it’s a note about how to find the little bit of information you need from a large output – so it’s really a short note that has to include a long output.

A couple of days ago I discussed an execution plan that displayed some variation in the way it handled subqueries and even threw in a little deception by displaying an anti-join that was the result of transforming a “not exists” subquery and a semi-join that looked at first sight as if it were going to be the result of transforming an “exists” subquery.

In a recent blog note I made the point that there is a very simple rule (“first child first”) for reading execution plans if the query (as written or after transformation by the optimizer) consists of a single “query block”. However, if you have a plan that is reporting multiple query blocks you have to be careful that you identify the boundaries of the individual query blocks and manage to link them together correctly.

Every now and again someone posts a piece of SQL on the Oracle Developer Forum that includes a predicate with an expression like to_date(date_column). This is a problem for several reasons – not the least being the type of performance problem that showed up in a post from a couple of years back that has just been resurrected.

Before I examine the performance detail, here’s a simple demo of the “wrong data” problem that can go unnoticed, cut-n-paste from a 12.2.0.1 session of SQL*Plus:

I woke up last night with a brilliant solution to a problem that’s been bugging me for more than a year. How does a call to report_sql_monitor() manage to produce output like this:

One of the most important skills needed when investigating badly performing SQL is the ability to read Execution Plans. It’s a topic I’ve written and spoken about frequently – even to the extent of doing full-day seminars – but there’s always scope for finding another way of presenting the method.

This is a note based on a few introductory Powerpoint slides I created for the (sadly cancelled) Polish OUG Workshop and Tanel Poder’s Virtual Conference taking a slightly different approach from one I normally use to get people stated on interpreting (serial) execution plans.

I want to begin with a query, rather than a plan:

I’ve made casual remarks in the past about how “ANSI”-style SQL introduces extra complications in labelling or identifying query blocks – which means it’s harder to hint correctly. This is a note to show how the optimizer first transforms “ANSI” SQL into “Oracle” syntax. I’m going to write a simple 4-table join in classic Oracle form and check the execution plan with its query block names and fully qualified table aliases; then I’ll translate to the ANSI equivalent and repeat the check for query block names and aliases , finally I’ll rewrite the query in classic Oracle syntax that reproduces the query block names and fully qualified table aliases that we got from the ANSI form.

We start by creating and indexing 4 tables (with a script that I’ve been using for various tests for several years, but the results I’ll show come from 19c):

I’ve just been prompted to complete and publish a draft I started a few years ago. It’s (ultimately) about a feature that appeared in 9i but doesn’t seem to show up very often at client sites or as a common solution to performance problems on the various Oracle forums – but maybe that’s not surprising given how slowly analytic functions have been taken up.

I want to work towards the feature by starting with a requirement, then examine several solutions. To supply a touch of realism I’ll create an orders table, which holds a customer id and an order date (including time), ,and then ask for a report of the most recent order for each customer. Here’s some starting data: