Oracle Scratchpad

February 29, 2012

Missing Filter

Filed under: Bugs,Execution plans,Oracle,subqueries — Jonathan Lewis @ 9:30 pm BST Feb 29,2012

I see that Christian Antognini posted a note about an interesting little defect in Enterprise Manager a little while ago – it doesn’t always know how to interpret execution plans. The problem appears in Christians’ example when a filter subquery predicate is applied during an index range scan – it’s a topic I wrote about a few months ago with the title “filter bug” because the plan shows (or, rather, fails to show) a “missing” filter operation, which has been subsumed into the predicate section of the thing that would otherwise have been the first child of the filter operation – the rule of recursive descent through the plan breaks, and the ordering that OEM gives for the operations goes wrong.

Christian’s example shows the filter hiding itself when it should be applied to an index scan – as does my example from a few months ago – but the operation can also go missing when it is supposed to apply to a table access (whether a full scan, or an access by rowid), and the recursive descent algorithm that OEM is (probably) using to work out the execution order breaks in that case too.

The easiest way to construct an example of the anomaly is to write a query with a subquery which is blocked from unnesting (I’ve done this by using an outer join in the correlated predicate, but you could simply use a no_unnest hint), and then pushed (push_subq) to execute as early as possible in the plan:

select	/*+
                qb_name(main)
                leading(t1@main t2@subq1 t3@main)
                push_subq(@subq1)
        */
        t1.v1
From
        t1, t3
Where
        t1.n2 = 15
and     exists (
                select --+ qb_name(subq1)
                        null
                from    t2
                where   t2.n1(+) = 15
                and     t2.id(+) = t1.id
        )
and     t3.n1 = t1.n1
and     t3.n2 = 15
;

select * from table(dbms_xplan.display(null,null,'basic rows predicate'));

-------------------------------------------------------
| Id  | Operation                     | Name  | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     9 |
|*  1 |  HASH JOIN                    |       |     9 |
|*  2 |   TABLE ACCESS FULL           | T1    |     8 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2    |     1 |
|*  4 |     INDEX UNIQUE SCAN         | T2_PK |     1 |
|*  5 |   TABLE ACCESS FULL           | T3    |   157 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T3"."N1"="T1"."N1")
   2 - filter("T1"."N2"=15 AND  EXISTS (SELECT /*+ PUSH_SUBQ QB_NAME
              ("SUBQ1") */ 0 FROM "T2" "T2" WHERE "T2"."ID"(+)=:B1 AND
              "T2"."N1"(+)=15))
   3 - filter("T2"."N1"(+)=15)
   4 - access("T2"."ID"(+)=:B1)
   5 - filter("T3"."N2"=15)

As you can see, there’s a filter predicate at line 2, but this doesn’t come from a filter operation; it’s simply a filter subquery applied to the tablescan. To make it easier to read complex cases I sometimes take a text editor to a plan like this and put back the missing filter operation – which means inserting one line where the filter predicate appears and moving the filtered operation (in this case the full tablescan) and any descendent(s) thereof one step to the right, as follows:

-------------------------------------------------------
| Id  | Operation                     | Name  | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     9 |
|*  1 |  HASH JOIN                    |       |     9 |
|*  2a|   FILTER                      |       |     9 |
|*  2b|    TABLE ACCESS FULL          | T1    |     8 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2    |     1 |
|*  4 |     INDEX UNIQUE SCAN         | T2_PK |     1 |
|*  5 |   TABLE ACCESS FULL           | T3    |   157 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T3"."N1"="T1"."N1")
   2a- filter("T1"."N2"=15 AND  EXISTS (SELECT /*+ PUSH_SUBQ QB_NAME
              ("SUBQ1") */ 0 FROM "T2" "T2" WHERE "T2"."ID"(+)=:B1 AND
              "T2"."N1"(+)=15))
   3 - filter("T2"."N1"(+)=15)
   4 - access("T2"."ID"(+)=:B1)
   5 - filter("T3"."N2"=15)

If I don’t push the subquery (i.e. allow Oracle to execute late) then the plan changes to the following – showing you the filter operation that you might normally expect on a filter subquery:

------------------------------------------------------
| Id  | Operation                    | Name  | Rows  |
------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |
|*  1 |  FILTER                      |       |       |
|*  2 |   HASH JOIN                  |       |   173 |
|*  3 |    TABLE ACCESS FULL         | T1    |   157 |
|*  4 |    TABLE ACCESS FULL         | T3    |   157 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |
|*  6 |    INDEX UNIQUE SCAN         | T2_PK |     1 |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ QB_NAME ("SUBQ1") */ 0 FROM "T2" "T2"
              WHERE "T2"."ID"(+)=:B1 AND "T2"."N1"(+)=15))
   2 - access("T3"."N1"="T1"."N1")
   3 - filter("T1"."N2"=15)
   4 - filter("T3"."N2"=15)
   5 - filter("T2"."N1"(+)=15)
   6 - access("T2"."ID"(+)=:B1)

Here’s an image captured from the OEM screen (11g) for the plan that actually appeared. The run-time action starts with the full tablescan of t1 at the third line of the plan, and for each row we acquire from t1 we run the subquery at the fourth and fifth lines (fifth first, then fourth) – but as you can see, OEM thinks the action starts with a unique scan of index t2_pk at the fifth line of the plan.

Moral: whenever you know there’s a filter subquery in the plan (and you’ll see it in the predicate section) make sure you think carefully about when it runs.

2 Comments »

  1. […] the point at which each predicate was applied. Generally this isn’t too difficult, although subquery pushing (and a couple of presentation bugs relating to other scalar subqueries) can cause a little […]

    Pingback by Parallel Execution – 1 | Oracle Scratchpad — October 13, 2013 @ 7:43 pm BST Oct 13,2013 | Reply

  2. […] I came close to writing a complete blog post about this problem with reading execution plans – then decided to check if I had mentioned it before. I have: twice: originally in 2010, and then again in 2012. […]

    Pingback by Interesting Plan | Oracle Scratchpad — November 29, 2013 @ 8:56 am BST Nov 29,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,905 other followers