oracle数据库CPU特别高的解决方法详解数据库

  system表空间100%，导致数据库无法访问

 

  系统表空间正常情况下只存放了数据字典之类的东西，所以占用的空间一般在500M以下。如果你的系统表空间占用比较多的空间，可能有以下几方面的原因：

1）没有为用户明确指定默认表空间，导致system系统表空间作为用户默认表空间

2）开启了审计,请检查此表的大小AUD$

你可以运行以下查询来检查一下系统表空间哪些表比较大：

    SQL select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments 

                where tablespace_name= SYSTEM group by segment_name) 

                where sx 100 order by sx desc;

  查看该表纪录数

   select count(*) sum from AUD$; 

解决方法大概可分为以下几种:

一、为system表空间另外新增一个数据文件。

     alter tablespace system add datafile D:/oracleXE/oradata/XE/system_01.dbf resize 1024M;

二、更改system表空间的数据文件SYSTEM.dbf分配空间。

alter database datafile D:/oracleXE/oradata/XE/system_01.dbf autoextend on;  

alter database datafile D:/oracleXE/oradata/XE/system_01.dbf resize 1024M;

三、truncate掉AUD$表并关闭审计功能(我是使用的这种,效果立竿见影,不过如果不关闭此功能, 需要定期清理此表)：

             SQL alter system set audit_trail=none scope=spfile;

             SQL showdown immediate;

             SQL startup;

四、将AUD$默认表空间由system移出。

如何用sql 语句查询出oracle的cpu使用率

 select * from (select sql_text,sql_id,cpu_time from v$sql order by cpu_time desc) where rownum =10 order by      

rownum asc ;

select * from (select sql_text,sql_id,cpu_time from v$sqlarea order by cpu_time desc) where rownum =10 order by rownum asc ;

这2个语句效果基本一样，一个从v$sql视图查询一个从v$sqlarea视图查询。

列出使用频率最高的5个查询：

select sql_text,executions

from (select sql_text,executions,

   rank() over

    (order by executions desc) exec_rank

   from v$sql)

where exec_rank

消耗磁盘读取最多的sql top5：

select disk_reads,sql_text

from (select sql_text,disk_reads,

   dense_rank() over

     (order by disk_reads desc) disk_reads_rank

   from v$sql)

where disk_reads_rank

找出需要大量缓冲读取（逻辑读）操作的查询：

select buffer_gets,sql_text

from (select sql_text,buffer_gets,

   dense_rank() over

     (order by buffer_gets desc) buffer_gets_rank

   from v$sql)

where buffer_gets_rank

1，使用Linux系统top命令查出占用cpu最高的进程

2，在sqlplus中执行如下sql:

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

其中 pid 是使用top 查看系统中进程占用CPU极高的PID

找到SQL语句进行相应的调整优化

3，分析找到的sql语句，如查看sql执行计划。

######查询结果集输出到一个文件里面

 set trimspool on

    set linesize 120

    set pagesize 2000

    set newpage 1

    set heading off

    set term off

    spool d:/slow_info.txt

    select sql_text,executions

from (select sql_text,executions,

   rank() over

    (order by executions desc) exec_rank

   from v$sql)

where exec_rank

    spool off

   

   查出问题的sql会话sid，并进行封杀操作

   

    select sid, serial#, sql_text, executions

from v$sql join v$session 

on v$sql.sql_id = v$session.sql_id

where cpu_time 20000;

##########查出问题的SQL执行杀进程操作：

执行以下语句杀死

alter system kill session sid,serial#

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/6684.html