發現dba_segments和dba_extents中統計段空間大小居然不一樣 ===========================================================

發現dba_segments和dba_extents中統計段空間大小居然不一樣

作者: yaanzy(http://yaanzy.itpub.net)

發表于: 2008.08.28 11:05

分類: Oracle技術

出處: http://yaanzy.itpub.net/post/1263/469662

---------------------------------------------------------------

最近在測試系統上發現dba_segments和dba_extents中統計段空間大小居然不一樣

SQL> select bytes,blocks,extents from dba_segments where segment_name='PROFILE' and owner='NAP3';

BYTES BLOCKS EXTENTS

---------- ---------- ----------

0 0 0

SQL> select bytes,blocks,extent_id from dba_extents where segment_name='PROFILE' and owner='NAP3';

BYTES BLOCKS EXTENT_ID

---------- ---------- ----------

167772160 20480 0

在metalink上搜尋到如下資料： Doc ID: Note:463101.1

HOW TO DISCOVER AND FIX THE MISTMATCH BETWEEN DBA_SEGMENTS AND DBA_EXTENTS DICTIONARY VIEWS

裡面講到當DML/DDL操作(parallel index creation, frequent deletes/inserts)會導緻這種不一緻，

導緻dba_segments中的不正确, 是以dba_extents中記錄的配置設定給段的空間值才是可信的值。

解決方法是執行一個Oracle internal的未寫入文檔的procedure:

DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS('<tablespace_name>');

（注意要把COMPATIBLE設定到10.0.0以上）

Issuing DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS fixes the DBA_SEGMENTS values. The tablespace

must be kept

online and read/write when this procedure is called. Runing this procedure requires

COMPATIBLE parameter to be set to 10.0.0.0 or greater.

The procedure fixes extents, blocks and bytes in the segment headers to synchronize seg$ and

segment header entries.

It holds the allocation enqueue for the tablespace till the command

is completed and this may delay some sort of operations in this tablespace (new extent allocation,

deallocate extent, etc.). So it needs to be run during an idle period.

可以通過如下語句來檢查系統中所有不一緻的segment：

select

s.tablespace_name, s.segment_name segment, s.partition_name,

s.owner owner, s.segment_type,

s.blocks sblocks, e.blocks eblocks,

s.extents sextents, e.extents eextents, s.bytes sbytes, e.bytes ebytes

from

dba_segments s,

(select count(*) extents, sum(blocks) blocks, sum(bytes) bytes, segment_name,

partition_name, segment_type, owner

from dba_extents

group

by segment_name,partition_name,segment_type,owner) e

where s.segment_name=e.segment_name

and s.owner = e.owner

and (s.partition_name = e.partition_name or s.partition_name is

null)

and s.segment_type = e.segment_type

dba_extents和dba_segments不一緻問題及原因

今天發現這兩個視圖查詢出來的空間有差異，通過網上查找，看到eygle大師的部落格以及其他網友的資料，簡單總結一下：

以下是轉載網站：

http://www.eygle.com/archives/2009/08/dba_extents_dba_segments.html

http://www.dbafan.com/blog/?p=146

看到都是從Metalink上得到答案，一句話，對于我們使用dba_extents時絕對正确的，

至于兩者差距主要是由于個并行索引建立、頻繁的DELETE/INSERT等操作中，Segemnt Header資訊未能及時更新，導緻段頭記錄的空間值和Extent Map不一緻。

下列語句可以查出不一緻的段:

?

select s.tablespace_name, s.segment_name segment, s.partition_name, s.owner owner, s.segment_type, s.blocks sblocks, e.blocks eblocks, s.extents sextents, e.extents eextents, s.bytes sbytes, e.bytes ebytes from dba_segments s, ( select count (*) extents, sum (blocks) blocks, sum (bytes) bytes, segment_name, partition_name, segment_type, owner from dba_extents group by segment_name,partition_name,segment_type,owner) e where s.segment_name=e.segment_name and s.owner = e.owner and (s.partition_name = e.partition_name or s.partition_name is null ) and s.segment_type = e.segment_type and s.owner not like 'SYS%' and ((s.blocks <> e.blocks) or (s.extents <> e.extents) or (s.bytes <> e.bytes));

 解決方法是運作procedure: TABLESPACE_FIX_SEGMENT_EXTBLKS

DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS(‘tablespace_name’);

Issuing DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS fixes the DBA_SEGMENTS values. The tablespace

must be kept online and read/write when this procedure is called. Runing this procedure requires COMPATIBLE parameter to be set to 10.0.0.0 or greater.

The procedure fixes extents, blocks and bytes in the segment headers to synchronize seg$ and

segment header entries.

It holds the allocation enqueue for the tablespace till the command is completed and this may delay some sort of operations in this tablespace (new extent allocation, deallocate extent, etc.). So it needs to be run during an idle period.

對于application并沒有什麼影響，隻是會影響一些依賴于dba_segments做監控的monitoring

解決方法沒試過，隻是作為一種儲備解決問題的手段。從網友那抄來了。回去後有時間再進行測試。