Re: Locks - how to monitor users operations

On Mon, 24 Feb 2003 09:22:13 +0100, Marcin Kubec wrote:

> 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

The V$s are synonyms on v_$ views, which are views on X$ tables, which are virtual tables created in the SGA on startup. Much good may all of that do you.

Why are you having problems with locking? Left to its own devices, Oracle locks at the lowest possible level, and in such a way as to achieve the highest possible concurrency. If you ever have a problem with locking, it is almost certainly because you are dealing with a piece of code written by someone who wasn't designing for Oracle, but was instead writing something for use in a SQL Server/Informix/DB2/Sybase environment.

In particular, why on Earth do you want to go around locking a table in 'S' mode (whatever that is, but I presume it means 'shared' mode)?? Shared locks happen to be taken on tables all the time in Oracle, but no-one in their right mind actually goes round doing it themselves and explicitly unless there is some profound misunderstanding about how Oracle works.

What specific business problem(s) have you got, and what would you like to see happen. Much better to have that sort of discussion, which might prove productive, that to start worrying about locking levels in Oracle, about which one can't do very much, and wouldn't want to, even if one could.

Regards
HJR Received on Mon Feb 24 2003 - 04:15:52 CST