4、查找近30分钟内等待多的用户

--filename:top_wait_by_user.sql
--What user is waiting the most?

SET LINESIZE 180
COL event FORMAT a60
COL total_wait_time FORMAT 999999999999999999

  SELECT ss.sid，
         NVL (ss.username， 'oracle') AS username，
         SUM (ash.wait_time + ash.time_waited) total_wait_time
    FROM v$active_session_history ash， v$session ss
   WHERE ash.sample_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE AND ash.session_id = ss.sid
GROUP BY ss.sid， ss.username
ORDER BY 3 DESC;

　　5、查找30分钟消耗多资源的SQL语句

--filename:top_sql_by_wait.sql
-- What SQL is currently using the most resources?
SET LINESIZE 180
COL sql_text FORMAT a90 WRAP
COL username FORMAT a20 WRAP
SET PAGESIZE 200

SELECT *
  FROM (  SELECT sqlarea.sql_text，
                 dba_users.username，
                 sqlarea.sql_id，
                 SUM (active_session_history.wait_time + active_session_history.time_waited)
                    total_wait_time
            FROM v$active_session_history active_session_history， v$sqlarea sqlarea， dba_users
           WHERE     active_session_history.sample_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE
                 AND active_session_history.sql_id = sqlarea.sql_id
                 AND active_session_history.user_id = dba_users.user_id
        GROUP BY active_session_history.user_id，
                 sqlarea.sql_text，
                 sqlarea.sql_id，
                 dba_users.username
        ORDER BY 4 DESC) x
 WHERE ROWNUM <= 11;

　　6、等待多的对象

--filename:top_object_by_wait.sql
--What object is currently causing the highest resource waits?
SET LINESIZE 180
COLUMN OBJECT_NAME FORMAT a30
COLUMN EVENT FORMAT a30

  SELECT dba_objects.object_name，
         dba_objects.object_type，
         active_session_history.event，
         SUM (active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time
    FROM v$active_session_history active_session_history， dba_objects
   WHERE active_session_history.sample_time BETWEEN SYSDATE - 60 / 2880 AND SYSDATE
         AND active_session_history.current_obj# = dba_objects.object_id
GROUP BY dba_objects.object_name， dba_objects.object_type， active_session_history.event
ORDER BY 4 DESC;