Hi,

We're planning to consolidate a large amount of our production
database into one database. Our customers will be charged based on
their use of the central machine's resources (other suggestions are
very welcome). I saw a method of keeping track of resource usage, by
means of the auditing mechanisms in Oracle:
- drop the sys.aud$ table
- create a new audit table in another schema
- provide sys with a synonym or view for that newly created aud$
- build a trigger which recognises logon and logoff.

I assume that'll work just fine. However, I'm not sure how to charge
our customer for aborted sessions.

Any suggestions are very welcome.

HTH,
Hans de Git

Hi Hans

I am sure that if you create the aud$ table under another schema and
point at it with a synonym you might invalidate your support contract.
Oracle no longer supports moving aud$ to another tablespace than system
let alone another schema. This was due to some customer not being able
to perform a recovery.

You do not need to do what you suggest as the system triggers allow you
to capture logon/logoff providing you have the right version?

hth

kind regards

Pete
--
Pete Finnigan
emailete (AT) petefinnigan (DOT) com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

On Tue, 8 Jul 2003 16:33:46 +0200, Hans de Git wrote
(in message <72383d4a.0307080633.e065a27 (AT) posting (DOT) google.com>):

Hi Hans,

I am not sure if this kind of billing is smart to introduce here. There are
quite some fluctuations between oracle server versions running the same
applications. Some queries could easily use double cpu, otheres less than in
older versions. How would you cope with that ? Besides that, if you are paid
for cpu usage, you'd better quit tuning the apps ... ;-)



--
met vriendelijke groeten,
Ronald
http://ronr.nl/unix-dba

Hi Roon,

Because we all use the same instance, our customers will sometimes
have to deal with a larger amount of cpu on their invoice, because of
upgrades. However, their overall benefit will outweigh that issue.
Just like in the mainframe world.

Now, about the billing itself. How about building a system trigger
(thanks Pete, the suggestion about the aud$ tables was an oldie
(before system triggers were introduced)) that queries v$sesstat
before logoff? Seems quite easy. Again, that does not solve the
problem of abended sessions.

Ronald, I agree that tuning would have a serious impact on our
revenues. However, those 'customers' work for the same company as I
do. I try to stay with the big picture....

Now the challenge is to map the cpu per session to resource manager
consumer groups.....

Regards,
Hans

Instead of billing by CPU cycles, have you thought about billing by
other statistics, such as 'user calls' or I/O rates? Not all db
transactions are created equally and some will consume more CPU than
others. But then again, not all db transactions will consume I/O rates
equally.

HTH,
Brian

Hans de Git wrote:
--
================================================== =================

Brian Peasland
dba (AT) remove_spam (DOT) peasland.com

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


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

Hi,

Instead of calculating each session CPU time, you can use Resource
Manager to see the amount of CPU consumed by a group of users. This
suppose that you can assign each user connected to the db, to a group.

The followwing query gives the CPU consumed by the defined groups:

select name, active_sessions, consumed_cpu_time
from v$rsrc_consumer_group;

Dias

hansdegit (AT) hotmail (DOT) com (Hans de Git) wrote in message news:<72383d4a.0307080633.e065a27 (AT) posting (DOT) google.com>...