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 ;