OraFAQ Forum: Performance Tuning » Difference in the plan output

Home » RDBMS Server » Performance Tuning » Difference in the plan output

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference in the plan output [message #150157]

Tue, 06 December 2005 09:56

bella13
Messages: 90
Registered: July 2005

Member

I have a question about my execution plan. and i appreciate any remarks and feedback. I am executing this
in a develpers environment. Approx 3-4 users are logged on.

I had this query of which i took the explain plan. the cost of the plan was 76. I then created an index on
one of the tables and fetched a new plan. and the cost changed to 256. That index was never used by the
optimizer but the cost still changed. which I thougt is strange.

I dropped that index and then executed the plan again. but the cost was still 256.

1. Now i am beginning to wonder if that index had anything to do with it? Since that index was never used in the
second plan. And even after dropping it, it never went back to the original.

2. What could have caused the plan to change so much? And why didnt it change to 76 again? Is it because
more developers are accessing the databse than when I was logged on?

3. What is a good cost? I know I can fetch my report and see, if there is any change in performance. But for
the sake of theory, given the cost is 256 and it is chanegd to 76. should i settle for the one that gives me
the cost of 76. or do you think this difference is not that significant? Should I be aiming for a lower cost?

Thanks,
b.

PS: I you wish to look at plan output, copy as is and paste in note/text pad. thanks

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 612 | 76 |
| 1 | TABLE ACCESS BY INDEX ROWID | FINANCIAL_DIVISION | 1 | 4 | 1 |
| 2 | INDEX UNIQUE SCAN | PK_FINANCIAL_DIVISION | 1 | | |
| 3 | TABLE ACCESS BY INDEX ROWID | FINANCIAL_DIVISION | 1 | 4 | 1 |
| 4 | INDEX UNIQUE SCAN | PK_FINANCIAL_DIVISION | 1 | | |
| 5 | TABLE ACCESS BY INDEX ROWID | FINANCIAL_DIVISION | 1 | 4 | 1 |
| 6 | INDEX UNIQUE SCAN | PK_FINANCIAL_DIVISION | 1 | | |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 7 | TABLE ACCESS BY INDEX ROWID | COUNTRY_CODE | 1 | 18 | 1 |
| 8 | INDEX UNIQUE SCAN | PK_COUNTRY_CODE | 1 | | |
| 9 | NESTED LOOPS OUTER | | 1 | 612 | 76 |
| 10 | NESTED LOOPS OUTER | | 1 | 596 | 75 |
| 11 | NESTED LOOPS OUTER | | 1 | 573 | 74 |
| 12 | NESTED LOOPS OUTER | | 1 | 551 | 73 |
| 13 | NESTED LOOPS OUTER | | 1 | 532 | 72 |
| 14 | NESTED LOOPS OUTER | | 1 | 506 | 71 |
| 15 | NESTED LOOPS OUTER | | 1 | 488 | 70 |
| 16 | NESTED LOOPS | | 1 | 461 | 69 |
| 17 | NESTED LOOPS | | 1 | 439 | 68 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 18 | NESTED LOOPS OUTER | | 1 | 308 | 67 |
| 19 | NESTED LOOPS | | 1 | 278 | 66 |
| 20 | NESTED LOOPS | | 1 | 228 | 65 |
| 21 | HASH JOIN | | 7 | 1386 | 51 |
| 22 | TABLE ACCESS FULL | PORTFOLIO | 82 | 6150 | 2 |
| 23 | HASH JOIN | | 13015 | 1563K| 48 |
| 24 | INDEX FULL SCAN | UK1_FINANCIAL_DIVISION | 6 | 24 | 1 |
| 25 | HASH JOIN | | 13015 | 1512K| 46 |
| 26 | TABLE ACCESS FULL | DIVISION_EXTENSION | 4 | 36 | 2 |
| 27 | TABLE ACCESS FULL | TAXLOTS | 26029 | 2796K| 43 |
| 28 | TABLE ACCESS BY INDEX ROWID| POSITIONS | 1 | 30 | 2 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 29 | INDEX RANGE SCAN | POSITIONS_IDX3 | 5 | | 1 |
| 30 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 50 | 1 |
| 31 | INDEX UNIQUE SCAN | PK_ACCOUNT | 1 | | |
| 32 | VIEW PUSHED PREDICATE | WIDINVOBJECTIVEINFO | 1 | 30 | 1 |
| 33 | NESTED LOOPS | | 55 | 1870 | 3 |
| 34 | TABLE ACCESS BY INDEX ROWID| FINANCIAL_DIVISION | 1 | 4 | 1 |
| 35 | INDEX UNIQUE SCAN | PK_FINANCIAL_DIVISION | 1 | | |
| 36 | TABLE ACCESS BY INDEX ROWID| INVESTMENT_OBJECTIVE | 55 | 1650 | 2 |
| 37 | INDEX RANGE SCAN | PK_INVESTMENT_OBJECTIVE | 55 | | 1 |
| 38 | TABLE ACCESS BY INDEX ROWID | SEC_MASTER | 1 | 131 | 1 |
| 39 | INDEX UNIQUE SCAN | PK_SEC_MASTER | 1 | | |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 40 | TABLE ACCESS BY INDEX ROWID | SEC_MASTER_CLIENT_DATA | 1 | 22 | 1 |
| 41 | INDEX UNIQUE SCAN | PK_SEC_MASTER_CLIENT_DATA | 1 | | |
| 42 | TABLE ACCESS BY INDEX ROWID | MINOR_INDUSTRY_CODE | 1 | 27 | 1 |
| 43 | INDEX UNIQUE SCAN | PK_MINOR_INDUSTRY_CODE | 1 | | |
| 44 | TABLE ACCESS BY INDEX ROWID | COUNTRY_CODE | 1 | 18 | 1 |
| 45 | INDEX UNIQUE SCAN | PK_COUNTRY_CODE | 1 | | |
| 46 | TABLE ACCESS BY INDEX ROWID | MINOR_SECURITY_TYPE | 1 | 26 | 1 |
| 47 | INDEX UNIQUE SCAN | PK_MINOR_SECURITY_TYPE | 1 | | |
| 48 | TABLE ACCESS BY INDEX ROWID | MAJOR_INDUSTRY_CODE | 1 | 19 | 1 |
| 49 | INDEX UNIQUE SCAN | PK_MAJOR_INDUSTRY_CODE | 1 | | |
| 50 | TABLE ACCESS BY INDEX ROWID | MAJOR_SECURITY_TYPE | 1 | 22 | 1 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 51 | INDEX UNIQUE SCAN | SYS_C0011877 | 1 | | |
| 52 | VIEW PUSHED PREDICATE | WIDPRICINGSOURCEINFO | 1 | 23 | 1 |
| 53 | NESTED LOOPS | | 20 | 540 | 3 |
| 54 | TABLE ACCESS BY INDEX ROWID | FINANCIAL_DIVISION | 1 | 4 | 1 |
| 55 | INDEX UNIQUE SCAN | PK_FINANCIAL_DIVISION | 1 | | |
| 56 | TABLE ACCESS BY INDEX ROWID | PRICING_SOURCE | 20 | 460 | 2 |
| 57 | INDEX RANGE SCAN | PK_PRICING_SOURCE | 20 | | 1 |
| 58 | VIEW PUSHED PREDICATE | WIDPRICETYPECODEINFO | 1 | 16 | 1 |
| 59 | NESTED LOOPS | | 7 | 140 | 3 |
| 60 | TABLE ACCESS BY INDEX ROWID | FINANCIAL_DIVISION | 1 | 4 | 1 |
| 61 | INDEX UNIQUE SCAN | PK_FINANCIAL_DIVISION | 1 | | |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 62 | TABLE ACCESS BY INDEX ROWID | PRICE_TYPE_CODE | 7 | 112 | 2 |
| 63 | INDEX RANGE SCAN | PK_PRICE_TYPE_CODE | 7 | | 1 |
----------------------------------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

71 rows selected.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 612 | 256 |
| 1 | TABLE ACCESS BY INDEX ROWID | FINANCIAL_DIVISION | 1 | 4 | 1 |
| 2 | INDEX UNIQUE SCAN | PK_FINANCIAL_DIVISION | 1 | | |
| 3 | TABLE ACCESS BY INDEX ROWID | FINANCIAL_DIVISION | 1 | 4 | 1 |
| 4 | INDEX UNIQUE SCAN | PK_FINANCIAL_DIVISION | 1 | | |
| 5 | TABLE ACCESS BY INDEX ROWID | FINANCIAL_DIVISION | 1 | 4 | 1 |
| 6 | INDEX UNIQUE SCAN | PK_FINANCIAL_DIVISION | 1 | | |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 7 | TABLE ACCESS BY INDEX ROWID | COUNTRY_CODE | 1 | 18 | 1 |
| 8 | INDEX UNIQUE SCAN | PK_COUNTRY_CODE | 1 | | |
| 9 | NESTED LOOPS OUTER | | 1 | 612 | 256 |
| 10 | NESTED LOOPS | | 1 | 582 | 255 |
| 11 | NESTED LOOPS OUTER | | 1 | 532 | 254 |
| 12 | NESTED LOOPS OUTER | | 1 | 505 | 253 |
| 13 | NESTED LOOPS | | 1 | 479 | 252 |
| 14 | HASH JOIN OUTER | | 49 | 22001 | 154 |
| 15 | HASH JOIN OUTER | | 49 | 21070 | 151 |
| 16 | HASH JOIN | | 49 | 19992 | 148 |
| 17 | HASH JOIN OUTER | | 49 | 18914 | 143 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 18 | HASH JOIN | | 49 | 18032 | 140 |
| 19 | HASH JOIN | | 146 | 34602 | 123 |
| 20 | TABLE ACCESS FULL | PORTFOLIO | 1634 | 119K| 3 |
| 21 | HASH JOIN OUTER | | 13015 | 2059K| 114 |
| 22 | HASH JOIN OUTER | | 13015 | 1766K| 79 |
| 23 | HASH JOIN | | 13015 | 1563K| 48 |
| 24 | INDEX FULL SCAN | UK1_FINANCIAL_DIVISION | 6 | 24 | 1 |
| 25 | HASH JOIN | | 13015 | 1512K| 46 |
| 26 | TABLE ACCESS FULL| DIVISION_EXTENSION | 4 | 36 | 2 |
| 27 | TABLE ACCESS FULL| TAXLOTS | 26029 | 2796K| 43 |
| 28 | VIEW | WIDPRICETYPECODEINFO | 14 | 224 | 4 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 29 | HASH JOIN | | 14 | 280 | 4 |
| 30 | INDEX FULL SCAN | UK1_FINANCIAL_DIVISION | 6 | 24 | 1 |
| 31 | TABLE ACCESS FULL| PRICE_TYPE_CODE | 7 | 112 | 2 |
| 32 | VIEW | WIDPRICINGSOURCEINFO | 40 | 920 | 4 |
| 33 | HASH JOIN | | 40 | 1080 | 4 |
| 34 | INDEX FULL SCAN | UK1_FINANCIAL_DIVISION | 6 | 24 | 1 |
| 35 | TABLE ACCESS FULL | PRICING_SOURCE | 20 | 460 | 2 |
| 36 | TABLE ACCESS FULL | SEC_MASTER | 4214 | 539K| 16 |
| 37 | TABLE ACCESS FULL | COUNTRY_CODE | 286 | 5148 | 2 |
| 38 | TABLE ACCESS FULL | SEC_MASTER_CLIENT_DATA | 4214 | 92708 | 4 |
| 39 | TABLE ACCESS FULL | MAJOR_SECURITY_TYPE | 8 | 176 | 2 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 40 | TABLE ACCESS FULL | MAJOR_INDUSTRY_CODE | 50 | 950 | 2 |
| 41 | TABLE ACCESS BY INDEX ROWID| POSITIONS | 1 | 30 | 2 |
| 42 | INDEX RANGE SCAN | POSITIONS_IDX3 | 5 | | 1 |
| 43 | TABLE ACCESS BY INDEX ROWID | MINOR_SECURITY_TYPE | 1 | 26 | 1 |
| 44 | INDEX UNIQUE SCAN | PK_MINOR_SECURITY_TYPE | 1 | | |
| 45 | TABLE ACCESS BY INDEX ROWID | MINOR_INDUSTRY_CODE | 1 | 27 | 1 |
| 46 | INDEX UNIQUE SCAN | PK_MINOR_INDUSTRY_CODE | 1 | | |
| 47 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 50 | 1 |
| 48 | INDEX UNIQUE SCAN | PK_ACCOUNT | 1 | | |
| 49 | VIEW PUSHED PREDICATE | WIDINVOBJECTIVEINFO | 1 | 30 | 1 |
| 50 | NESTED LOOPS | | 55 | 1870 | 3 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 51 | TABLE ACCESS BY INDEX ROWID | FINANCIAL_DIVISION | 1 | 4 | 1 |
| 52 | INDEX UNIQUE SCAN | PK_FINANCIAL_DIVISION | 1 | | |
| 53 | TABLE ACCESS BY INDEX ROWID | INVESTMENT_OBJECTIVE | 55 | 1650 | 2 |
| 54 | INDEX RANGE SCAN | PK_INVESTMENT_OBJECTIVE | 55 | | 1 |
---------------------------------------------------------------------------------------------

Attachment: diff.txt
(Size: 14.04KB, Downloaded 1264 times)

[Updated on: Tue, 06 December 2005 10:02]

Report message to a moderator

Re: Difference in the plan output [message #150161 is a reply to message #150157]

Tue, 06 December 2005 10:11

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

COST in explain plan cannot be considered as a measure of performance.
It is just a derived number.

[Updated on: Tue, 06 December 2005 10:11]

Report message to a moderator

Re: Difference in the plan output [message #150162 is a reply to message #150161]

Tue, 06 December 2005 10:15

bella13
Messages: 90
Registered: July 2005

Member

Thanks for your reply.

Then what is it that one can look at and say that the quey is expensive?

Not the joins ..indexes. Is there "A" parameter may be cost ..bytes anything one can look at, at a glance and say the query will be slow?

Thanks in advance,
b

Report message to a moderator

Re: Difference in the plan output [message #150197 is a reply to message #150157]

Tue, 06 December 2005 13:16

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Cost and the CBO is a complicated issue. You can't use it as an absolute value meaning of lower cost equals better performance, because as you saw it can change easily. J Lewis just wrote one of what he plans to be 3 books about it, and they are not casual reading.

You can use it within a the various steps of a query to get a general idea as to the cost along the way as a possible guide to know where to start. But you can do the same with cardinality (rows processed per step).

In general, you are better off timing things to see how long they will take, and then concentrating on the ones that are slow. Also look for things like LIO's and consistent gets in tools like autotrace statistics and TKPROF.

Report message to a moderator

Re: Difference in the plan output [message #150202 is a reply to message #150157]

Tue, 06 December 2005 13:30

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

One other thing to add which also helps is that you need to understand what the operations are that are happening in your plan. What is an index range scan, what is an index fast full scan, what is a hash join. These are described in the documents and books by people like Tom Kyte and are very important to understand conceptually, because it helps you see what the plan really means as far as strategy to get the data.

Report message to a moderator

Previous Topic:	tkprof different types of sort
Next Topic:	Automatic Workload Repository(AWR) and ADDM

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 16:01:17 CDT 2024