TM lock problem

From: Prem Khanna J <premj_at_rediffmail.com>
Date: 8 Sep 2004 07:25:10 -0000
Message-ID: <20040908072510.14287.qmail@webmail31.rediffmail.com>

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_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/

Received on Wed Sep 08 2004 - 02:22:15 CDT