有些时候,服务器可能会经历cpu消耗较高的性能问题,这类问题通常是由于系统中存在性能较低,或者存在错误的sql语句,消耗了大量的cpu,
平台:linux redhat(oracle 10.2.0.5.0 rac)
1,首先看top
top - 08:50:44 up 1:40, 2 users, load average: 0.34, 0.22, 0.17
Tasks: 154 total, 1 running, 153 sleeping, 0 stopped, 0 zombie
Cpu(s): 4.0%us, 2.7%sy, 0.0%ni, 86.4%id, 5.0%wa, 0.3%hi, 1.7%si, 0.0%st
Mem: 2010428k total, 1722676k used, 287752k free, 195608k buffers
Swap: 2048248k total, 0k used, 2048248k free, 852056k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3408 root 18 0 580m 24m 9456 S 1.0 1.2 1:00.29 crsd.bin
27 root 10 -5 0 0 0 S 0.3 0.0 0:00.79 kblockd/0
1398 root 10 -5 0 0 0 S 0.3 0.0 0:04.07 kjournald
3863 oracle RT 0 48520 8036 5324 S 0.3 0.4 0:00.77 oclsomon.bin
4489 oracle -2 0 286m 32m 19m S 0.3 1.7 0:07.37 oracle
4755 oracle -2 0 762m 46m 33m S 0.3 2.4 0:20.12 oracle
1 root 15 0 10348 688 576 S 0.0 0.0 0:00.30 init
2 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/0
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root 10 -5 0 0 0 S 0.0 0.0 0:00.64 events/0
6 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper
23 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
28 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid
67 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 cqueue/0
70 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khubd
72 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kseriod
139 root 15 0 0 0 0 S 0.0 0.0 0:00.00 khungtaskd
140 root 25 0 0 0 0 S 0.0 0.0 0:00.00 pdflush
141 root 15 0 0 0 0 S 0.0 0.0 0:00.82 pdflush
142 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kswapd0
143 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 aio/0
280 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 kpsmoused
304 root 17 -5 0 0 0 S 0.0 0.0 0:00.00 ata/0
从上面可以看出oracle用户的pid %cpu 表示占cpu的比例 表示此处出现性能问题
可以用ps -ef | grep pid 查看
然后用如下脚本找到sql语句
SQL> select /*+ordered*/
2 sql_text
3 from v$sqltext a
4 where (a.hash_value,a.address) in(
5 select decode(sql_hash_value,0,prev_hash_value,sql_hash_value
6 ),
7 decode(sql_hash_value,0,prev_sql_addr,sql_address)
8 from v$session b
9 where b.paddr=(select addr from v$process c
10 where c.spid='&pid'))
11 order by piece ;
Enter value for pid: 4755
old 10: where c.spid='&pid'))
new 10: where c.spid='4755'))
no rows selected
SQL>
(本环境没有低性能sql语句)
SQL> select s.saddr,s.sid,s.serial#,s.paddr,p.pid,p.spid from v$session s,v$process p
2 where s.paddr=p.addr and p.spid='&pid';
Enter value for pid: 4755
old 2: where s.paddr=p.addr and p.spid='&pid'
new 2: where s.paddr=p.addr and p.spid='4755'
SADDR SID SERIAL# PADDR PID SPID
---------------- ---------- ---------- ---------------- ---------- ------------
0000000085798440 165 1 000000008566AFD0 7 4755
SQL>
跟踪sql 此前需要这是sql_trace为真(alter system set sql_trace=true;)
exec dbms_system.set_sql_trace_in_session(165,1,true);
$ORACLE_BASE/admin/test/udump/
test_ora_26907.trc
***
***
BEGIN dbms_system.set_sql_trace_in_session(165,1,true); END;
END OF STMT
PARSE #1:c=0,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1372785181987173
EXEC #1:c=1000,e=179,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1372785181987421
*** 2014-07-19 09:07:20.348
XCTEND rlbk=0, rd_only=1
来分析sql的性能
如果有必要可以用alter ststem kill session 'sid,serial#'来杀死性能较低的sql语句
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27013009/viewspace-1222546/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27013009/viewspace-1222546/