解決問題的過程：

1)查看服務器CPU使用情況

輸入top

會發現排在前面的都是oracle的進程，而且CPU占用率都是50以上。

$top

top - 16:28:41 up 4 days, 22:35,? 1 user,? load average: 21.49, 22.14, 22.05

Tasks: 299 total,? 31 running, 268 sleeping,?? 0 stopped,?? 0 zombie

Cpu(s): 79.2%us,? 1.5%sy,? 0.0%ni, 18.4%id,? 0.8%wa,? 0.0%hi,? 0.0%si,? 0.0%st

Mem:?? 7793040k total,? 7659492k used,?? 133548k free,?? 165424k buffers

Swap: 10223608k total,?? 763068k used,? 9460540k free,? 4435980k cached

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

29533 oracle??? 17?? 0 3291m? 36m? 26m R???????98.9 0.5? 98:36.33 oracle

7825oracle??? 17?? 0 3285m? 30m? 25m R???????? 97.0 0.4 267:59.25 oracle

13527 oracle??? 17?? 0 3291m? 36m? 26m R???????97.0? 0.5 362:08.86 oracle

32000 oracle??? 17?? 0 3291m? 36m? 26m R??????? 95.1 0.5 198:58.18 oracle

查出進程的作用

ps -ef|grep 712

oracle???? 712???? 1? 4 Sep02 ???????? 01:38:25 oracleuidorcl (LOCAL=NO)

root????? 2223? 2171? 0 16:36 pts/3??? 00:00:00 grep 712

oracle?? 17712???? 1? 9 Aug31 ???????? 07:14:20 oracleuidorcl (LOCAL=NO)

oracle?? 27120???? 1? 0 15:47 ???????? 00:00:01 oracleuidorcl (LOCAL=NO)

# ps -ef|grep 32000

root????? 2259? 2171? 0 16:37 pts/3??? 00:00:00 grep 32000

oracle?? 32000???? 1? 5 Sep01 ???????? 03:19:37 oracleuidorcl (LOCAL=NO)

# ps -ef|grep 16447

root????? 2283? 2171? 0 16:37 pts/3??? 00:00:00 grep 16447

oracle?? 16447???? 1? 9 Aug31 ???????? 07:15:57 oracleuidorcl (LOCAL=NO)

是用來做oracle連接的。

2)查出導致慢的原因

查進程執行的是什么語句

sqlplus / as sysdba

SELECT

sql_text

FROM 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$session b

WHERE b.paddr =

(SELECT addr FROM v$process c WHERE c.spid = '&pid'))

ORDER BY piece ASC

輸入上面的進程ID:29533

SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD

HH24:MI:SS TZD') AS curr_timestamp, COUNT(db_user) AS failed_co

unt FROM sys.dba_common_audit_trail WHERE action BETWEEN 100 AND

102 AND returncode != 0 AND TO_CHAR(extended_timestamp, 'YYYY-M

M-DD HH24:MI:SS') >= TO_CHAR(current_timestamp - TO_DSINTERVAL('

0 0:30:00'), 'YYYY-MM-DD HH24:MI:SS')

發現，每個進程ID都是執行這條語句

后面群上面問了一下，說是建議把審計功能關閉

3)關閉oracle審計，解決問題

查看audit模式

show parameter audit;

shutdown abort;

startup nomount;

alter ystem set audit_trail=none scope=spfile;

shutdown abort;

startup;

刪除簽權數據，oracle用戶登錄到數據庫服務器：

truncate table SYS.AUD$;

本文出自：億恩科技【www.enkj.com】

總結

以上是生活随笔為你收集整理的oracle查询cpu占用率高,解决oracle进程CPU占用过高问题的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。