查詢Oracle正在執行和執行過的SQL語句

　　正在執行的

　　select ausername asidbSQL_TEXT bSQL_FULLTEXT

　　from v$session a v$sqlarea b

　　where asql_address = baddress

　　執行過的

　　select bSQL_TEXTbFIRST_LOAD_TIMEbSQL_FULLTEXT

　　from v$sqlarea b

　　where bFIRST_LOAD_TIME between /:: and

　　/:: order by bFIRST_LOAD_TIME

　　(此方法好處可以查看某一時間段執行過的sql並且 SQL_FULLTEXT 包含了完整的 sql 語句)

　　其他

　　select OSUSERPROGRAMUSERNAMESCHEMANAMEBCpu_TimeSTATUSBSQL_TEXT

　　from V$SESSION A LEFT JOIN V$SQL B ON ASQL_ADDRESS=BADDRESS AND ASQL_HASH_VALUE=BHASH_VALUE order by bcpu_time desc

　　select address sql_text piece

　　from v$session v$sqltext

　　where address = sql_address

　　 and machine = < you machine name >

　　order by address piece

　　查找前十條性能差的sql

　　SELECT * FROM (select PARSING_USER_IDEXECUTIONSSORTS

　　COMMAND_TYPEDISK_READSsql_text FROM v$sqlarea

　　order BY disk_reads DESC )where ROWNUM< ;

　　查看占io較大的正在運行的session

　　SELECT sesidseserial#prSPIDseusernamesestatus

　　seterminalseprogramseMODULEsesql_addresssteventst

　　ptextsiphysical_reads

　　siblock_changes FROM v$session sev$session_wait st

　　v$sess_io siv$process pr WHERE stsid=sesid AND st

　　sid=sisid AND sePADDR=prADDR AND sesid> AND st

　　wait_time= AND stevent NOT LIKE %SQL% ORDER BY physical_reads DESC

From:http://tw.wingwit.com/Article/program/Oracle/201311/19070.html