暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Oracle的存储信息-数据文件信息记录&数据段信息(DBA_SEGMENTS)

原创 eygle 2019-10-14
2666

数据文件信息记录(DBA_DATA_FILES)


我们说DBA_TABLESPACES记录的是表空间的逻辑信息,而DBA_DATA_FILES视图则记录的是物理数据文件的信息,这些信息包括数据文件的名称、大小、所属表空间等信息:

SQL> select file_id,relative_fno,file_name,tablespace_name,round(bytes/1024/1024/1024,2) GB
  2  from dba_data_files;
FILE_ID RELATIVE_FNO FILE_NAME                                          TABLESPA      GB
-------- ------------ -------------------------------------------------- -------- -------
       1            1 +DATADG/smsboss/datafile/system.261.577731551      SYSTEM       .98
       2            2 +DATADG/smsboss/datafile/undotbs1.260.577731573    UNDOTBS1    5.86
       3            3 +DATADG/smsboss/datafile/sysaux.262.577731593      SYSAUX      1.07
       4            4 +DATADG/smsboss/datafile/users.256.577731607       USERS        .10
       5         1024 +DATADG/smsboss/datafile/bossmgr.271.577809233     BOSSMGR   205.08
       6            6 +DATADG/smsboss/datafile/dbmon.5417.595530625      DBMON        .01
 
6 rows selected.


对于使用文件系统的数据库,如果希望降低数据库在操作系统上的空间耗用,可以通过Resize Datafile的方式,数据文件能够Resize的最小大小依赖于数据文件上的对象存储。



数据段信息(DBA_SEGMENTS)


段(Segment)是Oracle中数据对象的存在形式,可以通过DBA_SEGMENTS查询数据库中段的信息,这些信息包括对象名称、类型、所属表空间、空间使用等信息。下面通过一个简单的查询来统计不同表空间中对象的空间使用情况:

SQL> select tablespace_name,count(*) nobjects,round(sum(bytes)/1024/1204/1024,2) GB,
  2  sum(blocks),sum(extents) from dba_segments
  3  group by rollup(tablespace_name);
TABLESPA   NOBJECTS      GB SUM(BLOCKS) SUM(EXTENTS)
-------- ---------- ------- ----------- ------------
BOSSMGR        1208  169.70     6538080        40863
DBMON             7     .00          44            8
SYSAUX         3162     .79       30344         4723
SYSTEM         1255     .70       26842         2613
UNDOTBS1         10     .04        1352           40
USERS             4     .01         212           23
               5646  171.22     6596874        48270
 
7 rows selected.


可以注意到,对于最大的一个表空间BOSSMGR,通过上一节的查询,这个表空间的数据文件占用了205GB的空间,而真正被使用的空间约为170GB。


这里引入一个新的概念:高水位标记(High Water Mark,HWM)。简单来说,对于以MSSM方式管理的Segment,HWM就是指这个Segment中已使用和未使用过的Block的分界线,HWM之上的空间在格式化之前不能被使用,即在HWM以上的数据块均为未格式化的块,这些未格式化的块在格式化之前是不能被insert数据的;在数据库事务中,当请求新的空闲块并且现有空闲列表(free list)中的块不能满足要求时,HWM将会向上移动,然后格式化一组数据块并加入Free List提供使用。在HWM之下的数据块也可能存在空闲情况,当数据被删除之后,数据块被释放重新回到空闲列表,又可以被其他数据变更所使用,HWM通常只会向上增长,不会自动收缩。


图5-7是一个数据段的HWM示意图,描述了HWM及空间使用的关系。


image.png

图5-7  一个数据段的HWM示意图


如果把图5-7扩展到一个数据文件上去,数据文件也存在一个类似的HWM概念,在HWM之上的空间是尚未分配给任何对象的,如果希望缩减数据文件的大小(通过Resize的方式),这个HWM就是数据文件能够收缩到的极限值。


回顾一下之前的数据文件转储信息,我们很容易在段头发现Highwater信息,这个信息就是HWM值:

Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 255
                  last map  0x00000000  #maps: 0      offset: 4128
      Highwater::  0x00c00056  ext#: 0      blk#: 76     ext size: 127


HWM会影响Oracle执行全表扫描时的读取行为,对于全表扫描操作,Oracle必须读取HWM下的所有数据块,如果一个数据表由于DELETE操作删除了大部分记录,但是HWM并不会降低,所以再次执行全表扫描时,Oracle仍然需要读取对象段中的所有数据块,这显然会影响性能,来看以下测试,创建一个测试表,并用autotrace跟踪全表扫描的数据读取:

