Link to home
Start Free TrialLog in
Avatar of titanium0203
titanium0203

asked on

How to avoid User Block?

Currently I'm facing a lot of user blocking as per attached.
are there any parameters that I need to increase to avoid these
from happen again and again. User generated image
Avatar of MrNed
MrNed
Flag of Australia image

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.
Avatar of tangchunfeng
tangchunfeng


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,program,machine,blocking_session, row_wait_obj#, sql_id
FROM v$session)
SELECT LPAD(' ', LEVEL ) || sid sid,serial#, object_name,username,program,machine,
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
Avatar of titanium0203

ASKER

Hi MrNed,

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.
Hi MrNed,

Are there any parameters that I can use?
Sorry, I don't understand what you mean by parameters.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial