Re: Locks - how to monitor users operations

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

Why is anyone locking anything in a production in an Oracle production schema? Don't you know the code being executed and why? One rarely, if ever, needs to lock a table in Oracle. And if a table does need to be locked it should be for a very good reason and be well documented. If PL/SQL code being executed by users is locking tables I'd likely send the code back to development.

Try as I might by looking in sql.bsq, catalog.sql, and in my notes I can find no record of an RX lock. Do you mean an XR lock?

The dynamic views are not built on any objects but rather against memory structures.

If you can, please described with greater clarity what is happening and why ... and what you wish to have happen and why. The solution is likely not monitoring the locks ... but understanding why anyone would institute them in the first place. Well unless they think Oracle is SQL Server.

Daniel Morgan Received on Mon Feb 24 2003 - 02:37:28 CST