Querying the most resource-intensive queries from V$sqlarea
Select B.username Username,a.disk_reads reads,
A.executions Exec,a.disk_reads/decode (a.executions,0,1,a.executions) 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; Replacing the disk_reads column with the buffer_gets column gives you information about the SQL statement that consumes the most memory. V$sql is a SQL statement that has been resolved in a memory-shared SQL zone. List the 5 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; consumes disk reads the most 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; find queries that require a large buffer read (logical read) operation: 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 queries for the most resource-intensive queries
