求思路：分区表实际使用数据块的块数远小于系统分配块数

DataKW

分区表的一个分区，实际使用4262个块，而系统扩展分配了366592个块，系统监控显示，小于5分钟内空间扩展了3.5个G,导致分区所在的表空间被撑爆！

数据库版本：Release 10.2.0.4.0


实际使用的块查询：

select fno,rbn,count(0) from  (select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) rbn,dbms_rowid.rowid_row_number(rowid) rrn from  table_name partition(part_17) ) tab group by fno,rbn ;

查询结果：4262


系统扩展分配的块查询：

select sum(BLOCKS) from dba_extents where segment_name='TABLE_NAME' AND PARTITION_NAME='PART_17';

查询结果：366592



这个表的记录数：

select count(0) from TABLE_NAME partition(part_17);

  COUNT(0)
----------
    110656----每行平均长度265个字节

Maclean Liu(刘相兵

select fno,rbn,count(0) from  (select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) rbn,dbms_rowid.rowid_row_number(rowid) rrn from  table_name partition(part_17) ) tab group by fno,rbn ;

查询结果：4262


==>这只说明 有数据行的 是4262个块， 但毫无实际意义， 这是一种过时的看法， 请摒弃掉。

Maclean Liu(刘相兵

set echo off
alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';
SELECT * FROM DBA_TAB_MODIFICATIONS where table_owner = 'TABLE_NAME' AND PARTITION_NAME='PART_17';


set linesize 80 pagesize 1400
select * from dba_tab_partitions where table_owner = 'TABLE_NAME' AND PARTITION_NAME='PART_17';


查一下

DataKW

SQL> SELECT * FROM DBA_TAB_MODIFICATIONS where table_name = 'T_INFO_ACCOUNT' AND PARTITION_NAME='PART_17';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP           TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------- --- -------------
BTUPAYPROD                     T_INFO_ACCOUNT                 PART_17                                                             1891      17237          0 2013/04/04 06:58:03 NO              0

SQL>  select * from dba_tab_partitions where table_name = 'T_INFO_ACCOUNT' AND PARTITION_NAME='PART_17';

TABLE_OWNER                    TABLE_NAME                     COM PARTITION_NAME                 SUBPARTITION_COUNT HIGH_VALUE                                                                       HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME                  PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE  FREELISTS FREELIST_GROUPS LOGGING COMPRESS   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED       BUFFER_ GLO USE
------------------------------ ------------------------------ --- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- -------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------------- ------- --- ---
BTUPAYPROD                     T_INFO_ACCOUNT                 NO  PART_17                                         0 '420000'                                                                                         8                 17 TBS_CUSTOMER_ACCOUNT_DATA01            10                     1        255          65536                      1 2147483645                                         YES     DISABLED     108860       4150           74       1014          0         268       23964 2013/03/27 04:23:55 DEFAULT NO  NO

SQL>

Maclean Liu(刘相兵

2013/03/27 04:23:55  ==>
上一次收集统计信息时    108860行       4150块

从 DBA_TAB_MODIFICATIONS 看没有大规模的 INSERT和DELETE

Maclean Liu(刘相兵

action plan:
1. 给出 该表的完整ddl语句

2.

给出下面脚本的输出

1. set serveroutput on;
   
2. 
   
3.       declare
   
4.         v_unformatted_blocks number;
   
5.         v_unformatted_bytes number;
   
6.         v_fs1_blocks number;
   
7.         v_fs1_bytes number;
   
8.         v_fs2_blocks number;
   
9.         v_fs2_bytes number;
   
10.         v_fs3_blocks number;
    
11.         v_fs3_bytes number;
    
12.        v_fs4_blocks number;
    
13.        v_fs4_bytes number;
    
14.        v_full_blocks number;
    
15.        v_full_bytes number;
    
16.      begin
    
17.      dbms_space.space_usage ('&OWNER', '&TABNAME', 'TABLE', v_unformatted_blocks,
    
18.      v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
    
19.      v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
    
20.      dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
    
21.      dbms_output.put_line('FS1 Blocks              = '||v_fs1_blocks);
    
22.      dbms_output.put_line('FS2 Blocks              = '||v_fs2_blocks);
    
23.      dbms_output.put_line('FS3 Blocks              = '||v_fs3_blocks);
    
24.      dbms_output.put_line('FS4 Blocks              = '||v_fs4_blocks);
    
25.      dbms_output.put_line('Full Blocks              = '||v_full_blocks);
    
26.      end;
    
27.      /
    
28. 
    
29. SELECT TABLE_NAME ,  (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
    
30. "Data lower than HWM in MB"   FROM  DBA_TABLES WHERE  UPPER(owner) =UPPER('&OWNER') order by 2 desc;
    
31. 
    
32. select tablespace_name, allocated_space, reclaimable_space from table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'ALL'))

复制代码

DataKW

declare
        v_unformatted_blocks number;
        v_unformatted_bytes number;
        v_fs1_blocks number;
        v_fs1_bytes number;
        v_fs2_blocks number;
        v_fs2_bytes number;
        v_fs3_blocks number;
        v_fs3_bytes number;
       v_fs4_blocks number;
       v_fs4_bytes number;
       v_full_blocks number;
       v_full_bytes number;
     begin
      dbms_space.space_usage ('&OWNER', '&TABNAME','TABLE PARTITION', v_unformatted_blocks,
      v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
      v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, '&PARTNAME');
     dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
     dbms_output.put_line('FS1 Blocks              = '||v_fs1_blocks);
     dbms_output.put_line('FS2 Blocks              = '||v_fs2_blocks);
     dbms_output.put_line('FS3 Blocks              = '||v_fs3_blocks);
     dbms_output.put_line('FS4 Blocks              = '||v_fs4_blocks);
     dbms_output.put_line('Full Blocks              = '||v_full_blocks);
     end;
     /


Unformatted Blocks = 360827
FS1 Blocks              = 3
FS2 Blocks              = 2
FS3 Blocks              = 12
FS4 Blocks              = 758
Full Blocks              = 4147

PL/SQL procedure successfully completed.





SELECT TABLE_NAME ,  (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB"   FROM  DBA_TABLES WHERE  UPPER(owner) =UPPER('&OWNER') order by 2 desc;
TABLE_NAME                     Data lower than HWM in MB
------------------------------ -------------------------
T_INFO_ACCOUNT                                1382.59251


select tablespace_name, allocated_space, reclaimable_space from table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'ALL'));

TABLESPACE_NAME                ALLOCATED_SPACE RECLAIMABLE_SPACE
------------------------------ --------------- -----------------
TBS_CUSTOMER_ACCOUNT_DATA01         2906192651         477456054

DataKW

建表语句：
create table T_INFO_ACCOUNT_P
(
  ACCOUNT_NO           VARCHAR2(20) not null,
  ACCOUNT_NAME         VARCHAR2(40 CHAR),
  CUSTOMER_NO          VARCHAR2(16),
  OPEN_DATE            DATE,
  CLOSE_DATE           DATE,
  VALID_DATE           DATE,
  STATUS               CHAR(1),
  COMM_STATUS          CHAR(1),
  TYPE                 CHAR(1),
  GRADE                CHAR(1),
  IS_REALNAME          CHAR(1),
  APANAGE              VARCHAR2(15) not null,
  UNIT_ID              VARCHAR2(30),
  AREA_CODE            VARCHAR2(6),
  CITY_CODE            VARCHAR2(10),
  FORBIDDEN_TXN        VARCHAR2(2000),
)
partition by list (area_code)
(
partition part_01 values ('330000') tablespace TBS_CUSTOMER_ACCOUNT_DATA01,
partition part_02 values ('320000') tablespace TBS_CUSTOMER_ACCOUNT_DATA02,
partition part_03 values ('340000') tablespace TBS_CUSTOMER_ACCOUNT_DATA03,
partition part_04 values ('440000') tablespace TBS_CUSTOMER_ACCOUNT_DATA04,
partition part_05 values ('350000') tablespace TBS_CUSTOMER_ACCOUNT_DATA01,
partition part_06 values ('510000') tablespace TBS_CUSTOMER_ACCOUNT_DATA02,
partition part_07 values ('430000') tablespace TBS_CUSTOMER_ACCOUNT_DATA03,
partition part_08 values ('360000') tablespace TBS_CUSTOMER_ACCOUNT_DATA04,
partition part_09 values ('150000') tablespace TBS_CUSTOMER_ACCOUNT_DATA01,
partition part_10 values ('460000') tablespace TBS_CUSTOMER_ACCOUNT_DATA02,
partition part_11 values ('610000') tablespace TBS_CUSTOMER_ACCOUNT_DATA03,
partition part_12 values ('650000') tablespace TBS_CUSTOMER_ACCOUNT_DATA04,
partition part_13 values ('210000') tablespace TBS_CUSTOMER_ACCOUNT_DATA01,
partition part_14 values ('110000') tablespace TBS_CUSTOMER_ACCOUNT_DATA02,
partition part_15 values ('530000') tablespace TBS_CUSTOMER_ACCOUNT_DATA03,
partition part_16 values ('620000') tablespace TBS_CUSTOMER_ACCOUNT_DATA04,
partition part_17 values ('420000') tablespace TBS_CUSTOMER_ACCOUNT_DATA01,
partition part_18 values ('640000') tablespace TBS_CUSTOMER_ACCOUNT_DATA02,
partition part_19 values ('130000') tablespace TBS_CUSTOMER_ACCOUNT_DATA03,
partition part_20 values ('140000') tablespace TBS_CUSTOMER_ACCOUNT_DATA04,
partition part_21 values ('540000') tablespace TBS_CUSTOMER_ACCOUNT_DATA01,
partition part_22 values ('410000') tablespace TBS_CUSTOMER_ACCOUNT_DATA02,
partition part_23 values ('370000') tablespace TBS_CUSTOMER_ACCOUNT_DATA03,
partition part_24 values ('220000') tablespace TBS_CUSTOMER_ACCOUNT_DATA04,
partition part_25 values ('630000') tablespace TBS_CUSTOMER_ACCOUNT_DATA01,
partition part_26 values ('230000') tablespace TBS_CUSTOMER_ACCOUNT_DATA02,
partition part_27 values ('120000') tablespace TBS_CUSTOMER_ACCOUNT_DATA03,
partition part_28 values ('520000') tablespace TBS_CUSTOMER_ACCOUNT_DATA04,
partition part_29 values ('450000') tablespace TBS_CUSTOMER_ACCOUNT_DATA01,
partition part_30 values ('310000') tablespace TBS_CUSTOMER_ACCOUNT_DATA02,
partition part_31 values ('500000') tablespace TBS_CUSTOMER_ACCOUNT_DATA03,
partition  other  VALUES (DEFAULT) tablespace TBS_CUSTOMER_ACCOUNT_DATA04
);

Maclean Liu(刘相兵

Unformatted Blocks = 360827 ==>  未格式化的块有360827 个， 这解释了 为什么
select sum(BLOCKS) from dba_extents where segment_name='TABLE_NAME' AND PARTITION_NAME='PART_17'; 查询结果为366592

Maclean Liu(刘相兵

对于该Unformatted Blocks


已知有相关BUG
Abstract: TABLESPACE IS ABNORMALLY INCREASED  BY UNFORMATTED BLOCKS

http://www.askmaclean.com/archiv ... B8%80%E4%BE%8B.html

Maclean Liu(刘相兵

ODM MORE FINDING:

Sudden Increase in Unformatted Blocks

symptoms

Unexplained increases in unformatted blocks in a tablespace. Very few of the unformatted blocks ever get used. More space is added whenever a new block is requested resulting in even more unformatted blocks. The number of unformatted blocks varies from one database to another. Some databases only add a few thousand unformatted blocks at one time. Others add millions of unformatted blocks all at once. Once the problem starts the only way to prevent new unformatted blocks from being added is to rebuild the tablespace.
Changes

No changes to environment.

Cause

It's unknown exactly what triggers the problem.
Solution

This issue has been reported in numerous bugs in both 9i and 10g.

10g Bug 6416822 - MANY EXTENTS ARE ALLOCATED SUDDENLY AND HHWM IS RAISED.

9i Bug 5987262 - TABLESPACE IS ABNORMALLY INCREASED BY UNFORMATTED BLOCKS

The fix is included in the patch for BUG 5890312 - HANG OBSERVED WHILE CREATING CTXCAT INDEX. This bug is not visible to customers.

Maclean Liu(刘相兵

一般这种短期内Unformatted Blocks 迅速增长的问题是 9i、10g中一些仍不明确的space layer代码的bug引起的，目前的看法是10.2.0.5+最新的PSU一般可以预防该BUG 。

可以通过 shrink/recreate 表(或表分区)和索引(或索引分区)来workaround这个问题，事实上我感觉这个Bug的触发条件并不简单，也就是说出现概率并不高；但如果您觉得自己不是那么幸运，那么还尽快将您的产品数据库升级到10.2.0.4.4以上或者10.2.0.5(我们是激进派)

DataKW

好的，非常非常感谢刘大！