SQL> connect eygle/eygle
Connected.
SQL> create table test as select * from dba_objects;
Table created.
SQL> set autotrace trace stat
SQL> select count(*) from test;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         80  consistent gets
         77  physical reads


删除数据,刷新Buffer,再次执行全表扫描查询:

SQL> delete from test where rownum <2001;
2000 rows deleted.
SQL> commit;
Commit complete.
SQL> alter session set events 'immediate trace name flush_cache level 1';
Session altered.
SQL> select count(*) from test;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         80  consistent gets
         78  physical reads


注意到两次查询的consistent gets没有改变,这就是HWM的影响。对于通常的对象,我们不太需要关注其HWM的影响,但是如果表的删除操作非常频繁,表中的大部分Block已经为空,那么可能就需要关注其空间及性能问题。


对于FLM下的本地管理表空间,可以通过Analyze数据表的方法来评估HWM下有多少blocks是不包含数据的,以下测试说明了这个过程,创建一个包含10万数据的测试表:

SQL> CREATE TABLE test NOLOGGING
  2  AS
  3  SELECT o.* FROM
  4  (SELECT * FROM dba_objects WHERE ROWNUM <10001) o,
  5  (SELECT * FROM dba_objects WHERE ROWNUM <11) o2
  6  ORDER BY o.object_id;
Table created.


然后分析该表,可以看到该表共使用了1261个Block,有18个空的数据块,这时HWM应该位于1261个数据块附近:

SQL> analyze table test compute statistics;
Table analyzed.
SQL> select table_name,num_rows,AVG_ROW_LEN,BLOCKS,EMPTY_BLOCKS from dba_tables
  2  where table_name='TEST';
TABLE_NAME             NUM_ROWS AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS
-------------------- ---------- ----------- ---------- ------------
TEST                     100000          89       1261           18


删除数据之后,记录数减少,但是Oracle使用的Block数量并不会变化,也就是HWM并未移动:

SQL> delete from test where rownum <50001;
50000 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select table_name,num_rows,AVG_ROW_LEN,BLOCKS,EMPTY_BLOCKS from dba_tables
  2  where table_name='TEST';
TABLE_NAME             NUM_ROWS AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS
-------------------- ---------- ----------- ---------- ------------
TEST                      50000          92       1261           18


可以计算共有多少数据块被使用以及空数据块占HWM下数据块的百分比:

SQL> select count(distinct(substr(rowid,1,15))) blocks from test;
    BLOCKS
----------
       654
SQL> select 654/1261*100 from dual;
654/1261*100
------------
  51.8636003


注意:如果这个比例过高,也并不一定意味着需要调整,如果有新的记录插入该表,那么这些空间是可以重用的。如果表的空间空置率一直偏高,并且数据读取倾向于使用全表扫描,则可以考虑重整表的存储。


对于以ASSM方式管理的Segment,在其段头里会存在两种高水位标记,它们分别是LHWM(Low HWM)和HHWM(High HWM),即低高水位和高高水位,如下所示:

Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 128  
                  last map  0x00000000  #maps: 0      offset: 2716 
      Highwater::  0x028266c9  ext#: 0      blk#: 64     ext size: 128  
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 60   
  mapblk  0x00000000  offset: 0    
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x0282668d  ext#: 0      blk#: 4      ext size: 128

 

可以看到这里高高水位是0x028266c9,低高水位是0x0282668d。

SQL> select getbfno('0x028266c9') bfno from dual;
BFNO
------------------------------
datafile# is:10
datablock is:157385
SQL> select getbfno('0x0282668d ') bfno from dual;
BFNO
------------------------------
datafile# is:10
datablock is:157325


也就是说上述Segment的高高水位是file 10,block 157385;其低高水位是file 10,block 157325。


由于ASSM管理方式下不存在Free List ,所以LHWM和HHWM概念被引入用于实现段空间管理。在ASSM管理模式下,当一个会话向表中插入数据时,数据库首先格式化一个位图块(而不是像以前那样格式化一组数据块),这个位图块代替Free List用于跟踪段中的数据块的状态变化,数据库通过位图块去寻找空闲块并在使用前对其进行格式化。正是由于这个格式化的变化,所以在LHWM和HHWM之间可能存在格式化和未格式化的数据块。


