Home » RDBMS Server » Performance Tuning » clarification of query EXPLAIN plan (Oracle 10G, Solaris 10)
icon5.gif  clarification of query EXPLAIN plan [message #500991] Thu, 24 March 2011 21:11 Go to next message
Tlg13team
Messages: 95
Registered: June 2008
Location: MGL
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?
Re: clarification of query EXPLAIN plan [message #500992 is a reply to message #500991] Thu, 24 March 2011 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
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.
Re: clarification of query EXPLAIN plan [message #500993 is a reply to message #500992] Thu, 24 March 2011 22:15 Go to previous messageGo to next message
Tlg13team
Messages: 95
Registered: June 2008
Location: MGL
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?
Re: clarification of query EXPLAIN plan [message #500994 is a reply to message #500993] Thu, 24 March 2011 22:24 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
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
Re: clarification of query EXPLAIN plan [message #501209 is a reply to message #500994] Sun, 27 March 2011 12:39 Go to previous messageGo to next message
Kevin Meade
Messages: 1943
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
Re: clarification of query EXPLAIN plan [message #501216 is a reply to message #501209] Sun, 27 March 2011 20:15 Go to previous message
Tlg13team
Messages: 95
Registered: June 2008
Location: MGL
Member
Thank you explain.

The "billdate" field type is number.
Previous Topic: why db need kill session instantly
Next Topic: row cache lock/dc_users??
Goto Forum:
  


Current Time: Thu Oct 23 11:05:42 CDT 2014

Total time taken to generate the page: 0.06839 seconds