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

港控/mmm° 2021-06-26 16:06 794阅读 0赞

`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,executionsfrom (select sql_text,executions,   rank() 
    over    (order by executions desc) exec_rank  from v$sql)where exec_rank <=5;

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

select disk_reads,sql_textfrom (select sql_text,disk_reads,   dense_rank() 
    over     (order by disk_reads desc) disk_reads_rank   from v$sql)
    where disk_reads_rank <=5;

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

select buffer_gets,sql_textfrom (select sql_text,buffer_gets,   dense_rank() 
    over     (order by buffer_gets desc) buffer_gets_rank   from v$sql)
    where buffer_gets_rank<=5;

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,executionsfrom (select sql_text,executions,   rank() over    (order by executions desc) exec_rank   
    from v$sql)where exec_rank <=5;    
    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#';