------------------------------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超高问题分析捕捉 结束---------------------------------------
Oracle数据库CPU超高问题分析捕捉
最新推荐文章于 2023-02-10 10:19:25 发布