【TUNE_ORACLE】Oracle健康檢查基礎專案（三）專案檢查步驟概述其二

Oracle健康檢查基礎專案檢查步驟概述其二

1. 表空間

1.1 SYSTEM 表空間

系統表空間中非常不建議建立普通的使用者物件（比如表、索引等）。如果建立了會導致很多碎片，並阻止系統表的增長。

找出system表空間中不屬於 SYS 或 SYSTEM的物件：

SQL> select owner, segment_name, segment_type
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM');

 

1.2 SYSAUX 表空間

SYSAUX表空間是SYSTEM表空間的輔助表空間，在建立或升級資料庫時，SYSAUX 表空間會被自動建立。之前建立並使用獨立表空間的一些資料庫元件，現在也會使用 SYSAUX 表空間。

注：

（1）如果SYSAUX表空間不可用，則核心資料庫功能將仍可操作。使用 SYSAUX 表空間的資料庫功能可能會失敗，或是功能受限。

（2）如果配置不當，SYSAUX表空間中儲存的資料量可能非常大，並隨著時間推移而增長到無法管理的大小，所以對有一些元件需要特別注意。

檢查哪些元件正佔用空間：

SQL> select space_usage_kbytes, occupant_name, occupant_desc
from v$sysaux_occupants
order by 1 desc;

 

1.3 本地管理表空間與字典管理表空間

本地管理表空間，也稱為 LMT，相對於資料字典管理表空間有一定的優勢。

要驗證哪個表空間為 Locally Managed（本地管理）或 Dictionary Managed（字典管理），可以使用以下SQL：

SQL> select tablespace_name, extent_management
from dba_tablespaces;

 

1.4 臨時表空間

本地管理空間表將臨時檔案用於臨時表空間，而字典管理表空間使用臨時型別的表空間。預設情況下，所有表空間建立時都為 PERMANENT，因此應確保專用於臨時segment的表空間為TEMPORARY型別，檢查SQL如下：

SQL> select tablespace_name, contents
from dba_tablespaces;
 
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
USER_DATA PERMANENT
ROLLBACK_DATA PERMANENT
TEMPORARY_DATA TEMPORARY

另外，還需要確保資料庫上的使用者都分配了臨時型別的表空間。以下SQL可以查出了將永久表空間指定為其預設臨時表空間的所有使用者：

SQL> select u.username, t.tablespace_name
from dba_users u, dba_tablespaces t
where u.temporary_tablespace = t.tablespace_name
and t.contents <> 'TEMPORARY';

注：

系統使用者SYS和SYSTEM會將SYSTEM表空間顯示為它們的預設臨時表空間。該值也可以改變，以防止 SYSTEM 表空間中產生碎片，比如：

SQL> alter user SYSTEM temporary tablespace TEMP;

臨時表空間中分配的空間是可以重複使用的。這是因為基於效能的考慮，以避免由於持續分配和取消分配 extent 和 segment所產生瓶頸。因此，當檢視臨時表空間中的可用空間時，可能會始終顯示為已滿的狀態，所以 11g引入了新檢視dba_temp_free_space可以查出臨時表空間的真實使用率。以下查詢可以列出關於臨時 segment 使用情況的更多有用資訊：

（1）檢視臨時表空間的大小：

SQL> select tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by tablespace_name;

（2）檢視臨時表空間的“高水位線”：

SQL> select tablespace_name, sum(bytes_cached)/1024/1024 mb
from v$temp_extent_pool
group by tablespace_name;

 

1.5 表空間碎片

表空間碎片過多會對效能產生影響，特別是系統上正進行許多 full table scan（全表掃描）或者index fast full scan（索引快速全掃描）時。碎片的另一個缺點是，當所有可用空間的總和遠遠超出被需要的空間時，可能會得到空間不足的錯誤訊息。

 

解決碎片的唯一辦法是重新建立物件，可以使用 "alter table .. move" 命令，或者使用邏輯匯入匯出。

如果需要對系統表空間消除碎片，則必須重建整個資料庫，因為無法刪除系統表空間。

 

2. 資料庫物件

2.1 Extent（區）的數量

儘管過度擴充套件物件的效能影響不是很大，但是很多過度擴充套件物件積聚起來確實會影響效能。下面SQL將列出分配的extent超過了指定最小量的所有物件：

SQL> select owner, segment_type, segment_name, tablespace_name,
count(blocks), SUM(bytes/1024) "BYTES K", SUM(blocks)
from dba_extents
where owner NOT IN ('SYS','SYSTEM')
group by owner, segment_type, segment_name, tablespace_name
having count(*) > xxx  --根據實際情況更改成具體數值
order by segment_type, segment_name;

注：

通常，對於extent個數超過100或200的物件，可以透過使用更大的extent來重建

 

2.2 分配下一個extent

由於segment可以增長，因此在需要時它們可以分配下一個extent。如果表空間中沒有足夠的可用空間，則無法分配下一個 extent，且物件無法增長。下面SQL返回了所有無法分配其下一個extent的segment：

SQL> select s.owner, s.segment_name, s.segment_type,
s.tablespace_name, s.next_extent
from dba_segments s
where s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name);

注：

如果表空間中有許多碎片，則此查詢可能會返回仍然可以增長的物件。上述查詢基於可以表空間中的最大可用塊。如果有許多這樣彼此相連的“較小”可用塊，則Oracle資料庫將合併這些塊以提供extent分配。

 

2.3 索引

基本不需要重建索引！基本不需要重建索引！基本不需要重建索引！重要的事情說三遍。

具體原因可參考：

定期重建索引的利與弊： http://blog.itpub.net/69992972/viewspace-2765192/