Re: Locks - how to monitor users operations

"Marcin Kubec" <marcin.kubec_at_warta-vita.com.pl> wrote in message news:b3ckni$pns$1_at_nemesis.news.tpi.pl...
> Hi!
>
> I wish to know if anybody "fought" whith the problem of locks?
> For outline the problem:
> If one user is going an operation which lock table A in RX mode others
> can't lock this table in S mode.
> Some of that kind operations (blocking each others) are executed a long
> time, too long.
> I wish to know which pieces of code are responsible for this, and wno
bloks
> who, when and how long one session blocks another
> and how offen this situations happens.
> I thought of use something like database trigger somthing like ON_LOCK -
> which would be fired when the lock is established.
> But I haven't found that kind of trigger.
> I haven't found any sys table in which information about lock are
inserted
> and updated. There are only dynamic vievs (v$lock,v$session_wait,etc).
>
> Do you know any solution, how to force an Oracle to insert information
> about lock,session,objects (into any table - best to my own table )
exactly
> in time of establishing the lock?
>
> I don't mind to exactly monitoring only current users sessions, but mostly
> trace the systems work for a short period (a day) and based of this
trace -
> analyze the bottleneck of the system (code - mostly PL/SQL packages)
>
> By the way ,based on which objects are the Oracle dynamic views built?
>
> Thanks for any sugestion,
>
> Martin
>

I agree entirely with Richard and Howard.

The following analysis is over-simplistic, I know, and relies on defaults, but as far as I am concerned, the general guidelines are:

1. Don't issue lock statements in code
2. Don't use dbms_lock (unless you want to sleep ;-)
3. Design the application half-decently
4. As Richard says, if you need to use locks like select for update, restrict them to batch updates, or minimise the number of rows and/or the length of time. These row-level locks are held until you commit or rollback.

Oracle will then give you a robust, scalable, multiple concurrent user, read-committed model out of the box.

And this is one of Oracle's biggest advantages for long-trousered applications.

Regards,
Paul Received on Mon Feb 24 2003 - 13:38:13 CST