Oracle Scratchpad

May 19, 2009

Odd Filter

Filed under: Execution plans,subqueries — Jonathan Lewis @ 6:39 pm BST May 19,2009

A little while ago someone sent me a brief email about an odd section of execution plan that they had seen. To make things a little more challenging they didn’t send the SQL statement, and they only sent me a few lines from the middle of the plan to see if I could explain what was going on. This is what is looked like:


| 27  |       FILTER                          |                             |
| 28  |        TABLE ACCESS BY INDEX ROWID    | LOAN_APPLICATION            |
| 29  |         INDEX RANGE SCAN              | IDX_LA_LOAN_DATE            |
| 30  |        SORT AGGREGATE                 |                             |
| 31  |         TABLE ACCESS BY INDEX ROWID   | PRIME_LOAN_APPLICATION      |
| 32  |          INDEX RANGE SCAN             | IDX_PLA_LOAN_APPLICATION_ID |
| 33  |          SORT AGGREGATE               |                             |
| 34  |           TABLE ACCESS BY INDEX ROWID | OTHER_LOAN_APPLICATION      |
| 35  |            INDEX RANGE SCAN           | IDX_OLA_LOAN_APPLICATION_ID |

27 - FILTER(+>0)
28 - FILTER("LA"."CANCELLED_FLAG"=0)
29 - ACCESS("LA"."LOAN_DATE">=TO_DATE(' 2009-06-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
   AND "CO"."LOAN_DATE"<=TO_DATE(' 2009-06-02 23:59:59', 'SYYYY-MM-DD HH24:MI:SS'))
31 - FILTER("PLA"."STATUS_ID"3)
32 - ACCESS("PLA"."LOAN_APPLICATION_ID"=:B1)
34 - FILTER("OLA"."STATUS_ID"3)
35 - ACCESS("OLA"."LOAN_APPLICATION_ID"=:B1)

Their comment was:

    I think this is like a 1>0 type filter, but I don’t recall seeing this one before so I thought I’d check to see if there is something else to this:The FILTER of (+>0) is the odd thing.  

The comment about the “1>0 type filter” is a reference to the type of filter I’ve mentioned in a note on Conditional SQL; but this isn’t relevant in this case.

What you’re looking at is two simultaneous defects in the execution plan information generated by Oracle. The first is one I’ve mentioned in the past which can appear when you have multiple subqueries – and seems to be particularly prevalent when they are filter subqueries against the same table. The shape of this part of the plan should be:


| 27  |       FILTER                          |                             |
| 28  |        TABLE ACCESS BY INDEX ROWID    | LOAN_APPLICATION            |
| 29  |         INDEX RANGE SCAN              | IDX_LA_LOAN_DATE            |
| 30  |        SORT AGGREGATE                 |                             |
| 31  |         TABLE ACCESS BY INDEX ROWID   | PRIME_LOAN_APPLICATION      |
| 32  |          INDEX RANGE SCAN             | IDX_PLA_LOAN_APPLICATION_ID |
| 33  |        SORT AGGREGATE                 |                             |
| 34  |         TABLE ACCESS BY INDEX ROWID   | OTHER_LOAN_APPLICATION      |
| 35  |          INDEX RANGE SCAN             | IDX_OLA_LOAN_APPLICATION_ID |

This is a problem that has largely been fixed in 11g, but there are still cases where the depth column of the plan_table is calculated incorrectly and you still have to check the id and parent_id columns to get the correct indentation.

The second defect is a limitation (possibly deliberate) of v$sql_plan – the optimizer does not record the subquery text that should appear in the filter_predicates and access_predicates columns of the plan_table. That strange filter predicate in line 27 “FILTER (+>0)” should really read something like:


27 - (select max() from prime_loan_application where {correlation predicate}) +
     (select max() from other_loan_application where {correlation predicate}) > 0

I’ve picked max() fairly arbitrarily to represent the aggregation requirementindicated by the ‘sort aggregate’ operations. This type of subquery text will be reported correctly when you use explain plan but doesn’t appear in the run-time plans.

(Note: Table and column names have been changed to conceal the identify of the application).

2 Comments »

  1. Hi Jonathan,

    it seems like all those bugs with incorrect indentation when using DBMS_XPLAN are consequences of adding more functions in 10g (DISPLAY_[AWR|CURSOR|SQLSET]) and not using CONNECT BY anymore against underlying structures. It’s strange, since correctly written hierarchical queries are simpler and should not introduce much overhead.

    Comment by Timur Akhmadeev — May 20, 2009 @ 7:07 am BST May 20,2009 | Reply

    • Oops, I’ve missed that that was already pointed out in “Filter plan error”.

      Comment by Timur Akhmadeev — May 20, 2009 @ 7:24 am BST May 20,2009 | 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,906 other followers