Solved

How to avoid User Block?

Posted on 2011-03-06
7
564 Views
Last Modified: 2012-05-11
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
Comment
Question by:titanium0203
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 7

Expert Comment

by:MrNed
ID: 35053294
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
 
LVL 6

Expert Comment

by:tangchunfeng
ID: 35053417

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
 

Author Comment

by:titanium0203
ID: 35053551
Hi MrNed,

yes insert statement, what should I do next?
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 7

Expert Comment

by:MrNed
ID: 35053652
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
 

Author Comment

by:titanium0203
ID: 35053831
Hi MrNed,

Are there any parameters that I can use?
0
 
LVL 7

Expert Comment

by:MrNed
ID: 35054490
Sorry, I don't understand what you mean by parameters.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 125 total points
ID: 35056178
>>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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question