Re: Strange SQL plan

srivenu_at_hotmail.com wrote:
> I would appreciate if someone can explain this to me.
>
> This is the SQL
>
> SELECT ROUND (TO_NUMBER (NVL (prc.pricing_attribute53, 0)))
> monthly_fee,
> ROUND (TO_NUMBER (NVL (prc.pricing_attribute58, 0))) one_time_fee
> FROM aso_quote_lines_all ql,
> aso_line_relationships qlr,
> aso_price_attributes prc,
> aso_quote_line_details qld
> WHERE
> ql.quote_header_id = 485 AND
> ql.item_type_code = 'MDL' AND
> qlr.quote_line_id = ql.quote_line_id AND
> qlr.relationship_type_code = 'CONFIG' AND
> prc.quote_line_id = qlr.related_quote_line_id AND
> qld.quote_line_id = ql.quote_line_id AND
> qld.config_header_id = 37060
> /
>
> ----------------------------------------------------------------------------------------------
> | Id | Operation | Name |
> Rows | Bytes | Cost |
> ----------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | |
> 958 | 51732 | 3750 |
> | 1 | TABLE ACCESS BY INDEX ROWID | ASO_PRICE_ATTRIBUTES |
> 1 | 10 | 3 |
> | 2 | NESTED LOOPS | |
> 958 | 51732 | 3750 |
> | 3 | NESTED LOOPS | |
> 958 | 42152 | 876 |
> |* 4 | HASH JOIN | |
> 273 | 6825 | 57 |
> | 5 | TABLE ACCESS BY INDEX ROWID| ASO_QUOTE_LINE_DETAILS |
> 274 | 3014 | 16 |
> |* 6 | INDEX RANGE SCAN | ASO_QUOTE_LINE_DETAILS_N5 |
> 274 | | 4 |
> |* 7 | TABLE ACCESS BY INDEX ROWID| ASO_QUOTE_LINES_ALL |
> 549 | 7686 | 40 |
> |* 8 | INDEX RANGE SCAN | ASO_QUOTE_LINES_ALL_N1 |
> 2116 | | 6 |
> |* 9 | TABLE ACCESS BY INDEX ROWID | ASO_LINE_RELATIONSHIPS |
> 4 | 76 | 3 |
> |* 10 | INDEX RANGE SCAN | ASO_LINE_RELATIONSHIPS_N1 |
> 4 | | 2 |
> |* 11 | INDEX RANGE SCAN | ASO_PRICE_ATTRIBUTES_N2 |
> 1 | | 2 |
> ----------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 4 - access("QLD"."QUOTE_LINE_ID"="QL"."QUOTE_LINE_ID")
> 6 - access("QLD"."CONFIG_HEADER_ID"=37060)
> 7 - filter("QL"."ITEM_TYPE_CODE"='MDL')
> 8 - access("QL"."QUOTE_HEADER_ID"=485)
> 9 - filter("QLR"."RELATIONSHIP_TYPE_CODE"='CONFIG')
> 10 - access("QLR"."QUOTE_LINE_ID"="QL"."QUOTE_LINE_ID")
> 11 - access("PRC"."QUOTE_LINE_ID"="QLR"."RELATED_QUOTE_LINE_ID")
>
> Note: cpu costing is off
>
> Now if i write it like this
>
> SELECT ROUND (TO_NUMBER (NVL (prc.pricing_attribute53, 0)))
> monthly_fee,
> ROUND (TO_NUMBER (NVL (prc.pricing_attribute58, 0))) one_time_fee
> FROM x aqha,
> aso_quote_lines_all ql,
> aso_line_relationships qlr,
> aso_price_attributes prc,
> aso_quote_line_details qld
> WHERE
> aqha.QUOTE_HEADER_ID = ql.QUOTE_HEADER_ID and
> aqha.quote_header_id = 485 AND
> ql.item_type_code = 'MDL' AND
> qlr.quote_line_id = ql.quote_line_id AND
> qlr.relationship_type_code = 'CONFIG' AND
> prc.quote_line_id = qlr.related_quote_line_id AND
> qld.quote_line_id = ql.quote_line_id AND
> qld.config_header_id = 37060
> /
>
>
>
> X is some dummy table which has good number of distinct
> quote_header_id's (2,805 distinct quote_header_id's out of a total of
> 4,303 rows).
>
>
> -----------------------------------------------------------------------------------------------
> | Id | Operation | Name |
> Rows | Bytes | Cost |
> -----------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | |
> 1 | 58 | 54 |
> | 1 | TABLE ACCESS BY INDEX ROWID | ASO_PRICE_ATTRIBUTES |
> 1 | 10 | 3 |
> | 2 | NESTED LOOPS | |
> 1 | 58 | 54 |
> | 3 | NESTED LOOPS | |
> 1 | 48 | 51 |
> | 4 | NESTED LOOPS | |
> 1 | 29 | 48 |
> |* 5 | HASH JOIN | |
> 2 | 36 | 42 |
> |* 6 | INDEX RANGE SCAN | IX |
> 2 | 8 | 1 |
> |* 7 | TABLE ACCESS BY INDEX ROWID| ASO_QUOTE_LINES_ALL |
> 549 | 7686 | 40 |
> |* 8 | INDEX RANGE SCAN | ASO_QUOTE_LINES_ALL_N1 |
> 1098 | | 6 |
> |* 9 | TABLE ACCESS BY INDEX ROWID | ASO_QUOTE_LINE_DETAILS |
> 1 | 11 | 3 |
> |* 10 | INDEX RANGE SCAN | ASO_QUOTE_LINE_DETAILS_N1 |
> 1 | | 2 |
> |* 11 | TABLE ACCESS BY INDEX ROWID | ASO_LINE_RELATIONSHIPS |
> 4 | 76 | 3 |
> |* 12 | INDEX RANGE SCAN | ASO_LINE_RELATIONSHIPS_N1 |
> 4 | | 2 |
> |* 13 | INDEX RANGE SCAN | ASO_PRICE_ATTRIBUTES_N2 |
> 1 | | 2 |
> -----------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 5 - access("AQHA"."QUOTE_HEADER_ID"="QL"."QUOTE_HEADER_ID")
> 6 - access("AQHA"."QUOTE_HEADER_ID"=485)
> 7 - filter("QL"."ITEM_TYPE_CODE"='MDL')
> 8 - access("QL"."QUOTE_HEADER_ID"=485)
> 9 - filter("QLD"."CONFIG_HEADER_ID"=37060)
> 10 - access("QLD"."QUOTE_LINE_ID"="QL"."QUOTE_LINE_ID")
> 11 - filter("QLR"."RELATIONSHIP_TYPE_CODE"='CONFIG')
> 12 - access("QLR"."QUOTE_LINE_ID"="QL"."QUOTE_LINE_ID")
> 13 - access("PRC"."QUOTE_LINE_ID"="QLR"."RELATED_QUOTE_LINE_ID")
>
> Why does the plan change so dramatically ?
> Why is there a drop in cardinality from ASO_QUOTE_LINE_DETAILS.
>
> thanks & regards
> srivenu

