| Comparision of Two Execution Plan [message #119026] |
Tue, 10 May 2005 06:10  |
sujit_ocp
Messages: 7
Registered: May 2005
Location: Chennai
|
Junior Member |
|
|
Hi All,
I have a small clarification from you.
Can you see the Following plans and let me know your perception.
Both these tables involved in this query, are inserted Junk Records for Testing.
I have considered the volume for next 3 years.
These tables are analyzed.
Optimizer has bydefault given the (1) Execution Plan where the cost involves is less.
But to me this can even be tuned further for achieving the better Performance.
So my suggesion is Plan (2).
Table No. of Records ( After 3 Years )
--------------------------------------- -----------------------
CLPA_MA_IT_HR_FAMILY 600
CLPA_MA_IT_HIERARCHY 3400
1)
EXPLAIN PLAN FOR
select HR.IT_HR_PRODUCT_FAMILY,
FM.IT_HR_FM_VALUE
from CLPA_MA_IT_HR_FAMILY FM,
CLPA_MA_IT_HIERARCHY HR
where FM.IT_HR_FM_ID = HR.IT_HR_PRODUCT_FAMILY
order by FM.IT_HR_FM_VALUE
/
------------------------------------------------------------------------------------------------------------------------------------- ---
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------------------------------------------------- ---
| 0 | SELECT STATEMENT | | 3353 | 63707 | 29 |
| 1 | SORT ORDER BY | | 3353 | 63707 | 29 |
|* 2 | HASH JOIN | | 3353 | 63707 | 6 |
| 3 | TABLE ACCESS FULL | CLPA_MA_IT_HR_FAMILY | 590 | 10030 | 2 |
| 4 | INDEX FAST FULL SCAN | CLPA_MIH_PRD_FMLY_EXP_IN | 3355 | 6710 | 3 |
------------------------------------------------------------------------------------------------------------------------------------- --
2)
Modified Version (Suggested)
======================
EXPLAIN PLAN FOR
select /*+ FIRST_ROWS */
HR.IT_HR_PRODUCT_FAMILY,
FM.IT_HR_FM_VALUE
from CLPA_MA_IT_HR_FAMILY FM,
CLPA_MA_IT_HIERARCHY HR
where FM.IT_HR_FM_ID = HR.IT_HR_PRODUCT_FAMILY
order by FM.IT_HR_FM_VALUE
/
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3353 | 63707 | 2385 |
| 1 | SORT ORDER BY | | 3353 | 63707 | 2385 |
| 2 | NESTED LOOPS | | 3353 | 63707 | 2362 |
| 3 | TABLE ACCESS FULL | CLPA_MA_IT_HR_FAMILY | 590 | 10030 | 2 |
|* 4 | INDEX RANGE SCAN | CLPA_MIH_PRD_FMLY_EXP_IN | 6 | 12 | 4 |
-------------------------------------------------------------------------------------------------------------------------------------
Interpretation :
Here Smaller Table ( CLPA_MA_IT_HR_FAMILY ) is driving the inner table ( CLPA_MIH_PRD_FMLY_EXP_IN ) , which is to me is correct.
In Plan (2) First Optimizer has gone for full table scan of smaller (outer) table, and for each key of the outer table , Inner table is accessed by it's associative index.
But my only hitch here is the cost. The Cost of later plan is more than previous plan. Although You can see in the Previous plan, Extra Row Reading for the inner table
which is eliminated in the later approach.
My doubt is which one you prefer, 1) less cost at the cost of slower response time.
2) Faster response time at the cost of moderate cost.
Thanks in Advance
Regards,
Sujit
|
|
|
|
|
|
| Re: Comparision of Two Execution Plan [message #119056 is a reply to message #119053] |
Tue, 10 May 2005 08:14  |
sujit_ocp
Messages: 7
Registered: May 2005
Location: Chennai
|
Junior Member |
|
|
Hi Mahesh,
Thanks a lot for the reply.
Can you tell me how do I see how much logical I/O is involved in a Query. I mean which tool will give me that info (EXPLAIN PLAN,AUTOTRACE or TKPROF).
Could you please tell me precisely.
Thanks in Advance,
Regards,
Sujit
|
|
|
|
|
|
| Re: Comparision of Two Execution Plan [message #119263 is a reply to message #119026] |
Wed, 11 May 2005 09:07 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'd second the notion on the reading, and that lower cost number does not always equal better performance. The oracle performance guide, especially in 10g, is pretty informative.
As far as your specific query plans, it looks to me (guessing just on the plan, I of course don't know the data) like the first choice will be faster at retrieving all of the rows your qurry is retrieving, but the second plan will be faster at retrieving the first few rows.
That is a standard difference between the optimizer modes all_rows, which tends to like hash joins, and first_rows, which tends to like nested loops. So it depends on your situation.
So what factors led you to think the optimizers first plan was not good and that your modified plan is better?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
