Oracle FAQ Your Portal to the Oracle Knowledge Grid


RE: Performance Metrics

From: Delmolino, Dominic <>
Date: Mon, 31 Oct 2005 12:40:44 -0500
Message-ID: <A33AAC5D9958DC4BB728EB57FD87E7B2A65262@VAMAIL3.CORPIT.NSI.NET>


Have they told you want they want to do with the results of these "performance metrics"? What will they be used for? If they won't be used for anything, then why produce them :-)

But seriously, I consider it my job to enable the business to take in as much money as possible while spending as little as possible -- so, I look for business metrics like how many orders we process per hour and compare that to system resource consumption per hour.

Ideally, we process trillions of orders per hour while consuming very little system resource :-)

In my system, we use business metrics like customer logins per hour (we record login history in the DB) and order line items per hour -- and I compare them to thinks like logical reads per hour and executions per hour (via statspack/perfstat).

Ideally the business metrics increase while the system resource metrics decrease. If that's the case, I can tell my management that they can migrate to cheaper, smaller hardware or delay hardware upgrades until we need to. If it's not happening -- I tell my management I'm aware of it and that I'm working to fix it -- and they should see the results after we drop our new software to production.

If they don't see results, they can hold me accountable. And that's how it should be.

-----Original Message-----

[] On Behalf Of Mark W. Farnham Sent: Monday, October 31, 2005 8:21 AM
To:; Subject: RE: Performance Metrics


What business are you in?

Find out which business processes are essential to your business, which are opportunities for profit if done faster, and any service level agreements with customers that cost your money if transaction completion is slower than some rate.

Design a dummy transaction that can be factored out of the real results or reversed for each important business function. Usually this can be done without custom programming, but you need to get sign off from whoever controls your Sarbox (Anderson bankruptcy re-employment act) policies. You also need to make sure that your dummy transaction cannot trigger a business event. For example, if you code up an order entry dummy transaction, make sure that the withdrawal from inventory does not trigger a re-order event, and make sure you stop short of actually generating the paperwork to schedule the manufacture of something to be delivered to your office.

The dummy transactions should be light-weight so that you do not materially degrade performance during peak load. Design switches to turn off each dummy transaction so you can prove that your dummy transaction alone is not material in load (or in the alternative find out that you designed a problem transaction). Also Design a master switch to turn off all dummy transactions. This is convenient for when everything going to hell in a hand cart, you've sampled enough during the crisis, and you want to absolutely rule out the dummy transactions from consideration as the cause of the problem. This is useful even if you know to a scientific fact that the dummy transactions cannot be part of the problem.

Measure pretty much all the time and log the results so you can tell when "the system is slow" from the perspective of business users. Most especially, as business grows over time and transactions overall increase in rate, you might eventually reach a point where your dummy transactions are chronically slower. That is a good time to inspect your capacity planning process for a review if you don't already have upgrades scheduled.
A good side check is rate of redo log generation, but remember to factor out any special maintenance events as outliers. Also track system level statistics such as cpu utilization, disk(farm) saturation, network saturation, controller load, etc. A correlation of these numbers against the rates of your dummy transactions can be very useful in predicting future degradation.

Also find a way to count user transactions per hour or per day by some useful category that represents the business. Once you get enough points to feed into a least squares analysis for reasonably confident projections, this can also inform your capacity planning process. (Don't bother printing the results unless you also calculate a confidence range.)

Make a projection how long it would take you to be back in operation at an alternate site if everything goes perfectly in the transition. State that is a best case analysis in your report and significant risk factors in the plan.

Make a projection how long it would take to do a local recovery if something goes so bad you have to do a full restore. Make a projection how long it would take to do a local recovery if you lose an entire storage array.

There is more, but that should put you in good stead. None of this has anything at all to do with performance tuning or repairing a bad current process (although there is a chance you might identify a bad process if a dummy transaction you would presume is for a trivial amount of work takes forever.) If your management is asking the question for respond to some fad of "these numbers are good" then this won't help you much. But if your management is trying to get a handle on the system time requirements to process business flows, you will put them in a position to make good decisions about how to improve the business. One of those decisions will likely be to justly reward you for taking a vague question and giving them a meaningful answer that causes the business to make more money.

Rightsizing, Inc.
Mark W. Farnham
36 West Street
Lebanon, NH 03766-1239
tel: (603) 448-1803

-----Original Message-----

[]On Behalf Of Lou Avrami Sent: Friday, October 28, 2005 3:56 PM
Subject: Performance Metrics

Hello all,

Upper management has just ask my group to provide performance metrics for our database servers and databases. Even after a couple of conversations we're not quite sure what they mean by "performance metrics", and I'm sure they aren't quite sure what they mean, either. ;)

We could give them Statspack report output, but that probably would be too much information.

If anyone out there has any ideas on what kind of (very) high-level Oracle database metrics might be appropriate, it would be very helpful.

Lou Avrami


-- Received on Mon Oct 31 2005 - 11:43:17 CST

Original text of this message