Re: caching and benchmarking oracle?

Hi,

I don't know how the constraint clauses for different users differ from each other.
But I would recommend to put the constraints as oracle parameters (works for all Java/C/C++/VB ...). This way, all users would still use the same set of prepared statements on the server, the client just passes some parameters with different values.

Also, if it's a decision support system, use stored procs and funcs as much it's possible. A lot of times, people tend to generate dynamic SQL where static SQL does it, too... and it's much faster.

By the way, another thing to speed stuff up is to utilize connection pooling.
Do it step by step and repeat your stress and volume tests (regression testing from change to change ...).

bye
Stefan

gdas_at_my-deja.com wrote:
>
> Hello and happy holidays.
>
> Can anyone provide any advice on any relatively easy steps to take to
> benchmark the performance of an oracle database?
>
> My dilemma is that I need to procure hardware for a deployment but I am
> not entirely sure how much hardware I need. We have a development
> server right now used for a beta and it is pretty under-utilized. The
> problem is we expect the business to grow (hopefully) after the
> deployment so we expect our usage to increase. So I'm trying to find
> the high watermark for the current server and use that to help
> determine what to buy (and justify that purchase).
>
> This system is a read-only reporting system. So query performance is
> my main concern.
>
> We simulated several stress tests but are suspicious of them due to the
> fact that once things were cached in the SGA then simply repeating the
> requests over and over again didn't really provide realistic results.
>
> The problem is that different users login with different usernames and
> passwords and each user essentially gets a different constraint in all
> the where clauses of the queries.
>
> Right now I don't have an expensive stress test tool that is flexible
> enough to simulate different users doing different things at the same
> time. Can anyone recommend one?
>
> Or is there any way to temporarily disable the cache for this test?
> Or are there any formulas that anyone knows that I might be able to use
> to roughly extrapolate the load based on a limited scale test? The
> alternative is to basically run enough concurrent users (different
> users) until the machine is thrashed or the performance per session
> degrades significantly.
>
> This isn't my area of expertise, so I'm just trying to figure out how
> to do this. I appreciate any advice.
>
> Gavin
>
> Sent via Deja.com
> http://www.deja.com/
Received on Wed Dec 27 2000 - 10:16:46 CST