Query inefficient SQL statements

Finding and running bad SQL in the system is an old topic. We need to analyze it based on our actual situation. You must not use the methods described below as a dogma. When these SQL statements are used, grouping operations are performed on the system table, which also increases the system load. After the system is started for a period of time, we recommend that you check the time when the load is low in the middle of the night (for example, one month. Be sure to analyze specific issues.
Below are some of my favorite methods to find bad SQL:

Select * from (select buffer_gets, SQL _text
From v $ sqlarea
Where buffer_gets> 500000
Order by buffer_gets DESC) Where rownum <= 30;

-- SQL statements with many executions

Select SQL _text, executions from
(Select SQL _text, executions from V $ sqlarea order by executions DESC)
Where rownum <81;

-- Read SQL statements with multiple hard disks

Select SQL _text, disk_reads from
(Select SQL _text, disk_reads from V $ sqlarea order by disk_reads DESC)
Where rownum <21;

-- Sorting Multiple SQL statements

Select SQL _text, sorts from
(Select SQL _text, sorts from V $ sqlarea order by sorts DESC)
Where rownum <21;

-- The number of analyses is too large, and the number of executions is too small. You need to bind variables to write SQL statements.

Set pagesize 600;
Set linesize 120;
Select substr (SQL _text, 1, 80) "SQL", count (*), sum (executions) "totexecs"
From v $ sqlarea
Where executions <5
Group by substr (SQL _text, 1, 80)
Having count (*)> 30
Order by 2;

-- Cursor observation

Set pages 300;
Select sum (A. value), B. Name
From v $ sesstat A, V $ statname B
Where a. Statistic # = B. Statistic #
And B. Name = 'opened cursors current'
Group by B. Name;
Select count (0) from V $ open_cursor;
Select user_name, SQL _text, count (0) from V $ open_cursor
Group by user_name, SQL _text having count (0)> 30;

-- View the SQL statement executed by the current user & Username

Select SQL _text from V $ sqltext_with_newlines where (hash_value, address) in
(Select SQL _hash_value, SQL _address from V $ session where username = '& username ')
Order by address, piece;