Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to avoid User Block?

Posted on 2011-03-06
7
Medium Priority
?
566 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

610 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