ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
SELECT TRUNC(A.ELAPSED_TIME /
DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)) ELAPSED_TIME,
TRUNC(A.BUFFER_GETS /
DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)) BUFFER_GETS,
A.EXECUTIONS,
A.LAST_ACTIVE_TIME,
TRUNC(A.DISK_READS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)) DISK_READS,
A.HASH_VALUE,
A.SORTS,
A.SQL_FULLTEXT SQL_TEXT
FROM V$SQL A
WHERE PARSING_SCHEMA_NAME <> 'SYS'
ORDER BY 1 DESC;
select sql_id,child_number,sql_text,elapsed_time from (select sql_id,child_number,sql_text,elapsed_time,cpu_time,disk_reads,rank () over (order by elapsed_time desc) as elapsed_rank from v$sql) where elapsed_rank<10;