oracle查詢使用頻率和磁碟消耗需要緩衝大小

標籤：

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;

 

oracle查詢使用頻率和磁碟消耗需要緩衝大小