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

Oracle性能優化:收縮臨時表空間

2013-11-13 15:51:41  來源: Oracle 

  當排序操作重建索引等大型操作無法在內存中完成時臨時表空間將為排序提供便利一般情況下臨時表空間為多個用戶多個會話所共享不能為會話分批空間配額臨時表空間耗用過度且在不能自動擴展的情形下將收到ORAunable to extend temp segment 錯誤下面

  描述了過度擴展後如何釋放臨時表空間

  一臨時表空間何時釋放

  檢索數據的會話游標關閉時占用的臨時空間即被釋放

  數據庫關閉重啟(一般情況)會話 log off

  二釋放過大的臨時表空間

  <span > 查看當前臨時表空間的情況SQL> select * from v$version where rownum<

  BANNER

  

  Oracle Database g Release bit Production SQL> @temp_sort_segment

  +==================================================================================+ | Segment Name            The segment name is a concatenation of the             | |                           SEGMENT_FILE (File number of the first extent)         | |                           and the                                                | |                           SEGMENT_BLOCK (Block number of the first extent)       | | Current Users           Number of active users of the segment                  | | Total Temp Segment Size Total size of the temporary segment in MB              | | Currently Used Bytes    Bytes allocated to active sorts                        | | Extent Hits             Number of times an unused extent was found in the pool | | Max Size                Maximum number of MB ever used                         | | Max Used Size           Maximum number of MB used by all sorts                 | | Max Sort Size           Maximum number of MB used by an individual sort        | | Free Requests           Number of requests to deallocate                       | +==================================================================================+——>此時臨時表空間go_temp中達到了GB Tablespace  Segment Current Currently Pct   Extent      Max Max Used Max Sort     Free Name     Name   Users   Used MB Used     Hits  Size MB  Size MB  Size MB Requests

  

  TEMP           SYS                                                   GO_TEMP        SYS                                                **************          —— ——      —— —— —— —— ——sum                                                                   

  SQL> col tbsname format a SQL> select sname tbsnametname(tbytes//) mbtstatus   from v$tablespace sv$tempfile t   where sts# = tts#

  TBSNAME         NAME                                                       MB STATUS

  

  TEMP            /u/database/ORADB/temp/tempORADBdbf                  ONLINE GO_TEMP         /u/database/ORADB/temp/ORADB_tempORADBdbf          ONLINE

  SQL> @temp_usage      ——>此時temp已使用的為MB而GO_TEMP未使用

  TABLESPACE             MB_TOTAL    MB_USED    MB_FREE

  

  GO_TEMP                                 TEMP                                       

  觀察及分析臨時表空間的耗用情況SQL> select count(*) from big_table    ——>開啟另一個session

  COUNT(*)

  

  

  SQL> select * from big_table order by desc  ——>對big_table 實施排序

  SQL> alter index pk_stock_tbl_arc rebuild      ——>開啟另一個session重建索引

  SQL> @temp_sort_segmentsql           ——>可以看到此時temp表空間耗用達到MBgo_temp的耗用達到MB

  Tablespace  Segment Current Currently Pct   Extent      Max Max Used Max Sort     Free Name     Name   Users   Used MB Used     Hits  Size MB  Size MB  Size MB Requests

  

  TEMP           SYS                                                 GO_TEMP        SYS                                              **************          —— ——      —— —— —— —— ——sum                                                                 

  SQL> @temp_sort_userssql  ——>獲得當前排序的會話

  INST_ID SID_SERIAL Username   OSUSER          SPID         MODULE     PROGRAM       MB_USED TABLESPACE STATEMENTS

  

     SCOTT      oracle                  SQL*Plus  oracle@SZD        TEMP                B (TNS V V

     GO_ADMIN   oracle                   SQL*Plus  oracle@SZD        GO_TEMP           B (TNS V V

  使用resize縮小臨時表空間如不能縮小轉到下一步SQL> SELECT alter database tempfile || aname || resize || bsiz || M  resize_command   FROM v$tempfile a         (SELECT ceil(tmsizemaxblk * bkvalue / / ) siz        FROM (SELECT nvl(MAX(segblk#) ) maxblk             FROM v$sort_usage) tmsize              (SELECT VALUE             FROM v$parameter             WHERE NAME = db_block_size) bk) b

  RESIZE_COMMAND

  

  alter database tempfile /u/database/ORADB/temp/ORADB_tempORADBdbf resize Malter database tempfile /u/database/ORADB/temp/tempORADBdbf resize M

  ——>實際上此時占用GB的臨時數據文件已經縮小alter database tempfile /u/database/ORADB/temp/ORADB_tempORADBdbf resize M

  Database altered

  ——>為便於演示此時假定TEMP為過大的臨時表空間且不能釋放——>下面調整表明已使用空間超出了分配的空間SQL> alter database tempfile /u/database/ORADB/temp/tempORADBdbf resize Malter database tempfile /u/database/ORADB/temp/tempORADBdbf resize M * ERROR at line ORA file contains used data beyond requested RESIZE value

  SQL> select count(*) from v$sort_usage where tablespace=TEMP   ——>當前有未釋放的臨時段

  COUNT(*)

  

  

  /**************************************************/ /* Author Robinson Cheng                         */ /* Blog  ;    */ /* MSN   robin              */ /* QQ                                   */ /**************************************************/

  新建一個中轉臨時表空間SQL> create temporary tablespace temp tempfile /u/database/ORADB/temp/ORADB_tempdbf   size m autoextend on

  Tablespace created

  ——>如果此時過大的臨時表空間為缺省的臨時表空間則必須將缺省的臨時表空間設置為新的臨時表空間之後SQL> select property_nameproperty_value from database_properties   where property_name like DEFAULT_TEMP_TABLESPACE

  PROPERTY_NAME                  PROPERTY_VALUE

  

  DEFAULT_TEMP_TABLESPACE        TEMP

  SQL> alter database default temporary tablespace temp

  Database altered

  轉移用戶到中轉臨時表空間——>過大臨時表空間上的那些用戶需要遷移到新建的臨時表空間——>查詢dba_users視圖查詢哪些用戶位於過大的臨時表空間之上——>並使用下面的命令將其切換到新的臨時表空間alter user <username> temporary tablespace temp

  等到過大臨時表空間上的沒有臨時段被使用即已經全部釋放即可刪除過大的臨時表空間

  SQL> show user    ——>由於當前用戶為scott所以臨時表空間未能釋放USER is SCOTT

  SQL> conn / as sysdba   ——>切換到sysdba Connected

  SQL> @temp_usage      ——>臨時段已經被釋放

  TABLESPACE             MB_TOTAL    MB_USED    MB_FREE

  

  GO_TEMP                                     TEMP                                       

  ——>如果沒有釋放在可以kill session的情況下kill session利用前面獲得的sidserial#來執行(前提是允許該情況發生)

  alter system kill session

  刪除過大的臨時表空間

  SQL> alter tablespace temp tempfile offline   ——>先將其脫機

  Tablespace altered

  SQL> drop tablespace temp including contents and datafiles   ——>刪除臨時表空間及相應的文件

  Tablespace dropped

  SQL> select sname tbsnametname(tbytes//) mbtstatus   from v$tablespace sv$tempfile t   where sts# = tts#

  TBSNAME         NAME                                                       MB STATUS

  

  GO_TEMP       /u/database/ORADB/temp/ORADB_tempORADBdbf           ONLINE TEMP        /u/database/ORADB/temp/ORADB_tempdbf                ONLINE

  ——>也可以使用下面的命令來完成僅僅刪除單個文件ALTER DATABASE TEMPFILE /u/database/ORADB/temp/tempORADBdbf DROP INCLUDING DATAFILES ——>刪除單個文件

  根據需求可以創建原來的臨時表空間並將切換出去用戶切換到此臨時表空間</span>

  三總結

  關注alert_<sid>log文件中的ORA錯誤並調查什麼原因導致該錯誤有些時候並不是由於當前的SQL 導致臨時表空間不能擴展很可能由於前一個SQL耗用了%的臨時表空間而後一個SQL執行時即出現錯誤對於此類情況應調查前一SQL並調整避免過多的磁盤排序

  如果基於空間壓力應該關閉臨時表空間的自動擴展因此為臨時表空間設定合理的大小就成了一個問題個人的解決方案是首先檢查ORA其次是觀察業務高峰期的峰值如前面查詢中的字段Max Size( Maximum number of MB ever used)的值來預估如果大師們有更好的建議不妨拍磚

  通過重啟數據庫臨時表空間所耗用的大小有時候並不能縮小

  在Oracle g之前一般是通過創建中轉臨時表空間來達到縮小的目的不是很完美因為有些時候臨時段未釋放導致不能刪除臨時表空間及數據文件g可以直接使用下面的命令來完成

  alter tablespace temp shrink space

  alter tablespace temp shrink tempfile <dir> keep n <mb/kb>

  系統缺省的臨時表空間不能被刪除因此如果系統缺省的臨時表空間過大刪除前應該新置一個系統缺省的臨時表空間

  刪除過大臨時表空間前其上的用戶應該先將其設定到中轉臨時表空間重建後再將其置回原狀態

  減少磁盤排序的首要任務調整SQL如避免笛卡爾積為表添加合理的索引等其次要考慮PGA的值是否設定合理


From:http://tw.wingwit.com/Article/program/Oracle/201311/17414.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.