常用监控SQL

1）查询正在执行的SQL

SELECT SID,SERIAL#, EVENT, PROGRAM, CLIENT_IP, (SYSDATE - SQL_EXEC_START)*86400, WAIT_SID, CURRENT_SQL,SQL_ID, module FROM V$SESSION WHERE STATUS = 'ACTIVE';

2）查询longsql

SELECT * FROM V$LONGSQL ORDER BY ELAPSED_TIME DESC LIMIT 20;

SELECT SQL_TEXT,
       buffer_gets,
       executions,
       buffer_gets / executions AVG
  FROM v$sqlarea
 WHERE executions > 0
   AND buffer_gets > 100000
 ORDER BY 4 desc

limit 20;

3）查询长时间的锁/事务

SELECT * FROM V$TRANSACTION ORDER BY BEGIN_TIME DESC LIMIT 20;