有些时候，服务器可能会经历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博客

诊断和解决cpu消耗较高的问题

最新文章