Interesting. Note the access items for the second plan:

access("AQHA"."QUOTE_HEADER_ID"="QL"."QUOTE_HEADER_ID")
access("AQHA"."QUOTE_HEADER_ID"=485)
access("QL"."QUOTE_HEADER_ID"=485)

Your second SQL statement did not include the third restriction in the WHERE clause: "QL"."QUOTE_HEADER_ID"=485. Oracle automatically determined this through predicate pushing. If I recall correctly from Jonathan Lewis's "Cost Based Oracle Fundamentals" book, versions of Oracle prior to 10.x have a tendency to drop the join condition "AQHA"."QUOTE_HEADER_ID"="QL"."QUOTE_HEADER_ID" during predicate pushing, which would imply that the plan would likely be different for a 10.x and 9.x Oracle database, and likely also different from a 8.x Oracle database. The default value for OPTIMIZER_MAX_PERMUTATIONS was reduced from 80,000 in Oracle 8i to 2,000 for Oracle 9i - that means that instead of analyzing up to 80,000 different join constructions between the tables, Oracle must stop after searching through 2,000 possible join conditions, looking for what it believes is the least expensive access path. Your specification of: aqha.quote_header_id = 485 and aqha.QUOTE_HEADER_ID = ql.QUOTE_HEADER_ID likely forced that as the starting point of the join, since Oracle believed that it would result in the least number of rows passed on to the next level. For the second SQL statement, Oracle believes that it will return 1 row to the client, while in the first SQL statement, Oracle believes that it will return 958 rows.

What version of Oracle are you using? How up to date are the table and index statistics? How was the plan generated, from a 10046 trace (or 10053) - if not, then it is likely just a plan, and the actual execution path may be different. Are you using histograms on the columns?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Sep 26 2006 - 06:03:45 CDT