SELECT * FROM (SELECT Sql_Text, Sql_Id, Cpu_Time FROM V$sql ORDER BY Cpu_Time DESC) WHERE Rownum <= 10 ORDER BY Rownum ASC; SELECT * FROM (SELECT Sql_Text, Sql_Id, Cpu_Time FROM V$sqlarea ORDER BY Cpu_Time DESC) WHERE Rownum <= 10 ORDER Byrownum ASC;
这2个语句效果基本一样，一个从v$sql视图查询一个从v$sqlarea视图查询。
列出使用频率最高的5个查询：SELECT Sql_Text, Executions FROM (SELECT Sql_Text, Executions, Rank() Over(ORDER BY Executions DESC) Exec_Rank FROM V$sql) WHERE Exec_Rank <= 5;
消耗磁盘读取最多的sql top5： SELECT Disk_Reads, Sql_Text FROM (SELECT Sql_Text, Disk_Reads, Dense_Rank() Over(ORDER BY Disk_Reads DESC) Disk_Reads_Rank FROM V$sql) WHERE Disk_Reads_Rank <= 5;
找出需要大量缓冲读取（逻辑读）操作的查询：SELECT Buffer_Gets, Sql_Text FROM (SELECT Sql_Text, Buffer_Gets, Dense_Rank() Over(ORDER BY Buffer_Gets DESC) Buffer_Gets_Rank FROM V$sql) WHERE Buffer_Gets_Rank <= 5;
原文：http://www.cnblogs.com/chenv/p/5390735.html