Xref: alice comp.databases.oracle.server:65167 Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!newsfeed.mathworks.com!outfeed1.news.cais.net!info.usuhs.mil!oanews!dcoxmac.nswc.navy.mil!user From: dcox@nswc.navy.mil (David Cox) Newsgroups: comp.databases.oracle.server Subject: Charging for cpu usage (Oracle 8 on SGI) Date: Thu, 09 Sep 1999 14:23:38 -0400 Organization: Naval Surface Warfare Center Lines: 45 Message-ID: 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). 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. 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.