一、执行过的SQL
SELECT
SQL_ID, HASH_VALUE, ADDRESS, SQL_FULLTEXT,LAST_LOAD_TIME
FROM
V$SQL
ORDER BY
LAST_LOAD_TIME DESC;
-------------------------------------------------------------------------------------------------
SELECT
SQL_ID, HASH_VALUE, ADDRESS, B.SQL_FULLTEXT, B.FIRST_LOAD_TIME
FROM
V$SQLAREA B
ORDER BY
B.FIRST_LOAD_TIME DESC;
二、正在执行的SQL
SELECT
SSN.USERNAME, SSN.SID, SSN.SQL_ID,SAA.ADDRESS, SAA.HASH_VALUE, SAA.SQL_FULLTEXT
FROM
V$SESSION SSN, V$SQLAREA SAA
WHERE
SSN.SQL_ADDRESS = SAA.ADDRESS AND SSN.SQL_HASH_VALUE = SAA.HASH_VALUE;
三、读取磁盘次数最多的SQL
SELECT * FROM (
SELECT
SQL_ID,ADDRESS,HASH_VALUE,COMMAND_TYPE, PARSING_USER_ID, PARSING_SCHEMA_NAME, EXECUTIONS, SORTS, DISK_READS, BUFFER_GETS, CPU_TIME, SQL_FULLTEXT
FROM
V$SQLAREA
ORDER BY
DISK_READS DESC
)WHERE ROWNUM<10 ;
四、消耗CPU时间最多的SQL
SELECT * FROM (
SELECT
SQL_ID,ADDRESS,HASH_VALUE,COMMAND_TYPE, PARSING_USER_ID, PARSING_SCHEMA_NAME, EXECUTIONS, SORTS, DISK_READS, BUFFER_GETS, CPU_TIME, SQL_FULLTEXT
FROM
V$SQLAREA
ORDER BY
CPU_TIME DESC
)WHERE ROWNUM<10 ;
五、执行次数最多的SQL
SELECT * FROM (
SELECT
SQL_ID,ADDRESS,HASH_VALUE,COMMAND_TYPE, PARSING_USER_ID, PARSING_SCHEMA_NAME, EXECUTIONS, SORTS, DISK_READS, BUFFER_GETS, CPU_TIME, SQL_FULLTEXT
FROM
V$SQLAREA
ORDER BY
EXECUTIONS DESC
)WHERE ROWNUM<10 ;