如何找出引起ORA-1652的SQL?

space6212發表於2019-06-11


在alert檔案中,我們可能會看到這樣的報錯資訊:
 Wed Aug 20 17:16:37 2008
 ORA-1652: unable to extend temp segment by 128 in tablespace                 DBA_TEMP
 要解決這個問題,我們首先要導致這個問題的SQL,可能方法有幾種:
 1、設定events
  alter system set events '1652 trace name errorstack  level 1';
  這種方法有一定侷限:
  1)它不能獲取已發生的1652的錯誤資訊,只能對以後出現1652錯誤時生成一個trace檔案;
  2)用events,不清楚會對資料庫有什麼不好的影響。
 2、查詢V$SQL檢視:
  如select * from v$sql order by direct_writes/executions desc;
  這種方法的侷限性是:
  1)因為很難知道V$SQL檢視中的SQL執行時間,難以確認具體是那個SQL導致錯誤的
  2)引起問題的SQL極有可能已經被age out了
 3、 生成錯誤發生時的awr、statspack報表,從報表中的SQL ordered by Reads部分找出SQL
  這種方法更不可靠,因為:
  1) SQL ordered by Reads讀寫的不一定是臨時表空間
  2) awr/statspack報表是根據物理讀的總量排序的,如果導致問題的SQL執行次數少,那也是不會出現在這些報表中的。
  
 4、查詢awr相關檢視
  對於10G來說,這種方法是最可行、最準確的。
  SELECT DISTINCT TO_CHAR(SUBSTR(b.sql_text,1,4000))
     FROM sys.WRH$_SQLTEXT b
    WHERE b.sql_id IN
    (SELECT sql_id
       FROM
      (SELECT a.sql_id
         FROM sys.WRH$_SQLSTAT a
        WHERE a.parsing_schema_name NOT IN ('SYS')
      AND a.executions_total             >0
      AND a.direct_writes_total          >0
      AND a.SNAP_ID                     IN
        (SELECT SNAP_ID
           FROM sys.WRM$_SNAPSHOT
          WHERE to_date('2008:08:20 17:20:08','yyyy:mm:dd hh24:mi:ss') BETWEEN begin_interval_time AND end_interval_time
        )
     ORDER BY a.direct_writes_total/ a.executions_total DESC
      )
      WHERE rownum<=10
    );
   
   
    基本上,結果中的第一句只要不是insert /*+ append */之類的語句,那麼它就極有可能是導致ORA-1652的SQL。
    如果是9i,用statspack,也可以用類似的SQL從statspack檢視查到需要的結果。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/231499/viewspace-431727/,如需轉載,請註明出處,否則將追究法律責任。

相關文章