oracle sql优化

1. 找出最消耗资源的实时SQL

SELECT *
  FROM (  SELECT A.SID,
                 A.SQL_ID,
                 A.STATUS,
                 A.CPU_TIME / 1000000 CPU_SEC,
                 A.BUFFER_GETS,
                 A.DISK_READS,
                 B.SQL_TEXT,
                 B.SQL_FULLTEXT
            FROM V$SQL_MONITOR A, V$SQL B
           WHERE A.SQL_ID = B.SQL_ID
        ORDER BY A.CPU_TIME DESC)
 WHERE ROWNUM < 20;

2.如果需要批量杀掉数据库中执行缓慢的sql会话，执行如下SQL

• 第一种

SELECT    'alter system kill session '|| q'[']'|| S.SID || ',' || S.SERIAL# || q'[' immediate;]' FROM V$SESSION S WHERE S.SQL_ID = '' AND S.TYPE = 'USER' AND S.PROGRAM= 'w3wp.exe';

• 第二种

SELECT    'ALTER SYSTEM KILL SESSION ' || '''' || SID || ',' || SERIAL# || ''' IMMEDIATE;' FROM V$SESSION S WHERE s.sql_id = '' AND s.TYPE = 'USER' AND S.PROGRAM= 'w3wp.exe';