Important (resource-consuming) SQL statements in Oracle

When analyzing SQL Performance, you often need to determine the SQL statements with high resource consumption, which are summarized as follows:

1. View suspicious SQL statements
Select substr (to_char (s. pct, '99. 00'), 2) | '%' load,

S.exe cutions 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,

100 * ratio_to_report (disk_reads) over () pct

From sys. v _ $ SQL

Where command_type! = 47)

Where disk_reads> 50 * executions) s,

Sys. v _ $ sqltext p

Where s. ranking <= 5

And p. address = s. address

Order by 1, s. address, p. piece;

2. view the SQL statements that consume more memory.

Select B. username, a. buffer_gets, a.exe cutions,

A. disk_reads/decode(a.executions,, a.exe cutions), 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. View SQL statements with multiple logical reads
Select *

From (select buffer_gets, SQL _text

From v $ sqlarea

Where buffer_gets> 500000

Order by buffer_gets desc)

Where rownum <= 30;

4. view the SQL statements with multiple executions

Select SQL _text, executions

From (select SQL _text, executions from v $ sqlarea order by executions desc)

Where rownum <81;

5. view the SQL statements that read 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;

6. View SQL statements with multiple sorting orders

Select SQL _text, sorts

From (select SQL _text, sorts from v $ sqlarea order by sorts desc)

Where rownum <21;

7. The number of analyses is too large and the number of executions is too small. You must bind the variable to write the SQL statement.

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;

8 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;

9. 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;