正在执行的
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
