表记录不多, 却占用很大的空间,从哪里可以知道原因导致的.

oradbHome

dbms_space_admin.segment_dump

[ 本帖最后由 oradbHome 于 2009-5-10 12:36 编辑 ]

battalion

关注，顶一下

oradbHome

基本确认是个bug
Bug 5987262 Abstract : TABLESPACE IS ABNORMALLY INCREASED BY UNFORMATTED BLOCKS.
Bug 5890312 - A spin / hang / space leak can occur with ASSM segments

[ 本帖最后由 oradbHome 于 2009-5-12 21:07 编辑 ]

Yong Huang

We're having the same problem with one of our databases right now. But we can't reproduce on two other databases. Here's a comparison:

DB-1: 8-node RAC, 10.2.0.4, RHEL 4, ASSM auto allocate, 8k block size
DB-2: single node, 10.2.0.4, RHEL 4, ASSM auto allocate, 8k block size
DB-3: 2-node RAC, 10.2.0.4, RHEL 5, ASSM auto allocate, 8k block size

The same data load always creates this space wastage problem on DB-1 and always works fine on -2 and -3. We can't find any solution and have to allocate huge space to -1 and shrink table after data load.

There's really no significant difference between them. DB-1 has db_writer_processes set to 2 and -2 and -3 have no such setting (default 1). Even that should be irrelevant.

Can you reproduce your problem on another same-version database?

Yong Huang

oradbHome

原帖由 Yong Huang 于 2009-5-13 04:30 发表
We're having the same problem with one of our databases right now. But we can't reproduce on two other databases. Here's a comparison:

DB-1: 8-node RAC, 10.2.0.4, RHEL 4, ASSM auto allocate, 8k block size
DB-2: single node, 10.2.0.4, RHEL 4, ASSM auto allocate, 8k block size
DB-3: 2-node RAC, 10.2.0.4, RHEL 5, ASSM auto allocate, 8k block size

The same data load always creates this space wastage problem on DB-1 and always works fine on -2 and -3. We can't find any solution and have to allocate huge space to -1 and shrink table after data load.

There's really no significant difference between them. DB-1 has db_writer_processes set to 2 and -2 and -3 have no such setting (default 1). Even that should be irrelevant.

Can you reproduce your problem on another same-version database?

Yong Huang

Doc ID:         729149.1
Subject:         Table Growth Is Far More Than Expected
可以参考729149.1 是否存在过多的unformated blocks.

这个问题在我这里只有这一个表存在这个问题. 其它的数据库上没有发现.

Yong Huang

原帖由 oradbHome 于 2009-5-12 22:52 发表

Doc ID:         729149.1
Subject:         Table Growth Is Far More Than Expected
可以参考729149.1 是否存在过多的unformated blocks.

这个问题在我这里只有这一个表存在这个问题. 其它的数据库上没有发现.

Thanks for that note. Is it possible you apply that patch to see if the problem goes away?

We opened an SR with Oracle and the analyst suggested we apply patch 5890312. We haven't done it, but have run dbms_repair.segment_fix_status on all tables and indexes in the schema. Next time when we load data, we'll see if this procedure worked or not. We can't load data whenever we want. Have to wait till the beginning of each month.

Can you try segment_fix_status (see PL/SQL Reference in documentation or Bug 4660718)?

Yong Huang

[ 本帖最后由 Yong Huang 于 2009-5-14 09:39 编辑 ]

oradbHome

我昨天已经打了patch 5890312

到现在那个表也没有扩展extent , 我想是patch 启作用了.因为在前天还扩展了20几个32M 的extents .

Yong Huang

原帖由 Yong Huang 于 2009-5-13 15:52 发表

We opened an SR with Oracle and the analyst suggested we apply patch 5890312. We haven't done it, but have run dbms_repair.segment_fix_status on all tables and indexes in the schema. Next time when we load data, we'll see if this procedure worked or not. We can't load data whenever we want. Have to wait till the beginning of each month.

Can you try segment_fix_status (see PL/SQL Reference in documentation or Bug 4660718)?

Yong Huang

At the beginning of both June and July, we ran our data load on the database we had this problem on. We no longer have this problem. The only significant change we made was that at the end of May, we ran the procedure segment_fix_status (Ref: http://download.oracle.com/docs/ ... pair.htm#sthref4784). So we didn't have to apply the patch.

Yong Huang

ljc6386613

执行DBMS_REPAIR.SEGMENT_FIX_STATUS 查询表hang住

Yong Huang

> 执行DBMS_REPAIR.SEGMENT_FIX_STATUS 查询表hang住

Whenever you have a hang, the first thing you do is check the session's wait event.

Yong Huang