Oracle Scratchpad

April 15, 2010

Predicate (again)

Filed under: dbms_xplan — Jonathan Lewis @ 6:17 pm BST Apr 15,2010

I often make a fuss about making sure that people include the predicate section when looking at execution plans. Here’s another example demonstrating why it can be so helpful. We start with an SQL statement that happens to have a large number of child cursors – and every child cursor has exactly the same plan_hash_value (which almost guarantees they all have the same execution plan):

SELECT
	DISTINCT c.*,
	rmbc.display_order
FROM
	tcf_mpf_file_group tmfg
INNER JOIN
	file_group_currency fgc
ON	(fgc.tcf_mpf_file_group_id = tmfg.tcf_mpf_file_group_id)
LEFT OUTER JOIN
	ref_merchant_base_currency rmbc
ON	(rmbc.iso_code = fgc.base_currency_code)
INNER JOIN
	currency c
ON	(c.iso_code = fgc.base_currency_code)
WHERE
	tmfg.tcf_mpf_file_group_id =:"SYS_B_0"
ORDER BY
	NVL(rmbc.display_order, :"SYS_B_1")

You will notice from the names of the bind variables (SYS_B_n)that this is a system running with the cursor_sharing parameter set to force or similar. This suggests one obvious check – when cursor_sharing is set to similar, the optimizer will re-optimize statements where the bind variables reference columns that are: used in range-based predicates (not here), or partition key columns (not in this system) or have histograms on them. So I checked for a histogram on the tmfg.tcf_mpf_file_group_id column – and there wasn’t one.

Next step of investigation – pull the plan from memory and have a quick look at it, paying special attention to the predicate section:

-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                              |       |       |    10 (100)|          |
|   1 |  SORT ORDER BY         |                              |    14 |   658 |    10  (30)| 00:00:01 |
|   2 |   HASH UNIQUE          |                              |    14 |   658 |     9  (23)| 00:00:01 |
|*  3 |    HASH JOIN           |                              |    14 |   658 |     8  (13)| 00:00:01 |
|*  4 |     HASH JOIN OUTER    |                              |    14 |   308 |     5  (20)| 00:00:01 |
|   5 |      NESTED LOOPS      |                              |    14 |   196 |     1   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN| PK_TCF_MPF_FILE_GROUP        |     1 |     5 |     0   (0)|          |
|*  7 |       INDEX RANGE SCAN | UQ_FGC_TMFG_ID_BASE_FGN_CARD |    14 |   126 |     1   (0)| 00:00:01 |
|   8 |      TABLE ACCESS FULL | REF_MERCHANT_BASE_CURRENCY   |    35 |   280 |     3   (0)| 00:00:01 |
|   9 |     TABLE ACCESS FULL  | CURRENCY                     |   209 |  5225 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("C"."ISO_CODE"="FGC"."BASE_CURRENCY_CODE")
   4 - access("RMBC"."ISO_CODE"="FGC"."BASE_CURRENCY_CODE")
   6 - access("TMFG"."TCF_MPF_FILE_GROUP_ID"=:SYS_B_0)
   7 - access("FGC"."TCF_MPF_FILE_GROUP_ID"=:SYS_B_0)

Notice the appearance of an extra predicate on fgc.tcp_mpf_file_group_id. This has appeared through transitive closure. (You might also notice that the join condition between tmfg and fgc has disappeared, and in some cases this would result in a cartesian merge join but in this case the first table is known to return exactly one row through a unique index).

So transitive closure has cloned a predicate from a column that didn’t have a histogram to a column that did have a histogram which means the rule for cursor_sharing= similar comes into play and Oracle has been re-optimising this statement every time a new value appears for that bind variable. But you can’t see the threat when you look at the statement, you can only see it in the predicate section of the execution plan.

