熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

查找badsql的方法

2013-11-13 15:44:07  來源: Oracle 

  查找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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.