Missing Filter

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 Christian’s 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 operation that in other circumstances would 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 tablescan 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 at the earliest possible point in the plan:

rem
rem     Script:         push_subq_3.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2003
rem

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, the subquery appears in the filter predicate at operation 2, but this doesn’t come from a parent filter operation; it’s simply a subquery applied to each row accessed by 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.