Re: Database Load or Usage calculation

mune19642003_at_yahoo.com wrote:
> I think the messgae got confusing because of the SLA term used.
>
> The intention was to get an idea of how to measure the USAGE of a
> database and its GROWTH over time.

How do you define the usage of your automobile? That's not an easy question to answer. One might define the usage as the number of miles driven per year. And my pickup will show me that with it's fancy in-dash computer. But lets dig a little deeper. What if I drove 1,000 miles in 10 hours? Or what if I drove 1,000 miles in 100 hours? Each of those paints a completely different picture, one of averaging 100 miles per hour and the other of averaging 10 miles per hour. Since each was measured on the metric of total miles, we don't the same idea of *usage* when neither was used in the same manner. Ok...so my pickup's in-dash computer also includes the number of hours the vehicle was running and I can get an average called miles/hour. Most people would say that a vehicle that is driven mostly on the highway (thus a higher miles/hour) is better off than the same vehicle driven mostly in the city. So have we arrived at a good metric here to denote the usage? Have we considered if the vehicle was used to tow a fully loaded trailer? Have we considered environmental factors?

The whole point is that I do not think you can come up with a metric that defines the usage of the automobile...err database.

> After thinking on this a bit , I was wondering if the daily total of
> 'Number of Physical Reads' ( for example ) would be a good indicator
> of the growth of a database..... In most cases that number would grow
> as the DISK SPACE USAGE grows or as USERS grow and such!

Illustrating my point even further....what happens if the DBA changes the Buffer Cache size? This can have an impact on the number of physical reads. The simple act of changing the buffer cache can have a dramatic impact on the "usage" of the database. Similarly, a user submitting a rogue query (i.e. a cartesian product on two large tables) can have an impact on this particular metric.

And in this case, just because disk space usage grows or as users grow, does not necessarily mean that the number of physical reads will increase. You may find that more users keep database blocks in the buffer cache longer, thus lowering the number of physical reads...or then again, you may not.

> Mainly to chart this and show to the mgmt of this growth, instead of
> multiple charts for SPACE,USERS,SESSIONS etc etc
>

Again, we have to get down to what you want in the SLA. What does the business require of the application and the database? The business might require 99.9% uptime. The business might require being able to server 100,000 concurrent users.

As for management, you can certainly track various statistics and use them to show some sort of growth over time. Metrics that are commonly used are total used space, bytes received via SQL*Net from client, and number of user commits or calls. Each of these tell a different story and should be used in the correct context. But these are not typically part of the SLA.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown