Native SQL query - Need help

[Former Member]

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

[Former Member]

Hi,

check the link for native SQL

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/content.htm

Regards,

anirban

[Former Member]

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