ORACLE CPU过高的一次调整过程

 发现ＣＰＵ占用率一般都维持在９０％以上，由于大量使用FOR UPDATE，造成大量LATCH等等待现象，其中某些多线程ＳＱＬ又运行非常频繁。很久以前就发现这些问题，给出相对意见，建议不采用FOR UPDATE或在FOR UPDATE上加NOWAIT，由于项目进程问题一直没进行程序修改，决定在这次解决掉，以解后顾之忧。

调整前要知道如下问题
      1，CPU是多少MHZ
      2，SERVER 端的CPU是否有负荷较重
      3，CLIENT 端的CPU是否有负荷较重
      4，空闲时间（如半夜）CPU使用率是否超过15%，如果超过则需要特别注意了
      5，CPU的PEAK LOAD
      6，CPU的IDLE STATE

影响ＣＰＵ的因素
1，高的无必要的解析会代价昂贵。
     发现那些SQL运行了大量的PARSE
      select sql_text, parse_calls, executions from v$sqlarea order by parse_calls desc;
      SYS的总的PARSE情况
      select name, value from v$sysstat where name = ’parse count%’;
     只有硬解析才能减少，可以绑定变量，或增加每一个SESSION的CACHED CURSORS。
2，导致大量I/O的SQL也会明显占用CPU，如没有INDEX。BUFFER GETS一般会同CPU一块增长。可以通过v$sqlarea发现buffer_gets
3，其他等待时间，可以通过v$sesstat,v$sysstat查看

#VMSTAT 5 5 的CPU部分
      us user用掉的
      sy system用掉的
      id 空闲

调整前
TOPAS
      Name            PID CPU% PgSp Owner
      oracle       688416 25.2   4.6 orasbp
      oracle       569658 24.9   4.5 orasbp
      topas        676210   0.1   2.9 root
      syncd         77964   0.0   0.5 root
      hatsd        159792   0.0   8.3 root

或者ps aux|head
检查CPU数量
      /usr/sbin/bindprocessor -q
      The available processors are: 0 1 2 3

STATSPACK的信息
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
enqueue                             5,465        121      1,793    328      0.6
latch free                          2,986      2,669         21      7      0.3
根据消耗最多CPU的进程PID来得到SID详细信息
select s.sid from v$process p, v$session s
where s.paddr=p.addr and p.spid = &your_spid;
根据SID查SQL
SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE
= (select sql_hash_value from v$session
where SID = <problem_SID_you_got_from_last_step>) ;
还有
select n.name,s.value
from v$statname n,V$sesstat s
where n.statistic# = s.statistic#
and value > 0
and s.sid = (select a.sid from v$process p,v$session a
where p.addr =a.paddr
and a.terminal = userenv(’terminal’))
order by n.class,n.name
用以上SQL完成SHELL(shell信息在后面whoit.sh)，运行
sh whoit.sh 688416 来根据PID得到用户信息和SQL语句

在STATSPACK和我的whoit.sh都指定是这个ＳＱＬ的问题
SELECT demessageid, fromid, apptype, demessage,appversion,vovers
ion ,toid,tag FROM de_receivelog WHERE (status = :1 and rownum<
=1 and (dealtime is null or dealtime<sysdate) and delock=0) or (
delock=1 and Update_Date<sysdate-2/24) order by RECEIVETIME for
update

再看等待事件
select sid||' '||event||' '|| total_waits||' '||average_wait from v$session_event where sid=25
SQL> /
SID||''||EVENT||''||TOTAL_WAITS||''||AVERAGE_WAIT
--------------------------------------------------------------------------------
25 latch free 46180 1
25 control file sequential read 4 0
25 log file sync 1 0
25 db file sequential read 202 0
25 db file scattered read 445 1
25 SQL*Net message to client 22 0
25 SQL*Net message from client 22 0

只有一个DEFAULT 池
NAME                                      HIT_RATIO
---------------------------------------- ----------
DEFAULT                                   .88042806

设置db_keep_cache_size池，并ＫＥＥＰ表
ALTER TABLE customer STORAGE (BUFFER_POOL RECYCLE|KEEP|DFAULT)

SQL> analyze table sbpopt.de_receivelog compute statistics;
建立相关索引
alter table TI_REPAIR_DEED storage(buffer_pool keep);
alter table de_receivelog storage(buffer_pool keep);


看到相关的等待都是LATCH FREE，enqueue，估计是由于SELECT FOR UPDATE并且全表扫描造成的
查看每个Session的CPU利用情况：
select ss.sid||' '||se.command||' '||ss.value CPU ||' '||se.username||' '||se.program
from v$sesstat ss, v$session se
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid=ss.sid
and ss.sid>6
order by ss.value

根据STATSPACK的HASH VALUE 用SQL>@sprepsql得到
STATSPACK SQL report for Hash Value: 1710202187 Module: JDBC Thin Client
DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
SBP           3008872479 SBP                 1 9.2.0.6.0   NO      svodbp01
Start Id     Start Time         End Id      End Time       Duration(mins)
--------- ------------------- --------- ------------------- --------------
       44 23-May-06 14:13:01         45 23-May-06 14:28:00           14.98
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
   milliseconds (ms) for Per Execute
                                                       % Snap
                     Statement Total      Per Execute   Total
                     --------------- --------------- ------
        Buffer Gets:       6,938,821          7,608.4   63.34
         Disk Reads:               0              0.0     .00
     Rows processed:             197              0.2

CPU Time(s/ms):             508            557.2
Elapsed Time(s/ms):             607            665.8
              Sorts:           1,292              1.4
        Parse Calls:             191               .2
      Invalidations:               0
      Version count:               1
    Sharable Mem(K):              22
         Executions:             912
SQL Text
~~~~~~~~
SELECT demessageid, fromid, apptype, demessage,appversion,vovers
ion ,toid,tag FROM de_receivelog WHERE (status = :1 and rownum<
=1 and (dealtime is null or dealtime<sysdate) and delock=0) or (
delock=1 and Update_Date<sysdate-2/24) order by RECEIVETIME for
update nowait