Row Lock - CPU ilde % time becomes less than 10%
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Row Lock - CPU ilde % time becomes less than 10%

  1. #1
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132

    Row Lock - CPU ilde % time becomes less than 10%

    Oracle 8.1.7 - advance server linux

    OUr DB server CPU idle % time came below 10% and then checked in the DB what could be the issue.

    There was no problem about free space, file system space,
    but when i queried lock .sql i got this output.

    That time the cpu idle time was showing less than 10%

    After few minutes say after 20 to 25 minutes i queried and i got no rows selected , guess lock was released.
    and the cpu % idle time shoot up to 80% idle.

    I am not getting thAT PARTICULAR SQL WHICH IS GETTING LOCKED. IF I COULD GET THAT I CAN HAVE A LOOK AT IT. how can we find out which SQL is it - whether it is insert/update/

    SQL> @/home/oracle/dba/lockinfo.sql

    OS_USER OS_PID ORACLE_USER ORACLE_ID LOCK_TYPE LOCK_HELD LOCK_REQUESTED STATUS OWNER OBJECT_NAME
    ------------------------------ --------- ------------------------------ ---------- -------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
    oracle PRONTOMSP 423 DML Row-X (SX) None Not Blocking PRONTOMSP PRONTO_WISP_PREPAID_CARD
    oracle PRONTOMSP 84 DML Row-X (SX) None Not Blocking PRONTOMSP PRONTO_WISP_PREPAID_CARD


    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    Chennai
    Posts
    38
    Perform a joint of V$sqlarea (address) , v$session (sql_address) , v$locked_object and dba_objects to get the session detail , and the sql.

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    dont post duplicate threads, also fail to understand why you using cpu is a problem?

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If you're waiting on a lock, you DON'T consume significant CPU.

  5. #5
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    fail to understand why you using cpu is a problem
    Agreed. Use vmstat (e.g. vmstat 5 10) and look at the r (runnable queue of the CPU) column. If this is continuously greater than 2 times the number of CPUs then CPU resource is becoming a bottleneck. Also use sar -u (e.g. sar -u 5 5) and look at the %wio column (waiting for IO). It may be that this is very high and so your idle time is more than you think. A high %wio value indicates a disk bottleneck that needs to be resolved, and not a need for more CPU.

    [editing]

    In addition, look at the %usr column in the sar output. If much of the CPU is spent in this mode, then reducing the amount of logical IO Oracle is performing will reduce CPU consumption. You achieve this by tuning SQL.
    Last edited by hacketta1; 12-29-2004 at 08:13 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width