Former Member

Jul 15, 2008 at 06:12 AM

Native SQL query - Need help

31 Views

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