ASSM管理方式下的Segment的HHWM和LHWM的特点是:

(1)     所有在HHWM以上的数据块一定是未格式化的块;

(2)     所在在LHWM以下的数据块一定是格式化的块;

(3)     在LHWM和HHWM之间的数据快既可能是格式化的块,也可能是未格式化的块;

(4)     Oracle在全表扫描一个Segment的时候,会一直扫描到HHWM为止;


如果一个Segment的LHWM下出现了未格式化的块,那么Oracle在访问这个表的时候会报错ORA-08103,此时数据读取、建索引等操作将不能正常执行。下面来构造一个ORA-08103的例子,深入理解一下Oracle的内部原理。

对于以下这种以CTAS方式创建的表而言,其初始的LHWM是等于HHWM的:

SQL>create table t1 tablespace users as select * from dba_objects where rownum<1001;
Table created.
SQL>select count(*) from t1;
  COUNT(*)
----------
      1000
SQL>set wrap off
SQL>set linesize 2000
SQL>select dbms_rowid.rowid_relative_fno(rowid)||'_'|| \
2 dbms_rowid.rowid_block_number(rowid) location,count(*) from t1
3 group by dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid);
LOCATION                           COUNT(*)
-------------------------------- ----------
9_3724                                   70
9_3725                                   70
9_3726                                   69
9_3727                                   69
9_3728                                   69
9_3729                                   69
9_3730                                   69
9_3731                                   70
9_3732                                   70
9_3733                                   69
9_3734                                   69
9_3735                                   69
9_3736                                   69
9_3738                                   70
9_3739                                   29


现在手工将上述表t1的LHWM下的数据块(9_3739,即file 9,block 3739)用dd命令清零,即达到了在LHWM下构造一个未格式化的数据块的目的:

SQL>conn / as sysdba;
Connected.
SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
[P550]#dd if=/dev/zero of=/dras20/testdb/users01.dbf bs=8192 seek=3739 count=1 conv=notrunc
1+0 records in.
1+0 records out.
[P550]#sqlplus '/ as sysdba';
 
SQL*Plus: Release 9.2.0.6.0 - Production on Tue Dec 21 10:26:49 2010
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to an idle instance.
 
SQL>startup pfile=/dras20/testdb/inittestdb.ora
ORACLE instance started.
 
Total System Global Area  504858456 bytes
Fixed Size                   743256 bytes
Variable Size             285212672 bytes
Database Buffers          218103808 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.


重新启库后执行查询会发现ORA-08103已经如期而至:

SQL>select count(*) from scott.t1;
select count(*) from scott.t1
                           *
ERROR at line 1:
ORA-08103: object no longer exists
 
SQL>create unique index scott.idx_t1 on scott.t1(object_id);
create unique index scott.idx_t1 on scott.t1(object_id)
                                          *
ERROR at line 1:
ORA-08103: object no longer exists


ORA-08103是指,当扫描数据块读取LHWM下的所有数据块时,发现某个数据块上不存在表信息(或者索引信息等)时,即违反了数据库的一致性,随即抛出错误,提示数据块上本应该存在的对象“消失”了。


转储一个数据块,我们可以看到数据块头部存在的元数据信息,以下示例选择了OBJ$对象,转储其中的一个数据块:

SQL> col segment_name for a15
SQL> select segment_name,file_id,block_id from dba_extents where segment_name='OBJ$';
SEGMENT_NAME       FILE_ID   BLOCK_ID
--------------- ---------- ----------
OBJ$                     1      63497
OBJ$                     1      50569
OBJ$                     1      38793
OBJ$                     1      34569
OBJ$                     1      30345
OBJ$                     1      29857
OBJ$                     1      29081
OBJ$                     1      27553
OBJ$                     1      26769
OBJ$                     1      19993
OBJ$                     1      16737
 
SQL> alter system dump datafile 1 block 63506;
 
System altered.


然后找到跟踪文件:

SQL> show parameter dump
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /u01/admin/cndd/udump
p55a@/home/oracle> cd /u01/admin/cndd/udump
p55a@/u01/admin/cndd/udump> ls -sort|tail -1
  72 -rw-r-----   1 oracle        71010 Feb 11 10:53 cndd_ora_590288.trc


摘录一下数据块头部的信息,可以看到其中重要的内容:

