查看邏輯讀前10的SQL:
復制代碼 代碼如下:
set linesize 300;
set pagesize 300;
set long 50000;
SELECT *
FROM (
SELECT sql_fulltext AS sql, SQL_ID,buffer_gets / executions AS "Gets/Exec",buffer_gets, executions
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY "Gets/Exec" DESC
)
WHERE rownum <= 10;
查看物理讀前10的SQL:
復制代碼 代碼如下:
set linesize 300;
set pagesize 300;
set long 50000;
SELECT *
FROM (
SELECT sql_fulltext AS sql, SQL_ID,disk_reads / executions AS "Reads/Exec",disk_reads, executions
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY "Reads/Exec" DESC
)
WHERE rownum <= 10;
select t.SID,n.NAME,t.VALUE
from v$sesstat t, v$statname n
where t.STATISTIC# = n.STATISTIC#
and n.STATISTIC# in (9, 54)
and t.SID=&sid
查找前十條性能差的sql.
SELECT * FROM
(
SELECT PARSING_USER_ID
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC
)
WHERE ROWNUM<10 ;