1. 查看性能最差的前100sql
SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE ROWNUM<100
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE ROWNUM<100
2.oracle 10g 查看某session的历史执行sql情况(sql采样间隔1s)
oracle 10g 通过v$active_session_history查看某session(这里指定为190)的历史执行sql情况(sql采样间隔1s)
select s.SAMPLE_TIME,
sq.SQL_TEXT,
sq.DISK_READS,
sq.BUFFER_GETS,
sq.CPU_TIME,
sq.ROWS_PROCESSED,
–sq.SQL_FULLTEXT,
sq.SQL_ID
from v$sql sq, v$active_session_history s
where s.SQL_ID = sq.SQL_ID
and s.SESSION_ID = 190
order by s.SAMPLE_TIME desc;
select s.SAMPLE_TIME,
sq.SQL_TEXT,
sq.DISK_READS,
sq.BUFFER_GETS,
sq.CPU_TIME,
sq.ROWS_PROCESSED,
–sq.SQL_FULLTEXT,
sq.SQL_ID
from v$sql sq, v$active_session_history s
where s.SQL_ID = sq.SQL_ID
and s.SESSION_ID = 190
order by s.SAMPLE_TIME desc;