Home » RDBMS Server » Performance Tuning » How to estimate query response time and number of rows without executing query (Oracle 10.2.0.3 on Sun Solaris 5.10)
How to estimate query response time and number of rows without executing query [message #339224] Thu, 07 August 2008 02:08 Go to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
Hi,
I would like to know if it is possible to get the execution time of a query without actually executing it.

We are on Oracle 10.2.0.3 enterprised edition running on sun solaris.

Assume i have a query "SELECT * FROM CUSTOMER_MASTER"

Before i even execute this query, i need some mechanism (other than explain plan) which i can programatically plugin in the source code so that any query taking more than 300 seconds would not be executed at all. I would also like to know the number of expected rows from this query.

I am okay to have 20% deviation from expected to the actual execution time and rows.

Can you please help me to get this information.

Thanks in advance.
Giridhar
Re: How to estimate query response time and number of rows without executing query [message #339231 is a reply to message #339224] Thu, 07 August 2008 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no other way than explain plan.
YOu can also check in the SGA (v$sqlarea) if the query has already executed and take the time there.

Regards
Michel
Re: How to estimate query response time and number of rows without executing query [message #339236 is a reply to message #339231] Thu, 07 August 2008 02:19 Go to previous messageGo to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
Thanks Michael for your quick response.
I want to restrict users to give queries which takes more than 5 minutes and also > X number of rows. I want to alert them to re define their queries to be more selective than returning all rows from those tables. I was reading another article where they mentioned that some quest product has this feature where it estimates the rows or response time. May be they also would use "explain plan"?. I am looking at something which is reliable and also which i can plug into our program.

Thanks again
Giridhar
Re: How to estimate query response time and number of rows without executing query [message #339247 is a reply to message #339236] Thu, 07 August 2008 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I was reading another article where they mentioned that some quest product

Which ones (article and quest product)?

Regards
Michel
Re: How to estimate query response time and number of rows without executing query [message #339255 is a reply to message #339247] Thu, 07 August 2008 02:52 Go to previous message
gkodakalla
Messages: 49
Registered: March 2005
Member
http://www.experts-exchange.com/Database/Oracle/Q_21987375.html


http://www.quest.com/quest_central_for_oracle/sql_tuning/sql--tuning.asp
Previous Topic: need assist to delete histograms
Next Topic: Index is used still problem
Goto Forum:
  


Current Time: Fri Dec 09 21:15:58 CST 2016

Total time taken to generate the page: 0.12629 seconds