Block header dump:  0x0040f812
 Object id on Block? Y
 seg/obj: 0x12  csc: 0x01.80e716f4  itc: 1  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01


这里的对象号12是16进制,即10进制的18,对应的对象就是OBJ$,ORA-08103错误就是指找不到数据块头部的这些对象信息:

SQL> select object_name from dba_objects where object_id=18;
OBJECT_NAME
--------------------------------------------------------------------------------
OBJ$


 数据块接下来的部分记录了ITL、表数量、行数量及偏移量信息等:

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.000.00118ac4  0x00801d7b.b3ec.21  --U-    1  fsc 0x0000.80e716f5
 
data_block_dump,data header at 0x11041a044
===============
tsiz: 0x1fb8
hsiz: 0xc8
pbl: 0x11041a044
bdba: 0x0040f812
     76543210
flag=--------
ntab=1
nrow=91
frre=1
fsbo=0xc8
fseo=0x699
avsp=0x5d3
tosp=0x5d3
0xe:pti[0]      nrow=91 offs=0
0x12:pri[0]     offs=0x1f5e
0x14:pri[1]     sfll=2
0x16:pri[2]     sfll=4
0x18:pri[3]     offs=0x1f10
0x1a:pri[4]     sfll=5
0x1c:pri[5]     sfll=7
0x1e:pri[6]     offs=0x1ec5
0x20:pri[7]     sfll=61
0x22:pri[8]     offs=0x1e6e
0x24:pri[9]     offs=0x1e15
0x26:pri[10]    offs=0x1dbb
0x28:pri[11]    offs=0x1d6c
0x2a:pri[12]    offs=0x1d1f
0x2c:pri[13]    offs=0x1cd2
0x2e:pri[14]    offs=0x1c80
0x30:pri[15]    offs=0x1c39
。。。。。。。。。。。
block_row_dump:
tab 0, row 0, @0x1f5e
tl: 90 fb: --H-FL-- lb: 0x0  cc: 17
col  0: [ 4]  c3 07 2e 4c
col  1: [ 4]  c3 07 2e 4c
col  2: [ 2]  c1 3a
col  3: [27]
 43 4f 4e 54 52 41 43 54 54 42 4c 5f 42 41 43 4b 5f 50 52 49 4d 41 52 59 4b
 45 59
col  4: [ 2]  c1 05
col  5: *NULL*
col  6: [ 2]  c1 02
col  7: [ 7]  78 69 0b 18 10 1a 08
col  8: [ 7]  78 69 0b 18 10 1a 08
col  9: [ 7]  78 69 0b 18 10 1a 08
col 10: [ 2]  c1 02
col 11: *NULL*
col 12: *NULL*
col 13: [ 1]  80
col 14: *NULL*
col 15: [ 1]  80
col 16: [ 4]  c3 07 38 24


以上的这部分信息,在数据块内部,被定义为块头(Block Header)信息,这些信息和数据结构是非常重要的,例如通过ITL数据块实现了行级锁定,下图是常规的数据块结构图:


image.png


了解了以上原理,对于一些故障的判断也就可以从容的进行了,在某些情况下,truncate对于读取的会话同样会产生ORA-08103的错误,Oracle也有些Bug与此有关。

 

对于ASSM管理的Segment,可以通过Oracle提供的系统包DBMS_SPACE直接来计算其空间使用情况,为方便使用,可以创建如下过程:


CREATE   OR REPLACE PROCEDURE show_space_assm (
   p_segname     IN   VARCHAR2,
   p_owner       IN   VARCHAR2 DEFAULT USER,
   p_type        IN   VARCHAR2 DEFAULT 'TABLE'
)
AS
   l_fs1_bytes            NUMBER;
   l_fs2_bytes            NUMBER;
   l_fs3_bytes            NUMBER;
   l_fs4_bytes            NUMBER;
   l_fs1_blocks           NUMBER;
   l_fs2_blocks           NUMBER;
   l_fs3_blocks           NUMBER;
   l_fs4_blocks           NUMBER;
   l_full_bytes           NUMBER;
   l_full_blocks          NUMBER;
   l_unformatted_bytes    NUMBER;
   l_unformatted_blocks   NUMBER;
 
   PROCEDURE p (p_label IN VARCHAR2, p_num IN   NUMBER)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (RPAD (p_label,   40, '.') || p_num);
   END;
