Home » RDBMS Server » Performance Tuning » Comparision of Two Execution Plan
Comparision of Two Execution Plan [message #119026] Tue, 10 May 2005 06:10 Go to next message
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 #119053 is a reply to message #119026] Tue, 10 May 2005 08:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
'cost' cannot be considered as a measure for performance.
it is just a number, derived from many facts.
The plan that takes a better response time is considered to be better. You should actually into the 'logical i/o'.
Lesser the LIO , better is the sql.

Re: Comparision of Two Execution Plan [message #119056 is a reply to message #119053] Tue, 10 May 2005 08:14 Go to previous messageGo to next message
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 #119063 is a reply to message #119056] Tue, 10 May 2005 08:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I suggest some quality reading before doing anything.

Cary Millsap has written a document ( you need a free registration).
http://www.hotsos.com/e-library/abstract.php?id=7

and Tom Kyte's excelllllllent account on this.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6749454952894

and you can use statspack/tkprof anything you want...

[Updated on: Tue, 10 May 2005 08:30]

Report message to a moderator

Re: Comparision of Two Execution Plan [message #119263 is a reply to message #119026] Wed, 11 May 2005 09:07 Go to previous message
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?
Previous Topic: ARCHIVELOG GETTING FILLED
Next Topic: statspack top 5 wait event
Goto Forum:
  


Current Time: Mon Mar 18 23:59:48 CDT 2024