Oracle Database High CPU Usage

数据库运行慢，登录操作系统发现 CPU 使用率比平常要高
查看当前会话运行的 SQL Text 和 PLAN

SQL> select sql_id, count(1) from gv$session where type='USER' and status = 'ACTIVE' group by sql_id;
SQL_ID          COUNT(1)
------------- ----------
arz2wrgd05c5z          1

SQL> select addr,spid from v$process where spid='3268';

SQL> select sid,sql_id from v$session where paddr='0000000090CCF600';

SQL> select sql_text from v$sql where sql_id='arz2wrgd05c5z';

SQL>
select sql_id
 from v$session
 where paddr in (select addr from v$process
 where spid in ('5621','31801','31463'));

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

查看 SQL_TEXT

[root@oracle ~]# top
    PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 3428 oracle    20   0 1060988  80356  77608 R  97.7  4.0   0:32.34 oracle

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;

Enter value for pid: 3428
old   7:  WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid'))
new   7:  WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '3428'))

SQL_TEXT
----------------------------------------------------------------
declare num int:=0; begin loop num:=num+1; end loop; end;

生成 kill session 语句

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;'
FROM v$session WHERE sql_id IN ('&sql_id');