BEGIN
   DBMS_SPACE.space_usage (segment_owner           => p_owner,
                           segment_name            => p_segname,
                           segment_type            => p_type,
                           fs1_bytes               => l_fs1_bytes,
                           fs1_blocks              => l_fs1_blocks,
                           fs2_bytes               => l_fs2_bytes,
                           fs2_blocks              => l_fs2_blocks,
                           fs3_bytes               => l_fs3_bytes,
                           fs3_blocks              => l_fs3_blocks,
                           fs4_bytes               => l_fs4_bytes,
                           fs4_blocks              => l_fs4_blocks,
                           full_bytes              => l_full_bytes,
                           full_blocks             => l_full_blocks,
                             unformatted_blocks      =>   l_unformatted_blocks,
                             unformatted_bytes       =>   l_unformatted_bytes
                          );
   p ('free space 0-25% Blocks:',   l_fs1_blocks);
   p ('free space 25-50% Blocks:',   l_fs2_blocks);
   p ('free space 50-75% Blocks:',   l_fs3_blocks);
   p ('free space 75-100% Blocks:',   l_fs4_blocks);
   p ('Full Blocks:', l_full_blocks);
   p ('Unformatted blocks:',   l_unformatted_blocks);
END;
/

   


通过这个过程可以计算ASSM下表中空间的使用情况。


同上例,创建测试表并删除部分数据:

SQL> CREATE TABLE test NOLOGGING
  2  AS
  3  SELECT o.* FROM
  4  (SELECT * FROM dba_objects WHERE ROWNUM <10001) o,
  5  (SELECT * FROM dba_objects WHERE ROWNUM <11) o2
  6  ORDER BY o.object_id;
Table created.
 
SQL> analyze table test compute statistics;
Table analyzed.
 
SQL> select table_name,num_rows,AVG_ROW_LEN,BLOCKS,EMPTY_BLOCKS from dba_tables
  2  where table_name='TEST';
TABLE_NAME             NUM_ROWS AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS
-------------------- ---------- ----------- ---------- ------------
TEST                     100000          89       1291            5
 
SQL> delete from test where rownum <50001;
50000 rows deleted.
 
SQL> commit;
Commit complete.


现在可以通过show_space_assm过程来计算这个对象的空间使用情况:

SQL> set serveroutput on
SQL> exec show_space_assm('TEST')
free space 0-25% Blocks:................0
free space 25-50% Blocks:...............0
free space 50-75% Blocks:...............0
free space 75-100% Blocks:..............609
Full Blocks:............................654
Unformatted blocks:.....................0


表中Full的数据块共有654个,而75%~100% Free的Block有609个,这说明表中大部分数据块处于空闲和基本空闲状态,也可以直接再验证一下:

SQL> select count(distinct(substr(rowid,1,15))) nums from test;
      NUMS
----------
       655


注意:关于Show_space脚本在网络上有很多个版本,最初的版本来自Thomas Kyte的著作,该脚本的本质是调用DBMS_SPACE系统包,通过简化达到易于使用的目的。


以下是在SAP系统的Oracle数据库中,清理数据表XI_AF_MSG的实际案例。在客户的系统中,该表的存储空间占用了1.8G,除了表数据之外,该表还存在3个LOB字段,其中之一占用了10G的存储空间:

SQL> col segment_name for a30
SQL> select segment_name ,bytes/1024/1024/1024
2  from dba_segments where segment_name='XI_AF_MSG';
SEGMENT_NAME                   BYTES/1024/1024/1024
------------------------------ --------------------
XI_AF_MSG                                1.81640625
 
SQL> select column_name,segment_name from dba_lobs where table_name='XI_AF_MSG';
COLUMN_NAME                    SEGMENT_NAME
------------------------------ ------------------------------
MSG_BYTES                      SYS_LOB0000053939C00020$$
TRAN_HEADER                    SYS_LOB0000053939C00032$$
PP_HASH                        SYS_LOB0000053939C00038$$
SQL> select segment_name ,bytes/1024/1024/1024 GB from dba_segments
  2  where segment_name in (select segment_name from dba_lobs where table_name='XI_AF_MSG');
SEGMENT_NAME                                     GB
---------------------------------------- ----------
SYS_LOB0000053939C00038$$                .000061035
SYS_LOB0000053939C00032$$                .000061035
SYS_LOB0000053939C00020$$                10.8710938


这个表完全使用(Full Blocks)了17万个数据块,75%-100%的数据块有5万个:

