Predincting DB Server Capacity with Statspack and M/M/m/°°/FCFS Model

Hello all,

back on this list after a few years, I'd like to introduce myself with a tough one: The current job at the site I'm working at is to forecast whether the current server hardware is capable of serving about seven times more users (requests should rise linearly).

A few months ago we installed Statspack with two daily snapshots during typical business hours for trend analysis. So, I thought it could be nice to take some of the Statspack report results as an input to a queueing model analysis. Some of you may already know Cary Millsap's Excel Sheet that does most of the dirty work - IF YOU FEED IT WELL. The example in Cary's book is about a single statement, but for a simple approach I want to use the averages of all statements available from Statspack.

What I do currently:
- enter Statspack's "Executes Per Second" in to the average arrival rate (lambda)

• enter Statspack's "execute count" / "CPU used by this session" * 100 into the average service rate (mu).

And this is where the doubts arise:

1. Which would be the better input value for service rate (arrival rate): CPU/Calls (calls/s) or CPU/executes (executes/s)?
2. Is "CPU used by this session" really measured in centiseconds? (8.1.7)
3. When I compare the calculated average utilization per CPU (rho) with the actual average reported by the Server monitoring there's quite a difference (21% vs. 29%). What could be the cause of this? Is there a twist in my approach?
4. What is a suitable time frame to build an average of the CPU utilization? What I mean is: Ideally, a CPU is either idle or 100% utilized. For reporting (or forecasts in my case), you build an average over a certain timespan. But how long or short should that be? There were several discussions about CPU utilization on oracle-l, asktom and the usenet which I followed closely. My understanding of this topic by example is: If I have a timeframe of typical utilization (e.g. 15 minutes during peak business hours), I'll have to take the average CPU util of this timeframe and all the other averages from statspack accordingly. Right?

Any suggestions warmly welcome.

Reagrds,
--==/ Uwe \==--

--
http://www.freelists.org/webpage/oracle-l