How ORACLE locates SQL that consumes resources

When analyzing SQL performance, it is often necessary to determine how much SQL is consumed by resources, as summarized below:

1 View questionable SQL
Select substr (To_char (s.pct, ' 99.00 '),2) || '% ' load,
S.executions executes,
P.sql_text
From (select Address,
Disk_reads,
Executions,
pct
Rank () Over (order by disk_reads DESC) ranking
From (select Address,
Disk_reads,
Executions,
-* Ratio_to_report (Disk_reads) over () pct
From Sys.v_$sql
where Command_type! = -)
where Disk_reads > -* executions) s,
Sys.v_$sqltext P
where S.ranking <=5
and p.address = s.address
ORDER BY1, S.address, p.piece;

2 View more memory-intensive SQL

Select B.username, A. Buffer_gets, A.executions,
A.disk_reads/decode (a.executions,0,1,a.executions), A.sql_text sql

From V$sqlarea A,dba_users b
where a.parsing_user_id = b.user_id
and A.disk_reads >10000
ORDER BY disk_reads Desc;

3 viewing logical read-more SQL
SELECT *
From (select Buffer_gets, Sql_text
From V$sqlarea
where Buffer_gets > 500000
ORDER BY buffer_gets Desc)
where rownum <= ;

4 View more executions of SQL
Select Sql_text, executions
From (select Sql_text, executions from V$sqlarea order by executions Desc)
where RowNum < Bayi;

5 View read hard disk for more SQL
Select Sql_text, Disk_reads
From (select Sql_text, disk_reads from V$sqlarea ORDER BY disk_reads Desc)
where RowNum < ;

6 view more SQL for sorting
Select Sql_text, Sorts
From (select Sql_text, sorts from V$sqlarea order by sorts Desc)
where RowNum < ;

7 analysis too many times, too few executions, to use the method of binding variables to write SQL
Set pagesize -;
Set Linesize -;
Select substr (Sql_text,1, the) "SQL", count (*), SUM (executions) "Totexecs"
From V$sqlarea
where executions <5
Group BY substr (Sql_text,1, the)
Having count (*) > -
ORDER BY2;

Observation of 8 cursors
Set pages -;
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) > -;

9 view SQL performed by 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;

How ORACLE locates SQL that consumes resources