SQL> set serveroutput on
SQL> exec show_space_assm('XI_AF_MSG','SAPSR3DB','TABLE');
free space 0-25% Blocks:................7
free space 25-50% Blocks:...............2012
free space 50-75% Blocks:...............3213
free space 75-100% Blocks:..............53591
Full Blocks:............................170657
Unformatted blocks:.....................712


在不断删除了部分数据之后,可以看到Full Blocks的数量降低,75%-100%的块数量增加:

SQL> exec show_space_assm('XI_AF_MSG','SAPSR3DB','TABLE');
free space 0-25% Blocks:................13
free space 25-50% Blocks:...............8
free space 50-75% Blocks:...............18
free space 75-100% Blocks:..............66127
Full Blocks:............................163314
Unformatted blocks:.....................712


自Oracle Database 10g开始,还可以使用Oracle内置的空间管理工具-段顾问(Segement Advisor)来分析指定段,从而Oracle会根据内置的专家系统给出调整建议。


下面调用DBMS_ADVISOR包,来执行建议收集工作:

SQL> VARIABLE taskid number
SQL> DECLARE
  2     NAME     VARCHAR2 (100);
  3     descrb   VARCHAR2 (100);
  4     obj_id   NUMBER;
  5  BEGIN
  6     NAME := 'Check Test Table of EYGLE';
  7     descrb := 'Check EYGLE.TEST';
  8     dbms_advisor.create_task ('Segment Advisor', :taskid, NAME, descrb, NULL);
  9     dbms_advisor.create_object (NAME,
 10                                 'TABLE',
 11                                 'EYGLE',
 12                                 'TEST',
 13                                 NULL,
 14                                 NULL,
 15                                 obj_id
 16                                );
 17     dbms_advisor.set_task_parameter (NAME, 'RECOMMEND_ALL', 'TRUE');
 18     dbms_advisor.execute_task (NAME);
 19  END;
 20  /
 
PL/SQL procedure successfully completed.
 
SQL> set serveroutput on
SQL> print taskid
    TASKID
----------
     49618
 
SQL> select * from dba_advisor_findings where task_id=49618;
OWNER        TASK_ID TASK_NAME                                   FINDING_ID TYPE          PARENT  OBJECT_ID
--------- ---------- ------------------------ ---------- ------  ----------- ----------
IMPACT_TYPE    IMPACT
--------------------------------------
MESSAGE
--------------------------------------------------------------------------------
MORE_INFO
--------------------------------------------------------------------------------
EYGLE         49618 Check Test Table of EYGLE       1 PROBLEM              1          1
 
The free space in the object is less than 10MB.
Allocated Space:10616832: Used Space:6221251: Reclaimable Space :4395581:


提示我们这个表使用的空间数量,以及可以回收的空间数量。如果空间使用存在问题,则可以查询dba_advisor_recommendations获得Oracle的推荐举措,查询dba_advisor_actions获得Oracle推荐采取的行为。当然更为简单的方式是使用Oracle 10g的Database Control,一切都可以通过图形界面简便快速地进行操作。


如果经过评估HWM的问题已经影响到了性能和存储,那么可以有多种措施来解决HWM的问题。


1.导出/导入与Truncate结合

如果要删除的是表中的所有记录,那么使用Truncate可以降低HWM;但是能使用Truncate的情况并不常见,如果表中剩余的是少量数据,可以通过EXP导出数据或者使用CTAS创建一张临时表,然后Truncate表,再导入或INSERT回数据,即可解决HWM问题。但是这种方法对于不间断服务的数据表并不适合。


2.RENAME+INSERT结合

对于连续使用的数据表,如果数据是以写为主的日志类数据,则可以通过RENAME将数据表更名,然后按原来的结构重建数据表,此时插入操作可以恢复,这个过程非常迅速,对于数据库影响极小,然后可以将RENAME表中的数据插入回来,这就完成了数据整理,HWM可以降低。这种方法适用于写为主的业务类型,不适合增删改查频繁的对象。


3.在线重定义(DBMS_REDEFINITION)

从Oracle 9i开始,Oracle引入了在线重定义新特性,通过DBMS_REDEFINITION包可以对表进行在线重定义,如修改字段名称、增加字段等,当然也可以借用这个包进行空间重整。

在重定义过程中,Oracle通过中间的临时表来记录中间变化数据,完成重定义后可以将数据整合到重定义的表中,数据库的正常操作可以继续进行,这种重组方式和方法2中介绍的过程类似,其原理如图5-8所示。


