Oracle SQL statements

Monitoring scripts executed by Oracle SQL statements! [@[email protected]]

--Find the method for bad sql:
SELECT * FROM (select Buffer_gets, Sql_text
From V$sqlarea
where Buffer_gets >500000
ORDER BY buffer_gets Desc) where rownum<=30;

--Perform the number of times SQL

Select Sql_text,executions from
(select Sql_text,executions from V$sqlarea ORDER BY executions Desc)
where rownum<81;

--Read more about the SQL of magnetic plates

Select Sql_text,disk_reads from
(select Sql_text,disk_reads from V$sqlarea ORDER BY disk_reads Desc)
where rownum<21;

--Sort multiple SQL

Select Sql_text,sorts from
(select Sql_text,sorts from V$sqlarea order by sorts Desc)
where rownum<21;

--Analysis of the number of times too much, too little to perform, to use kidnapped fixed kidnapped to write SQL

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;

--the observation of the target

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 have count (0) >30;

--View the SQL that was run by the previous user
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;

Oracle SQL statements