精华内容
下载资源
问答
  • Oracle 降低高水位线的方法

    千次阅读 2019-03-25 17:58:01
    高水位(HIGH WARTER MARK,HWM)好比水库中储水的水位,用于描述数据库中段的扩展方式。高水位对全表扫描方式有着至关重要的影响。当使用DELETE删除表记录时,高水位并不会下降,随之导致的是全表扫描的实际开销并...

    高水位(HIGH WARTER MARK,HWM)好比水库中储水的水位,用于描述数据库中段的扩展方式。高水位对全表扫描方式有着至关重要的影响。当使用DELETE删除表记录时,高水位并不会下降,随之导致的是全表扫描的实际开销并没有任何减少。

    例如,首先新建一张空表,大小占用64K,然后插入数据直到表大小变为50G,此时使用DELETE删除所有的数据并且提交,这个时候查询表的大小的时候依然是50G,这就是因为表的高水位没有释放的缘故,而在这时如果使用“SELECT * FROM TABLE_NAME;”语句来查询数据的话,那么查询过程就会很慢,因为Oracle要执行全表扫描,从高水位下所有的块都得去扫描,直到50G的所有块全部扫描完毕。曾遇到一个同事使用DELETE删除了一个很大的分区表,然后执行SELECT查询很久都没有结果,以为是数据库HANG住了,其实这个问题是由于高水位的缘故。所以,表执行了TRUNCATE操作,再次SELECT的时候就可以很快返回结果了。

    释放表的高水位通常有如下几种办法:

    (1)对表进行MOVE操作:ALTER TABLE TABLE_NAME MOVE;。若表上存在索引,则记得重建索引。

    (2)对表进行SHRINK SPACE操作:ALTER TABLE TABLE_NAME SHRINK SPACE;,注意,在执行该指令之前必须开启行移动:ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;。该方法的优点是:在碎片整理结束后,表上相关的索引仍然有效,缺点是会产生大量的UNDOREDO

    (3)复制要保留的数据到临时表T,DROP原表,然后RENAME临时表T为原表。

    (4)exp/imp或expdp/impdp重构表。

    (5)若表中没有数据则直接使用TRUNCATE来释放高水位。


    如何找出系统中哪些表拥有高水位呢?这里给出两种办法,①比较表的行数和表的大小关系。如果行数为0,而表的当前占用大小减去初始化时的大小(INITIAL_EXTENT)后依然很大,那么说明该表有高水位。②行数和块数的比率,即查看一个块可以存储多少行数据。如果一个块存储的行数少于5行甚至更少,那么说明有高水位。注意,这两种方法都不是十分准确,需要再对查询结果进行筛选。需要注意的是,在查询表的高水位时,首先需要分析表,以得到最准确的统计信息。

    下面给出用于查询高水位的几个SQL语句:

    SELECT D.OWNER,

           ROUND(D.NUM_ROWS / D.BLOCKS, 2),

           D.NUM_ROWS,

           D.BLOCKS,

           D.TABLE_NAME,

     ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024)  t_size

      FROM DBA_TABLES D

     WHERE D.BLOCKS > 10

       AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5

     AND d.OWNER NOT LIKE '%SYS%' ;

    或:

    SELECT OWNER,

           SEGMENT_NAME TABLE_NAME,

           SEGMENT_TYPE,

           GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /

                          GREATEST(NVL(HWM, 1), 1)),

                          2),

                    0) WASTE_PER

      FROM (SELECT A.OWNER OWNER,

                   A.SEGMENT_NAME,

                   A.SEGMENT_TYPE,

                   B.LAST_ANALYZED,

                   A.BYTES,

                   B.NUM_ROWS,

                   A.BLOCKS BLOCKS,

                   B.EMPTY_BLOCKS EMPTY_BLOCKS,

                   A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,

                   DECODE(ROUND((B.AVG_ROW_LEN * NUM_ROWS *

                                (1 + (PCT_FREE / 100))) / C.BLOCKSIZE,

                                0),

                          0,

                          1,

                          ROUND((B.AVG_ROW_LEN * NUM_ROWS *

                                (1 + (PCT_FREE / 100))) / C.BLOCKSIZE,

                                0)) + 2 AVG_USED_BLOCKS,

                   ROUND(100 *

                         (NVL(B.CHAIN_CNT, 0) / GREATEST(NVL(B.NUM_ROWS, 1), 1)),

                         2) CHAIN_PER,

                   B.TABLESPACE_NAME O_TABLESPACE_NAME

              FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C

             WHERE A.OWNER = B.OWNER

               AND SEGMENT_NAME = TABLE_NAME

               AND SEGMENT_TYPE = 'TABLE'

               AND B.TABLESPACE_NAME = C.NAME)

     WHERE GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /

                          GREATEST(NVL(HWM, 1), 1)),

                          2),

                    0) > 50

       AND OWNER NOT LIKE '%SYS%'

       AND BLOCKS > 100

     ORDER BY WASTE_PER DESC;

    最后再次提醒各位读者,若表执行了大量的DELETE操作后,则最好回收一下表的高水位。


     



    http://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#CNCPT89022
     

    Segment Space and the High Water Mark

    To manage space, Oracle Database tracks the state of blocks in the segment. The high water mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used.

    MSSM uses free lists to manage segment space. At table creation, no blocks in the segment are formatted. When a session first inserts rows into the table, the database searches the free list for usable blocks. If the database finds no usable blocks, then it preformats a group of blocks, places them on the free list, and begins inserting data into the blocks. In MSSM, a full table scan reads allblocks below the HWM.

    ASSM does not use free lists and so must manage space differently. When a session first inserts data into a table, the database formats a single bitmap block instead of preformatting a group of blocks as in MSSM. The bitmap tracks the state of blocks in the segment, taking the place of the free list. The database uses the bitmap to find free blocks and then formats each block before filling it with data. ASSM spread out inserts among blocks to avoid concurrency issues.

    Every data block in an ASSM segment is in one of the following states:

    • Above the HWM

      These blocks are unformatted and have never been used.

    • Below the HWM

      These blocks are in one of the following states:

      • Allocated, but currently unformatted and unused

      • Formatted and contain data

      • Formatted and empty because the data was deleted

    Figure 12-23 depicts an ASSM segment as a horizontal series of blocks. At table creation, the HWM is at the beginning of the segment on the left. Because no data has been inserted yet, all blocks in the segment are unformatted and never used.

    Figure 12-23 HWM at Table Creation

    De.ion of Figure 12-23 follows
    Description of "Figure 12-23 HWM at Table Creation"

    段空间和高水位标记

    oracle数据库通过跟踪段中的块状态来管理空间。高水位标记(HWM)是段中的一个点,超过该点的数据块是未格式化和未使用过的。

    MSSM使用空闲列表来管理段空间。在创建表时,段中的块并未被格式化。当一个会话初次向表中插入行时,数据库将搜索空闲列表来查找可用的块。如果数据库未找到可用的块,那么它会预格式化一组块,并将它们放置在空闲列表中,并开始将数据插入到块中。在MSSM中,全表扫描会读取HWM之下的所有块。

    ASSM不使用空闲列表,所以必须以不同的方式管理空间。当会话初次向表中插入数据时,数据库只格式化一个单一位图块,而不像在MSSM中那样预格式化一组块。位图取代了空闲列表,用于跟踪在段中的块的状态。数据库使用位图来查找可用的块,然后在往块写入数据之前将其格式化。ASSM将插入操作分散到多个块,以避免并发问题。

    在一个ASSM段中的每个数据块处于以下状态之一:

    HWM之上

        这些块是未格式化的,且从未使用过。

    HWM之下

    这些块处于以下状态之一:

    已分配,但当前未格式化且未使用

    已格式化且包含数据

    已格式化且为空,因为数据已被删除

    12-23将一个ASSM段描述为一系列水平的块。在创建表时,HWM在左侧段的开头。因为还未插入数据,段中的所有块都还是未格式化且从未使用过。

    图将12-23在创建表时的HWM


     

    Suppose that a transaction inserts rows into the segment. The database must allocate a group of blocks to hold the rows. The allocated blocks fall below the HWM. The database formats a bitmap block in this group to hold the metadata, but does not preformat the remaining blocks in the group.

    In Figure 12-24, the blocks below the HWM are allocated, whereas blocks above the HWM are neither allocated or formatted. As inserts occur, the database can write to any block with available space. The low high water mark (low HWM) marks the point below which all blocks are known to be formatted because they either contain data or formerly contained data.

    Figure 12-24 HWM and Low HWM

    De.ion of Figure 12-24 follows
    Description of "Figure 12-24 HWM and Low HWM"

    In Figure 12-25, the database chooses a block between the HWM and low HWM and writes to it. The database could have just as easily chosen any other block between the HWM and low HWM, or any block below the low HWM that had available space. In Figure 12-25, the blocks to either side of the newly filled block are unformatted.

    Figure 12-25 HWM and Low HWM

    De.ion of Figure 12-25 follows
    Description of "Figure 12-25 HWM and Low HWM"

    The low HWM is important in a full table scan. Because blocks below the HWM are formatted only when used, some blocks could be unformatted, as in Figure 12-25. For this reason, the database reads the bitmap block to obtain the location of the low HWM. The database reads all blocks up to the low HWM because they are known to be formatted, and then carefully reads only the formatted blocks between the low HWM and the HWM.

    Assume that a new transaction inserts rows into the table, but the bitmap indicates that insufficient free space exists under the HWM. In Figure 12-26, the database advances the HWM to the right, allocating a new group of unformatted blocks.

    Figure 12-26 Advancing HWM and Low HWM

    De.ion of Figure 12-26 follows
    Description of "Figure 12-26 Advancing HWM and Low HWM"

    When the blocks between the HWM and low HWM are full, the HWM advances to the right and the low HWM advances to the location of the old HWM. As the database inserts data over time, the HWM continues to advance to the right, with the low HWM always trailing behind it. Unless you manually rebuild, truncate, or shrink the object, the HWM never retreats.

    See Also:

    假设一个事务将行插入到段中。数据库必须分配一组块来容纳这些行。已分配的块在HWM之下。数据库格式化该组中的一个位图块来容纳元数据,但不会预格式化组中其余的块。

    在图12-24中,HWM之下的块是已分配的,而HWM之上的块是既未分配也未格式化的。插入发生时,数据库可以写入到任何具有可用空间的块。由低位高水位标记(HWM)标记一个点,该点之下的所有块都已知是已格式化的,要么包含数据,或以前曾包含数据。

    在图12-25中,数据库选定了HWM和低HWM之间的一个块,并往其中写入数据。数据库也可能会随意选择HWM和低HWM之间的任何其他块,或低HWM之下任何有可用空间的块。图12-25中,在新填充块两边的块都还是未格式化的。

    HWM在全表扫描中非常重要。因为HWM之下的块只在被使用时才格式化,所以可能还有一些块是未被格式化的,如图12-25所示。鉴于此,数据库读取位图块,以获取低HWM的位置。数据库读取低HWM之下的所有块,因为已知它们是已格式化的,然后仅仔细读取位于低 HWM 和 HWM 之间的已格式化块。

    假定一个新事务将行插入到该表,但位图指示在HWM之下没有足够的可用空间。在图12-26中,数据库向右推进HWM,分配一组新的未格式化块。

    HWM与低HWM之间的块填满时,HWM向右推进,而低HWM相应推进到旧的HWM的位置。数据库不断插入数据,随着时间的推移,HWM继续向右推进,而低HWM总尾随其后。除非您手动重建、截断、或缩小该对象,否则HWM从不倒退。

     


     




    备注:
    1)move不但可以重置水位线(HWM),解决松散表带来的 IO 浪费,还可以解决表中的行迁移问题。
        move表的话需要双倍的空间,否则无法成功。move表可以通过重新安排数据文件的空间达到收缩数据文件的目的。
        move表时,会产生exclusive lock 锁,此时只能对表进行 select 操作。
        move表之后,如果表上有索引,记得重建。
    2)shrink表只对ASSM管理的表有效,相关命令有:
        -----alter table TABLE_NAME shrink space;    整理碎片并回收空间
        -----alter table TABLE_NAME shrink space compact;     只整理碎片,不回收空间
         -----alter table TABLE_NAME shrink space cascate;      整理碎片回收空间,并连同表的级联对象一起整理(比如索引)
       能在线进行,不影响表上的DML操作,当然,并发的DML操作在shrink结束的时刻会出现短暂的block
       shrink的另外一个优点是在碎片整理结束后,表上相关的index仍然enable
    3)move的操作速度远远快于shrink 操作 ,不是一般的快,不是一个数量级的,而且shrink 会产生大量的undo 和redo 操作。
    4)truncate是DDL操作,相当于删表后重建。
    5)还有其他的方法,如导出后再重新导入。

    准备工作,创建一张表:

    CREATE TABLE TEST2
    (
        ID    NUMBER(10),
        NAME  VARCHAR2(32)
    );

    1、移动表:
    SQL> begin
      2  for i in 1..10000 loop
      3  insert into test2 values(i,'bbb');
      4  end loop;
      5  commit;
      6  end;
      7  /
    PL/SQL procedure successfully completed.

    SQL>  select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
    TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
    ------------------------------ ------------------------------ ---------- ------------
    TEST2                          LXM                                   244           12

    SQL> delete test2;
    100000 rows deleted.

    SQL> alter table test2 move;
    Table altered.

    SQL> analyze table test2 compute statistics;
    Table analyzed.

    SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';


    TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
    ------------------------------ ------------------------------ ---------- ------------
    TEST2                          LXM                                     0            8

    2、收缩表:
    SQL>    begin
      2  for i in 1..100000 loop
      3  insert into test2 values(i,'kkk');
      4  end loop;
      5  commit;
      6  end;
      7  /
    PL/SQL procedure successfully completed.

    SQL> analyze table test2 compute statistics;
    Table analyzed.

    SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
    TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
    ------------------------------ ------------------------------ ---------- ------------
    TEST2                          LXM                                   244           12

    SQL> delete test2;
    100000 rows deleted.

    SQL> alter table test2 shrink space;
    alter table test2 shrink space
    *
    ERROR at line 1:
    ORA-10636: ROW MOVEMENT is not enabled

    SQL> alter table test2 enable row movement;
    Table altered.

    SQL> alter table test2 shrink space;
    Table altered.

    SQL> analyze table test2 compute statistics;
    Table analyzed.

    SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
    TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
    ------------------------------ ------------------------------ ---------- ------------
    TEST2                          LXM                                     1            7

    3、truncate表
    SQL> begin
      2  for i in 1..100000 loop
      3  insert into test2 values(i,'kkk');
      4  end loop;
      5  commit;
      6  end;
      7  /
    PL/SQL procedure successfully completed.

    SQL> analyze table test2 compute statistics;
    Table analyzed.

    SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
    TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
    ------------------------------ ------------------------------ ---------- ------------
    TEST2                          LXM                                   244           12

    SQL> truncate table test2;
    Table truncated.

    SQL> analyze table test2 compute statistics;
    Table analyzed.

    SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
    TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
    ------------------------------ ------------------------------ ---------- ------------
    TEST2                          LXM                                     0            8


    4、新建临时表,然后rename
    SQL> begin
      2  for i in 1..100000 loop
      3  insert into test2 values(i,'kkk');
      4  end loop;
      5  commit;
      6  end;
      7  /
    PL/SQL procedure successfully completed.

    SQL> delete test2;
    100000 rows deleted.

    SQL> analyze table test2 compute statistics;
    Table analyzed.

    SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
    TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
    ------------------------------ ------------------------------ ---------- ------------
    TEST2                          LXM                                   244           12

    SQL> create table test3 as select * from test2;
    Table created.

    SQL> drop table test2;
    Table dropped.

    SQL> alter table test3 rename to test2;
    Table altered.

    SQL> analyze table test2 compute statistics;
    Table analyzed.

    SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
    TABLE_NAME                     TABLESPACE_NAME                    BLOCKS EMPTY_BLOCKS
    ------------------------------ ------------------------------ ---------- ------------
    TEST2                          LXM                                     0            0




    ==================================================
    空表移动无须重建索引:
    SQL> begin
      2  for i in 1..10000 loop
      3  insert into test2 values(i,'bbb');
      4  end loop;
      5  commit;
      6  end;
      7  /
    PL/SQL procedure successfully completed.

    SQL> select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes; 
    INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     STATUS   INITIAL_EXTENT MAX_EXTENTS
    ------------------------------ --------------------------- ------------------------------ -------- -------------- -----------
    IND_TEST2                      NORMAL                      TEST2                          VALID             65536  2147483645

    SQL> delete test2 where id=1;
    1 row deleted.
    SQL> alter table test2 move;
    Table altered.

    SQL> select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes;
    INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     STATUS   INITIAL_EXTENT MAX_EXTENTS
    ------------------------------ --------------------------- ------------------------------ -------- -------------- -----------
    IND_TEST2                      NORMAL                      TEST2                          UNUSABLE          65536  2147483645

    SQL> alter index ind_test2 rebuild;
    Index altered.

    SQL> select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes;
    INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     STATUS   INITIAL_EXTENT MAX_EXTENTS
    ------------------------------ --------------------------- ------------------------------ -------- -------------- -----------
    IND_TEST2                      NORMAL                      TEST2                          VALID             65536  2147483645

    SQL> delete test2;
    9999 rows deleted.
    SQL> alter table test2 move;
    Table altered.

    SQL> select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes;
    INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     STATUS   INITIAL_EXTENT MAX_EXTENTS
    ------------------------------ --------------------------- ------------------------------ -------- -------------- -----------
    IND_TEST2                      NORMAL                      TEST2                          VALID             65536  2147483645



           水位是记录段里能容纳数据的上限,高水位存在段里

           全表扫先读段头块,而后在段头块里面找到HWM

            下面用实验由内而外来理解Oracle的HWM

     

    [sql] view plain copy  print?

    1. --t表有一条数据  
    2.   
    3. hr@ORCL> select * from t;  
    4.   
    5.         ID NAME  
    6. ---------- ----------  
    7.          1 AAAAA  
    8.   
    9. --找t段的段头块  
    10.   
    11. hr@ORCL> select header_file,header_block from dba_segments where segment_name='T' and owner='HR';  
    12.   
    13. HEADER_FILE HEADER_BLOCK  
    14. ----------- ------------  
    15.           4          387  
    16.   
    17. --另开一个session,dump段头块  
    18.   
    19. sys@ORCL> alter session set tracefile_identifier='sys_dump_t_01';  
    20.   
    21. Session altered.  
    22.   
    23. sys@ORCL> alter system dump datafile 4 block 387;  
    24.   
    25. System altered.  


           dump的部分trc内容摘入

     

     

    [sql] view plain copy  print?

    1. Extent Control Header  
    2. -----------------------------------------------------------------  
    3. Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8  
    4.                 last map  0x00000000  #maps: 0      offset: 2716  
    5.     Highwater::  0x01000189  ext#: 0      blk#: 8      ext size: 8   --Highwater就是高水位,0x01000189这个是HWM的地址  
    6. #blocks in seg. hdr's freelists: 0  
    7. #blocks below: 5  
    8. mapblk  0x00000000  offset: 0  
    9.                  Unlocked  
    10. --------------------------------------------------------  
    11. Low HighWater Mark :  
    12.     Highwater::  0x01000189  ext#: 0      blk#: 8      ext size: 8  
    13. #blocks in seg. hdr's freelists: 0  
    14. #blocks below: 5  
    15. mapblk  0x00000000  offset: 0  
    16. Level 1 BMB for High HWM block: 0x01000181  
    17. Level 1 BMB for Low HWM block: 0x01000181  
    18. --------------------------------------------------------  
    19. Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0  
    20. L2 Array start offset:  0x00001434  
    21. First Level 3 BMB:  0x00000000  
    22. L2 Hint for inserts:  0x01000182  
    23. Last Level 1 BMB:  0x01000181  
    24. Last Level II BMB:  0x01000182  
    25. Last Level III BMB:  0x00000000  
    26.    Map Header:: next  0x00000000  #extents: 1    obj#: 52713  flag: 0x10000000  
    27. Inc # 0  
    28. Extent Map  
    29. -----------------------------------------------------------------  
    30.  0x01000181  length: 8  
    31.   
    32. Auxillary Map  
    33. --------------------------------------------------------  
    34.  Extent 0     :  L1 dba:  0x01000181 Data dba:  0x01000184  
    35. --------------------------------------------------------  
    36.   
    37.  Second Level Bitmap block DBAs  
    38.  --------------------------------------------------------  
    39.  DBA 1:   0x01000182  
    40.   
    41. d dump data blocks tsn: 4 file#: 4 minblk 387 maxblk 387  

     

     

     

    [sql] view plain copy  print?

    1. --对t表做一次全表扫  
    2.   
    3. hr@ORCL> set autot traceonly  
    4. hr@ORCL> select * from t;  
    5.   
    6.   
    7. Execution Plan  
    8. ----------------------------------------------------------  
    9. Plan hash value: 1601196873  
    10.   
    11. --------------------------------------------------------------------------  
    12. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
    13. --------------------------------------------------------------------------  
    14. |   0 | SELECT STATEMENT  |      |     1 |    20 |     3   (0)| 00:00:01 |  
    15. |   1 |  TABLE ACCESS FULL| T    |     1 |    20 |     3   (0)| 00:00:01 |  
    16. --------------------------------------------------------------------------  
    17.   
    18. Note  
    19. -----  
    20.    - dynamic sampling used for this statement  
    21.   
    22.   
    23. Statistics  
    24. ----------------------------------------------------------  
    25.           0  recursive calls  
    26.           0  db block gets  
    27.           7  consistent gets   --全表扫读了6个块  
    28.           0  physical reads  
    29.           0  redo size  
    30.         469  bytes sent via SQL*Net to client  
    31.         385  bytes received via SQL*Net from client  
    32.           2  SQL*Net roundtrips to/from client  
    33.           0  sorts (memory)  
    34.           0  sorts (disk)  
    35.           1  rows processed  


           这6个块是如何算出来的呢?

     

     

    [sql] view plain copy  print?

    1. hr@ORCL> select file_id,block_id,blocks from dba_extents where segment_name='T';  
    2.   
    3.    FILE_ID   BLOCK_ID     BLOCKS  
    4. ---------- ---------- ----------  
    5.          4        385          8  


           这t段一共用了8个块,分别是385 386 387 388 389 390 391 392 393
           Highwater::  0x01000189  即:4号文件的393号块
           这个可由下面dbms_utility包算出

     

     

    [sql] view plain copy  print?

    1. sys@ORCL> select to_number('01000189','xxxxxxxx'from dual;  
    2.   
    3. TO_NUMBER('01000189','XXXXXXXX')  
    4. --------------------------------  
    5.                         16777609  
    6.   
    7. sys@ORCL> select dbms_utility.data_block_address_file(16777609) from dual;  
    8.   
    9. DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16777609)  
    10. ----------------------------------------------  
    11.                                              4  
    12.   
    13. sys@ORCL> select dbms_utility.data_block_address_block(16777609) from dual;  
    14.   
    15. DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16777609)  
    16. -----------------------------------------------  
    17.                                             393  


           读了一次段头块:4号文件387号块
           读了高水位之下的388 389 390 391 392 等5个块
           这样一共就读了6个块
           注:
             385是FIRST LEVEL BITMAP BLOCK
             386是SECOND LEVEL BITMAP BLOCK
             
           接着分析我们所dump的内容:
         

    [sql] view plain copy  print?

    1. Low HighWater Mark :  
    2.     Highwater::  0x01000189  ext#: 0      blk#: 8      ext size: 8   


           
           接下来谈谈highwater mark 和 low highwater mark
           
           low high water mark与high water mark 之间可能存在formated block也可以可能存在unformatted block

     

           

           先来理清dba_tables里面的字段blocks的含义
           
           dba_tables.blocks记录的是分析得到的 formatted block 的总数
           而 low hwm 和 high hwm之间可能同时存在 formatted block 和 unfomatted block
           所以准确地说  blocks 不能代表 low hwm 或high hwm
           如果 low hwm 和 high hwm之间正好没有formatted block时,dba_tables.blocks和low hwm下的blocks一致
           
           那么什么是Oracle中未格式化的块呢?
           
           未格式化,意思就是这个块,已经是属于这个段了,但是还保留着原来的样子没动
           格式化就是把块中的数据清除掉,并把块头改为这个对象的
           MSSM表空间中的段,只有一个高水位,高水位下的块都是格式化了的
           但是ASSM表空间中的段,有两个高水位:低高水位和高高水位
           即上文trc里的:Highwater::  0x01000189和Low HighWater Mark Highwater::  0x01000189
           低高水位下的块全部是格式化了的
           但是低高水位和高高水位之间的块,则可能是格式化了的,也可能是没有
           
           现在的t的高高水位是file 4,block 393;其低高水位是file 4,block 393
           
           我们现在再来看一下t现在data_object_id是多少:

     

    [sql] view plain copy  print?

    1. hr@ORCL> select object_id,data_object_id from dba_objects where object_name='T';  
    2.   
    3.  OBJECT_ID DATA_OBJECT_ID  
    4. ---------- --------------  
    5.      52713          52714  


           这里很明显t的data_object_id大于object_id
           也就是说,在t上曾经发生过move或truncate操作
           注意,对于truncate操作而言,truncate后其data_object_id不一定就是在原先的data_object_id上加1

     

     

    [sql] view plain copy  print?

    1. sys@ORCL> select to_char('52714','XXXXXXXX'from dual;  
    2.   
    3. TO_CHAR('  
    4. ---------  
    5.      CDEA  


           换句话说,t中现在在其低高水位和其高高水位之间的block,只要这个block上记录的data_object_id不等于CDEA
           我们可以通过dump里面的Block header dump部分中的seg/obj来判断其data_object_id是否与段编号相等
           那么这个block 就是一个未格式化的块
           也就是说,可以通过data_object_id来确定块是在HWM和LHWM的位置
           
           那么Oracle为什么要增加低高水位设置?出于什么目的?全表扫描时,是到低高水位,还是到高高水位?
           
           Oracle设计有一个原则,就是把一个大操作分散到很多小操作中,以保证某个大操作不会花费太长时间
           无论是延迟提交,还是什么,都体现了这种思想,这和Linux的理念有异曲同工之妙哦
           而低高水位线与高高水位线结合,正是这种思想的体现
           可以不用一次性格式化所有新分配的块,留一部分给下次操作来完成
           
           全表扫描时,通常都是读至低高水位线,然后根据位图去读低高与高高之间格式化过的块,避开未格式化的块
         
           如何查看HWM?如何知道HWM下有多少空闲的空间

           
           最实用的方法就是dump segment_header,速度快,而且对应用没有影响
           而且,trc里面的"#blocks in seg. hdr's freelists:"可以告诉我们HWM下有多少空闲块
           这里需要注意,如果dba_segments.freelist_groups > 1,那么freelist不再segment header中
           比如,freelist_group = 3 ,则你便要分别dump header后面的3个块,来看每个group的freelist的个数
           
           那么如何降低HWM呢?
            
           ① expdp/impdp
           ② 10G及以后的版本可以采用shrink,需要注意的是,表所在表空间须为自动段空间管理
              alter table tab_name enable row movement;
              alter table tab_name shrink space;
           ③ 使用move,不过在Move期间,会影响到DML语句,如UPDATE,也需要考虑空间问题
              总之move会锁表 而且是TM  另外还会影响index,所以,之后记得rebuild index
              alter table move tab_name;  在当前表空间中move
              alter table move tab_name tablespace tbs_name;  将其move到其他表空间
           ④ CTAS  然后rename,rebuild index
           ⑤ 在线重定义

     

            等等.......

     



    高水位(High Water Mark)的概念及高水位问题的解决

    说到HWM,我们首先要简要的谈谈ORACLE的逻辑存储管理.我们知道,ORACLE在逻辑存储上分4个粒度:表空间,段,区和块. 

     

    (1)块:是粒度最小的存储单位,现在标准的块大小是8K,ORACLE每一次I/O操作也是按块来操作的,也就是说当ORACLE从数据文件读数据时,是读取多少个块,而不是多少行. 

     

    (2) 区:由一系列相邻的块而组成,这也是ORACLE空间分配的基本单位,举个例子来说,当我们创建一个表PM_USER时,首先ORACLE会分配一区的空 间给这个表,随着不断的INSERT数据到PM_USER,原来的这个区容不下插入的数据时,ORACLE是以区为单位进行扩展的,也就是说再分配多少个 区给PM_USER,而不是多少个块.  

     

    (3) 段:是由一系列的区所组成,一般来说,当创建一个对象时(表,索引),就会分配一个段给这个对象.所以从某种意义上来说,段就是某种特定的数据.如 CREATE TABLE PM_USER,这个段就是数据段,而CREATE INDEX ON PM_USER(NAME),ORACLE同样会分配一个段给这个索引,但这是一个索引段了.查询段的信息可以通过数据字典: SELECT * FROM USER_SEGMENTS来获得,

     

    (4)表空间:包含段,区及块.表空间的数据物理上储存在其所在的数据文件中.一个数据库至少要有一个表空间.

     

    OK,我们现在回到HWM上来,那么,什么是高水位标记呢?这就跟ORACLE的段空间管理相关了.

     

     

    (一)ORACLE用HWM来界定一个段中使用的块和未使用的块.

     


    举 个例子来说,当我们创建一个表:PT_SCHE_DETAIL时,ORACLE就会为这个对象分配一个段.在这个段中,即使我们未插入任何记录,也至少有 一个区被分配,第一个区的第一个块就称为段头(SEGMENT HEADE),段头中就储存了一些信息,基中HWM的信息就存储在此.此时,因为第一个区的第一块用于存储段头的一些信息,虽然没有存储任何实际的记录, 但也算是被使用,此时HWM是位于第2个块.当我们不断插入数据到PM_USER后,第1个块已经放不下后面新插入的数据,此时,ORACLE将高水位之 上的块用于存储新增数据,同时,HWM本身也向上移.也就是说,当我们不断插入数据时,HWM会往不断上移,这样,在HWM之下的,就表示使用过的 块,HWM之上的就表示已分配但从未使用过的块.

     

    (二)HWM在插入数据时,当现有空间不足而进行空间的扩展时会向上移,但删除数据时不会往下移.

     

    这就好比是水库的水位,当涨水时,水位往上移,当水退出后,最高水位的痕迹还是清淅可见.
    考虑让我们看一个段,如一张表,其中填满了块,如图 1 所示。在正常操作过程中,删除了一些行,如图 2 所示。现有就有了许多浪费的空间:(I) 在表的上一个末端和现有的块之间,以及 (II) 在块内部,其中还有一些没有删除的行。

     

     

     

    图1:分配给该表的块。用灰色正方形表示行

     

    ORACLE 不会释放空间以供其他对象使用,有一条简单的理由:由于空间是为新插入的行保留的,并且要适应现有行的增长。被占用的最高空间称为最高使用标记 (HWM),如图 2 所示。

     

     
    图2:行后面的块已经删除了;HWM 仍保持不变

     

    (三)HWM的信息存储在段头当中.

     


    HWM本身的信息是储存在段头.在段空间是手工管理方式时,ORACLE是通过FREELIST(一个单向链表)来管理段内的空间分配.在段空间是自动管理方式时(ASSM),ORACLE是通过BITMAP来管理段内的空间分配.

     

    (四)ORACLE的全表扫描是读取高水位标记(HWM)以下的所有块.

     

    所以问题就产生了.当用户发出一个全表扫描时,ORACLE 始终必须从段一直扫描到 HWM,即使它什么也没有发现。该任务延长了全表扫描的时间。

     

    (五)当用直接路径插入行时 — 例如,通过直接加载插入(用 APPEND 提示插入)或通过 SQL*LOADER 直接路径 — 数据块直接置于 HWM 之上。它下面的空间就浪费掉了。

     

    我们来分析这两个问题,后者只是带来空间的浪费,但前者不仅是空间的浪费,而且会带来严重的性能问题.我们来看看下面的例子:

     

     

    (A)我们先来搭建测试的环境,第一步先创建一个段空间为手工管理的表空间:

     

    CREATE TABLESPACE "RAINNY" 
    LOGGING 
    DATAFILE 'D:ORACLE_HOMEORADATARAINNYRAINNY.ORA' SIZE 5M 
    AUTOEXTEND 
    ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL 
    SEGMENT SPACE MANAGEMENT MANUAL;

     

    (B)创建一个表,注意,此表的第二个字段我故意设成是CHAR(100),以让此表在插入1千万条记录后,空间有足够大:

     

    CREATE TABLE TEST_TAB(C1 NUMBER(10),C2 CHAR(100)) TABLESPACE RAINNY;

     

    插入记录

     

    DECLARE
        I NUMBER(10);

    BEGIN
        FOR I IN 1..10000000 LOOP
            INSERT INTO TEST_TAB VALUES(I,'TESTSTRING');
        END LOOP;
        COMMIT;

    END;

    /


    (C)我们来查询一下,看在插入一千万条记录后所访问的块数和查询所用时间:


    SQL> SET TIMING ON

    SQL> SET AUTOTRACE TRACEONLY

    SQL> SELECT COUNT(*) FROM TEST_TAB;

     

    ELAPSED: 00:01:03.05

     

    EXECUTION PLAN

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

    0 SELECT STATEMENT OPTIMIZER=CHOOSE (COST=15056 CARD=1)

    1 0 SORT (AGGREGATE)

    2 1 TABLE ACCESS (FULL) OF 'TEST_TAB' (COST=15056 CARD=10000

    000)

     

    STATISTICS

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

         RECURSIVE CALLS

         DB BLOCK GETS

    156310 CONSISTENT GETS

    154239 PHYSICAL READS

         REDO SIZE

    379    BYTES SENT VIA SQL*NET TO CLIENT

    503    BYTES RECEIVED VIA SQL*NET FROM CLIENT

         SQL*NET ROUNDTRIPS TO/FROM CLIENT

         SORTS (MEMORY)

         SORTS (DISK)

         ROWS PROCESSED


    我们来看上面的执行计划,这句SQL总供耗时是:1分3秒.访问方式是采用全表扫描方式(FTS),逻辑读了156310个BLOCK,物理读了154239个BLOCK. 
    我们来分析一下这个表: 

     

    BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'TEST', 
    TABNAME=> 'TEST_TAB',
    PARTNAME=> NULL);END;

    /


    发现这个表目前使用的BLOCK有: 156532,未使用的BLOCK(EMPTY_BLOCKS)为:0,总行数为(NUM_ROWS):1000 0000 

     

    (D)接下来我们把此表的记录用DELETE方式删掉,然后再来看看SELECT COUNT(*) FROM TEST_TAB所花的时间: 

     

    DELETE FROM TEST_TAB; 
    COMMIT;

     

    SQL> SELECT COUNT(*) FROM TEST_TAB; 

     

    ELAPSED: 00:01:04.03 

     

    EXECUTION PLAN 
    ---------------------------------------------------------- 
    0 SELECT STATEMENT OPTIMIZER=CHOOSE (COST=15056 CARD=1)

    1 0 SORT (AGGREGATE) 
    2 1 TABLE ACCESS (FULL) OF 'TEST_TAB' (COST=15056 CARD=1)

     

    STATISTICS 
    ---------------------------------------------------------- 
         RECURSIVE CALLS 
         DB BLOCK GETS 
    156310 CONSISTENT GETS 
    155565 PHYSICAL READS 
         REDO SIZE 
    378    BYTES SENT VIA SQL*NET TO CLIENT 
    503    BYTES RECEIVED VIA SQL*NET FROM CLIENT 
         SQL*NET ROUNDTRIPS TO/FROM CLIENT 
         SORTS (MEMORY) 
         SORTS (DISK) 
         ROWS PROCESSED

     

    大 家来看,在DELETE表后,此时表中已没有一条记录,为什么SELECT COUNT(*) FROM TEST_TAB花的时间为1分4秒, 反而比有记录稍微长点,这是为什么呢?而且大家看,其逻辑读了156310个 BLOCK,跟之前有一千万行记录时差不多,ORACLE怎么会这么笨啊?

     


    我们在DELETE表后再次分析表,看看有什么变化: 
    这时, TEST_TAB表目前使用的BLOCK是: 156532,未使用的BLOCK(EMPTY_BLOCKS)为:0,总行数为(NUM_ROWS)已变成:0 

    为什么表目前使的BLOCK数还是156532呢?


    问 题的根源就在于ORACLE的HWM.也就是说,在新增记录时,HWM会慢慢往上移,但是在删除记录后,HWM却不会往下移,也就是说,DELETE一千 万条记录后,此表的HWM根本没移动,还在原来的那个位置,所以,HWM以下的块数同样也是一样的.ORACLE的全表扫描是读取ORACLE高水位标记 下的所有BLOCK,也就是说,不管HWM下的BLOCK现在实际有没有存放数据,ORACLE都会一一读取,这样,大家可想而知,在我们DELETE表 后,ORACLE读了大量的空块,耗去了大量的时间.

     


    我们再来看DELETE表后段空间实际使用的状况:

     

     

    SQL> EXEC SHOW_SPACE('TEST_TAB','TEST');

    TOTAL BLOCKS............................164352 --总共164352块 
    TOTAL BYTES.............................1346371584
    UNUSED BLOCKS...........................7168 --有7168块没有用过,也就是在HWM上面的块数
    UNUSED BYTES............................58720256
    LAST USED EXT FILEID....................9
    LAST USED EXT BLOCKID...................158856-- BLOCK ID 是针对数据文件来编号的,表示最后使用的一个EXTENT的第一个BLOCK的编号 
    LAST USED BLOCK.........................1024 -- 在最后使用的一个EXTENT 中一共用了1024块 

     

    PL/SQL PROCEDURE SUCCESSFULLY COMPLETED

     

    总 共用了164352块,除了一个SEGMENT HEADER,实际总共用了164351个块,有7168块从来没有使用过。LAST USED BLOCK表示在最后一个使用的EXTENT 中使用的BLOCK, 结合 LAST USED EXT BLOCK ID可以计算 HWM 位置 :


    LAST USED EXT BLOCK ID + LAST USED BLOCK -1 = HWM 所在的数据文件的BLOCK编号

     

    代入得出: 158856+1024-1=159879,这个就是HWM所有的BLOCK编号

    HWM所在的块:TOTAL BLOCKS- UNUSED BLOCKS=164352-7168=157184,也就是说,HWM在第157184个块,其BLOCKID是159879

     

    (E)结下来,我们再做几个试验:

     

    第一步:执行ALTER TABLE TEST_TAB DEALLOCATE UNUSED;

    我们看看段空间的使用状况:

     

    SQL> EXEC SHOW_SPACE('TEST_TAB','TEST');

     

    TOTAL BLOCKS............................157184
    TOTAL BYTES.............................1287651328
    UNUSED BLOCKS...........................0
    UNUSED BYTES............................0
    LAST USED EXT FILEID....................9
    LAST USED EXT BLOCKID...................158856
    LAST USED BLOCK.........................1024

     

    此 时我们再代入上面的公式,算出HWM的位置: 157184-0=157184 HWM所在的BLOCK ID是158856+1024-1=159879,跟刚刚的没有变化,也就是说执行ALTER TABLE TEST_TAB DEALLOCATE UNUSED后,段的高水位标记的位置没有改变,但是大家看看UNUSED BLOCKS变为0了,总的块数减少到157184,这证明,DEALLOCATE UNUSED为释放HWM上面的未使用空间,但是并不会释放HWM下面的自由空间,也不会移动HWM的位置. 

     

    第二步:我们再来看看执行ALTER TABLE TEST_TAB MOVE后段空间的使用状况: 

     

    SQL> EXEC SHOW_SPACE('TEST_TAB','TEST');

     

    TOTAL BLOCKS............................8

    TOTAL BYTES.............................65536

    UNUSED BLOCKS...........................5

    UNUSED BYTES............................40960

    LAST USED EXT FILEID....................9

    LAST USED EXT BLOCKID...................2632

    LAST USED BLOCK.........................3


    此时,总共用到的块数已变为8, 我们再代入上面的公式,算出HWM的位置: 8-5=3 HWM所在的BLOCK ID是2632+3-1=2634,

     

    OK, 我们发现,此时HWM的位置已经发生变化,现在HWM的位置是在第3个BLOCK,其BLOCK ID是2634,所有数据文件的ID是9(这个没有发生变化,数据文件还是原来的那个数据文件,只是释放了原来的自由空间),最后使用的块数也变为3,也 就是说已经使用了3块,HWM就是在最后一个使用的块上,即第3个块上.大家可能会觉得奇怪,为什么释放空间后,未使用的块还有5个啊?也就是说HWM之 上还是有5个已分配但从未使用的块.答案就跟HWM移动的规律有关.当我们在插入数据时,ORACLE首先在HWM之下的块当中定位自由空间(通过自由列 表FREELIST),如果FREELIST当中没有自由块了,ORACLE就开始往上扩展,而HWM也跟着往上移,每5块移动一次.我们来看 ORACLE的说明: 

     

    The high water mark is: 
    -Recorded in the segment header block 
    -Set to the beginning of the segment on the creation 
    -Incremented in five-block increments as rows are inserted 
    -Reset by the truncate command 
    -Never reset by the delete command 
    -Space above the high-water-mark can be reclaimed at the table level by using the following command: 
    ALTER TABLE DEALLOCATE UNUSED… 

     

    我们再来看看:SELECT COUNT(*) FROM TEST_TAB所花的时间: 

     

    SQL> SELECT COUNT(*) FROM TEST_TAB; 

     

    ELAPSED: 00:00:00.00 

    EXECUTION PLAN 
    ----------------------------------------------------------

    0 SELECT STATEMENT OPTIMIZER=CHOOSE 
    1 0 SORT (AGGREGATE) 
    2 1 TABLE ACCESS (FULL) OF 'TEST_TAB'

     

    STATISTICS 
    ---------------------------------------------------------- 
        RECURSIVE CALLS 
        DB BLOCK GETS

        CONSISTENT GETS 
        PHYSICAL READS 
        REDO SIZE 
    378   BYTES SENT VIA SQL*NET TO CLIENT 
    503   BYTES RECEIVED VIA SQL*NET FROM CLIENT 
        SQL*NET ROUNDTRIPS TO/FROM CLIENT 
        SORTS (MEMORY) 
        SORTS (DISK) 
        ROWS PROCESSED 

     

    很快,不到1秒.

     

    我们最后再来对表作一次分析, 此时这个表目前使用的BLOCK为: 0,未使用的BLOCK(EMPTY_BLOCKS)为:0,总行数为(NUM_ROWS):0 
    从 中我们也可以发现,分析表和SHOW_SPACE显示的数据有点不一致.那么哪个是准的呢?其实这两个都是准的,只不过计算的方法有点不同.事实上,当你 创建了一个对象如表以后,不管你有没有插入数据,它都会占用一些块,ORACLE也会给它分配必要的空间.同样,用ALTER TABLE MOVE释放自由空间后,还是保留了一些空间给这个表. 
    最后,我们再来执行TRUNCATE命令,截断这个表,看看段空间的使用状况: 

     

    TRUNCATE TABLE TEST_TAB; 

     

    SQL> EXEC SHOW_SPACE('TEST_TAB','TEST'); 

     

    TOTAL BLOCKS............................8 
    TOTAL BYTES.............................65536 
    UNUSED BLOCKS...........................5 
    UNUSED BYTES............................40960 
    LAST USED EXT FILEID....................9 
    LAST USED EXT BLOCKID...................2632 
    LAST USED BLOCK.........................3 

     

    PL/SQL PROCEDURE SUCCESSFULLY COMPLETED 

     

    SQL> 

     

    我们发现TRUNCATE后和MOVE没有什么变化. 

     

    为了,最终验证一下我上面的观点,我再DROP一下表,然后新建这个表,看看这时在没有插入任何数据之前,是否ORACLE确实有给这个对象分配必要的空间: 

     

    DROP TABLE TEST_TAB; 

     

    CREATE TABLE TEST_TAB(C1 NUMBER(10),C2 CHAR(100)) TABLESPACE RAINNY;

     

    SQL> EXEC SHOW_SPACE('TEST_TAB','TEST'); 

    TOTAL BLOCKS............................8 
    TOTAL BYTES.............................65536 
    UNUSED BLOCKS...........................5 
    UNUSED BYTES............................40960 
    LAST USED EXT FILEID....................9 
    LAST USED EXT BLOCKID...................2112 
    LAST USED BLOCK.........................3

     

    大家看,即使我没有插入任何一行记录,ORACLE还是给它分配了8个块.当然这个跟建表语句的INITIAL 参数及MINEXTENTS参数有关:请看TEST_TAB的存储参数: 

     

    S TORAGE
    (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    ); 

     

    也就是说,在这个对象创建以后,ORACLE至少给它分配一个区,初始大小是64K,一个标准块的大小是8K,刚好是8个BLOCK. 

     

     

    总结: 

     

    在9I中: 

     

    (1)如果MINEXTENT 可以使ALTER TABLE TABLENAME DEALLOCATE UNUSED将HWM以上所有没使用的空间释放
    (2)如果MINEXTENT >HWM 则释放MINEXTENTS 以上的空间。如果要释放HWM以上的空间则使用KEEP 0。
    ALTER TABLE TABLESNAME DEALLOCATE UNUSED KEEP 0;
    (3) TRUNCATE TABLE DROP STORAGE(缺省值)命令可以将MINEXTENT 之上的空间完全释放(交还给操作系统),并且重置HWM。 
    (4)如果仅是要移动HWM,而不想让表长时间锁住,可以用TRUNCATE TABLE REUSE STORAGE,仅将HWM重置。
    (5)ALTER TABLE MOVE会将HWM移动,但在MOVE时需要双倍的表空间,而且如果表上有索引的话,需要重构索引 
    (6)DELETE表不会重置HWM,也不会释放自由的空间(也就是说DELETE空出来的空间只能给对象本身将来的INSERT/UPDATE使用,不能给其它的对象使用) 

     

    在ORACLE 10G: 

     

    可以使用ALTER TABLE TEST_TAB SHRINK SPACE命令来联机移动HWM, 
    如果要同时压缩表的索引,可以发布:ALTER TABLE TEST_TAB SHRINK SPACE CASCADE
    注意:在使用此命令时需要先使行可迁移row movement(具体见例子)。
             与使用ALTER TABLE MOVE 不同的是执行此命令后并不需要重构索引。

     

     

    Oracle 官方说明 

     

    Shrinking Database Segments Online
    You use online segment shrink to reclaim fragmented free space below the high water mark in an Oracle Database segment. The benefits of segment shrink are these:
        * Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.
        * The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.
    Segment shrink is an online, in-place operation. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operation are blocked for a short time at the end of the shrink operation, when the space is deallocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete. Segment shrink does not require extra disk space to be allocated.
    Segment shrink reclaims unused space both above and below the high water mark. In contrast, space deallocation reclaims unused space only above the high water mark. In shrink operations, by default, the database compacts the segment, adjusts the high water mark, and releases the reclaimed space.
    Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any rowid-based triggers defined on the object.
    Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
        * IOT mapping tables
        * Tables with rowid based materialized views
        * Tables with function-based indexes

     

     

    操作的过程:

     

    SQL> create table demo as select * from dba_source;

    Table created.
    Elapsed: 00:00:05.83

     

    SQL> select count(*) from demo;

     

      COUNT(*)
    ----------
        210992
    Elapsed: 00:00:01.06

     

    SQL> insert into demo select * from demo;
    210992 rows created.
    Elapsed: 00:00:59.83

     

    SQL> commit;
    Commit complete.

     

    //得到一个40万条记录的表,下面来查看这个表空间分布情况。

     

    SQL> exec show_space('demo','auto');

     

    PL/SQL procedure successfully completed.
    Elapsed: 00:00:00.07

     

    SQL> set serveroutput on

     

    SQL>  exec show_space('demo','auto');

     

    Total Blocks............................9216
    Total Bytes.............................75497472
    Unused Blocks...........................768
    Unused Bytes............................6291456
    Last Used Ext FileId....................4
    Last Used Ext BlockId...................8328
    Last Used Block.........................256

     

    一共有9216个数据块,HWM在9216-768=8448这个块.
    也可以通过查看extents得到HWM=8*16+128*63+256=8192+256=8448

     

    PL/SQL procedure successfully completed.
    Elapsed: 00:00:00.01

     

    SQL> delete from demo where rownum<220000;

     

    219999 rows deleted.
    Elapsed: 00:00:40.99

     

    SQL> commit;

     

    Commit complete.
    Elapsed: 00:00:00.01

     

    SQL>  exec show_space('demo','auto');

     

    Total Blocks............................9216
    Total Bytes.............................75497472
    Unused Blocks...........................768
    Unused Bytes............................6291456
    Last Used Ext FileId....................4
    Last Used Ext BlockId...................8328
    Last Used Block.........................256

     

    PL/SQL procedure successfully completed.

     

    //删除操作后表的HWM没有变化,还是在第8448块这个位置。
    Elapsed: 00:00:00.00

     

    SQL> alter table demo shrink space;
    alter table demo shrink space
    *
    ERROR at line 1:
    ORA-10636: ROW MOVEMENT is not enabled

    //先要enable row movement才能shrink
    Elapsed: 00:00:00.09

     

    SQL> alter table demo enable row movement;

     

    Table altered.
    Elapsed: 00:00:00.10

     

    SQL>  alter table demo shrink space;

     

    Table altered.
    Elapsed: 00:01:35.51

     

    SQL>  exec show_space('demo','auto');

     

    Total Blocks............................3656
    Total Bytes.............................29949952
    Unused Blocks...........................0
    Unused Bytes............................0
    Last Used Ext FileId....................4
    Last Used Ext BlockId...................3720
    Last Used Block.........................72

     

    PL/SQL procedure successfully completed.
    Elapsed: 00:00:00.02

    //可以看到HWM降到了3656这个块上面!

     

     

     

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

    最近学习了一阵子Oracle, 感觉Oracle真的是博大精深, 包括Oralce内存结构,性能调整,数据备份等都不简单, 这些对开发也很重要, 下面把做的Oracle高水位线的一些实验贴出来, 方便以后Review: 
    高水位线实验: 
    -- 创建test3表 
    SQL> create table test3 as 
      2  select * from dba_objects where 1 = 2; 
    Table created 
    -- 查看表中分配块,区大小 
    SQL> SELECT segment_name, segment_type, blocks -- 分配数据块数, extents -- 分配区块数 
      2    FROM dba_segments 
      3   WHERE segment_name = 'TEST3' 
      4  ; 
    SEGMENT_NAME                                                                     SEGMENT_TYPE           BLOCKS    EXTENTS 
    -------------------------------------------------------------------------------- ------------------ ---------- ---------- 
    TEST3                                                                            TABLE                       8          1 
    TEST3                                                                            TABLE                       8          1 
    -- 分析表TEST3表 
    SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS; 
    Table analyzed 
    -- 查询TEST3表高水位线 
    SQL> SELECT blocks -- 高水位线(占用TEST3表数据块数), empty_blocks -- TEST3表空闲块数, num_rows 
      2    FROM user_tables 
      3   WHERE table_name = 'TEST3'; 
        BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    ---------- ------------ ---------- 
             0            7          0 
    -- 因为未向TEST3表中插入任何数据,因此此表的高水位线为0,现向TEST3表中插入数据再观察 
    SQL> insert into test3 
      2  select * from dba_objects; 
    50361 rows inserted 
    SQL> commit; 
    Commit complete 
    -- 重新分析表 
    SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS; 
    Table analyzed 
    -- 再次查看表中分配块,区大小 
    SQL> SELECT segment_name, segment_type, blocks, extents 
      2    FROM dba_segments 
      3   WHERE segment_name = 'TEST3' 
      4  ; 
    SEGMENT_NAME                                                                     SEGMENT_TYPE           BLOCKS    EXTENTS 
    -------------------------------------------------------------------------------- ------------------ ---------- ---------- 
    TEST3                                                                            TABLE                       8          1 
    TEST3                                                                            TABLE                     768         21 
    此时看到BLOCKS数已增长到768, 也就是Oracle分配给TEST3表768个数据块,21个区 
    -- 再次查看TEST3表高水位线 
    SQL> SELECT blocks, empty_blocks, num_rows 
      2    FROM user_tables 
      3   WHERE table_name = 'TEST3'; 
        BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    ---------- ------------ ---------- 
           689           78      50361 
    已增长到689个块, 还有78个空闲块,689 + 78 = 767, 比分配的少1个数据块,是因为这一个数据块是用作segment header 
    -- 现将TEST3表delete,在查看高水位线 
    SQL> delete from test3; 
    50361 rows deleted 
    SQL> commit; 
    Commit complete 
    SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS; 
    Table analyzed 
    SQL> 
    SQL> SELECT blocks, empty_blocks, num_rows 
      2    FROM user_tables 
      3   WHERE table_name = 'TEST3'; 
        BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    ---------- ------------ ---------- 
           689           78          0 
    发现此表高水位线并未减少,证明delete只是删除表中数据块的记录,但并不会使表中的高水位线下降, 在进行全表扫描时会Oracle会扫描表中高水位线下的所有数据块, 
    因此数据虽然被删除了,但查询时有可能还是很慢。所以在进行大表删除时应使用truncate语句,看下面实验: 
    SQL> truncate table test3; 
    Table truncated 
    SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS; 
    Table analyzed 
    SQL> 
    SQL> SELECT blocks, empty_blocks, num_rows 
      2    FROM user_tables 
      3   WHERE table_name = 'TEST3'; 
        BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    ---------- ------------ ---------- 
             0            7          0 
    现在表中高水位下降到0了, 一点心得, 记录下来。

     

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

    4.  修正ORACLE 表的高水位线

      在ORACLE 中,执行对表的删除操作不会降低该表的高水位线。而全表扫描将始终读取一个段(extent) 中所有低于高水位线标记的块。如果在执行删除操作后不降低高水位线标记,则将导致查询语句的性能低下。

     

    下面的方法都可以 降低高水位线标记 

     

    1.   执行表重建指令 alter table table_name move;

    在线转移表空间ALTER TABLE  ...  MOVE TABLESPACE  ..

    当你创建了一个对象如表以后, 不管你有没有插入数据 , 它都会占用一些块 ,ORACLE 也会给它分配必要的空间 . 同样 , 用 ALTER TABLE MOVE 释放自由空间后 , 还是保留了一些空间给这个表 .   

    ALTER TABLE  ...   MOVE 后面不跟参数也行,不跟参数表还是在原来的表空间, M ove后记住重建索引 .  如果以后还要继续向这个表增加数据,没有必要move , 只是释放出来的空间,只能这个表用,其他的表或者 segment 无法使用该空间 。 

    2.   执行alter table table_name shrink space; 

    注意, 此命令为Oracle 10g 新增功能 ,再执行该指令之前必须允许行移动 alter table table_name enable row movement;

    3.   复制要保留的数据到临时表t , drop 原表,然后 rename 临时表 t 为原表

    4.  用逻辑导入导出: E mp/ I mp

    5.  A lter    table table_name deallocate unused  

      注: 这证明,DEALLOCATE UNUSED 为释放 HWM 上面的未使用空间 , 但是并不会释放 HWM 下面的自由空间 , 也不会移动 HWM 的位置 . 

    6.  尽量使用 truncate .

     

    注意:

    在9I 中 : 

    1. 如果是 INEXTENT   可以使ALTER TABLE TABLENAME DEALLOCATE UNUSED 将 HWM 以上所有没使用的空间释放

    2.  如果MINEXTENT >HWM  则释放 MINEXTENTS  以上的空间。如果要释放 HWM 以上的空间则使用 KEEP 0 。

    ALTER TABLE TABLESNAME DEALLOCATE UNUSED KEEP 0;

    3.  TRUNCATE TABLE DROP STORAGE (缺省值 ) 命令可以将MINEXTENT  之上的空间完全释放 (交还给操作系统 ), 并且重置 HWM 。 

    4.  如果仅是要移动HWM, 而不想让表长时间锁住 , 可以用 TRUNCATE TABLE REUSE STORAGE, 仅将 HWM 重置。

    5.  ALTER TABLE MOVE会将 HWM 移动 , 但在 MOVE 时需要双倍的表空间 , 而且如果表上有索引的话 , 需要重构索引 

    6.  DELETE表不会重置 HWM, 也不会释放自由的空间 ( 也就是说 DELETE 空出来的空间只能给对象本身将来的 INSERT/UPDATE 使用 , 不能给其它的对象使用 ) 

     

    在ORACLE 10G: 

    1.  可以使用ALTER TABLE TEST_TAB SHRINK SPACE 命令来联机移动 HWM, 

    2.  如果要同时压缩表的索引, 可以发布 :ALTER TABLE TEST_TAB SHRINK SPACE CASCADE

     

     

     

    5. HWM 特点:

     

    1.  ORACLE用 HWM 来界定一个段中使用的块和未使用的块 .

     

    举个例子来说, 当我们创建一个表时 ,ORACLE 就会为这个对象分配一个段 . 在这个段中 , 即使我们未插入任何记录 , 也至少有一个区被分配 , 第一个区的第一个块就称为段头 (SEGMENT HEADE), 段头中就储存了一些信息 , 基中 HWM 的信息就存储在此 . 此时 , 因为第一个区的第一块用于存储段头的一些信息 , 虽然没有存储任何实际的记录 , 但也算是被使用 , 此时 HWM 是位于第 2 个块 . 当我们不断插入数据到  后, 第 1 个块已经放不下后面新插入的数据 , 此时 ,ORACLE 将高水位之上的块用于存储新增数据 , 同时 ,HWM 本身也向上移 . 也就是说 , 当我们不断插入数据时 ,HWM 会往不断上移 , 这样 , 在 HWM 之下的 , 就表示使用过的块 ,HWM 之上的就表示已分配但从未使用过的块 .

     

    2.  HWM在插入数据时 , 当现有空间不足而进行空间的扩展时会向上移 , 但删除数据时不会往下移 .

    这就好比是水库的水位, 当涨水时 , 水位往上移 , 当水退出后 , 最高水位的痕迹还是清淅可见 .

    ORACLE 不会释放空间以供其他对象使用,有一条简单的理由:由于空间是为新插入的行保留的,并且要适应现有行的增长。被占用的最高空间称为最高使用标记  (HWM) ,

     

    3.  HWM的信息存储在段头当中 .

    HWM本身的信息是储存在段头 . 在段空间是手工管理方式时 ,ORACLE 是通过 FREELIST( 一个单向链表 ) 来管理段内的空间分配 . 在段空间是自动管理方式时 (ASSM),ORACLE 是通过 BITMAP 来管理段内的空间分配 .

     

     

    4. ORACLE的全表扫描是读取高水位标记 (HWM) 以下的所有块 .

    所以问题就产生了. 当用户发出一个全表扫描时, ORACLE  始终必须从段一直扫描到  HWM ,即使它什么也没有发现。该任务延长了全表扫描的时间。

     

    5.  当用直接路径插入行时  即使HWM 以下有空闲的数据库块,键入在插入数据时使用了 append关键字,则在插入时使用 HWM 以上的数据块,此时 HWM 会自动增大。

     



    一、什么是水线(High Water Mark)?

    所有的oracle段(segments,在此,为了理解方便,建议把segment作为表的一个同义词) 都有一个在段内容纳数据的上限,我们把这个上限称为"high water mark"或HWM。这个HWM是一个标记,用来说明已经有多少没有使用的数据块分配给这个segment。HWM通常增长的幅度为一次5个数据块,原则上HWM只会增大,不会缩小,即使将表中的数据全部删除,HWM还是为原值,由于这个特点,使HWM很象一个水库的历史最高水位,这也就是HWM的原始含义,当然不能说一个水库没水了,就说该水库的历史最高水位为0。但是如果我们在表上使用了truncate命令,则该表的HWM会被重新置为0。

               

    二、HWM数据库的操作有如下影响:

    a) 全表扫描通常要读出直到HWM标记的所有的属于该表数据库块,即使该表中没有任何数据。

    b) 即使HWM以下有空闲的数据库块,键入在插入数据时使用了append关键字,则在插入时使用HWM以上的数据块,此时HWM会自动增大。

                   

    三、如何知道一个表的HWM?

    a) 首先对表进行分析:

    ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;

    b) SELECT blocks, empty_blocks, num_rows

    FROM user_tables

    WHERE table_name = <tablename>;

    说明:

    BLOCKS 列代表该表中曾经使用过得数据库块的数目,即水线。

    EMPTY_BLOCKS 代表分配给该表,但是在水线以上的数据库块,即从来没有使用的数据块。

                

    让我们以一个有28672行的BIG_EMP1表为例进行说明:

    1) SQL> SELECT segment_name, segment_type, blocks

    FROM dba_segments

    WHERE segment_name='BIG_EMP1';

    SEGMENT_NAME       SEGMENT_TYPE    BLOCKS     

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

    BIG_EMP1           TABLE           1024       

    1 row selected.             

                    

    2) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;

    Statement processed.

               

    3) SQL> SELECT table_name,num_rows,blocks,empty_blocks

    FROM user_tables

    WHERE table_name='BIG_EMP1';

    TABLE_NAME  NUM_ROWS  BLOCKS     EMPTY_BLOCKS

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

    BIG_EMP1    28672     700        323

    1 row selected.

    注意:

    BLOCKS + EMPTY_BLOCKS (700+323=1023)比DBA_SEGMENTS.BLOCKS少1个数据库块,这是因为有一个数据库块被保留用作segment header。DBA_SEGMENTS.BLOCKS 表示分配给这个表的所有的数据库块的数目。USER_TABLES.BLOCKS表示已经使用过的数据库块的数目。

                 

    4) SQL> SELECT COUNT (DISTINCT

    DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||

    DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"

    FROM big_emp1;

    Used

    ----------

    700

    1 row selected.

                  

    5) SQL> delete from big_emp1;

    28672 rows processed.

              

    6) SQL> commit;

    Statement processed.

     

    7) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;

    Statement processed.

     

    8) SQL> SELECT table_name,num_rows,blocks,empty_blocks

    FROM user_tables

    WHERE table_name='BIG_EMP1';

    TABLE_NAME  NUM_ROWS   BLOCKS   EMPTY_BLOCKS

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

    BIG_EMP1    0          700      323

    1 row selected.

     

    9) SQL> SELECT COUNT (DISTINCT

    DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||

    DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"

    FROM big_emp1;

    Used

    ----------

    0 ----这表名没有任何数据库块容纳数据,即表中无数据

    1 row selected.

                            

    10) SQL> TRUNCATE TABLE big_emp1;

    Statement processed.

                       

    11) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;

    Statement processed.

                       

    12) SQL> SELECT table_name,num_rows,blocks,empty_blocks

    2> FROM user_tables

    3> WHERE table_name='BIG_EMP1';

    TABLE_NAME  NUM_ROWS  BLOCKS    EMPTY_BLOCKS

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

    BIG_EMP1    0         0         511

    1 row selected.

                                   

    13) SQL> SELECT segment_name,segment_type,blocks

    FROM dba_segments

    WHERE segment_name='BIG_EMP1';

    SEGMENT_NAME  SEGMENT_TYPE  BLOCKS

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

    BIG_EMP1      TABLE         512

    1 row selected.

    注意:

    TRUNCATE命令回收了由delete命令产生的空闲空间,注意该表分配的空间由原先的1024块降为512块。

    为了保留由delete命令产生的空闲空间,可以使用TRUNCATE TABLE big_emp1 REUSE STORAGE.

    用此命令后,该表还会是原先的1024块。

                     

    四、Oracle表段中的高水位线HWM

    在Oracle数据的存储中,可以把存储空间想象为一个水库,数据想象为水库中的水。水库中的水的位置有一条线叫做水位线,在Oracle中,这条线被称为高水位线(High-warter mark, HWM)。在数据库表刚建立的时候,由于没有任何数据,所以这个时候水位线是空的,也就是说HWM为最低值。当插入了数据以后,高水位线就会上涨,但是这里也有一个特性,就是如果你采用delete语句删除数据的话,数据虽然被删除了,但是高水位线却没有降低,还是你刚才删除数据以前那么高的水位。也就是说,这条高水位线在日常的增删操作中只会上涨,不会下跌。

                  

    下面我们来谈一下Oracle中Select语句的特性。Select语句会对表中的数据进行一次扫描,但是究竟扫描多少数据存储块呢,这个并不是说数据库中有多少数据,Oracle就扫描这么大的数据块,而是Oracle会扫描高水位线以下的数据块。现在来想象一下,如果刚才是一张刚刚建立的空表,你进行了一次Select操作,那么由于高水位线HWM在最低的0位置上,所以没有数据块需要被扫描,扫描时间会极短。而如果这个时候你首先插入了一千万条数据,然后再用delete语句删除这一千万条数据。由于插入了一千万条数据,所以这个时候的高水位线就在一千万条数据这里。后来删除这一千万条数据的时候,由于delete语句不影响高水位线,所以高水位线依然在一千万条数据这里。这个时候再一次用select语句进行扫描,虽然这个时候表中没有数据,但是由于扫描是按照高水位线来的,所以需要把一千万条数据的存储空间都要扫描一次,也就是说这次扫描所需要的时间和扫描一千万条数据所需要的时间是一样多的。所以有时候有人总是经常说,怎么我的表中没有几条数据,但是还是这么慢呢,这个时候其实奥秘就是这里的高水位线了。

                    

    那有没有办法让高水位线下降呢,其实有一种比较简单的方法,那就是采用TRUNCATE语句进行删除数据。采用TRUNCATE语句删除一个表的数据的时候,类似于重新建立了表,不仅把数据都删除了,还把HWM给清空恢复为0。所以如果需要把表清空,在有可能利用TRUNCATE语句来删除数据的时候就利用TRUNCATE语句来删除表,特别是那种数据量有可能很大的临时存储表。

                 

    在手动段空间管理(Manual Segment Space Management)中,段中只有一个HWM,但是在Oracle9iRelease1才添加的自动段空间管理(Automatic Segment Space Management)中,又有了一个低HWM的概念出来。为什么有了HWM还又有一个低HWM呢,这个是因为自动段空间管理的特性造成的。在手段段空间管理中,当数据插入以后,如果是插入到新的数据块中,数据块就会被自动格式化等待数据访问。而在自动段空间管理中,数据插入到新的数据块以后,数据块并没有被格式化,而是在第一次在第一次访问这个数据块的时候才格式化这个块。所以我们又需要一条水位线,用来标示已经被格式化的块。这条水位线就叫做低HWM。一般来说,低HWM肯定是低于等于HWM的。

                      

    五、修正ORACLE表的高水位线

    在ORACLE中,执行对表的删除操作不会降低该表的高水位线。而全表扫描将始终读取一个段(extent)中所有低于高水位线标记的块。如果在执行删除操作后不降低高水位线标记,则将导致查询语句的性能低下。下面的方法都可以降低高水位线标记。

    1.执行表重建指令 alter table table_name move;

    (在线转移表空间ALTER TABLE 。。。 MOVE TABLESPACE 。。。ALTER TABLE 。。。 MOVE 后面不跟参数也行,不跟参数表还是在原来的表空间,move后记住重建索引。如果以后还要继续向这个表增加数据,没有必要move,只是释放出来的空间,只能这个表用,其他的表或者segment无法使用该空间)

    2.执行alter table table_name shrink space; 注意,此命令为Oracle 10g新增功能,再执行该指令之前必须允许行移动alter table table_name enable row movement;

    3.复制要保留的数据到临时表t,drop原表,然后rename临时表t为原表

    4.emp/imp

    5.alter   table  table_name  deallocate   unused  

    6.尽量truncate吧

    展开全文
  • 当对DB2表进行大批量的操作之后,就会发现查询非常慢,索引也不起作用了.这就可能是统计、索引信息没有刷新的问题,以下是解决办法. ...查看高水位线使用: db2 list tablespaces show detail

    当对DB2表进行大批量的操作之后,就会发现查询非常慢,索引也不起作用了.这就可能是统计、索引信息没有刷新的问题,以下是解决办法.

    db2 connect to DBNAME 
    db2 reorg table SCHEMANAME.TABLENAME allow read access 
    db2 runstats on table SCHEMANAME.TABLENAME on all columns with distribution and detailed indexes all 

    查看统计信息使用:

    db2 "SELECT CARD,STATS_TIME FROM SYSCAT.TABLES WHERE TABNAME = 'TABLENAME'" 

    查看高水位线使用:

    db2 list tablespaces show detail
    展开全文
  • DB2 v8.2 降低高水位线经验

    千次阅读 2016-06-17 13:55:59
    测试环境上在反复做几次升级后发现数据库表空间的HWM比used pages 出很多,所以尝试降低高水位线。 开始网上搜索资料讲用runstats、reorg命令,各种尝试后并不管用,后来又了解到db2dart命令,总算让我看到了希望...

    测试环境上在反复做几次升级后发现数据库表空间的HWM比used pages 高出很多,所以尝试降低高水位线。

    开始网上搜索资料讲用runstats、reorg命令,各种尝试后并不管用,后来又了解到db2dart命令,总算让我看到了希望。

    db2dart有三个选项:DHWM(Dump HWM)、LHWM(Lower HWM)、RHWM(Remove HWM) 摘抄自《DB2数据库管理最佳实践》

    只需使用LHWM选项就足够了,该选项会提供降低HWM的建议,下面是使用方法:

    db2dart dbname /lhwm      (回车后会提示输入需要操作的表空间id及期望的HWM值,表空间id可以通过db2 list tablespaces show detail命令查看,期望值可以填used pages的值也可以填0)

    以空格分隔输入两个参数后回车将会生成一个以数据库名开头的.RPT文件,打开该文件即可看到提供的几条建议:


    图中提示先导出表ZJWEB.RUN_STATUS_TABLE的数据再重建该表,再将数据导入,需要注意的是,LHWM给出的建议并不能总是获得预期的结果。

    依照这几条建议做完后发下HWM并没有降低,之后又了解到alter table move命令,由于若index跟表在同一个表空间的话会使index变为UNUSABLE,所以没有尝试。

    从图书馆借到《DB2数据库管理最佳实践》,里面有详细介绍降低高水位线的方法,其中db2dart 命令的使用跟我的操作是一样的,但是从书中还了解到:表在离线重组(Reorg)时会保留原数据,同时在表空间内进行一份数据复制,当复制结束后删除原表数据块。如果HWM下没有足够的空间保存数据复制,则重组不但不能降低HWM,反而会导致HWM增加。

    看到这里恍然大悟,我在每次操作完它的建议后都会reorg一次,而我们普遍用到reorg方式是离线方式(reorg table tabname),在线方式(reorg table tabname inplace allow write access)会记录大量日志甚至是表大小的几倍且速度较慢。

    再次执行它的建议,先将表数据导出,再drop表后重新db2 list tablespaces show detail发现HWM已经降低到接近used pages 了,然后将表重建并导入数据。此时HWM已经跟used pages一致了。

    展开全文
  • 想要确定哪些表需要降低高水位线,要基于有正确的统计信息的,统计信息的错误也将导致结果的错误。 SYS @ prod > SELECT NUM_ROWS ,  2 AVG_ROW_LEN * NUM_ROWS / 1024 / 1024...
    一、什么是水线(High Water Mark)?

         所有的oracle段(segments,在此,为了理解方便,建议把segment作为表的一个同义词) 都有一个在段内容纳数据的上限,我们把这个上限称为"high water mark"或HWM。 这个HWM是一个标记,用来说明已经有多少没有使用的数据块分配给这个segment。HWM通常增长的幅度为一次5个数据块,原则上HWM只会增大,不会缩小,即使将表中的数据全部删除,HWM还是为原值,由于这个特点,使HWM很象一个水库的历史最高水位,这也就是HWM的原始含义,当然不能说一个水库没水了,就说该水库的历史最高水位为0。但是如果我们在表上使用了truncate命令,则该表的HWM会被重新置为0。


    二、HWM数据库的操作有如下影响:

    a) 全表扫描通常要读出直到HWM标记的所有的属于该表数据库块,即使该表中没有任何数据。
    b) 即使HWM以下有空闲的数据库块,键入在插入数据时使用了append关键字,则在插入时使用HWM以上的数据块,此时HWM会自动增大。


    三、查找哪些表需要降低高水位线
    想要确定哪些表需要降低高水位线,要基于有正确的统计信息的,统计信息的错误也将导致结果的错误。

    1. SYS@prod > SELECT NUM_ROWS,
    2.   2 AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9 NEED,
    3.   3 BLOCKS * 8 / 1024 TRUE,
    4.   4 (BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9) RECOVER_MB,
    5.   5 TABLE_NAME
    6.   6 FROM dba_tables
    7.   7 WHERE tablespace_name = 'VASTTBS'
    8.   8 AND BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9 > 100
    9.   9 AND rownum < 11
    10.  10 order by RECOVER_MB desc;
    NUM_ROWS:表中现有的行数
    NEED:行的平均长度*行数/1024/1024/0.9=表需要的空间,单位为MB,其中0.9为1-pctfree得到的。一般情况下创建表pctfree默认都是10%
    TRUE:占用空间
    RECOVER_MB:可恢复的空间
    where条件中,1定位表空间;2筛选出能压缩至少100M的表,也可根据需求增加减少;3显示前11行。

    四、如何降低高水位线
    降低高水位线的方式一共有7种,在这里我们一一列举出来。
    a)    truncate 表
    b)    开启对表的shrink
    c)    CTAS
    d)    表移动
    e)    exp/imp
    f)    expdp/impdp
    g)    在线重定义


    接下来将用实验的方式将各方法的操作以及效果列举出来。

    oracle数据库版本如下
    1. SYS@prod > select * from v$version;
    2. BANNER
    3. --------------------------------------------------------------------------------
    4. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    5. PL/SQL Release 11.2.0.3.0 - Production
    6. CORE 11.2.0.3.0 Production
    7. TNS for Linux: Version 11.2.0.3.0 - Production
    8. NLSRTL Version 11.2.0.3.0 - Production

    实验环境模拟
    1. --创建一张表
    2. VAST@prod > create table ote as select * from dba_objects;
    3. Table created.
    4. --创建索引
    5. VAST@prod > create index ind_ote_id on ote(object_id);
    6. Index created.
    7. --创建同义词
    8. VAST@prod > create or replace public synonym ote for vast.ote;
    9. Synonym created.
    10. --收集统计信息
    11. VAST@prod > ANALYZE TABLE OTE COMPUTE STATISTICS; --ESTIMATE/COMPUTE
    12. Table analyzed.
    13. --段的信息
    14. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
    15. BYTES/1048576 BLOCKS EXTENTS
    16. ------------- ---------- ----------
    17.             9 1152 24
    18. --删除表中数据
    19. VAST@prod > delete from ote;
    20. 75355 rows deleted.
    21. VAST@prod > commit;
    22. Commit complete.
    23. --段的信息
    24. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
    25. BYTES/1048576 BLOCKS EXTENTS
    26. ------------- ---------- ----------
    27.             9 1152 24
    28. --分析表块数,空闲块数,行数
    29. VAST@prod > SELECT blocks, empty_blocks, num_rows
    30.   2 FROM user_tables
    31.   3 WHERE table_name = 'OTE';
    32.     BLOCKS EMPTY_BLOCKS NUM_ROWS
    33. ---------- ------------ ----------
    34.       1099           53      75355
    35. --BLOCKS + EMPTY_BLOCKS (1099+53=1152)DBA_SEGMENTS.BLOCKS 表示分配给这个表的所有的数据库块的数目。
    36. --USER_TABLES.BLOCKS表示已经使用过的数据库块的数目。USER_TABLES.EMPTY_BLOCKS则表示剩余块的数目。
    37. --插入数据
    38. VAST@prod > insert into ote select * from dba_objects where object_id<20000;
    39. 19677 rows created.
    40. VAST@prod > commit;
    41. Commit complete.
    42. --段的信息
    43. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
    44. BYTES/1048576 BLOCKS     EXTENTS
    45. ------------- ---------- ----------
    46.             9       1152         24
    47. --可见即便插入了数据,但是分给OTE段的块数目没有改变,这是因为之前删除了一部分数据,oracle在插入的时候,找到了这些可以被覆盖的块,
    48. --将这些数据插入到这些块中,故没有分配新的块给OTE段
    49. --分析表块数,空闲块数,行数
    50. VAST@prod > SELECT blocks, empty_blocks, num_rows
    51.   2 FROM user_tables
    52.   3 WHERE table_name = 'OTE';
    53.     BLOCKS EMPTY_BLOCKS NUM_ROWS
    54. ---------- ------------ ----------
    55.       1099           53      75355
    56. --使用hint
    57. --使用append将会把新插入的数据插到高水位线之后,省去了查找可覆盖块的时间,这种方式也可提高插入的速度,但是会提升高水位线的高度。
    58. VAST@prod > insert /*+append*/ into ote select * from dba_objects where object_id<20000;
    59. 19677 rows created.
    60. VAST@prod > commit;
    61. Commit complete.
    62. --段的信息
    63. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
    64. BYTES/1048576 BLOCKS     EXTENTS
    65. ------------- ---------- ----------
    66.            11       1408         26
    67. --可见这次没有查找可覆盖的块,而是直接将数据插在了高水位线之后的空闲块中。        
    68. --收集统计信息
    69. VAST@prod > ANALYZE TABLE OTE COMPUTE STATISTICS; --ESTIMATE/COMPUTE
    70. Table analyzed.
    71. --分析表块数,空闲块数,行数
    72. VAST@prod > SELECT blocks, empty_blocks, num_rows
    73.   2 FROM user_tables
    74.   3 WHERE table_name = 'OTE';
    75.     BLOCKS EMPTY_BLOCKS NUM_ROWS
    76. ---------- ------------ ----------
    77.       1370           38      39354
    78. --对表的信息查看时,建议先进行统计信息的收集,不然会得到错误的信息。

    1、truncate 表
    1. --截断表
    2. VAST@prod > truncate table ote;
    3. Table truncated.
    4. --收集统计信息
    5. VAST@prod > analyze table ote compute statistics; --estimate/compute
    6. Table analyzed.
    7. --段的信息
    8. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
    9. BYTES/1048576 BLOCKS     EXTENTS
    10. ------------- ---------- ----------
    11.         .0625          8          1
    12. --此时可见OTE的段只剩下8个块了,因为表还是存在的,段头还是记录了一些基本信息的。        
    13. --分析表块数,空闲块数,行数
    14. VAST@prod > SELECT blocks, empty_blocks, num_rows
    15.   2 FROM user_tables
    16.   3 WHERE table_name = 'OTE';
    17.     BLOCKS EMPTY_BLOCKS NUM_ROWS
    18. ---------- ------------ ----------
    19.          0            8          0
    20. --检查索引以及同义词是否可用
    21. VAST@prod > col name for a15;
    22. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
    23.   2 union all
    24.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';

    25. TYPE    NAME            STATUS
    26. ------- --------------- --------
    27. INDEX   IND_OTE_ID      VALID
    28. SYNONYM IND_OTE_ID      VALID        
    29. --可见此种方式执行过后,原索引以及同义词还是可用的。
    优点:速度快
    缺点:表中数据全无
    建议:当需要delete全表的时候,确定表中数据无用,可以使用这种方式进行清理。切记,谨慎。


    接下来的六种方式,均采用清理表中部分数据,与开始对全表进行delete略有不同。


    2、开启表的shrink
    1. --开启行迁移
    2. VAST@prod > alter table ote enable row movement;
    3. Table altered.
    4. --查看表的状态
    5. VAST@prod > select table_name,
    6.   2 tablespace_name,
    7.   3 ROW_MOVEMENT,
    8.   4 read_only,
    9.   5 segment_created
    10.   6 from user_tables
    11.   7 where table_name = 'OTE';
    12. TABLE_NAME                     TABLESPACE_NAME                ROW_MOVE REA SEG
    13. ------------------------------ ------------------------------ -------- --- ---
    14. OTE                            VASTTBS                        ENABLED  NO  YES
    15. --开启shrink
    16. VAST@prod > alter table ote shrink space;
    17. Table altered.
    18. --关闭行迁移
    19. VAST@prod > alter table ote disable row movement;
    20. Table altered.
    21. --查看表的状态
    22. VAST@prod > select table_name,
    23.   2 tablespace_name,
    24.   3 ROW_MOVEMENT,
    25.   4 read_only,
    26.   5 segment_created
    27.   6 from user_tables
    28.   7 where table_name = 'OTE';
    29. TABLE_NAME                     TABLESPACE_NAME                ROW_MOVE REA SEG
    30. ------------------------------ ------------------------------ -------- --- ---
    31. OTE                            VASTTBS                        DISABLED NO YES
    32. --收集统计信息
    33. VAST@prod > analyze table ote compute statistics;
    34. Table analyzed.
    35. --段的信息
    36. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
    37. BYTES/1048576 BLOCKS     EXTENTS
    38. ------------- ---------- ----------
    39.         3.375        432         19
    40. --分析表块数,空闲块数,行数
    41. VAST@prod > SELECT blocks, empty_blocks, num_rows
    42.   2 FROM user_tables
    43.   3 WHERE table_name = 'OTE';

    44.     BLOCKS EMPTY_BLOCKS NUM_ROWS
    45. ---------- ------------ ----------
    46.        413           19      29678
    47. --检查索引以及同义词是否可用
    48. VAST@prod > col name for a15;
    49. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
    50.   2 union all
    51.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';

    52. TYPE    NAME            STATUS
    53. ------- --------------- --------
    54. INDEX   IND_OTE_ID      VALID
    55. SYNONYM IND_OTE_ID      VALID
    优点:对于表中已清除部分数据依然可用,
    缺点:暂不详

    3、CTAS
    1. --创建一张新表
    2. VAST@prod > create table ote1 as select * from ote;
    3. Table created.
    4. --收集统计信息
    5. VAST@prod > analyze table ote1 compute statistics;
    6. Table analyzed.
    7. --段的信息
    8. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE1';
    9. BYTES/1048576 BLOCKS EXTENTS
    10. ------------- ---------- ----------
    11.             4        512         19
    12. --分析表块数,空闲块数,行数
    13. VAST@prod > SELECT blocks, empty_blocks, num_rows
    14.   2 FROM user_tables
    15.   3 WHERE table_name = 'OTE1';
    16.     BLOCKS EMPTY_BLOCKS NUM_ROWS
    17. ---------- ------------ ----------
    18.        429           83      29678
    19. --检查索引以及同义词是否可用
    1. 原索引以及同义词依然可用,但是在同义词以及索引是指向原OTE表。
    2. 建议:对于这种情况,可以在业务可以停止的时候进行,在ote1表新建对应索引,对应同义词等。然后rename OTE表,再将OTE1 rename为OTE。又或者将CTAS后将原表truncate,再将数据导入。

    4、使用table move的方式
    1. --使用table move的方式
    2. VAST@prod > alter table ote move tablespace vasttbs;
    3. Table altered.
    4. --收集统计信息
    5. VAST@prod > analyze table ote compute statistics;
    6. Table analyzed.
    7. --段的信息
    8. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
    9. BYTES/1048576 BLOCKS     EXTENTS
    10. ------------- ---------- ----------
    11.             4        512         19
    12. --分析表块数,空闲块数,行数
    13. VAST@prod > SELECT blocks, empty_blocks, num_rows
    14.   2 FROM user_tables
    15.   3 WHERE table_name = 'OTE';
    16.     BLOCKS EMPTY_BLOCKS NUM_ROWS
    17. ---------- ------------ ----------
    18.        429           83      29678
    19. --检查索引以及同义词是否可用
    20. VAST@prod > col name for a15;
    21. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
    22.   2 union all
    23.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
    24. TYPE    NAME            STATUS
    25. ------- --------------- --------
    26. INDEX   IND_OTE_ID      UNUSABLE
    27. SYNONYM IND_OTE_ID      VALID
    28. 发现这时,索引已经失效,对索引进行在线重建。
    29. --在线重建索引
    30. VAST@prod > alter index ind_ote_id rebuild online;
    31. Index altered.
    32. --检查索引以及同义词是否可用
    33. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
    34.   2 union all
    35.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
    36. TYPE    NAME            STATUS
    37. ------- --------------- --------
    38. INDEX   IND_OTE_ID      VALID
    39. SYNONYM IND_OTE_ID      VALID
    优点:降低了高水位线
    缺点:因为表的移动,需要将索引进行重建

    5、exp/imp
    1. --exp/imp降低高水位线
    2. [oracle@vedeu ~]$ exp vast/vast file=/home/oracle/ote.dmp tables=ote log=ote.log
    3. Export: Release 11.2.0.3.0 - Production on Mon Jan 23 11:54:49 2017
    4. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
    5. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    6. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    7. Export done in US7ASCII character set and AL16UTF16 NCHAR character set
    8. server uses AL32UTF8 character set (possible charset conversion)
    9. About to export specified tables via Conventional Path ...
    10. . . exporting table OTE 29678 rows exported
    11. EXP-00091: Exporting questionable statistics.
    12. EXP-00091: Exporting questionable statistics.
    13. Export terminated successfully with warnings.
    14. --删除ote表
    15. VAST@prod > drop table ote purge;
    16. Table dropped.
    17. --导入表
    18. [oracle@vedeu ~]$ imp vast/vast file=/home/oracle/ote.dmp full=y
    19. Import: Release 11.2.0.3.0 - Production on Mon Jan 23 11:56:23 2017
    20. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
    21. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    22. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    23. Export file created by EXPORT:V11.02.00 via conventional path
    24. import done in US7ASCII character set and AL16UTF16 NCHAR character set
    25. import server uses AL32UTF8 character set (possible charset conversion)
    26. . importing VAST's objects into VAST
    27. . importing VAST's objects into VAST
    28. . . importing table "OTE" 29678 rows imported
    29. Import terminated successfully without warnings.
    30. --收集统计信息
    31. VAST@prod > analyze table ote compute statistics;
    32. Table analyzed.
    33. --段的信息
    34. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
    35. BYTES/1048576 BLOCKS     EXTENTS
    36. ------------- ---------- ----------
    37.            4         512         19
    38. --分析表块数,空闲块数,行数
    39. VAST@prod > SELECT blocks, empty_blocks, num_rows
    40. 2 FROM user_tables
    41. 3 WHERE table_name = 'OTE';
    42. BLOCKS EMPTY_BLOCKS NUM_ROWS
    43. ---------- ------------ ----------
    44.        496           16      29678
    45. --检查索引以及同义词是否可用
    46. VAST@prod > col name for a15;
    47. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
    48. 2 union all
    49. 3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
    50. TYPE    NAME            STATUS
    51. ------- --------------- --------
    52. INDEX   IND_OTE_ID      VALID
    53. SYNONYM IND_OTE_ID      VALID

    6、expdp/impdp
        expdp/impdp这种方式也可以降低高水位线,方法类似exp/imp就不在做介绍。
    7、在线重定义
    1. --在线重定义
    2. 在线重定义同时也是我比较喜欢的一种方式,因为它可以不停业务,就完成降低高水位线。
    3. --设定主键
    4. VAST@prod > alter table ote add constraint pk_ote_id primary key (object_id);
    5. Table altered.
    6. --检查是否可以在线重定义
    7. VAST@prod > exec dbms_redefinition.can_redef_table( 'VAST','OTE',dbms_redefinition.cons_use_pk);
    8. PL/SQL procedure successfully completed.
    9. --定义中间表
    10. VAST@prod > create table ote_interim as select * from ote where 1=0;
    11. Table created
    12. --开始在线重定义
    13. VAST@prod > exec dbms_redefinition.start_redef_table('VAST','OTE','OTE_INTERIM',
    14.                                                    col_mapping => 'OWNER OWNER,
    15.                                                                    OBJECT_NAME OBJECT_NAME,
    16.                                                                    SUBOBJECT_NAME SUBOBJECT_NAME,
    17.                                                                    OBJECT_ID OBJECT_ID,DATA_OBJECT_ID DATA_OBJECT_ID,
    18.                                                                    OBJECT_TYPE OBJECT_TYPE,
    19.                                                                    CREATED CREATED,
    20.                                                                    LAST_DDL_TIME LAST_DDL_TIME,
    21.                                                                    TIMESTAMP TIMESTAMP,
    22.                                                                    STATUS STATUS,
    23.                                                                    TEMPORARY TEMPORARY,
    24.                                                                    GENERATED GENERATED,
    25.                                                                    SECONDARY SECONDARY,
    26.                                                                    NAMESPACE NAMESPACE,
    27.                                                                    EDITION_NAME EDITION_NAME',
    28.                                                     options_flag => dbms_redefinition.cons_use_pk);
    29. PL/SQL procedure successfully completed.
    30. --处理索引以及约束对象
    31. VAST@prod > declare
    32.   2 error_count number;
    33.   3 begin
    34.   4 error_count := 0;
    35.   5 dbms_redefinition.copy_table_dependents(uname => 'VAST',orig_table => 'OTE',
    36.   6 int_table => 'OTE_INTERIM',
    37.   7 copy_indexes => dbms_redefinition.cons_orig_params,
    38.   8 num_errors => error_count);
    39.   9 dbms_output.put_line(to_char(error_count));
    40.  10 end;
    41.  11 /
    42. PL/SQL procedure successfully completed.
    43. --结束在线重定义
    44. VAST@prod > exec dbms_redefinition.finish_redef_table('VAST','OTE','OTE_INTERIM');
    45. PL/SQL procedure successfully completed.
    46. --收集统计信息
    47. VAST@prod > analyze table ote compute statistics;
    48. Table analyzed.
    49. --段的信息
    50. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
    51. BYTES/1048576 BLOCKS     EXTENTS
    52. ------------- ---------- ----------
    53.             4        512         19
    54. --分析表块数,空闲块数,行数
    55. VAST@prod > SELECT blocks, empty_blocks, num_rows
    56.   2 FROM user_tables
    57.   3 WHERE table_name = 'OTE';
    58.     BLOCKS EMPTY_BLOCKS NUM_ROWS
    59. ---------- ------------ ----------
    60.        429           83      29678
    61. --检查索引以及同义词是否可用
    62. col name for a15;
    63. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
    64.   2 union all
    65.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
    66. TYPE    NAME            STATUS
    67. ------- --------------- --------
    68. INDEX   IND_OTE_ID      VALID
    69. SYNONYM IND_OTE_ID      VALID
    优点:降低高水位线的同时,它是这七种办法中唯一不需要停业务的方式。
    建议:生产中大多数时候是没办法停止的,即便有停机时间,还是有其他好多操作要进行,在线重定义提供了很大的帮助。然而在线重定义的作用还远远不止于此。


    -------END-------








    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30820196/viewspace-2132866/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/30820196/viewspace-2132866/

    展开全文
  • 高水位线详解

    2018-12-03 10:11:28
    在 Oracle 数据的存储中,可以把存储空间想象为一个水库,数据想象为水 库中的水。水库中的水的位置有一条线叫做水位线,在 Oracle 中,这条线被称为`高水位线(High-warter mark, HWM)
  • 降低oracle高水位线方法总结

    千次阅读 2018-07-10 11:27:27
    1. 执行表重建指令 alter table table_name move(验证不可行,不降低水位线,但可释放表空间)当你创建了一个对象如表以后,不管你有没有插入数据,它都会占用一些块,ORACLE也会给它分配必要的空间.同样,用ALTER ...
  • Oracle之降低高水位线

    千次阅读 2015-10-14 08:46:28
    从10g开始,Oracle开始提供Shrink的命令,假如我们的表空间中支持自动段空间管理(ASSM),就可以使用这个特性缩小段,即降低HWM。这里需要强调一点,10g的这个新特性,仅对ASSM表空间有效,否则会报 ORA-10635: ...
  • 降低高水位线

    2016-08-22 08:47:35
    1、建立初始化数据演示数据表中的高水位的变化(1)、建立用户和表SYS@odb>create user test8 identified by test8;SYS@odb>grant connect,resource to test8;SYS@odb>conn test8/test8TEST8@odb>create...
  • 手动降低高水位线

    2016-10-16 00:09:11
    一张表,当用delete删除时,数据是被删除了,可是在数据块中的空间还没有释放,则高水位线 依然在原来的位置,当我们查询该表的数据时候,空块依然还被搜索。为了提高查询效率,降低I/O 的读取,我们不得不进行...
  • 硬盘空间不足,打算删除数据库中的多余数据,但删除数据后,硬盘硬盘空间不能释放。 【delete后用:alter table table_name move truncate后...--delete/truncate删除,释放表空间、降低高水位线、resize释放磁盘空间相关
  • 在前一段时间,生产环境中有几个很大的分区表,由于存在太多的碎片,导致表里的数据就几十条,但是查询的时候特别...很明显是高水位线导致的问题。 一般来说这类问题,使用备份->truncate->insert的方式比较保...
  • 如果使用:db2 list tablespaces show detail,看到某个DMS表空间的已用页数低于高水位标记,则有可能通过如下方法降低高水位标记:重组表空间的某个表;或者将某个表中的数据导出,然后将它删除,重新创建该表再将...
  • 降低高水位线的方法

    2016-09-09 16:20:23
    不但可以重置水位线( HWM ),解决松散表带来的 IO 浪费,还可以解决表中的行迁移问题。  move表的话需要双倍的空间,否则无法成功。move表可以通过重新安排数据文件的空间达到收缩数据文件的目的。  move表...
  • 一、Oracle表段中的高水位线HWM 在Oracle数据的存储中,可以把存储空间想象为一个水库,数据想象为水库中的水。水库中的水的位置有一条线叫做水位线,在Oracle中,这条线被称为高水位线(High-warter mark, HWM)。...
  • 在一个数据库使用下面SQL找出了一批需要降低高水位线的表,其中有几个表没有数据,于是我打算用TRUNCATE来降低高水位线HWM SELECT a.owner, a.segment_name, a.segment_type, a.tablespace_name, a.blocks "real ...
  • 降低一个表的高水位线(HWM)的6种方式,6种方式修正ORACLE表的高水位线  在ORACLE中,执行对表的删除操作不会降低该表的高水位线。而全表扫描将始终读取一个段(extent)中所有低于高水位线标记的块。如果在执行删除...
  • oracle 高水位线详解

    2020-08-10 10:55:02
    一、什么是水线(High Water Mark)?...HWM通常增长的幅度为一次5个数据块,原则上HWM只会增大,不会缩小,即使将表中的数据全部删除,HWM还是为原值,由于这个特点,使HWM很象一个水库的历史最高水位,这也就是HWM的原始
  • SQL优化_高水位线导致的性能问题

    千次阅读 2015-07-07 09:54:14
    今天晨会,开发人员说SIT数据库有一张表,之前查询很快就能返回结果,现在需要5分钟才能返回结果,需要协助查找原因并优化,数据库版本11.2.0.4 64 bit for linux,SQL语句如下: ...select * FROM TB_XXXX a ...
  • 表在删除数据的时候高水位线降低,只是把块中的此行数据变为删除状态。如此一来,表中删除的数据多时候,表就会浪费很多空间。如下所示,表中的14天记录占用了28个块。 我们利用shrink space来降低表的高水位线...
  • 硬盘空间不足,打算删除数据库中的多余数据,但删除数据后,硬盘硬盘空间不能释放。【delete后用:alter table table_name move ...--delete/truncate删除,释放表空间、降低高水位线、resize释放磁盘空间相关优化汇总--...
  • -- 创建表空间,表空间不自动扩展,表,插入数据,直到不能插入为止 。 SYS@test> create tablespace tbs_hmw datafile '/u01/oradata/test/tbs_hmw01.dbf' size 380M autoextend off; Tablespace created. ...
  • Oracle 降低高水位线

    2015-03-30 16:56:57
    高水位线 什么是高水位线(High Water Mark)? 所有的oracle段(segments)都有一个在段内容纳数据的上限,我们把这个上限称为“High Water Mark” 或HWM。这个HWM是一个标...
  • oracle高水位线,终于知道DELETE和TRUNCATE为什么不一样了

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,630
精华内容 1,852
关键字:

降低高水位线