Important (resource-consuming) SQL statements in Oracle

1. view the SQL statements that consume the most memory (V $ sqlarea)

 1   Select  B. username,  2 A. buffer_gets, --  The number of times the sub-cursor runs this statement.  3 A.exe cutions, --  Number of times that this statement is executed by all sub-game targets  4 A. buffer_gets / Decode(a.exe cutions, 0 ,1 , A.exe cutions ), --  The number of times this statement reads memory once  5   A. SQL _text SQL  6   From  V $ sqlarea A, dba_users B  7   Where A. parsing_user_id = B. User_id   And A. buffer_gets > 10000  8   Order   By Buffer_gets Desc ;

2. view the SQL statements that consume more disks (V $ sqlarea)

 1   Select  B. username,  2 A. disk_reads, --  Number of disk reads caused by running this statement on all sub-cursors  3 A.exe cutions, -- Number of times that this statement is executed by all sub-game targets  4 A. disk_reads / Decode(a.exe cutions, 0 , 1 , A.exe cutions ), --  -- This statement reads the disk once  5   A. SQL _text SQL  6   From  V $ sqlarea A, dba_users B  7   Where A. parsing_user_id= B. User_id   And A. disk_reads >  10000  8   Order   By Disk_reads Desc ;

3. view the SQL statements that are frequently executed (V $ sqlarea)

1 SelectSQL _text, executions2 FromV $ sqlarea3 WhereRownum<814 Order ByExecutionsDesc
 

4. View SQL statements with multiple sorting types (V $ sqlarea)

1 SelectSQL _text, sorts2 FromV $ sqlarea3 Order BySortsDesc4 WhereRownum<21;
 

5. The number of analyses is too large and the number of executions is too small. You need to bind a variable to write SQL statements (V $ sqlarea)

 1   Select Substr (SQL _text, 1 ,80 ) "SQL ", Count ( * ), Sum  (Executions) "totexecs"  2   From  V $ sqlarea  3   Where Executions <  5    --  SQL _text execution times less than 5  4  Group   By Substr (SQL _text, 1 , 80  )  5   Having   Count ( * ) >  30    --  SQL _text analysis times greater than 30  6   Order  By   2 ;

6. The top five SQL statements (V $ sqlarea)

 
1 SelectSQL _text, SQL _id, elapsed_time, cpu_time, user_io_wait_time2 FromSYS. V $ sqlarea3 WhereRownum< 6 4 Order By 5 Desc
 

7. view the SQL statement executed by the current user & username (V $ sqltext_with_newlines, V $ session)

 1   Select  SQL _text  2   From  V $ sqltext_with_newlines  3   Where (Hash_value, address) In  4 ( Select  SQL _hash_value, SQL _address  5         From V $ session  6         Where Username =  '  & Username  '  )  7   Order   By Address, piece;