Re: Performance estimation

How does this spreadsheet work? There are simply too many variables to correctly estimate the performance of a SQL query. What is the CPU speed? How much memory in the system? How much allocated to the buffer cache? These questions can be easily answered and tend to be static. How many concurrent users are connected? What transactions are they doing? Is the data required to resolve the query cached or can must it be read from disk? Is the query already parsed and able to be used with bind variables? Is there a network slowdown in progress? Is the query being issued through a Oracle Form, ODBC, or SQL*Plus? If the query is a join, which join method is used (not always static)? If joining, are the tables clustered or are materialized views being used? There just seems to be too many dynamically changing variables which can not lead to any accurate estimate.

Just my 3.14159265 cents worth,
Brian

sergey_s_at_my-deja.com wrote:
>
> I've worked once with a Sybase DBA who had created an Excel spreadsheet
> for estimating performance of his queries. He would enter a bunch of
> numbers such as cache size, unit (block) size, etc. And the spreadsheet
> would spit out some totals (estimated execution time, resource usage,
> etc.). This was a long time ago, and I didn't realize the value of it as
> I was pretty inexperienced then.
>
> Has anyone done anything similar for Oracle? I would love to come up
> with a spreadsheet like that. If you have any thoughts on this or if you
> have done something like that, could you share it? I could use a
> good starting point or any ideas.
>
> Thank you in advance!
> Sergey
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================