Enq - TX - Row Lock Contention?

Oct 10, 2012

In my current project, I am handling an Oracle database with version 10.2.0.3. I analyzed the AWR report for the past month abd saw the following wait events consistently:

CPU time - 45.6% (% Total Call Time)
db file sequential read - 30.6% (% Total Call Time)
enq: TX - row lock contention - 18.8 % (% Total Call Time)

I have uploaded the report for your reference.What should be my approach to troubleshoot this?

Attached File(s)

 awr_report.html ( 382.28K )
Number of downloads: 6

View 1 Replies


ADVERTISEMENT

Row Lock Contention - Trace File Shared

Nov 19, 2012

I am having enq: TX - row lock contention in top wait event. it is occurring between 10pm - 2am.

We are having sqlloader job running every one hour(conventional path). But for the specific period of time i am getting "Global Enqueue Services Deadlock detected". Between 10-5. I analyzed related trace file it is make me little confusion.I found there are four insert query culprit for this locking. out of four sql , tow of them are ran by same SID, other two insert ran by same id. I got confused because how same sid locking them self. trace file below. during this period oracle maintenance window is active.

Trace file:

*** 2012-10-09 03:40:31.135
user session for deadlock lock 0x15365e060
sid: 1104 ser: 22256 audsid: 8797820 user: 49/iurth flags: 0x45
pid: 71 O/S info: user: oracle, term: UNKNOWN, ospid: 8601
image: oracle@sgh0909
client details:
[code]....

View 3 Replies View Related

When Index Lock Contention Occurs / How To Know Which Index Is Causing

Aug 27, 2012

We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.

This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.

Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.

View 5 Replies View Related

Server Administration :: Use Of LM ( Lock Mode) In V$lock?

Oct 4, 2012

we know we can see lock mode held in session can be analysed using LM column in v$lock.But i confused in seeing LM column it all shows in numbers from 0 to 6.

eg

0,'None(0)',
1,'Null(1)',
2,'Row Share(2)',
3,'Row Exclu(3)',
4,'Share(4)',
5,'Share Row Ex(5)',
6,'Exclusive(6)')

View 1 Replies View Related

SQL & PL/SQL :: Contention Between JOB_QUEUE_PROCESSES And Processes

Aug 30, 2011

In the program i wrote, posted in this thread I parallelize 340 jobs.

To do this, according to oracle documentation, i set the job_queue_processes parameters to 450. This works fine, all jobs are submitted, but, when i chek the dba_scheduler_job_run_details, i notice that there is a difference between REQ_START_DATE, which is my requested start date, when i enabled my job, and the ACTUAL_START_DATE, which is the date on which Oracle start this process.

And the more i submit job, the more difference between ACTUAL_START_DATE and REQ_START_DATE increase. But for each job, run duration is less than 1second. I check the processes parameter, and it is set to 100. if i have to increase the processes parameter to 450 too ?

View 5 Replies View Related

Performance Tuning :: Getting Enq - TS - Contention?

Jul 26, 2011

I am getting enq: TS - contention as top event in AWR Report.where can i look into database to fix this issue.

View 14 Replies View Related

TX - Index Contention On Update

Sep 3, 2012

I have a UPDATE statement which sets values on non-indexes columns and do not use indexed-column in where clause (so every column used in statement are not indexes). Lastly, I had some problem with database and saw in ASH report in TOP SQLs that update with events enq: TX - index contention and buffer busy waits. What does it mean? I read a lot of information about index contention, but always related to INSERT and DELETE. What has UPDATE to do with this contention?

View 3 Replies View Related

Server Administration :: Contention On Data Blocks?

Oct 22, 2010

We are suffering from very bad application response for last few days, when i try to check and drill down, where the actual contention is? I came to know that there may be contention on data blocks, which may be a prime reason for degraded performance. Herewith i'm pasting my actual stats of gathered from v$waitstat. I gone through some of asktom docs and find that there may be a problem with freelist or segment space management. My data tablespace is segment space management = Manual.

My main question is

1) Should i need to increase freelist value (Right now my value is 1)

2) Or i have to move on segment space management = auto

SQL> select * from v$waitstat;

CLASS COUNT TIME
------------------ ---------- ----------
data block 2022 4052
sort block 0 0
save undo block 0 0
segment header 1 1
save undo header 0 0
free list 0 0
extent map 0 0
1st level bmb 0 0
2nd level bmb 0 0
3rd level bmb 0 0
bitmap block 0 0
bitmap index block 0 0
file header block 0 0
unused 0 0
system undo header 0 0
system undo block 0 0
undo header 6 0
undo block 0 0
18 rows selected.

