Re: Track user resource usage

On Sat, 28 Feb 2004 08:22:09 -0800, Raj Jamadagni wrote:

> In past what I have done is ... in the logon trigger, I do
>
> insert into stats_collection as select * from v$sesstat ;
>
> in the logoff trigger ... you would again do the same thing, but this
> time update the values in stats_collection with the _difference_
> between (v$sesstat.value - stats_collection.value) for the sid. There
> you have it .... you could of course restrict number of rows that you
> need by using specific stat names .... You get the idea?? YMMV
>
> Works like a charm.
> Raj

Well, that is almost the same thing as what is done by AUDIT SESSION. Why not simply turn on auditing? Logoff trigger will work like a charm, but then again, so will auditing. User written auditing is usually more expensive performancewise then the normal auditing. To what extent may statistics collection at the end of the session go beyond the info collected by auditing, and what's the use? DBMS_RESOURCE_MANAGER makes it possible to control the values of parallelism, CPU consumption and various pool resources. I believe that one can use statspack and V$SYSSTAT to correctly determine the correct values for the plan directives. I used it successfully to prevent processes from using parallel queries on OLTP nodes of 8i OPS cluster and I know it works.

-- 
Some people are only alive because it is illegal to shoot them.