Query the most resource-consuming queries using V $ sqlarea and V $ SQL

Reference: http://blog.chinaunix.net/u/3866/showart_396335.html

-----------------------
V $ sqlarea, V $ SQL
----------------------- Select B. Username username, A. disk_reads reads,
A.exe cutions exec, A. disk_reads/decode(a.executions,, a.exe cutions) rds_exec_ratio,
A. SQL _text statement
From v $ sqlarea A, dba_users B
Where a. parsing_user_id = B. user_id
And a. disk_reads> 100000
Order by A. disk_reads DESC; replace the disk_reads column with the buffer_gets column to obtain information about the SQL statements that occupy the most memory. V $ SQL is the parsed SQL statement in the memory shared SQL area. List the five most frequently used queries: Select SQL _text, executions
From (select SQL _text, executions,
Rank () over
(Order by executions DESC) exec_rank
From v $ SQL)
Where exec_rank <= 5; top 5 SQL statements that consume the most disk reads:
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; find the query that requires a large number of buffer read (logical read) Operations: 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;