View 1 Replies View Related

TX Lock Without Corresponding TM Lock

Jul 23, 2012

I was pretty sure that every TX lock needs TM lock (at least one). But now on my 9.2.0.8 I can see:

SQL> select * from v$lock where type <> 'MR';

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
070000026525D648 070000026525D668          4 RT          1          0          6          0    6551105          0
070000026525D3E8 070000026525D408          5 XR          4          0          1          0    6551108          0
070000026525D6E0 070000026525D700          6 TS          2          1          3          0    6551103          0
0700000267173060 07000002671731D8         36 TX     524304    1532490          6          0       2952          0
0700000266EC07F0 0700000266EC0968         63 TX     655400    1550373          6          0       3750          0
0700000266EC0E78 0700000266EC0FF0         65 TX     720899    1461986          6          0       3711          0
0700000265FB6638 0700000265FB67B0         79 TX     327689    1556563          6          0       3825          0
0700000266EDEA08 0700000266EDEB80         85 TX     786469    1365502          6          0       3461          0
0700000266F52498 0700000266F52610        101 TX     196609    1556026          6          0       3850          0
0700000266EDDD10 0700000266EDDE88        126 TX    1114150    1344174          6          0       1474          0
0700000266F517A0 0700000266F51918        137 TX     589849    1605371          6          0       6345          0
07000002671554B8 0700000267155630        207 TX     262156    1529066          6          0       3826          0
07000002670E13B8 07000002670E1530        238 TX     917504    1391304          6          0         66          0
0700000266D094A8 0700000266D094D0        238 TM     194337          0          2          0         35          0
0700000266D093D8 0700000266D09400        238 TM      43802          0          2          0         35          0
0700000266D09308 0700000266D09330        238 TM       7387          0          2          0         35          0
0700000266D09238 0700000266D09260        238 TM       7374          0          2          0         35          0
0700000266D09168 0700000266D09190        238 TM       7380          0          3          0         35          0
0700000266D09098 0700000266D090C0        238 TM       7228          0          3          0         66          0
0700000266F51E28 0700000266F51FA0        261 TX     393224    1563714          6          0       4586          0

So whats can cause this, I can see that TX locks are mostly from JDBC connection from Application server .

View 3 Replies View Related

SQL & PL/SQL :: How To Lock Particular Row In A Table

Jul 19, 2012

how one can lock a particular row in a table.for example i have a employee table in which 50 records. now i want to lock only 10 records of the employee table.

View 8 Replies View Related

SQL & PL/SQL :: Row Level Lock

Mar 14, 2011

I have a transaction table with some custom properties and two status columns. There are 2 different applications(.Net and Pl/SQL Procedure) using the table. Both the process run parallel and fetch records one by one, perform some calculation and update the status column.

-.Net updates - Extraction_status
-Pl/SQL updates - Ingestion_status

There are likely more chances that both applications will fetch the sane record and try to update the same row. This will cause a lock. Can i use row level lock before update by each application? Or is there any other methods/process in which this can be handled. ?

View 11 Replies View Related

SQL & PL/SQL :: Lock On Loop

Aug 28, 2012

begin
for i in 1..10000 loop
update table1
set col1= col1+1
where type =1;
commit;
end loop;
end;

My question is, if a strored procedure contains the script above and the said procedure will be invoked by two or more sessions at the same time. Does it mean that the 1st session will lock the related rows and other sessions will have to wait for loop in session one to finish?

View 18 Replies View Related

PL/SQL :: How To Lock Session

Jul 18, 2013

i have application where user logs and perform manipulation operation such as insert and update on database table.some time its possible that same user login twice, i just want to hold one session  until and unless dml operation perform by another session gets complete.how to accomplish this in database level .

View 6 Replies View Related

PL/SQL :: Lock On Package

Feb 14, 2013

I am unable to compile my package because of a lock being held on it . Let me provide the details for better clarity .

select * from dba_ddl_locks where session_id=111 and owner='RAHUL';

session_id  owner       name                type                           mode_held         mode_requested
111           RAHUL     RAHUL_PKG        BODY                          NULL              None
111           RAHUL     RAHUL_PKG     Table/Procedure/Type      NULL              NoneThe session details of the session id 111 is as below

select sid,serial#,user#,username,command,status,process,sql_exec_start from v$session where sid=122

sid          serial#          user#                   username                     command                             status                     process      SQL_EXEC_START
111          3558             222                         RAHUL                      47                                   KILLED                   4420            13-DEC-12 02.00.00 AM