11 Comments »

  1. Hello Sir,

    “We start with an SQL statement that happens to have a large number of child cursors – and every child cursor has exactly the same plan_hash_value (which almost guarantees they all have the same execution plan):”

    you said there is many child cursors and they have same plan_hash_value who could be this possible ?

    could you please flash more light on this
    Thanks

    Comment by Henish — April 16, 2010 @ 2:58 am BST Apr 16,2010 | Reply

  2. >you said there is many child cursors and they have same plan_hash_value who could be this possible ?
    Randolf Geist has a post on this topic: PLAN_HASH_VALUE – How equal (and stable?) are your execution plans – part 1

    Comment by Timur Akhmadeev — April 16, 2010 @ 8:23 am BST Apr 16,2010 | Reply

  3. > you said there is many child cursors and they have same plan_hash_value who could be this possible

    Timur,

    as I understand the question I think it is more about the relation between multiple child cursors and the same execution plan – which is something quite usual, since e.g. CURSOR_SHARING=SIMILAR creates a new child cursor under certain circumstances (as outlined by Jonathan) that gets then optimized again. It may very well happen that this repeated optimization leads to exactly the same execution plan / PLAN_HASH_VALUE.

    I guess Henish is under the impression that different child cursors would only be created if the optimization lead to different plans, but this is not the case with CURSOR_SHARING=SIMILAR. It is the other way round – a new child cursor is created (for different input values) and gets then optimized. Whether the resulting plan will be different or not doesn’t matter.

    Adaptive Cursor Sharing is different in this regard – it tries to minimize the number of different child cursors, something that didn’t work very well in the initial 11.1.0.6 release, but has been improved in 11.1.0.7. May be this is more what Henish is thinking of.

    Randolf

    Comment by Randolf Geist — April 16, 2010 @ 6:22 pm BST Apr 16,2010 | Reply

  4. timur : Thanks for the link

    Randolf: Thanks for your valuble input, yes you get my point correctly

    Thanks for clarification

    Henish

    Comment by Henish — April 16, 2010 @ 9:16 pm BST Apr 16,2010 | Reply

  5. [...] Cartesian joins are not automatically a sign of problems.  Previous blog articles have demonstrated that a Cartesian join may be more efficient than other methods for certain situations (see Improving Performance by Using a Cartesian Join and Improving Performance by Using a Cartesian Join 2), the execution plan may simply display a Cartesian join rather than a nested loops join when one of the row sources is expected to return a single row, or the optimizer may introduce a Cartesian join as a transformation due of transitive closure (see this article). [...]

    Pingback by Explain Plan Shows a Cartesian Merge Join, How Would You Help? « Charles Hooper's Oracle Notes — December 24, 2010 @ 12:28 pm BST Dec 24,2010 | Reply

  6. [...] deliberately omitted to show the predicate part of this explain plan because my first reaction to this e-mail was to ask for  the real [...]

    Pingback by Tuning a SQL query using TKPROF « — June 7, 2012 @ 8:34 am BST Jun 7,2012 | Reply

  7. […] looking carefully to that execution plan and to its predicate part (always consider the predicate part) I ended up asking myself the following […]

    Pingback by Interpreting Execution Plan | Mohamed Houri’s Oracle Notes — May 18, 2013 @ 11:04 am BST May 18,2013 | Reply

  8. […] I have deliberately omitted the predicate part for clarity (I will show it in a […]

    Pingback by Tuning via row source execution plan | Mohamed Houri’s Oracle Notes — December 25, 2013 @ 4:25 pm BST Dec 25,2013 | Reply

  9. […] – there’s a script to recreate the issue; note, also, the significance of the predicate section of the execution plan. It’s bug 6782665, fixed in […]

    Pingback by Predicate Order | Oracle Scratchpad — February 26, 2014 @ 8:14 am BST Feb 26,2014 | Reply

  10. […] Me: no, it is not the same plan. Please always consider the predicate part […]

    Pingback by Index design | Mohamed Houri’s Oracle Notes — March 2, 2014 @ 8:35 am BST Mar 2,2014 | 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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,113 other followers