« 恩墨科技第一次Oracle性能优化培训圆满结束 | Blog首页 | 20090804 - 简报这一天 »
dba_extents和dba_segments不一致问题及原因
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2009/08/dba_extents_dba_segments.html
在这次培训中,有位朋友提到了一个问题,那就是:DBA_EXTENTS和DBA_SEGMENTS关于空间的记录信息可能不一致。此前我没有注意过这个问题,但是可以判断DBA_EXTENTS的信息应当是可靠的,因为如果区间管理出问题,那么区的分配与归属将普遍存在严重的问题。链接:https://www.eygle.com/archives/2009/08/dba_extents_dba_segments.html
网上关于这个内容的描述,主要来自eagleFan的一篇日志。
Metalink Note 463101.1文章 HOW TO DISCOVER AND FIX THE MISTMATCH BETWEEN DBA_SEGMENTS AND DBA_EXTENTS DICTIONARY VIEWS 描述了这一问题。
根据描述,这个问题主要发生在并行索引创建、频繁的DELETE/INSERT等操作中,主要原因是Segemnt Header信息未能及时更新,导致段头记录的空间值和Extent Map不一致
Some types of DML/DDL operations (parallel index creation, frequent deletes/inserts) on此外在某些从低版本升级到10g的系统可能会出现这个问题,在DMT到LMT的迁移中,可能在空间管理上出现Bug,引发问题。
segments can create mismatches in the bytes, blocks and extents columns reported in
dba_segments and dba_extents dictionary. The mismatch mainly exists between segment header values and extent maps in the segment header. It is expected that individual values for each extent is correct but the summary of these values isn't reflected in the segment header due to a problem.
Metalink提供如下一段代码用于检索可能存在问题的数据段:
SELECT /*+ RULE */在我的一个Oracle 9i的系统中,找到了一个存在问题的对象:
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)
);
SQL> col TABLESPACE_NAME for a20可是转储Segemnt Header信息,可以发现,段头的记录是正确的:
SQL> col PARTITION_NAME for a10
SQL> SELECT /*+ RULE */
2 s.tablespace_name, s.segment_name SEGMENT, s.partition_name,
3 s.owner owner, s.segment_type, s.blocks sblocks, e.blocks eblocks,
s.extents sextents, e.extents eextents, s.BYTES sbytes, e.BYTES ebytes
4 FROM dba_segments s,
5 (SELECT COUNT (*) extents, SUM (blocks) blocks, SUM (BYTES) BYTES,
segment_name, partition_name, segment_type, owner
FROM dba_extents
6 7 8 9 GROUP BY segment_name, partition_name, segment_type, owner) e
10 WHERE s.segment_name = e.segment_name
AND s.owner = e.owner
11 12 AND (s.partition_name = e.partition_name OR s.partition_name IS NULL)
13 AND s.segment_type = e.segment_type
14 AND s.owner NOT LIKE 'SYS%'
15 AND ( (s.blocks <> e.blocks)
16 OR (s.extents <> e.extents)
17 OR (s.BYTES <> e.BYTES)
18 );
TABLESPACE_NAME SEGMENT PARTITION_ OWNER SEGMENT_TYPE SBLOCKS EBLOCKS SEXTENTS
-------------------- -------------------- ---------- ---------- --------------- ---------- ---------- ----------
EEXTENTS SBYTES EBYTES
---------- ---------- ----------
PUSHMOBIL CM_APP_GATHERMOBILE PUSHMOBIL TABLE 1372700 1372704 85794
85794 1.1245E+10 1.1245E+10
buffer tsn: 20 rdba: 0x0442f77b (17/194427)这种情况,只能说明字典表seg$在内存中展示的数据与实际存储并不相符,这种不一致,重启数据库也许可以恢复一致;而10g中的大范围差别Oracle推荐用
scn: 0x081a.2f46a1d7 seq: 0x03 flg: 0x04 tail: 0xa1d72303
frmt: 0x02 chkval: 0x8002 type: 0x23=PAGETABLE SEGMENT HEADER
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 85794 #blocks: 1372704
last map 0x11005cd9 #maps: 169 offset: 2716
Highwater:: 0x0d0f84b9 ext#: 85793 blk#: 16 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 1367064
mapblk 0x11005cd9 offset: 142
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x0d0f84b9 ext#: 85793 blk#: 16 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 1372697
mapblk 0x11005cd9 offset: 142
Level 1 BMB for High HWM block: 0x0dce6ee9
Level 1 BMB for Low HWM block: 0x0dce6ee9
--------------------------------------------------------
Segment Type: 1 nl2: 6 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x0d0ebbea
Last Level 1 BMB: 0x0dce6ee9
Last Level II BMB: 0x0d0ebbea
Last Level III BMB: 0x00000000
Map Header:: next 0x0443f76a #extents: 307 obj#: 31098 flag: 0x20000000
Extent Map
-----------------------------------------------------------------
0x0442f779 length: 16
DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_EXTBLKS('tablespace_name');
修复,不过要慎重,这一不一致不会带来实质性的影响。
记录一下,有机会再细致研究!
-The End-
历史上的今天...
>> 2018-08-04文章:
>> 2008-08-04文章:
>> 2007-08-04文章:
By eygle on 2009-08-04 09:58 | Comments (6) | FAQ | 2355 |
鄙人不才, 早年(1996)看到的Oracle文档将 Extent 翻译成 片(扩充片) 比较合适.
牛,1996年还没听说过Oracle。
片,没有流行起来!
哈哈,木匠96年就接触oracle了,太强悍了
再补充一句, Segment翻译成 "段", 这回公认了吧. ^_^
还有:
Extent 扩充,
Expand 扩展
MISTMATCH 这个单词是不是错了啊!~
MISTMATCH 这个单词是不是错了啊!~