The strange this is that we have tried killing this session and the status above even shows ' 'KILLED', stilll the dba_ddl_locks is showing the lock details on the package RAHUL_PKG and we are unable to compile the package (the locking session is running since 13-Dec-2012 ) and we are unable to kill that . Also , is it like if i have executed a package that is referencing some other package then we wont be able to compile the referenced package until and unless the dependent package has completed its execution ??

View 6 Replies View Related

Rowlevel Dead Lock

Aug 19, 2011

In my trace file am getting the following error, I understand this is due to the row level deadlock between two sessions accessing the same rows in a table.

*** 2011-08-17 20:22:27.969
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock:

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-016e0017-0001e10f 75 2370 X 153 2269 X
TX-0217000f-00009247 153 2269 X 75 2370 X

session 2370: DID 0001-004B-0015DCCEsession 2269: DID 0001-0099-000FB698
session 2269: DID 0001-0099-000FB698session 2370: DID 0001-004B-0015DCCE

Rows waited on:
Session 2370: obj - rowid = 000329C9 - AAAynJAAIAAEkVIAAG
(dictionary objn - 207305, file - 8, block - 1197384, slot - 6)
Session 2269: obj - rowid = 000329F2 - AAAynyAARAAEhMBAAA
(dictionary objn - 207346, file - 17, block - 1184513, slot - 0)

----- Information for the OTHER waiting sessions -----
Session 2269:
sid: 2269 ser: 36409 audsid: 563917587 user: 107/PICKLE flags: 0x8000041
pid: 153 O/S info: user: oracle, term: UNKNOWN, ospid: 27324
image: oracle@snokhup4000
client details:
O/S info: user: appsrv, term: , ospid: 1234
machine: snokhup255 program:
current SQL:

DELETE FROM moderation_queue WHERE parent_id =:1 AND submission_type =:2 AND site_id IN ( select site_id from sites where personna_id= 1 )
----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=5rywrxc0mpcc1) -----
delete from shadow_users where user_id=:1 and personna_id=:2

--------------------------------------------------------------------------------

how to overcome or avoid the rowlevel deadlock.

View 3 Replies View Related

SQL & PL/SQL :: How To Release Lock On Table

Apr 12, 2010

How to Release Lock on table ? ( without killing session )

create table x ( a number);
insert into x(a) values (1);

Lets lock table.

declare
cursor c is select * from x for update;
begin
open c ;
end;
[code]....

View 15 Replies View Related

Instance Lock On ARC4

Mar 1, 2012

In the past, we setup a standby database on our production server. My OS is HPUX, Oracle 10g and my database is in archive mod. We are no longer using the standby database method. My problem now is that one of my tablespace (/oradata/tn/rd2_temp 20gb) became 100% full on my production system. In trying to provide more space, I removed one of the standby redo logs. Instead of using ALTER database, I just did a rm from the OS. As I said earlier my STANDBY DB is inoperatable.

When I tried to drop the tablespace(/oradata/tn/rd2_temp it is 20gb), I am getting an oracle error (ora-19528 redo logs being cleared may need access to files). Im also getting and INSTANCE LOCK on ARC4. The rd2_temp tablespace is now offline. If I get rid of rd2_temp, I will be able to gain more space back to /oradata/tn.

My database is still up and working okay.

View 3 Replies View Related

SQL & PL/SQL :: How To Use Lock Statement In Oracle

Sep 17, 2013

I have a scenario where lock statements in mysql need to be converted to oracle. how to convert them.

LOCK TABLES `ABC` WRITE;
UNLOCK TABLES;

View 10 Replies View Related

SQL & PL/SQL :: Identifying Row Lock Condition?

May 6, 2013

I have a situation where I need to check if a row in the table locked i.e (Select for Update with NO WAIT applied on it). If yes go fetch the next row .

View 4 Replies View Related

SQL & PL/SQL :: Lock Table Manually?

Jul 15, 2010

How can one lock a table manually?

View 7 Replies View Related

SQL & PL/SQL :: DEADLOCK Found When Trying To Get Lock

Dec 5, 2012

While i am trying to execute below mentioned query i am facing "DEADLOCK FOUND WHEN TRYING TO GET LOCK" error.

UPDATE PLAN
SET PLAN_PMS_BLOCK_ID =''
WHERE PLAN_PMS_BLOCK_ID<>''
AND PLAN_PMS_BLOCK_ID NOT BETWEEN '0' AND '9'
AND PLAN_PMS_BLOCK_ID NOT BETWEEN 'A' AND 'Z'
AND PLAN_PMS_BLOCK_ID NOT IN('-')
AND LENGTH(PLAN_PMS_BLOCK_ID )=1;

View 7 Replies View Related

How And When The Database Release A Row Lock

May 30, 2013

