查找badsql的方法

　　查找bad sql的方法

　　查找運行系統裡bad sql是一個古老的話題 我們要根據自己的實際情況來分析

　　絕不能教條的運用下面介紹的這些方法

　　使用這些SQL語句時會對系統表產生分組操作當然也增大了系統的負載

　　建議大家在系統啟動了一段時間後在半夜負載較輕的時間定時(例如:一個月)來查一查一定要具體問題具體分析

　　下面是我收藏的一些查找bad sql的方法:

　　column sql_text format a;
值得懷疑的SQL 來自
select
  substr(to_char(spct ) ) || %  load
  sexecutions  executes
  psql_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
          sysv_$sql
        where
          command_type !=
      )
    where
      disk_reads > * executions
  )  s
  sysv_$sqltext  p
where
  sranking <= and
  paddress = saddress
order by
  saddress ppiece
/

　　 邏輯讀多的SQL
select * from (select buffer_gets sql_text
from v$sqlarea
where buffer_gets >
order by buffer_gets desc) where rownum<=;

　　 執行次數多的SQL
  select sql_textexecutions from
  (select sql_textexecutions from v$sqlarea order by executions desc)
   where rownum<;

　　 讀硬盤多的SQL
  select sql_textdisk_reads from
  (select sql_textdisk_reads from v$sqlarea order by disk_reads desc)
   where rownum<;

　　 排序多的SQL
  select sql_textsorts from
   (select sql_textsorts from v$sqlarea order by sorts desc)
    where rownum<;

　　分析的次數太多執行的次數太少要用綁變量的方法來寫sql
set pagesize ;
set linesize ;
select substr(sql_text) sql count(*) sum(executions) totexecs
   from v$sqlarea
   where executions <
   group by substr(sql_text)
   having count(*) >
   order by ;

　　 游標的觀察
set pages ;
select sum(avalue) bname
     from v$sesstat a v$statname b
     where astatistic# = bstatistic#
       and bname = opened cursors current
     group by bname;

　　select count() from v$open_cursor;

　　select user_namesql_textcount() from v$open_cursor
group by user_namesql_text having count()>;

　　查看當前用戶&username執行的SQL
select sql_text from v$sqltext_with_newlines where (hash_valueaddress) in
(select sql_hash_valuesql_address from v$session where username=&username)
order by addresspiece;

From:http://tw.wingwit.com/Article/program/Oracle/201311/17220.html