image.png

图5-8  在线重定义示意图


因为数据最终要整合到新的“结果表”中,所以HWM问题可以得到解决。


4.Shrink新特性

不管通过哪一种方法,空间重整一直是一个难题,所以从Oracle 10g开始,Oracle引入了一个新的功能用于支持在线的空间重整,这就是联机段空间回收(Shrinking Database Segments Online)的功能。

联机段收缩仅对ASSM(自动段空间管理)表空间中的表有效,Shrink的本质就是对表执行一系列的DML操作,删除表末端的稀疏行,并在表的顶端重新插入。通过这样的一系列操作,可以填满表段中的“漏洞”空间,逐步将所有剩余空间留在表的末端,然后Oracle可以重置该表的HWM,释放自由空间。由于Shrink是针对数据行进行处理的,在表上会获得行级排他锁,所以并不会影响全表的DML操作,这也是Online的意义所在(但是Shrink可能会产生大量Redo,影响归档量,在操作时需要考虑)。


降低了HWM之后,全表扫描(Full Table Scan)需要扫描的数据块就可以减少,从而性能可以得到提升。图5-9说明了段空间回收的方式:


image.png

图5-9  段空间回收


由于回收段空间需要移动行数据,数据的ROWID会发生变化,索引会被同时维护,也因此在执行Shrink之前,需要设置表的ENABLE ROW MOVEMENT属性:

alter table <table_name> enable row movement;
alter table <table_name> shrink space;


但是需要注意的是,由于段空间重整是通过DML操作来完成的,所以会产生额外的redo,这个redo信息需要评估,如果数据表非常大,那么产生的redo可能是无法接受的。来看一个测试,执行以下命令将创建一个数据量为100万的数据表:

SQL> create table t tablespace eygle nologging
  2  as
  3  select o.* from
  4  (select * from dba_objects where rownum <10001) o,
  5  (select * from dba_objects where rownum <101) o2
  6  order by o.object_id;


这个表约耗用了100MB的空间:

SQL> select bytes/1024/1024 from user_segments where segment_name='T';
BYTES/1024/1024
---------------
         99.875


查询一下记录全表扫描的读取统计信息:

SQL> select count(*) from t;
Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
      12839  consistent gets
      12623  physical reads


删除表中部分数据:

SQL> delete from t where mod(object_id,2)=0;
499800 rows deleted.
Elapsed: 00:03:13.67
SQL> commit;
Commit complete.


此时查询,再次执行全表扫描(注意第一次查询可能受延迟块清除影响,再次执行可用获得准确的全表扫描信息统计):

SQL> select count(*) from t;
Statistics
----------------------------------------------------------
        189  recursive calls
          0  db block gets
      12855  consistent gets
      10157  physical reads


注意到由于HWM的影响,全表扫描仍然需要读取所有的数据块。接下来执行Shrink操作,同时记录一下redo信息:

SQL> alter table t enable row movement;
Table altered.
SQL> select a.name,b.value    
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic# and a.name='redo size';
NAME                                VALUE
------------------------------ ----------
redo size                       228555548
SQL> alter table t shrink space;
Table altered.
SQL> select a.name,b.value    
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic# and a.name='redo size';
NAME                                VALUE
------------------------------ ----------
redo size                       421362176
SQL> select (421362176 - 228555548)/1024/1024 from dual;
(421362176-228555548)/1024/1024
-------------------------------
                     183.874729


注意到为了收缩这个空间,共产生了近200MB的redo信息,如果表上包含索引,那么Shrink产生的redo将会更多。所以能否在生产环境上对大数据量的表使用这一特性仍然是值得斟酌的。以上数据表Shrink之后空间耗用下降到47MB左右。

SQL> select bytes/1024/1024 from user_segments where segment_name='T';
BYTES/1024/1024
---------------
         46.875


那么再看此时的全表扫描的逻辑读:

SQL> select count(*) from t;
Statistics
----------------------------------------------------------
        181  recursive calls
          0  db block gets
       6066  consistent gets
       5268  physical reads


注意到逻辑读此时已成功地降低下来。

 

继续前面的实践案例,在清理了XI_AF_MSG表之后,对该表进行SHRINK SPACE,释放其存储空间,降低HWM,注意这个SHRINK动作是非常缓慢的,该表的收缩动作用时1小时左右:

