Re: Charging for cpu usage (Oracle 8 on SGI)

David,

I don't use Oracle on SGI, but have experience of CPU accounting using Oracle on an IBM Mainframe. This may be no use at all ...

David Cox wrote in message ...
> We are preparing to port our various oracle database applications
>from oracle 6 to oracle 8 (8.0.5.0.0), and would like to be able
>to charge our user community based on cpu usage. We had gotten
>the impression from various oracle database administration classes
>that it is possible to do so in oracle 7 and later, but now that
>we finally have a new system, we cannot figure out how.
>
> Our new platform is an SGI Origin 2000 with 8 cpu's. When we look
>at the v$sesstat table, at statistic#12 (CPU used by this session),
>the value is always 0, even after a sqlplus command that took 20
>seconds of wall clock time to complete (and the user remains within
>sqlplus, of course).

Have you got timed_statistics set to TRUE in your p-file?

>Apparently the task is broken down into oracle
>calls small enough to all be below the 10 millisecond threshold.
>So that statistic is useless to us. How else can we determine the
>cpu usage per user? As far as we can see, the Irix accounting system
>will not work, because all Oracle processes are charged against the
>user Oracle.

I'm not familiar with Irix accounting. Is it possible for one user to 'charge' their CPU to another user with this system? If so, Oracle may provide you with an Accounting Exit facility. This will allow the CPU to be charged to the correct user. Look in your OS-specific Oracle documentation. I found it in the Oracle Admin guide for our OS. I then re-wrote the accounting exit, based on the sample Oracle provide.

You'll need to consider if users are coming from the local OS or another box. You don't want remote_os_authentication (passwords)enabled, since you can't trust any old remote OS to have authenticated the user ID correctly. So, you can't charge CPU time on the basis of what some untrusted OS tells you.

HTH
>
> Also, even if we could use that statistic, the v$sesstat info is
>dynamic, and gone when the user completes an oracle session. We
>thought we were going to be able to use a work-around that was posted
>to this group two years ago (shown below), but it seems odd that
>such a capability does not already exist within oracle. We are looking
>at Oracle Trace to see if this will save the info for us without
>having to use this work-around, though we are hampered since we were
>not sent the online doc for Trace with the rest of the documentation
>we received. Will this product do what we want, or will it access the
>cpu usage statistic from the same (apparently worthless) place as
>that shown in v$sesstat?
>
>-David Cox
> Naval Surface Warfare Center
> Dahlgren, Virginia
>
> Here is the work-around that was posted to this group 2 years ago:
>
>How about this: Drop sys.aud$, create a table in the SYSTEM tablespace
>called AUD$, and create a synonym to it for the SYS user. That way, you
>have the auditing stuff writing to a normal table.
>
>Audit sessions, and place a before insert trigger on the aud$ table, that
>senses if the audit line is a logout. If the audit is a logout, insert the
>username and v$sesstat CPU statistic for the session being audited into a
>separate historical table. This will record CPU used for each session.
Later,
>you can tally up the total for each user by month.
Received on Fri Sep 17 1999 - 12:43:23 CDT