titanium0203
asked on
How to avoid User Block?
What are the queries causing the blocking? I assume its row level locking by some update query, but you first need to know the SQL to figure out an appropriate solution.
set lines 120
col SID for a8
col SERIAL# for 999999
col INST_ID for 9
col OBJECT_NAME for a20
col USERNAME for a10
col PROGRAM for a20
col MACHINE for a20
col SQL_TEXT for a100
WITH sessions AS
(SELECT /*+materialize*/
sid,serial#,username,progr
FROM v$session)
SELECT LPAD(' ', LEVEL ) || sid sid,serial#, object_name,username,progr
substr(sql_text,1,40) sql_text
FROM sessions s
LEFT OUTER JOIN dba_objects
ON (object_id = row_wait_obj#)
LEFT OUTER JOIN v$sql
USING (sql_id)
WHERE sid IN (SELECT blocking_session FROM sessions)
OR blocking_session IS NOT NULL
CONNECT BY PRIOR sid = blocking_session
START WITH blocking_session IS NULL
/
SID SERIAL# INST_ID OBJECT USERNAME PROGRAM MACHINE SQL_TEXT
123 1316 SYSTEM sqlplus@linuxora10grac1 (TNS V1-V3) linuxora10grac1
121 3971 1 A SYSTEM sqlplus@linuxora10grac1 (TNS V1-V3) linuxora10grac1 delete a
146 7166 1 B SYSTEM sqlplus@linuxora10grac1 (TNS V1-V3) linuxora10grac1 delete b
141 36238 2 B SYSTEM sqlplus@linuxora10grac2 (TNS V1-V3) linuxora10grac2 delete from b
in the sample, sid=123 is the root locker
ASKER
Hi MrNed,
yes insert statement, what should I do next?
yes insert statement, what should I do next?
Is the insert statement definitely doing the blocking, or is that the one being blocked?
An insert statement would block because:
* sessions trying to insert the exact same data
* too many sessions inserting different data in the same block
The first scenario is far more likely, you would have to verify that by looking at the exact sql and any bind variables. Only way to fix that is to change the application code to prevent that scenario.
An insert statement would block because:
* sessions trying to insert the exact same data
* too many sessions inserting different data in the same block
The first scenario is far more likely, you would have to verify that by looking at the exact sql and any bind variables. Only way to fix that is to change the application code to prevent that scenario.
ASKER
Hi MrNed,
Are there any parameters that I can use?
Are there any parameters that I can use?
Sorry, I don't understand what you mean by parameters.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.