登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

飞哥的技术博客

世上无难事,只怕有心人!

 
 
 

日志

 
 
 
 

ORACLE CPU过高的一次调整过程  

2009-06-28 09:37:37|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 ORACLE CPU过高的一次调整过程收藏

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

调整前要知道如下问题
      1
      3
      4
      5IDLE STATE

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


      us user调整前
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


      /usr/sbin/bindprocessor -q
      The available processors are: 0 1 2 3


                                                     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

select s.sid from v$process p, v$session s
where s.paddr=p.addr and p.spid = &your_spid;

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
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


NAME                                      HIT_RATIO
---------------------------------------- ----------
DEFAULT                                   .88042806


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并且全表扫描造成的
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 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

  评论这张
 
阅读(894)| 评论(1)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018