Oracle - 执行过的SQL、正在执行的SQL、消耗资源最多的SQL

一、执行过的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 ;