CPU 使用率高,和SQL 有很大关系。 用一下方法查查看, 有没有SQL 需要优化, 比如建索引等。 1. 先通过top命令查看产用资源较多的spid号2.查询当前耗时的会话ID,用户名,sqlID等: select sid,serial#,machine,username,program,sql_hash_value,sql_id, to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time from v$session where paddr in (select addr from v$process where spid in ('5648612','256523')); 3. 如果上一步sql_id或者 hash_value不为空,则可用v$sqlarea查出当前正在使用的sql select sql_text from v$sqltext_with_newlines where hash_value = &hash_value order by piece;select * from v$sql where sql_id='' oracle 实时查询最耗CPU资源的SQL语句 :http://blog.csdn.net/tianlesoftware/archive/2009/11/30/4898607.aspx------------------------------------------------------------------------------ Blog: http://blog.csdn.net/tianlesoftware 网上资源: http://tianlesoftware.download.csdn.net 相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx DBA1 群:62697716(满); DBA2 群:62697977
Tue May 11 13:10:06 2010 Interval: 2 Cswitch 500 Readch 274.5K
Syscall 7193 Writech 29214
CPU User% Kern% Wait% Idle% Reads 152 Rawin 0
ALL 69.1 1.6 0.4 29.0 Writes 101 Ttyout 211
Forks 1 Igets 0
Network KBPS I-Pack O-Pack KB-In KB-Out Execs 0 Namei 81
en1 40.6 110.6 106.6 14.4 26.1 Runqueue 6.5 Dirblk 0
lo0 0.1 1.0 1.0 0.0 0.0 Waitqueue 0.0Disk Busy% KBPS TPS KB-Read KB-Writ PAGING MEMORY
hdisk8 0.0 24.0 4.0 4.0 20.0 Faults 1297 Real,MB 32768
hdisk2 0.0 24.0 4.0 6.0 18.0 Steals 0 % Comp 44
hdisk9 0.0 12.0 1.0 4.0 8.0 PgspIn 0 % Noncomp 9
Network KBPS I-Pack O-Pack KB-In KB-Out Execs 0 Namei 81
Tue May 11 13:10:06 2010 Interval: 2 Cswitch 444 Readch 740.7K
Syscall 4432 Writech 29127
CPU User% Kern% Wait% Idle% Reads 280 Rawin 0
ALL 69.1 1.6 0.4 29.0 Writes 113 Ttyout 221
Forks 3 Igets 0
Network KBPS I-Pack O-Pack KB-In KB-Out Execs 1 Namei 240
en1 40.6 110.6 106.6 14.4 26.1 Runqueue 6.5 Dirblk 0
lo0 0.1 1.0 1.0 0.0 0.0 Waitqueue 0.0
Disk Busy% KBPS TPS KB-Read KB-Writ PAGING MEMORY
hdisk8 0.0 24.0 4.0 4.0 20.0 Faults 3543 Real,MB 32768
hdisk2 0.0 24.0 4.0 6.0 18.0 Steals 0 % Comp 44
hdisk9 0.0 12.0 1.0 4.0 8.0 PgspIn 0 % Noncomp 9
hdisk4 0.0 8.0 0.0 0.0 8.0 PgspOut 0 % Client 9
PageIn 0
Name PID CPU% PgSp Owner PageOut 7 PAGING SPACE
oracle 295000 12.8 9.2 oracle Sios 8 Size,MB 32768
oracle 217088 12.3 8.5 oracle % Used 0
oracle 336196 12.3 8.1 oracle NFS (calls/sec) % Free 100
oracle 164036 12.3 8.7 oracle ServerV2 0
oracle 397572 11.3 8.7 oracle ClientV2 0 Press:
oracle 167960 1.8 12.3 oracle ServerV3 0 "h" for help
oracle 234204 1.5 12.0 oracle ClientV3 0 "q" to quit
Signal 2 received
select sid,serial#,machine,username,program,sql_hash_value,sql_id,
to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time from v$session
where paddr in (select addr from v$process where spid in ('5648612','256523'));
3. 如果上一步sql_id或者 hash_value不为空,则可用v$sqlarea查出当前正在使用的sql
select sql_text
from v$sqltext_with_newlines
where hash_value = &hash_value
order by piece;select * from v$sql where sql_id=''
oracle 实时查询最耗CPU资源的SQL语句 :http://blog.csdn.net/tianlesoftware/archive/2009/11/30/4898607.aspx------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群:62697716(满); DBA2 群:62697977
看到个Paging Faults 3543 ,点有高啊,在你截到数据时你系统启动多长时间了?
还有,你页面交换空间和内存一模一样,个位数都一样,是巧合吗?不过used 是0%,正常吗
那前五个oracle进程就占了60%了,分析分析什么造成的。
可以截图,然后发CSDN空间,再把链接贴这。发几个命令的结果上来吧:
sar -r 1 10
vmstat 2 5
vmstat -s