–查找低效sql

SELECT EXECUTIONS,
       DISK_READS,
       BUFFER_GETS,
       ROUND((BUFFER_GETS – DISK_READS) / BUFFER_GETS, 2) Hit_radio,
       ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run,
       SQL_TEXT
  FROM V$SQLAREA
 WHERE EXECUTIONS > 0
   AND BUFFER_GETS > 0
   AND (BUFFER_GETS – DISK_READS) / BUFFER_GETS < 0.8
 ORDER BY 4 DESC;

–查找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：

select sql_text, executions
  from (select sql_text, executions from v$sqlarea order by executions desc)

 where rownum < 81;

–读硬盘多的SQL：

select sql_text, disk_reads
  from (select sql_text, disk_reads from v$sqlarea order by disk_reads desc)

 where rownum < 21;

–排序多的SQL：

select sql_text, sorts
  from (select sql_text, sorts from v$sqlarea order by sorts desc)

 where rownum < 21;

–分析的次数太多，执行的次数太少，要用绑变量的方法来写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;

–游标的观察：

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;

–查看当前用户&username执行的SQL：

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;