View sql statements with poor performance

Query the 10 worst-performing 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 10000000 OR disk_reads > 1000000

ORDER BY buffer_gets + 100 * disk_reads DESC

DISK_READS: represents the number of physical reads.

Analyze sql with poor performance

SELECT EXECUTIONS, DISK_READS, BUFFER_GETS

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 查询共享池中已经解析过的SQL语句及其相关信息 --EXECUTIONS 所有子游标的执行这条语句次数 --DISK_READS 所有子游标运行这条语句导致的读磁盘次数 --BUFFER_GETS 所有子游标运行这条语句导致的读内存次数 --Hit_radio 命中率 --Reads_per_run 每次执行读写磁盘数 笼统的说EXECUTIONS，BUFFER_GETS，Hit_radio越高表示读内存多，磁盘少是比较理想的状态，因此越高越好 另外两个越高读磁盘次数越多，因此低点好 获取执行次数最多的10个SQL select sql_text,executions from ( select sql_text,executions,rank() over(order by executions desc) exec_rank from v$sql ) where exec_rank 1 ) ) where exec_rank 100000 or disk_reads >

100000

Order by buffer_gets+100*disk_reads desc

The first 5 spend the most CPU and time:

Select sql_text,executions

Round (elapsed_time/1000000,2) elapsed_seconds

Round (cpu_time/1000000,2) cpu_secs from

(select * from v$sql order by elapsed_time desc)

Where rownum100 or m.CPU > 100 or m.LOGICAL_READS > 100) and m.session_id=s.SID and m.SESSION_SERIAL_NUM=s.SERIAL# order by m.PHYSICAL_READS DESC,m.CPU desc,m.LOGICAL_READS desc

The 5 most frequently used queries are:

Select sql_text,executions from (select sql_text,executions, rank () over (order by executions desc) exec_rank from v$sql) where exec_rank