오라클 오래걸리는 쿼리 확인

오라클 오래걸리는 쿼리 확인

SELECT ROWNUM NO,
       PARSING_SCHEMA_NAME,
       to_char(ELAPSED_TIME/(1000000 * decode(executions,null,1,0,1,executions)),999999.9999 ) AVG_ELAPSED_TIME ,
       executions,
       SQL_TEXT 쿼리 ,
       SQL_FULLTEXT
  FROM V$SQL
 WHERE  LAST_ACTIVE_TIME > SYSDATE-(1/24*2)
   -- AND LAST_ACTIVE_TIME  BETWEEN  to_Date('20111226163000','YYYYMMDDHH24MISS') AND to_Date('20111226170000','YYYYMMDDHH24MISS')
   -- AND ELAPSED_TIME >= 1 * 1000000 * decode(executions,null,1,0,1,executions)
   and PARSING_SCHEMA_NAME = 'ZIPCODE'
 ORDER BY AVG_ELAPSED_TIME DESC, executions DESC;

 

오라클 오래걸리는 쿼리 확인

SELECT TO_CHAR (SID) sid, serial# serialNumber,
       SUBSTR (TO_CHAR (last_call_et), 1, 6) executeSeconds, userName, machine,
       b.sql_text sqlText
  FROM v$session a, v$sqltext b
 WHERE username NOT IN ('SYSTEM', 'SYS')
   AND a.TYPE != 'BACKGROUND'
   AND a.status = 'ACTIVE'
   AND a.sql_address = b.address(+)
   AND a.sql_hash_value = b.hash_value(+)
  ORDER BY a.last_call_et DESC,  a.SID, a.serial#, b.address, b.hash_value, b.piece

현재 실행되고 있는 쿼리 와 실행 시간

SELECT TO_CHAR (SID) sid, serial# serialNumber,
   SUBSTR (TO_CHAR (last_call_et), 1, 6) executeSeconds, userName, machine,
   b.sql_text sqlText
  FROM v$session a, v$sqltext b
  WHERE username NOT IN ('SYSTEM', 'SYS')
   AND a.TYPE != 'BACKGROUND'
   AND a.status = 'ACTIVE'
   AND a.sql_address = b.address(+)
   AND a.sql_hash_value = b.hash_value(+)
  ORDER BY a.last_call_et DESC,
   a.SID,
   a.serial#,
   b.address,
   b.hash_value,
   b.piece