Linux下Oracle程序CPU使用率過高處理方式

問題現象：Linux下Oracle程序CPU使用率過高處理方式

問題分析：一般cpu占用效高都是排序、sql解析和全表掃描，這裡首先需要找出占用cpu最高的sql，然後檢視他的執行計劃，比如：看執行計劃是走索引還是全表掃描（剛開始檢視top發現占用同樣多的CPU的程序很多，還以為是oracle 的bug, 後來發現不是）

處理過程

1、登陸Linux，free -m查詢記憶體使用情況、

[[email protected] trace]$ free -m

total used free shared buffers cached

Mem: 16049 15930 118 0 1 15334

-/+ buffers/cache: 595 15454

Swap: 8015 298 7717

[[email protected] trace]$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.5 (Santiago)

2、使用top工具檢視程序資訊

[[email protected] trace]$ top

top - 12:32:24 up 4:48, 2 users, load average: 2.11, 2.25, 2.31

Tasks: 223 total, 4 running, 219 sleeping, 0 stopped, 0 zombie

Cpu(s): 15.0%us, 1.3%sy, 0.0%ni, 73.1%id, 10.5%wa, 0.0%hi, 0.0%si, 0.0%st

Mem: 16434548k total, 16344300k used, 90248k free, 776k buffers

Swap: 8208380k total, 305664k used, 7902716k free, 15732832k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

9201 oracle 20 0 4261m 2.0g 2.0g R 61.2 12.8 28:27.35 oracle

9940 oracle 20 0 4262m 2.0g 2.0g R 27.3 13.1 8:15.49 oracle

6913 oracle 20 0 4261m 2.0g 2.0g D 18.3 13.0 50:53.39 oracle

10925 oracle 20 0 4261m 2.0g 2.0g R 12.6 12.9 1:28.16 oracle

8898 oracle 20 0 4260m 2.0g 2.0g S 5.7 12.7 26:37.53 oracle

從中可以看出，程序号為9201的oracle程序占用CPU資源是最多的

三、檢視9201這個oracle程序對應的會話資訊

SELECT sql_text FROM v s q l t e x t a W H E R E ( a . h a s h v a l u e , a . a d d r e s s ) I N ( S E L E C T D E C O D E ( s q l h a s h v a l u e , 0 , p r e v h a s h v a l u e , s q l h a s h v a l u e ) , D E C O D E ( s q l h a s h v a l u e , 0 , p r e v s q l a d d r , s q l a d d r e s s ) F R O M v sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) FROM v sqltextaWHERE(a.hashv​alue,a.address)IN(SELECTDECODE(sqlh​ashv​alue,0,prevh​ashv​alue,sqlh​ashv​alue),DECODE(sqlh​ashv​alue,0,prevs​qla​ddr,sqla​ddress)FROMvsession b WHERE b.paddr =(SELECT addr FROM v p r o c e s s c W H E R E c . s p i d = ′ 920 1 ′ ) ) O R D E R B Y p i e c e A S C ; 或 S E L E C T s q l t e x t F R O M v process c WHERE c.spid = '9201')) ORDER BY piece ASC; 或 SELECT sql_text FROM v processcWHEREc.spid=′9201′))ORDERBYpieceASC;或SELECTsqlt​extFROMvsqltext a WHERE (a.hash_value, a.address) IN

(SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)

FROM v s e s s i o n b W H E R E b . p a d d r = ( S E L E C T a d d r F R O M v session b WHERE b.paddr =(SELECT addr FROM v sessionbWHEREb.paddr=(SELECTaddrFROMvprocess c WHERE c.spid = ‘&pid’)) ORDER BY piece ASC;

查詢結果如下：

SQL> SELECT sql_text FROM v s q l t e x t a W H E R E ( a . h a s h v a l u e , a . a d d r e s s ) I N ( S E L E C T D E C O D E ( s q l h a s h v a l u e , 0 , p r e v h a s h v a l u e , s q l h a s h v a l u e ) , D E C O D E ( s q l h a s h v a l u e , 0 , p r e v s q l a d d r , s q l a d d r e s s ) F R O M v sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) FROM v sqltextaWHERE(a.hashv​alue,a.address)IN(SELECTDECODE(sqlh​ashv​alue,0,prevh​ashv​alue,sqlh​ashv​alue),DECODE(sqlh​ashv​alue,0,prevs​qla​ddr,sqla​ddress)FROMvsession b WHERE b.paddr =(SELECT addr FROM v$process c WHERE c.spid = ‘9201’)) ORDER BY piece ASC;

SQL_TEXT

UPDATE ID_GRANT A SET A.ISTOTERM = 1 WHERE A.TERMID = :B4 AND A.

EXTENDID = :B3 AND A.DPID = :B2 AND A.VER <= :B1 AND A.ISTOTERM

= 0

結果顯示SQL語句就是我們之前建構的SQL。

在執行如下SQL語句，找出相應的會話資訊：

select sid,serial# ,username,osuser,machine,program,process,to_char(logon_time,‘yyyy-mm-dd hh24:mi:ss’) logon from

v s e s s i o n w h e r e p a d d r i n ( s e l e c t a d d r f r o m v session where paddr in ( select addr from v sessionwherepaddrin(selectaddrfromvprocess where spid in(‘9201’));

或者

select id,serial# ,username,osuser,machine,program,process,to_char(logon_time,‘yyyy-mm-dd hh24:mi:ss’) logon

from v s e s s i o n w h e r e p a d d r i n ( s e l e c t a d d r f r o m v session where paddr in ( select addr from v sessionwherepaddrin(selectaddrfromvprocess where spid in(’&pid’));

執行的結果如下：

SID SERIAL# USERNAME OSUSER MACHINE PROGRAM PROCESS LOGON

228 7259 CCENSE SYSTEM WORKGROUP\ECARDYINGYONG AutoDealService.exe 8932:5828 2020-04-10 10:59:00

到這裡，就可以根據SID和SERIAL#來殺掉使用者會話了。

SQL> alter system kill session ‘228,7259’ immediate;

System altered.

但是，這要是通過PMON程序來定期清除會話的，是以會有一定的時間間隔，如果情況很緊急，需要立即釋放資源，那麼就要殺作業系統程序了。

四、找出作業系統程序

select PADDR from v$session where SID=228 and SERIAL#=7259;

找出擁有會話的程序位址

然後通過PAAD号找出系統程序号

然後在作業系統上kill -9 9201即可。

擴充補充：查詢的where 條件字段沒有在索引裡面，導緻查詢慢。經過重建并增加相關字段到索引解決，但有點疑惑的是原來庫上查詢語句裡where條件字段也沒有在索引裡面（新庫是使用expdp導出再導入到新庫的），查詢還正常，CPU也不高，oracle資料庫真是博大精深，好多問題還有待研究。

另外複合索引一定要比對查詢的where條件，不然oracle不會走引索。