For optimization, finding SQL that consumes more resources is critical, and here are a few of the previously used SQL.
1. Query the most resource-intensive query 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.
2. 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;
3. The SQL TOP5 that consumes 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 queries that require a lot of buffered 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;
How to find SQL that consumes large resources