• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

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 Block
0
titanium0203
Asked:
titanium0203
1 Solution
 
MrNedCommented:
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.
0
 
tangchunfengCommented:

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
0
 
titanium0203Author Commented:
Hi MrNed,

yes insert statement, what should I do next?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
MrNedCommented:
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.
0
 
titanium0203Author Commented:
Hi MrNed,

Are there any parameters that I can use?
0
 
MrNedCommented:
Sorry, I don't understand what you mean by parameters.
0
 
slightwv (䄆 Netminder) Commented:
>>are there any parameters that I need to increase to avoid these

If you are referring to database parameters, no.  What everyone is saying is you likely need to change your application to commit more often or figure out the logic that is causing these blocks and code around them.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now