OraFAQ Forum: Performance Tuning » clarification of query EXPLAIN plan

Home » RDBMS Server » Performance Tuning » clarification of query EXPLAIN plan (Oracle 10G, Solaris 10)

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

clarification of query EXPLAIN plan [message #500991]

Thu, 24 March 2011 21:11

Tlg13team
Messages: 100
Registered: June 2008
Location: MGL

Senior Member

Dears,

I have compare a query execution plan at partition and non-partition 2 tables.


query1:
EXPLAIN PLAN 
  SET statement_id = 'text1N_600K' FOR
select *
from TEST_NONPARTITION
where billdate>='20050501' and billdate<'20070401'
	  and access_no='XXXXXX'

query2:
EXPLAIN PLAN 
  SET statement_id = 'text1P_600K' FOR
select *
from TEST_PARTITIONED
where billdate>='20050501' and billdate<'20070401'
	  and access_no='XXXXXX'

Non-partitioned table execution plan result:

STATEMENT_ID OPERATION OPTIONS COST
"text1N_600K" "SELECT STATEMENT" 1083
"text1N_600K" "TABLE ACCESS" "BY INDEX ROWID" 1083
"text1N_600K" "INDEX" "RANGE SCAN" 525

Partitioned table execution plan result:

STATEMENT_ID OPERATION OPTIONS COST
"text1P_600K" "SELECT STATEMENT" 767
"text1P_600K" "PARTITION RANGE" "ITERATOR" 767
"text1P_600K" "TABLE ACCESS" "FULL" 767

Total COST of execution plan of query1 = 1083+1083+525
Total COST of execution plan of query2 = 767+767+767

My measurement is correct or not?

Report message to a moderator

Re: clarification of query EXPLAIN plan [message #500992 is a reply to message #500991]

Thu, 24 March 2011 21:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>My measurement is correct or not?
We can't answer this question since we don't have your tables or data.

In Oracle characters between single quote marks are STRINGS
'this is a string, 20070401, not a Date'
When you need to convert STRING to DATE use TO_DATE() function.

Report message to a moderator

Re: clarification of query EXPLAIN plan [message #500993 is a reply to message #500992]

Thu, 24 March 2011 22:15

Tlg13team
Messages: 100
Registered: June 2008
Location: MGL

Senior Member

I try to explain above question in other words:

I have execute explain plan at a query. The explain plan is include several rows and each row have "COST" value.

Total explain plan COST is sum of each row costs. Am I correct?

Report message to a moderator

Re: clarification of query EXPLAIN plan [message #500994 is a reply to message #500993]

Thu, 24 March 2011 22:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Different Oracle versions have different mechanisms for generating EXPLAIN PLAN.
COST has to useful meaning to you, me or any human.
COST is a numerical representation generated & utilized by the Cost Based Optimizer.

http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/ds_appdev004.htm#ADMIN12199

Report message to a moderator

Re: clarification of query EXPLAIN plan [message #501209 is a reply to message #500994]

Sun, 27 March 2011 12:39

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Your issues here are not from a lack of understanding EXPLAIN PLAN, but from a lack of understanding PARITIONING.

Query 2 plan is telling you that it looked only at the rows it needed by scanning only the one partition of your table that had those rows.
Query 1 plan is telling you that it looked only at the rows it needed by scanning only that portion of an index that had those rows.

Here are some things to consider:

1) partitioning becomes more useful as the number of rows you are dealing with gets larger.
2) indexing can often come close to the benefits of partitioning without the increased workload partitioning involves.
3) sometimes indexing is actually faster than partioning depending upon your partitioning scheme.

Also I would like to re-iterate the point made by BLACK_SWAN. If your billdate defined as DATE or is it a number with the bytes arranged as yyyymmdd?

Kevin

Report message to a moderator

Re: clarification of query EXPLAIN plan [message #501216 is a reply to message #501209]

Sun, 27 March 2011 20:15

Tlg13team
Messages: 100
Registered: June 2008
Location: MGL

Senior Member

Thank you explain.

The "billdate" field type is number.

Report message to a moderator

Previous Topic:	why db need kill session instantly
Next Topic:	row cache lock/dc_users??

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 20:45:39 CST 2024