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,PREV_HASH_VALUE,SQL_HASH_VALUE),DECODE(SQL_HASH_VALUE,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,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,prev_hash_value,sql_hash_value
                          ),DECODE (sql_hash_value,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超高问题分析捕捉 结束---------------------------------------

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!