ORACLE查看过去执行sql的效率
SELECT S.SQL_TEXT,
S.SQL_FULLTEXT,
S.SQL_ID,
ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) "执行时间'S'",
S.EXECUTIONS "执行次数",
S.OPTIMIZER_COST "COST",
S.SORTS,
S.MODULE, --连接模式(JDBC THIN CLIENT:程序)
-- S.LOCKED_TOTAL,
--S.PHYSICAL_READ_BYTES "物理读",
-- S.PHYSICAL_READ_REQUESTS "物理读请求",
--S.PHYSICAL_WRITE_REQUESTS "物理写",
-- S.PHYSICAL_WRITE_BYTES "物理写请求",
S.ROWS_PROCESSED "返回行数",
S.DISK_READS "磁盘读",
S.DIRECT_WRITES "直接路径写",
S.PARSING_SCHEMA_NAME,
S.LAST_ACTIVE_TIME
FROM GV$SQLAREA S
WHERE ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) > 5 --100 0000微秒=1S
AND S.PARSING_SCHEMA_NAME = USER
AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD') =
TO_CHAR(SYSDATE, 'YYYY-MM-DD')
AND S.COMMAND_TYPE IN (2, 3, 5, 6, 189)
ORDER BY "执行时间'S'" DESC;
S.SQL_FULLTEXT,
S.SQL_ID,
ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) "执行时间'S'",
S.EXECUTIONS "执行次数",
S.OPTIMIZER_COST "COST",
S.SORTS,
S.MODULE, --连接模式(JDBC THIN CLIENT:程序)
-- S.LOCKED_TOTAL,
--S.PHYSICAL_READ_BYTES "物理读",
-- S.PHYSICAL_READ_REQUESTS "物理读请求",
--S.PHYSICAL_WRITE_REQUESTS "物理写",
-- S.PHYSICAL_WRITE_BYTES "物理写请求",
S.ROWS_PROCESSED "返回行数",
S.DISK_READS "磁盘读",
S.DIRECT_WRITES "直接路径写",
S.PARSING_SCHEMA_NAME,
S.LAST_ACTIVE_TIME
FROM GV$SQLAREA S
WHERE ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) > 5 --100 0000微秒=1S
AND S.PARSING_SCHEMA_NAME = USER
AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD') =
TO_CHAR(SYSDATE, 'YYYY-MM-DD')
AND S.COMMAND_TYPE IN (2, 3, 5, 6, 189)
ORDER BY "执行时间'S'" DESC;