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

[gajananpkini]

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

[Akila]

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.

[davey23uk]

dont post duplicate threads, also fail to understand why you using cpu is a problem?

[DaPi]

If you're waiting on a lock, you DON'T consume significant CPU.

[hacketta1]

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.