We are using the following statement to obtain a row lock in a table in the database(ORACLE 10G), SELECT * FROM {TABLE_NAME} WHERE ID = 1 for update and if we succeed grabbing the row lock we will continue to issue a update statement every 30 seconds to preserve the lock as far as possible. here is the update statement to preserve the lock,

UPDATE {TABLE_NAME} SET time = ? WHERE ID = 1.

As you see more longer we keep holding the row lock , more update statements are submitted in the pending transaction. In normal case our application can grab the exclusive row lock and works for a long time,however sometimes a connection reset exception is thrown and our application will close the connection(I assume the pending transaction will be rolled back by the database) and exit the JVM. Since other applications will keep trying to grab the same row lock to become the master role, we expect one of them can succeed but they are all failed because the database has not released the row lock as expected. how and when the row lock can get released in our use case?

View 14 Replies View Related

Deadlock Detected While Trying To Lock Object XDB

Jul 19, 2007

I keep getting an ora-04020: deadlock detected while trying to lock object XDB. SDNRB..The statement I'm trying to issue is:

REVOKE execute on abc."descript_T" FROM PUBLIC;

I am not able to find a solution for this besides the fact of trying the operation again at a later time.I did, but get the same error every single time.

View 6 Replies View Related

Security :: How To Know Which Session Causing TX Lock

Feb 28, 2011

I have to find out the cause of a lock where a particular session with some serial no is causing a TX level lock for certain duration.

View 17 Replies View Related

SQL & PL/SQL :: Getting Error ORA-00069 Cannot Acquire Lock

Jul 31, 2010

I am getting the Oracle Database error "ORA-00069 cannot acquire lock -- table locks disabled for string" in a stored proc. According to some sites I looked at, it says the Cause is "A command was issued that tried to lock the table indicated in the message. Examples of commands that can lock tables are: LOCK TABLE, ALTER TABLE ... ADD (...), and so on.". And the Action they suggest is "Use the ALTER TABLE ... ENABLE TABLE LOCK command, and retry the command".

I am not doing either a lock table or alter table command in my procedure, so what else could be causing this?

View 3 Replies View Related

SQL & PL/SQL :: Find Lock On Particular Date And Time?

Dec 18, 2012

we can easily find out the locks which are currently active session.

Is any other way to find out locks on particular date and time using data dictionary?

For example, we have to find whether TT_objects occured the lock or not on ON yesterday.

View 3 Replies View Related

SQL & PL/SQL :: Exclusive Lock On Tables - Run Job By Scheduling

Sep 27, 2011

@ the end of the day means After date changes i need to populate the new row in Some of my tables with the previous date values with the new date.. In that time some other Transaction will Occur on those tables so we need to lock all the tables in that time..Even i tried by doing the Exclusive lock on the tables...

execute immediate 'LOCK TABLE CTR_SALES IN EXCLUSIVE MODE NOWAIT';
execute immediate 'LOCK TABLE CTR_STK_ASSIGN IN EXCLUSIVE MODE NOWAIT';

And how to run this function @ 12:00 in the midnight by scheduling.... And 1 more thing how to pick the recent (Transaction) row on that table. I tried from the rowid it's not working

View 1 Replies View Related

Server Administration :: Lock Output?

Jun 6, 2012

modify this script to show the table name, rowidwhich is locked?

In addtion,output of script below. In particular, I am trying to understand if there anyactive locks.

set pagesize 85
col username format a10
col osuser format a15
col sid format 9999

[code]...

View 7 Replies View Related

Temporarily Lock Account After So Many Bad Logins?

Jan 19, 2009

I'm not a DBA, nor do i play one on TV.

Is there a way to only temporarily lock an account after so many bad logins? I'm being told by my DBA that there is not, and I'm just shocked that Oracle wouldn't build in that functionality.

View 4 Replies View Related

ORA-09968 :: Unable To Lock File?

Feb 6, 2013

I am using Oracle 11g in my Windows machine. I wrote a multithreaded program that will be inserting into Oracle database so I was getting this exception before-

ORA-12519: TNS:no appropriate service handler found

So after going through various article on the internet. I found that the solution for above exception is to increase the number of processes- So I followed the below thing and it works fine for me after that.

cmd>sqlplus / as sysdba
sqlplus>alter system set processes=300 scope=spfile;
sqlplus>shut immediate;
sqlplus>startup

Now after some more time, I increased the processes to something like below

alter system set processes=1000000 scope=spfile;

And since then whenever I am starting up my Oracle database, it is giving me this exception below and it is happening since one day.

ORA-10997: another startup/shutdown operation of this instance inprogress
ORA-09968: unable to lock file

View 21 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved