RE: Access statistics

For what its worth,

Oracle has a nice feature for resource management called PROFILEs:

CREATE PROFILE profile LIMIT
[ SESSION_PER_USER [ session_limit | UNLIMITED | DEFAULT ] ]
[ CPU_PER_SESSION cpu_session_limit | UNLIMITED | DEFAULT ] ]
[ CPU_PER_CALL cpu_call_limit | UNLIMITED | DEFAULT ] ]
[ CONNECT_TIME connect_limit | UNLIMITED | DEFAULT ] ]
[ IDLE_TIME idle_limit | UNLIMITED | DEFAULT ] ]
[ LOGICAL READS_PER_SESSION read_session_limit | UNLIMITED | DEFAULT
] ]
[ LOGICAL READS_PER_CALL read_call_limit | UNLIMITED | DEFAULT ] ]
[ PRIVATE_SGA memory_limit | UNLIMITED | DEFAULT ] ]
[ COMPOSITE_LIMIT resource_cost_limit | UNLIMITED | DEFAULT ] ]

which limits things like CPU_PER_CALL and LOGICAL_READS_PER_SESSION
to a profile. The ALTER USER command then allows you to assign a
PROFILE to a user. This is really nice, since you can prevent
run-away queries from denying service by the database to other users.
It also can prevent a user from soaking up all of the available
connections. You must set a flag in your initSID.ora configuration
profile for ORACLE to support profiles. Since Jan is collecting these
statistics any way (if the appropriate configuration flag is set), it
would be pretty trivial to implement PROFILEs in PostgreSQL.

Mike Mascari
mascarm(at)mascari(dot)com

-----Original Message-----
From: Tom Lane [SMTP:tgl(at)sss(dot)pgh(dot)pa(dot)us]

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> So outing myself not beeing a *real programmer*, this is what
> I have so far:

Hmm ... what is the performance of all this like? Seems like a lot
of overhead. Can it be turned off?

> * Backends call some collector functions at various places
> now (these will finally be macros), that count up table
> scans, tuples returned by scans, buffer fetches/hits and
> the like.

Have you removed the existing stats-gathering support
(backend/access/heap/stats.c and so on)? That would buy back
at least a few of the cycles involved ...

regards, tom lane