发现CPU占用率一般都维持在90%以上,由于大量使用FOR UPDATE,造成大量LATCH等等待现象,其中某些多线程SQL又运行非常频繁。很久以前就发现这些问题,给出相对意见,建议不采用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
影响CPU的因素
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都指定是这个SQL的问题
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池,并KEEP表
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.2CPU 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
ORACLE CPU过高的一次调整过程
最新推荐文章于 2024-04-17 10:04:41 发布