Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle locks

Re: Oracle locks

From: Joel Garry <joel-garry_at_home.com>
Date: 27 Feb 2003 13:53:51 -0800
Message-ID: <91884734.0302271353.aa8675e@posting.google.com>


Andrew Allen <andrew.allen_at_handleman.com> wrote in message news:<3E5D22FD.10804_at_handleman.com>...
> Stefano wrote:
> > DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E5BDA7F.EDC35DDF_at_exesolutions.com>...
> >
> >>Stefano wrote:
> >>
> >>
> >>>Hi,
> >>>
> >>>I need to keep track of when a lock is created and destroyed on a
> >>>specific table, I know that V$LOCK (and other views) contains the
> >>>information I need, but I need to create a sort of audit trail. I
> >>>tried with the following:
> >>>
> >>>audit insert on V$LOCK
> >>>audit delete on V$LOCK
> >>>
> >>>and I verified that when a lock is created one or more rows are
> >>>created (and can be seen) in V$LOCK, but nevertheless the addition of
> >>>new rows is not recorder in the audit trail table and infact:
> >>>
> >>>select USERNAME, TERMINAL,
> >>>to_char(TIMESTAMP,'dd-mon-yyyy hh:mm:ss'),
> >>>OBJ_NAME, ACTION_NAME
> >>>from dba_audit_object
> >>>
> >>>returns no new rows.
> >>>I guess that this is because rows are added somewhere else and since
> >>>V$LOCK is a view the events of INSERT and DELETE are not related to
> >>>the view itself but to the tables underneath.
> >>>I tried to retrieve the information about which tables lay under
> >>>V$LOCK view but I wasn't able.
> >>>Is there somebody that is willing to provide me this piece of
> >>>information?
> >>>Is there somebody that knows a different solution to my problem
> >>>
> >>>Thank you very much in advance
> >>>
> >>>Regards
> >>>
> >>>Stefano
> >>
> >>Why do you want to audit locking? Trying to see if you can slow Oracle
> >>down to a pathetic crawl, an academic exercise, or is there a business
> >>case?
> >>
> >>Also, how many transactions per time period and how many simultaneous
> >>users?
> >>
> >>Daniel Morgan
> >
> >
> > The business case is that I have to keep track of how much time is
> > spent by a user on a specific record, by either editing or reviewing
> > it. The tricky part is that the Client-Server application accessing
> > the data is "off the shelf" and I can't modify the source code. To
> > complicate even more they would like to keep track also of the time
> > the users spend by simply viewing the record; they can infact open a
> > case through the interface, review and close it withouth making any
> > modification (but this time should be measured anyway).
> > Being not able to see what's inside the client program, I discovered
> > that at the Oracle level when a record is displayed on screen, the
> > program locks it for update and therefore I thought that if I can keep
> > track of when the lock is created and destroyed I can have the start
> > and the end of the activity made on it, regardless of the action
> > (insert, update or simply review).
> >
> If, as I understand it, this OTB application does a SELECT <something>
> FOR UPDATE each time the user queries a record then I would get out of
> that shop ASAP because you will be in for NOTHING BUT GRIEF. A well
> written application will do a select to present the data then another
> select for update immediately before applying the changes -- to confirm
> that the record has not changed while it is being viewed/edited.
> Individual row updates will take miliseconds so measuring this is moot.
>
> As for measuring how long a user is viewing/editing a record, cannot be
> done -- at least not with any degree of reliability and accuracy.

Maybe if it is using sql*net he can grep the listener.log. I wrote some routines to do that, but basically to see who was on and when, not how long. But from that I was getting some v$session and v$process information which could perhaps be used... depending on what the app does, it might be as simple as using OS scripts to watch when the process disappears.

Sniffing could be quite reliable and accurate.

Other posts tell my opinion of the business case, however.

jg

--
@home is bogus.
<a href="http://www.fanderson.org.uk/news.html#Thunderbirds
Movie">Austin Powers meets Star Trek?/A>
Received on Thu Feb 27 2003 - 15:53:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US