Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Native SQL query - Need help

Former Member
0 Kudos

Hi All,

We have a native SQL query accessing Oracle database(given below).

Can anyone please let me know what this query is about and how can we fine tune the query?

SELECT O.OBJECT_NAME ,

H.SID,

HS.MACHINE,

HS.PROCESS,

W.SID,

WS.MACHINE,

WS.PROCESS,

H.CTIME,

W.CTIME,

WS.ROW_WAIT_OBJ#,

WS.ROW_WAIT_FILE#,

WS.ROW_WAIT_BLOCK#,

WS.ROW_WAIT_ROW#,

HP.SPID,

WP.SPID

FROM V$LOCK H, V$LOCK W, V$LOCK I, V$LOCK I2, ALL_OBJECTS O,

V$SESSION HS, V$SESSION WS, V$PROCESS HP, V$PROCESS WP

WHERE H.ID1 = W.ID1

AND H.SID <> W.SID

AND H.TYPE IN ('TX','DL')

AND H.REQUEST = 0

AND H.SID = I.SID

AND I.TYPE = 'TM'

AND I.ID1 = O.OBJECT_ID

AND I.ID1 = I2.ID1

AND W.SID = I2.SID

AND I2.TYPE = 'TM'

AND H.SID = HS.SID

AND W.SID = WS.SID

AND HS.PADDR = HP.ADDR

AND WS.PADDR = WP.ADDR

INTO :EXCL_LOCK_WAITERS-OBJ_NAME ,

:EXCL_LOCK_WAITERS-HOLDER_SID ,

:EXCL_LOCK_WAITERS-H_HOSTNAME ,

  • :EXCL_LOCK_WAITERS-HOLDER_PID ,

:HOLDER_PID ,

:EXCL_LOCK_WAITERS-WAITER_SID ,

:EXCL_LOCK_WAITERS-W_HOSTNAME ,

  • :EXCL_LOCK_WAITERS-WAITER_PID ,

:WAITER_PID ,

:EXCL_LOCK_WAITERS-HELD_SINCE ,

:EXCL_LOCK_WAITERS-WAITSSINCE,

:ROW_WAIT_OBJ,

:ROW_WAIT_FILE,

:ROW_WAIT_BLOCK,

:ROW_WAIT_ROW,

:H_PROCESS,

:W_PROCESS

ENDEXEC

Thanks in advance.

Neethu Mohan

2 REPLIES 2

Former Member
0 Kudos

Former Member
0 Kudos

Hi Neethu,

It gives you an overwiew of blocking Oracle sessions.

1. In general, the SQL checks Oracle sessions (SID's) that were requirering a DML lock ('TM') and now holding row locks (TX) to prevent destructive interference of simultaneous conflicting DML or DDL operations. DML statements automatically acquire both table-level locks and row-level locks ('TX') => holders

It joins these with the sessions that are waiting of releasing the lock by the holders => waiters.

2. it retrieves the detail information wich Oracle process , the object (table) , it's row , block and file

are affected by the locks.

3. Normally, the locks are only hold for a short period of time. If you have blocking sessions it may be of a log running task (i.e. mass data update of a table) ; but it could also be a application bug due to improper handling of concurrent updates of the same object.

4. Tuning

V$tables are expensive to query: Why?

v$ tables are generally Oracle memory structures.

v$ tables are not read consistent.

v$ tables require latches to access -- cannot modify and read memory at the same

time.

heavy access to v$ tables like this may cause some serious heavy duty contention.

Especially if you self join V$lock several times.

So the best would be to save the contents of V$LOCK in some table:

Create table mylocks as select * from v$lock;

Use that table for self-joining and joins to the other tables.

You can also CTAS the other v$ tables to bypass the performance bottleneck while retrieving

v$ directly.

You can empty or drop the created tables any time for new data.

Because you want to investigate only lock hold for a longert time to copy the v$ memory structures into

physical tables is not a disadvantage. You certainly will wait longer on finishing your query

instead of copy them into the tables.

Hope this helped

yk