Home » SQL & PL/SQL » SQL & PL/SQL » Cost and Performance (Oracle 9i, 10g, Unix)
Cost and Performance [message #309155] Wed, 26 March 2008 11:47 Go to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Hi,
I am using Cost based optimer for my application. I have 2 queries; assume Query 1 and Query 2.

The cost of execution of query 1 is around 400 and the cost of execution of Query 2 is 200.

But the execution time seems to be reverse. Query 1 executes faster and query 2 seems to execute slower. I checked this out by setting the "set timing on" command on sqlplus.

This seems to me as little contradictory, cost being high and execution time lesser. Does the cost have a relationship with the time ?

Which one is recommended to be used for high performance ( minimum response time ?

Regards
Amitava
Re: Cost and Performance [message #309161 is a reply to message #309155] Wed, 26 March 2008 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does the cost have a relationship with the time ?

No.
It is just an indicator for Oracle to choose a plan for a specific query and can't be compare between queries.

Regards
Michel
Re: Cost and Performance [message #309167 is a reply to message #309161] Wed, 26 March 2008 13:04 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Thanks Michel,
But can you clarify me one point. If Query 1 and Query 2 are giving exactly the same output, then also we cannot compare the costs of these queries? So the bottom line is that we can simply ignore the cost. we need to focus on the cpu reads and other attributes of the execution plan to tune a query? Please correct me if I am wrong or misunderstood.

Thanks
Amitava..
Re: Cost and Performance [message #309171 is a reply to message #309167] Wed, 26 March 2008 13:14 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All parameters are important.
The best is to keep statistics up to date and let oracle optimizer do its work helping it with a good query writing.

Regards
Michel

Previous Topic: Getting ORA-00604 followed by ORA-1003
Next Topic: how to reset the sequence value
Goto Forum:
  


Current Time: Thu Dec 08 22:25:23 CST 2016

Total time taken to generate the page: 0.14206 seconds