oracle数据库经常会出现占用cpu100%的进程，然后系统就挂了，怎么找出引起这种故障的sql语句

在故障发生时，尝试用下面的语句抓取数据库引起故障的点。

//

在oracle中监控死锁

//

SELECT snusername,

mSID,

snSERIAL#,

mTYPE,

DECODE(mlmode,

0,

'None',

1,

'Null',

2,

'Row Share',

3,

'Row Excl',

4,

'Share',

5,

'S/Row Excl',

6,

'Exclusive',

lmode,

LTRIM(TO_CHAR(lmode, '990'))) lmode,

DECODE(mrequest,

0,

'None',

1,

'Null',

2,

'Row Share',

3,

'Row Excl',

4,

'Share',

5,

'S/Row Excl',

6,

'Exclusive',

request,

LTRIM(TO_CHAR(mrequest, '990'))) request,

mid1,

mid2

FROM v$session sn, v$lock m

WHERE (snSID = mSID AND mrequest != 0) --存在锁请求，即被阻塞

OR (snSID = mSID --不存在锁请求，但是锁定的对象被其他会话请求锁定

AND mrequest = 0 AND lmode != 4 AND

(id1, id2) IN (SELECT sid1, sid2

FROM v$lock s

WHERE request != 0

AND sid1 = mid1

AND sid2 = mid2))

ORDER BY id1, id2, mrequest;

//

定位引起oracle死锁的sql

//

select sql_text from v$sql where hash_value in

(select sql_hash_value from v$session where sid in

(select session_id from v$locked_object))

//

下面的SQL查询可以用于确定锁住数据库对象的锁：

//

select

cowner,

cobject_name,

cobject_type,

bsid,

bserial#,

bstatus,

bosuser,

bmachine

from

v$locked_object a ,

v$session b,

dba_objects c

where

bsid = asession_id

and

aobject_id = cobject_id;

//

显示哪些会话被锁住

//

/ showlocksql /

COLUMN o_name format a10

COLUMN lock_type format a20

COLUMN object_name format a15

SELECT RPAD (oracle_username, 10) o_name, session_id SID,

DECODE (locked_mode,

0, 'None',

1, 'Null',

2, 'Row share',

3, 'Row Execlusive',

4, 'Share',

5, 'Share Row Exclusive',

6, 'Exclusive'

) lock_type,

object_name, xidusn, xidslot, xidsqn

FROM v$locked_object, all_objects

WHERE v$locked_objectobject_id = all_objectsobject_id;

//

显示所有的TM和TX锁

//

/ showalllocksql /

SELECT SID, TYPE, id1, id2,

DECODE (lmode,

0, 'None',

1, 'Null',

2, 'Row share',

3, 'Row Exclusive',

4, 'Share',

5, 'Share Row Exclusive',

6, 'Exclusive'

) lock_type,

request, ctime, BLOCK

FROM v$lock

WHERE TYPE IN ('TX', 'TM');

//

在Oracle数据库中,可以通过kill session的方式来终止一个进程,其基本语法结构为:

被kill掉的session,状态会被标记为killed,Oracle会在该用户下一次touch时清除该进程

我们发现当一个session被kill掉以后，该session的paddr被修改，如果有多个session被kill，那么多个session

的paddr都被更改为相同的进程地址:

//

alter system kill session 'sid,serial#' ;

//

在oracle中kill掉的进程有时还需要等待pmon回滚数据库已经占有的资源

有时候我们需要使用下面的脚本找出那些已经在oracle中kill掉的进程，在 *** 作系统中在kill一次

//

select paddr from v$process p where pid <> 1

minus

select spaddr from v$session s;

$ kill -9 &paddr

CPU占用过高诊断思路

mpstat -P ALL 1，查看cpu使用情况，主要消耗在sys即os系统调用上

perf top，cpu主要消耗在_spin_lock

生成perf report查看详细情况

CPU主要消耗在mutex争用上，说明有锁热点。

采用pt-pmp跟踪mysqld执行情况，热点主要集中在mem_heap_alloc和mem_heap_free上。

Pstack提供更详细的API调用栈

Innodb在读取数据记录时的API路径为

row_search_for_mysql --》row_vers_build_for_consistent_read --》mem_heap_create_block_func --》mem_area_alloc --》malloc --》  _L_unlock_10151 --》__lll_unlock_wait_private

row_vers_build_for_consistent_read会陷入一个死循环，跳出条件是该条记录不需要快照读或者已经从undo中找出对应的快照版本，每次循环都会调用mem_heap_alloc/free。

而该表的记录更改很频繁，导致其undo history list比较长，搜索快照版本的代价更大，就会频繁的申请和释放堆内存。

Linux原生的内存库函数为ptmalloc，malloc/free调用过多时很容易产生锁热点。

当多条 SQL 并发执行时，会最终触发os层面的spinlock，导致上述情形。

解决方案

将mysqld的内存库函数替换成tcmalloc，相比ptmalloc，tcmalloc可以更好的支持高并发调用。

修改mycnf，添加如下参数并重启

[mysqld_safe]malloc-lib=tcmalloc

上周五早上7点执行的 *** 作，到现在超过72小时，期间该实例没有再出现cpu长期飙高的情形。

以下是修改前后cpu使用率对比

1、估计是娱乐大师的CPU数据库还未更新导致的。

2、不过话说回来，一个性能测试软件这样的表现我认为就是不合格。

3、下面的是Passmark跑分成绩对比，单核性能、多核性能都是R5-3500X强很多。

以上就是关于oracle数据库经常会出现占用cpu100%的进程，然后系统就挂了，怎么找出引起这种故障的sql语句全部的内容，包括:oracle数据库经常会出现占用cpu100%的进程，然后系统就挂了，怎么找出引起这种故障的sql语句、求助，数据库某个进程cpu占用高、cpu性能为什么amd3500X比i3-4160鲁大师分还低等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！