From oracle-l-bounce@freelists.org Fri Sep 10 10:37:56 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i8AFbuw29484 for ; Fri, 10 Sep 2004 10:37:56 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i8AFbuI29479 for ; Fri, 10 Sep 2004 10:37:56 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CFA5972C496; Fri, 10 Sep 2004 10:43:52 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13260-39; Fri, 10 Sep 2004 10:43:52 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 37CAD72C47F; Fri, 10 Sep 2004 10:43:52 -0500 (EST) Message-ID: From: JayMiller@TDWaterhouse.com To: oracle-l@freelists.org Subject: RE: TM lock problem Date: Fri, 10 Sep 2004 11:39:35 -0400 MIME-Version: 1.0 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 9374 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: JayMiller@TDWaterhouse.com Precedence: normal Reply-To: JayMiller@TDWaterhouse.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@rediffmail.com] Sent: Wednesday, September 08, 2004 3:25 AM To: oracle-l@freelists.org 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@freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/ -- To unsubscribe - mailto:oracle-l-request@freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/