Oracle displays the SQL statements in which logical and physical read resources are ranked

Oracle displays the SQL statements in which logical and physical read resources are ranked

View the SQL statements before the first 10 logical reads:

Copy codeThe Code is as follows:
Set linesize 300;
Set pagesize 300;
Set long 50000;
SELECT *
FROM (
SELECT SQL _fulltext AS SQL, SQL _ID, buffer_gets/executions AS "Gets/Exec", buffer_gets, executions
From v $ SQLAREA
WHERE buffer_gets> 10000
Order by "Gets/Exec" DESC
)
WHERE rownum <= 10;

View the top 10 SQL statements for physical reads:

Copy codeThe Code is as follows:
Set linesize 300;
Set pagesize 300;
Set long 50000;
SELECT *
FROM (
SELECT SQL _fulltext AS SQL, SQL _ID, disk_reads/executions AS "Reads/Exec", disk_reads, executions
From v $ SQLAREA
WHERE disk_reads> 1000
Order by "Reads/Exec" DESC
)
WHERE rownum <= 10;

Oracle often needs to count the logical read and physical read information of each session. It is inconvenient to directly use SQL. It is necessary to design a program to simplify viewing a session.

Select t. SID, n. NAME, t. VALUE
From v $ sesstat t, v $ statname n
Where t. STATISTIC # = n. STATISTIC #
And n. STATISTIC # in (9, 54)
And t. SID = & sid
 
How to find SQL statements that occupy a large amount of resources in oracle

Find the top 10 SQL statements with poor performance.
SELECT * FROM
(
SELECT PARSING_USER_ID
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
SQL _text
FROM v $ sqlarea
Order by disk_reads DESC
)
Where rownum <10;