oracle数据库访问超高延时,Oracle数据库CPU超高问题分析捕捉

------------------------------Oracle数据库CPU超高问题分析捕捉 开始---------------------------------------

--根据进程号查询对应的SQL语句

SELECT LISTAGG(SQL_TEXT, '') WITHIN GROUP(ORDER BY PIECE)

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;

/

SELECT LISTAGG(SQL_TEXT, '') WITHIN GROUP(ORDER BY PIECE)

FROM (SELECT SQL_TEXT, A.PIECE

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);

/

--根据进程行找查询语句

select addr,spid from v$process where spid='11296';

select sid,sql_id from v$session where paddr='0000000382C3E9E0';

select sql_text from v$sql where sql_id='00z33j9a8xm9b';

--根据进程号查询并生成KILL掉占用CPU过高的语旬

SELECT 'alter system kill session ''' || T.SID || ',' || T.SERIAL# || ''';'

FROM V_$SESSION T

WHERE T.SID =

(SELECT SID

FROM V$SESSION

WHERE PADDR = (SELECT ADDR FROM V$PROCESS WHERE SPID = '21717'));

SELECT /*+ ORDERED */

sql_text,a.*

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 =13415))

ORDER BY piece ASC;

select * from V_$SESSION t;

------------------------------Oracle数据库CPU超高问题分析捕捉 结束---------------------------------------