SQL> alter table sapsr3db.xi_af_msg enable row movement;
Table altered.
 
Elapsed: 00:00:00.14
SQL> alter table sapsr3db.xi_af_msg shrink space ;
Table altered.
 
Elapsed: 01:03:17.09


在收缩过程中,用show_space_assm 检查空间使用情况,可以该表使用数据块的数量不断减少:

SQL> exec show_space_assm('XI_AF_MSG','SAPSR3DB','TABLE');
free space 0-25% Blocks:................1
free space 25-50% Blocks:...............2054
free space 50-75% Blocks:...............2970
free space 75-100% Blocks:..............117344
Full Blocks:............................107111
Unformatted blocks:.....................712
 
SQL> exec show_space_assm('XI_AF_MSG','SAPSR3DB','TABLE');
free space 0-25% Blocks:................1
free space 25-50% Blocks:...............2054
free space 50-75% Blocks:...............2970
free space 75-100% Blocks:..............117369
Full Blocks:............................107086
Unformatted blocks:.....................712


最终该表仅占用了近9万个数据块,空闲空间被释放了出去:

SQL> exec show_space_assm('XI_AF_MSG','SAPSR3DB','TABLE');
free space 0-25% Blocks:................5
free space 25-50% Blocks:...............0
free space 50-75% Blocks:...............0
free space 75-100% Blocks:..............1
Full Blocks:............................88744
Unformatted blocks:.....................0


5.Move对象

通过Move操作移动对象,可以降低HWM,但是Move之后,索引需要重建,而且在Move的过程中会影响在线应用,所以这种方法的使用较为有限。


继续之前的案例,为了回收LOB对象占用的大量存储空间,我们对LOB对象进行了一次在线MOVE操作,该MOVE操作花费了4个小时时间,将大对象的存储空间降低到6G左右:

SQL> alter table sapsr3db.xi_af_msg move lob(MSG_BYTES) store as (tablespace PSAPLOB);
Table altered.
 
Elapsed: 04:04:47.04
SQL> select segment_name,bytes/1024/1024 MB from dba_segments where tablespace_name='PSAPLOB';
SEGMENT_NAME                                     MB
---------------------------------------- ----------
SYS_IL0000053939C00020$$                      .0625
SYS_LOB0000053939C00020$$                      6079
在MOVE的过程中(Move完成之前),新的表空间中的LOB对象以临时段的形态存在:
SQL> select segment_name,bytes/1024/1024 MB
2  from dba_segments where tablespace_name='PSAPLOB';
SEGMENT_NAME                           MB
------------------------------ ----------
37.11                                 168
37.19                               .0625


OVE操作会使相关索引失效,需要检查状态,重建相关索引:

SQL> select index_name,status,last_analyzed from dba_indexes where table_name='XI_AF_MSG';
INDEX_NAME                     STATUS           LAST_ANALYZE
------------------------------ ---------------- ------------
SYS_IL0000053939C00020$$       VALID
SYS_IL0000053939C00032$$       VALID
SYS_IL0000053939C00038$$       VALID
I_XI_AF_STARTUP_1              UNUSABLE         04-MAR-11
IND_SENT_RECV_TIME             UNUSABLE         04-MAR-11
I_XI_AF_EOIO                   UNUSABLE         04-MAR-11
I_XI_AF_STARTUP_3              UNUSABLE         04-MAR-11
I_XI_AF_STARTUP_2              UNUSABLE         04-MAR-11
SYS_C00142245                  UNUSABLE         07-MAR-11
SQL> alter index I_XI_AF_STARTUP_1 rebuild online;
 
Index altered.


6.其他的考虑

空间的重整始终是一个难题,各种方法都是以一定的性能牺牲为代价的,这就使得很多方法在实际的生产环境中并不可用。所以更好的办法应该是从应用入手、从规划入手,从最开始就能够避免一些可能出现的问题。


在最常采用的方法中,分区是一个常用的手段,涉及大量数据变更的数据表,很多可以通过分区来处理,由于分区表可以针对分区进行诸如DROP、TRUNCATE等操作,从而可以很容易地对分区进行维护,进而解决一系列的空间问题。


当然分区并不是万能的,其适用环境也是有限,所以真正能够解决问题的方法还是来自我们自己,通过对Oracle各种技术手段的认识和了解之后,我们才能够制定出适合我们需要的空间维护手段。

最后修改时间:2019-10-14 17:10:29
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论