RE: TM lock problem
- From: JayMiller@xxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 10 Sep 2004 11:39:35 -0400
Does the user have multiple sessions open? You can see in EM if the lock
might be held by one session (e.g., an update issued without a commit) and
he's being blocked in another session.
Jay Miller
-----Original Message-----
From: Prem Khanna J [mailto:premj@xxxxxxxxxxxxxx]
Sent: Wednesday, September 08, 2004 3:25 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: TM lock problem
Hi all,
the env is oracle 9.2.0.4 / AIX5.2L
i am facing TM lock problem on a table.
it's small table with 3 fields . only one user is
working on it . no other concurrent user is using the
table.this only user does some insert/delete on the table
and he faces this lock error.
Checking it with EM console,shows that that table is
locked by TM (Row Exclusive) lock.
i thought of "alter table .. disable table lock" ,
but that will not be a permanent solution anyway.
with just one user working on it , why do i face
this problem ? can't understand why this happens ?
statspack report during that period, shows
"log file sync" among the first top wait events.
the log files are 10M now. will making it bigger help me ?
Load Profile
~~~~~~~~~~~~ Per Second Per Tx
--------------- -------
Redo size: 21,216.86 2,675.56
Logical reads: 465.08 58.65
Block changes: 71.96 9.07
Physical reads: 20.01 2.52
Physical writes: 6.43 0.81
User calls: 48.68 6.14
Parses: 7.91 1.00
Hard parses: 0.16 0.02
Sorts: 6.98 0.88
Logons: 0.01 0.00
Executes: 34.59 4.36
Transactions: 7.93
% Blocks changed per Read: 15.47 Recursive Call %: 50.79
Rollback per transaction %: 0.16 Rows per Sort: 6.40
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.99
Buffer Hit %: 96.56 In-memory Sort %: 100.00
Library Hit %: 99.05 Soft Parse %: 98.01
Execute to Parse %: 77.12 Latch Hit %: 99.81
Parse CPU to Parse Elapsd %: 72.00 % Non-Parse CPU: 94.25
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 88.31 88.26
% SQL with executions>1: 78.67 81.10
% Memory for SQL w/exec>1: 70.37 72.54
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
% Total
Event Waits Time (s) Ela Time
--------------------------------- ------------ -------- -------
CPU time 25 44.36
log file sync 7,334 18 31.42
db file sequential read 1,774 4 7.49
db file parallel write 81 3 5.29
rdbms ipc reply 21 2 3.53
how to avoid this error ? can someone guide me ?
--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
