system表空間不足的問題分析(二)
今天收到一條不太起眼的報警郵件，大體內容是某個表空間的空間有些緊張了。大體內容如下：
Tablesapce: CMBI_SNZG_DATA: 92.2% [Warning!]
根據這個資訊，很明顯是需要新增資料檔案了，但是同時還有一個警告就是磁碟空間也告警了，那麼這個看起來簡單的問題得好好琢磨琢磨了，其實是幾件事，一件是做一些資料清理，釋放部分表空間，甚至可以透過釋放資料檔案的空間來進一步釋放磁碟空間，第二件是給表空間告警的表空間新增資料檔案。
首先檢視資料庫中的使用者佔有的資料量的情況,可以看到佔用率比較靠前的幾個使用者。而這次報警的是第三個使用者CMBI_SNZG.
USERNAME Default TBS TEMP TBS CREATED Size (Mb)
------------------------------ --------------- --------------- ------------------- ------------
CMBI_MIN CMBI_MIN_DATA TEMP_NEW 2014-06-03 10:37:16 253,624
BIDATA BIDATA_DATA TEMP_NEW 2012-10-17 11:56:00 226,679
CMBI_SNZG CMBI_SNZG_DATA TEMP_NEW 2014-08-08 18:21:54 213,879
CMBI_QSMY2 CMBI_QSMY2_DATA TEMP_NEW 2014-06-27 17:59:21 207,010
CMBI_TEST CMBI_TLBB_DATA TEMP_NEW 2014-06-11 22:54:29 118,653
CMBI_QSMYQZ CMBI_QSMYQZ_DAT TEMP_NEW 2014-06-27 18:01:34 82,681
SYS SYSTEM TEMP_NEW 2011-09-17 09:46:22 46,608
CMBI_DTLM CMBI_DTLM_DATA TEMP_NEW 2014-06-11 10:09:10 21,919
進一步分析發現，這個使用者下佔用表空間最多的是幾個日誌表。
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_MB
------------------------------ ------------------------------ ------------------ ----------
CMBI_SNZG M_START_LOG TABLE 160
CMBI_SNZG IND_M_ONLINE_LOG INDEX 168
CMBI_SNZG IND_M_SDKSTART_LOG INDEX 520
CMBI_SNZG M_ONLINE_LOG TABLE 1472
CMBI_SNZG M_SDKSTART_LOG TABLE 3392
CMBI_SNZG IND_M_GAMEEVENT_LOG INDEX 22463
CMBI_SNZG M_GAMEEVENT_LOG TABLE 185619
對於這些日誌表在統計系統中還是有一些保留時長，允許刪除較早的歷史資料，但是比較暈的這幾個表都是普通表，沒有做分割槽，那麼刪除意味值資料空間勉強釋放，但是物理空間無法釋放。而且刪除的代價比較高。在這個時候還是需要和開發的同事做一些確認才可以清理或者整改+清理。
所以這個時候處理問題就看起來比較棘手了。看來原本的兩種處理思路都沒有奏效。那麼還有什麼空間呢。
其中一個亮點就是在最開始的時候，SYS使用者佔用的表空間竟然有40多個G,這個是很不正常的。憑著以往的經驗，一般是aud$佔用的空間過大導致SYSAUX過大。
表空間的具體使用資料如下：
Tablespace STA M A Init Total MB Free MB Used MB LrgstMB MaxExt %Fr A
SYSAUX OLN L S 64K 51,030 4,039 46,991 1,005 2147483645 8 *
SYSTEM OLN L S 64K 14,400 565 13,835 498 2147483645 4 *
那麼這個場景中是不是aud$導致的呢，結果使用dba_segments過濾查詢，發現竟然都是WRH$的一些基表。
OWNER SEGMENT_NAME SIZE_MB
------- ------------------------------------- ----------
SYS WRH$_SQL_PLAN 54
SYS SYS_LOB0000006331C00004$$ 57
SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 72
SYS WRH$_SQLSTAT 107
SYS WRM$_SNAPSHOT_DETAILS 234
SYS WRM$_SNAPSHOT_DETAILS_INDEX 280
SYS WRH$_LATCH_CHILDREN_PK 15950
SYS WRH$_LATCH_CHILDREN 28309
可以使用下面的語句進行一個簡單的驗證，發現大多數資料都來自於awr.
SQL> SELECT occupant_name "Item",
2 space_usage_kbytes / 1048576 "Space Used (GB)",
3 schema_name "Schema",
4 move_procedure "Move Procedure"
5 FROM v$sysaux_occupants
6* ORDER BY 2 desc
SQL> /
Item pace Used (GB) Schema Move Procedure
---------------------------------- -------- ----------------------------------------------------------------
SM/AWR 45.0463257 SYS
SM/OPTSTAT .176513672 SYS
SM/ADVISOR .16973877 SYS
XDB .153869629 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
SDO .07220459 MDSYS MDSYS.MOVE_SDO
EM .04486084 SYSMAN emd_maintenance.move_em_tblspc
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
那麼佔用空間較大的段是哪幾個呢？發現都是清一色來自WRH$_LATCH_CHILDREN
SQL> SELECT *
2 FROM (SELECT SEGMENT_NAME,
3 PARTITION_NAME,
4 SEGMENT_TYPE,
5 BYTES/1024/1024
6 FROM DBA_SEGMENTS
7 WHERE TABLESPACE_NAME = 'SYSAUX'
8 ORDER BY 4 DESC)
9* WHERE ROWNUM <= 10
SQL> /
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024
------------------------------ ------------------------------ ------------------ ---------------
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53068 TABLE PARTITION 3343
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53692 TABLE PARTITION 3046
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_52588 TABLE PARTITION 3014
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_52904 TABLE PARTITION 2895
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_54009 TABLE PARTITION 2891
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53864 TABLE PARTITION 2551
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53260 TABLE PARTITION 2540
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_52760 TABLE PARTITION 2535
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53548 TABLE PARTITION 2530
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53404 TABLE PARTITION 2524
對於這部分資料可以做一些簡單的解讀，它的分割槽設定還是有一定的規律，分割槽會根據db_id和snap_id來組合生成對應的分割槽名稱。
比如DB_ID為：
SQL> select DBID from v$database;
DBID
----------
723611991
對應的快照為：
DB_NAME BEGIN_SNAP END_SNAP SNAPDATE LVL DURATION_MINS DBTIME
--------- ---------- ---------- ------------------------------ ---------- ------------- ----------
BIDB 54009 54010 07 Feb 2016 00:00 2 29 1
54010 54011 07 Feb 2016 00:30 2 30 0
如果對於v$latch_children有一些影響，可以從statspack找到一些資訊，statspack的幫助文件中是這麼描述設定的收集資訊的等級，level 6的部分有這麼一段描述。
6.2. Time Units used for Performance Statistics
Oracle now supports capturing certain performance data with millisecond and
microsecond granularity.
Views which include microsecond timing include:
- v$session_wait, v$system_event, v$session_event (time_waited_micro column)
- v$sql, v$sqlarea (cpu_time, elapsed_time columns)
- v$latch, v$latch_parent, v$latch_children (wait_time column)
- v$sql_workarea, v$sql_workarea_active (active_time column)
其中v$latch_children赫然在列。那麼這種情況可能是怎麼造成的呢，這部分統計資訊是不是過於詳細了。可以透過引數statistics來做進一步驗證。
SQL> show parameter statis
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
statistics_level string ALL
timed_os_statistics integer 60
timed_statistics boolean TRUE
奇怪的是這個等級竟然不是預設的TYPICAL而是ALL,難怪這部分的資訊佔用的資料空間會格外大。
檢視備庫的設定資訊，做一個簡單的對比和驗證。
SQL> show parameter statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
所以這個問題就很顯而易見了，就是收集統計資訊的等級為ALL導致生成了大量的統計資訊資料。可以把資料庫修改為TYPICAL的模式就可以了。
SQL> alter system set statistics_level=typical;
System altered.
好了一個潛在的問題已經解決了，這部分的空間也可以手工刪除，更多細節不再贅述。
對於AWR還有一些資料值得參考，那就是快照的生成頻率和保留時長。目前的配置為：
SQL>select *from dba_hist_wr_control
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ --------------------------------------------------------------------------- ----------
723611991 +00000 00:30:00.0 +00030 00:00:00.0 DEFAULT
可以適當縮短保留時長，生成快照的頻率也可以降低。
exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>14*24*60);
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ --------------------------------------------------------------------------- ----------
723611991 +00000 01:00:00.0 +00014 00:00:00.0 DEFAULT
所以透過這些設定可以釋放出一部分寶貴的空間，當然也就可以轉換為新增的資料檔案大小了。最後把這些空間釋放了之後，再新增一個資料檔案，這個問題就暫時告一段落，至少可以在節後再進一步做更多的處理了。
關於第一篇，可以參見
system表空間不足的問題分析 http://blog.itpub.net/23718752/viewspace-1805645/
Tablesapce: CMBI_SNZG_DATA: 92.2% [Warning!]
根據這個資訊，很明顯是需要新增資料檔案了，但是同時還有一個警告就是磁碟空間也告警了，那麼這個看起來簡單的問題得好好琢磨琢磨了，其實是幾件事，一件是做一些資料清理，釋放部分表空間，甚至可以透過釋放資料檔案的空間來進一步釋放磁碟空間，第二件是給表空間告警的表空間新增資料檔案。
首先檢視資料庫中的使用者佔有的資料量的情況,可以看到佔用率比較靠前的幾個使用者。而這次報警的是第三個使用者CMBI_SNZG.
USERNAME Default TBS TEMP TBS CREATED Size (Mb)
------------------------------ --------------- --------------- ------------------- ------------
CMBI_MIN CMBI_MIN_DATA TEMP_NEW 2014-06-03 10:37:16 253,624
BIDATA BIDATA_DATA TEMP_NEW 2012-10-17 11:56:00 226,679
CMBI_SNZG CMBI_SNZG_DATA TEMP_NEW 2014-08-08 18:21:54 213,879
CMBI_QSMY2 CMBI_QSMY2_DATA TEMP_NEW 2014-06-27 17:59:21 207,010
CMBI_TEST CMBI_TLBB_DATA TEMP_NEW 2014-06-11 22:54:29 118,653
CMBI_QSMYQZ CMBI_QSMYQZ_DAT TEMP_NEW 2014-06-27 18:01:34 82,681
SYS SYSTEM TEMP_NEW 2011-09-17 09:46:22 46,608
CMBI_DTLM CMBI_DTLM_DATA TEMP_NEW 2014-06-11 10:09:10 21,919
進一步分析發現，這個使用者下佔用表空間最多的是幾個日誌表。
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_MB
------------------------------ ------------------------------ ------------------ ----------
CMBI_SNZG M_START_LOG TABLE 160
CMBI_SNZG IND_M_ONLINE_LOG INDEX 168
CMBI_SNZG IND_M_SDKSTART_LOG INDEX 520
CMBI_SNZG M_ONLINE_LOG TABLE 1472
CMBI_SNZG M_SDKSTART_LOG TABLE 3392
CMBI_SNZG IND_M_GAMEEVENT_LOG INDEX 22463
CMBI_SNZG M_GAMEEVENT_LOG TABLE 185619
對於這些日誌表在統計系統中還是有一些保留時長，允許刪除較早的歷史資料，但是比較暈的這幾個表都是普通表，沒有做分割槽，那麼刪除意味值資料空間勉強釋放，但是物理空間無法釋放。而且刪除的代價比較高。在這個時候還是需要和開發的同事做一些確認才可以清理或者整改+清理。
所以這個時候處理問題就看起來比較棘手了。看來原本的兩種處理思路都沒有奏效。那麼還有什麼空間呢。
其中一個亮點就是在最開始的時候，SYS使用者佔用的表空間竟然有40多個G,這個是很不正常的。憑著以往的經驗，一般是aud$佔用的空間過大導致SYSAUX過大。
表空間的具體使用資料如下：
Tablespace STA M A Init Total MB Free MB Used MB LrgstMB MaxExt %Fr A
SYSAUX OLN L S 64K 51,030 4,039 46,991 1,005 2147483645 8 *
SYSTEM OLN L S 64K 14,400 565 13,835 498 2147483645 4 *
那麼這個場景中是不是aud$導致的呢，結果使用dba_segments過濾查詢，發現竟然都是WRH$的一些基表。
OWNER SEGMENT_NAME SIZE_MB
------- ------------------------------------- ----------
SYS WRH$_SQL_PLAN 54
SYS SYS_LOB0000006331C00004$$ 57
SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 72
SYS WRH$_SQLSTAT 107
SYS WRM$_SNAPSHOT_DETAILS 234
SYS WRM$_SNAPSHOT_DETAILS_INDEX 280
SYS WRH$_LATCH_CHILDREN_PK 15950
SYS WRH$_LATCH_CHILDREN 28309
可以使用下面的語句進行一個簡單的驗證，發現大多數資料都來自於awr.
SQL> SELECT occupant_name "Item",
2 space_usage_kbytes / 1048576 "Space Used (GB)",
3 schema_name "Schema",
4 move_procedure "Move Procedure"
5 FROM v$sysaux_occupants
6* ORDER BY 2 desc
SQL> /
Item pace Used (GB) Schema Move Procedure
---------------------------------- -------- ----------------------------------------------------------------
SM/AWR 45.0463257 SYS
SM/OPTSTAT .176513672 SYS
SM/ADVISOR .16973877 SYS
XDB .153869629 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
SDO .07220459 MDSYS MDSYS.MOVE_SDO
EM .04486084 SYSMAN emd_maintenance.move_em_tblspc
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
那麼佔用空間較大的段是哪幾個呢？發現都是清一色來自WRH$_LATCH_CHILDREN
SQL> SELECT *
2 FROM (SELECT SEGMENT_NAME,
3 PARTITION_NAME,
4 SEGMENT_TYPE,
5 BYTES/1024/1024
6 FROM DBA_SEGMENTS
7 WHERE TABLESPACE_NAME = 'SYSAUX'
8 ORDER BY 4 DESC)
9* WHERE ROWNUM <= 10
SQL> /
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024
------------------------------ ------------------------------ ------------------ ---------------
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53068 TABLE PARTITION 3343
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53692 TABLE PARTITION 3046
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_52588 TABLE PARTITION 3014
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_52904 TABLE PARTITION 2895
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_54009 TABLE PARTITION 2891
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53864 TABLE PARTITION 2551
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53260 TABLE PARTITION 2540
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_52760 TABLE PARTITION 2535
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53548 TABLE PARTITION 2530
WRH$_LATCH_CHILDREN WRH$_LATCH__723611991_53404 TABLE PARTITION 2524
對於這部分資料可以做一些簡單的解讀，它的分割槽設定還是有一定的規律，分割槽會根據db_id和snap_id來組合生成對應的分割槽名稱。
比如DB_ID為：
SQL> select DBID from v$database;
DBID
----------
723611991
對應的快照為：
DB_NAME BEGIN_SNAP END_SNAP SNAPDATE LVL DURATION_MINS DBTIME
--------- ---------- ---------- ------------------------------ ---------- ------------- ----------
BIDB 54009 54010 07 Feb 2016 00:00 2 29 1
54010 54011 07 Feb 2016 00:30 2 30 0
如果對於v$latch_children有一些影響，可以從statspack找到一些資訊，statspack的幫助文件中是這麼描述設定的收集資訊的等級，level 6的部分有這麼一段描述。
6.2. Time Units used for Performance Statistics
Oracle now supports capturing certain performance data with millisecond and
microsecond granularity.
Views which include microsecond timing include:
- v$session_wait, v$system_event, v$session_event (time_waited_micro column)
- v$sql, v$sqlarea (cpu_time, elapsed_time columns)
- v$latch, v$latch_parent, v$latch_children (wait_time column)
- v$sql_workarea, v$sql_workarea_active (active_time column)
其中v$latch_children赫然在列。那麼這種情況可能是怎麼造成的呢，這部分統計資訊是不是過於詳細了。可以透過引數statistics來做進一步驗證。
SQL> show parameter statis
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
statistics_level string ALL
timed_os_statistics integer 60
timed_statistics boolean TRUE
奇怪的是這個等級竟然不是預設的TYPICAL而是ALL,難怪這部分的資訊佔用的資料空間會格外大。
檢視備庫的設定資訊，做一個簡單的對比和驗證。
SQL> show parameter statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
所以這個問題就很顯而易見了，就是收集統計資訊的等級為ALL導致生成了大量的統計資訊資料。可以把資料庫修改為TYPICAL的模式就可以了。
SQL> alter system set statistics_level=typical;
System altered.
好了一個潛在的問題已經解決了，這部分的空間也可以手工刪除，更多細節不再贅述。
對於AWR還有一些資料值得參考，那就是快照的生成頻率和保留時長。目前的配置為：
SQL>select *from dba_hist_wr_control
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ --------------------------------------------------------------------------- ----------
723611991 +00000 00:30:00.0 +00030 00:00:00.0 DEFAULT
可以適當縮短保留時長，生成快照的頻率也可以降低。
exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>14*24*60);
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ --------------------------------------------------------------------------- ----------
723611991 +00000 01:00:00.0 +00014 00:00:00.0 DEFAULT
所以透過這些設定可以釋放出一部分寶貴的空間，當然也就可以轉換為新增的資料檔案大小了。最後把這些空間釋放了之後，再新增一個資料檔案，這個問題就暫時告一段落，至少可以在節後再進一步做更多的處理了。
關於第一篇，可以參見
system表空間不足的問題分析 http://blog.itpub.net/23718752/viewspace-1805645/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1987680/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- system表空間不足的問題分析
- sysaux 表空間不足問題處理UX
- 系統臨時表空間不足問題
- 閃回區空間不足引發的SQL問題分析SQL
- swap空間不足問題解決
- ORACLE的SYSTEM 表空間Oracle
- 歸檔目錄空間不足造成的問題
- 利用RMAN遷移表空間碰到的問題（二）
- 遷移SYSTEM表空間為本地管理表空間
- oracle system 表空間32G問題解決一例Oracle
- UNDO表空間不足解決方法
- 如何解決 Linux 中“磁碟空間不足”的問題Linux
- oracle 表空間 不足時如何處理Oracle
- TEMP表空間不足解決 - temp group
- Tablespace Fragmentation - 表空間碎片問題Fragment
- 索引表空間不足的幾個處理思路索引
- 處理TEMP表空間滿的問題
- 分析表空間空閒率並收縮表空間
- dataguard之邏輯備庫表空間不足
- 使用mod對資料進行進行分組解決TEMP表空間不足的問題
- 一次HASH JOIN 臨時表空間不足的分析和優化思路優化
- 4.2.1.7 規劃 SYSTEM 和 SYSAUX 表空間UX
- system表空間爆滿解決方法
- imp/EXP 表空間轉換問題
- 從system/sysaux空間轉移TABLE&Index到其它表空間UXIndex
- 【UNDO】使用重建UNDO表空間方法解決UNDO表空間過大問題
- db2解決load後系統空間不足問題DB2
- 解決系統盤空間不足的不二法寶
- 傳輸表空間及問題處理
- ORACLE SYSTEM表空間異常與審計的功能Oracle
- 查詢表空間容量時顯示大小為空的問題
- 表空間檢測異常的問題診斷
- Oracle使用者預設表空間的問題Oracle
- 利用RMAN遷移表空間碰到的問題（五）
- 利用RMAN遷移表空間碰到的問題（四）
- 利用RMAN遷移表空間碰到的問題（三）
- 利用RMAN遷移表空間碰到的問題（一）
- 【實驗】重建臨時表空間解決臨時表空間過大問題