Re: CPU monthly consumption per user

On Wed, 10 Dec 1997, Daniel Pinol wrote:
>
> I'd need ANY way to count how much CPU each of the users of my database
> has spent over a month. I've thought about 3 ways, but none is
> satisfying:
>
> -SQL TRACE: I could parse the generated files, but they are too big and
> represent too much overhead.
> -AUDIT: It cannot count CPU usage.
> -v$sesstat: Since the SIDs are reused, I cannot count the consumption
> by user.

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.

--
Jeremiah Wilton      http://www.wolfenet.com/~jeremiah