Solved

How to avoid User Block?

Posted on 2011-03-06
7
562 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to connect SQL Server from my Oracle database? 11 130
Queries 15 45
Oracle Forms 10g: How-To Prevent a Commit of 1 Record in Master-Detail block. 3 50
oracle collections 2 19
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

791 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