Re: Transaction accounting for Oracle

From: Herold Myrland <hm_at_odin.NoSubdomain.NoDomain>
Date: 17 Sep 92 16:57:40 GMT
Message-ID: <1992Sep17.165740.2825_at_news.uit.no>


In article <1992Sep16.183718.1_at_us.oracle.com>, sstephen_at_us.oracle.com writes:
|> In article <1992Sep15.064241.23618_at_news.uit.no>, hm_at_odin.fna.no (Herold Myrland) writes:
|> > Are there any who know how to map the machine resources consumed
|> > by the Oracle processes (different deamons) onto the Oracle user
|> > which initiated the command?
|> >
|> > Whenever an application (SQL*Forms, SQL*Plus, others...) connects
|> > to a DB this is done in the name of a certain Oracle user. However
|> > the Unix system resources consumed by the DB deamons are logged
|> > onto Unix user 'root' or 'ora' (depending on which users that runs
|> > them).
|> >
|> > I am looking for a possibility to log the Oracle deamon activities
|> > caused by each Oracle users's transactions (SELECT, FETCH, UPDATE,
|> > INSERT, DELETE, ...) .
|> >
|> > Does anybody out there know of such a beastie.
|> >
|> >
|> >
|> Be a little more specific. Users perform transactions. The background processes
|> manage the database, (writing out dirty buffers, cleaning up dead processes and
|> deadlocks, archiving data). There is no way to map USERS to system processes
|> because they are not related. 20 people may have updated the database before
|> database writer decides the SGA is too full and writes out the blocks.
|>
|> Maybe you really mean, how to map USERS to transactions or BACKGROUND processes
|> to resources. Tell us what you mean.

I will try to make this simple.

Whenever a USER runs a CLIENT application (SQL*Plus, SQL*Forms, ...) the UNIX resources (CPU time, memory, I/O, ...) consumed by this application is assigned to this spesific UNIX user. The Oracle SERVER processes however (shadow and background processes) run as UNIX user 'ora' and consequently _their_ UNIX resource consumption is assigned to the 'ora' user.

The user is logged onto SQL*Plus (and consequently also the DB) as USER (lets say BILL) from his UNIX account 'bill'. All UNIX resources which SQL*Plus "uses" during this session is accounted for at UNIX user 'bill'. However the resources which the shadow process (and probably also the background processes ARCH, DBWR, LGRW, PMON and SMON) are accounted for the UNIX user 'ora'.

Isn't there ANY possibility to measure the amount of transactions/resource usage these Oracle SERVER processes performs on behalf of Oracle user BILL.

I am of the impression of that when a user runs a session like this the UNIX resource usage is mainly caused by these SERVER processes and minorly by the CLIENT resources. Hence a hypotetical UNIX accouting report could be like:

COMMAND		UNIX	TOTAL		TOTAL
NAME		USER	CPU-MIN		KCOREMIN
------------------------------------------------
oracle		ora	700.50		549825.0    <-- Oracle Server process
SQL*Plus	bill	  8.25		 30500.2    <-- Client
SQL*Forms	bob	 10.10		 12900.0    <-- Client
SQL*Plus	charles	  5.80		  2010.8    <-- Client


Best regards,

Herold Myrland
Manager of FUNN-Narvik as (Norwegian R&D Network, Narvik)

E-mail : hm_at_fna.no
Phone  : +47 82 22100
Fax    : +47 82 47260
Received on Thu Sep 17 1992 - 18:57:40 CEST

Original text of this message