Hi Experts,
I am using below SQL to get the last running commands and its stats and users information.
For this I make LAST_LOAD_TIME as a rising column, and execute this query in every 10 min.
V$SQLAREA.LAST_LOAD_TIME,
V$SQLAREA.LAST_ACTIVE_TIME
But in V$SQLAREA is populating data very frequently, so using timestamp as a rising column is not a good idea, because in this scenario I lost boundary conditions event.
Please suggest how I fetch all event in my Index.
SELECT SESION.SID,
SESION.USERNAME,
SESION.PROCESS,
SQLAREA.SHARABLE_MEM,
SQLAREA.PERSISTENT_MEM,
SQLAREA.RUNTIME_MEM,
SQLAREA.FETCHES,
SQLAREA.EXECUTIONS,
CASE
WHEN executions > 0 THEN
ROUND(elapsed_time/executions,3)
ELSE NULL
END elap_per_exec,
SQLAREA.END_OF_FETCH_COUNT,
SQLAREA.LOADS,
SQLAREA.FIRST_LOAD_TIME,
SQLAREA.DISK_READS,
SQLAREA.DIRECT_WRITES,
SQLAREA.BUFFER_GETS,
SQLAREA.PLSQL_EXEC_TIME,
SQLAREA.JAVA_EXEC_TIME,
SQLAREA.ROWS_PROCESSED,
SQLAREA.COMMAND_TYPE,
SQLAREA.OPTIMIZER_MODE,
SQLAREA.OPTIMIZER_COST,
SQLAREA.PARSING_SCHEMA_ID,
SQLAREA.PARSING_SCHEMA_NAME,
SQLAREA.MODULE,
SQLAREA.SERIALIZABLE_ABORTS,
SQLAREA.CPU_TIME,
SQLAREA.ELAPSED_TIME,
SQLAREA.REMOTE,
SQLAREA.OBJECT_STATUS,
SQLAREA.LAST_LOAD_TIME,
SQLAREA.LAST_ACTIVE_TIME
FROM V$SQLAREA SQLAREA, V$SESSION SESION
WHERE SQLAREA.HASH_VALUE = SESION.SQL_HASH_VALUE (+)
AND SQLAREA.ADDRESS = SESION.SQL_ADDRESS (+)
AND SQLAREA.LAST_LOAD_TIME > ?
ORDER BY SQLAREA.LAST_LOAD_TIME ASC
... View more