Oracle 并行DDL介绍

 

     Oracle并行中最有用的就是并行DDL。我们都知道，并行执行通常不适用于OLTP系统。实际上，对于数据仓库，并行查询也变得越来越没有意义。因为目前数据仓库，可能会基于一个web前端，通常也会有数以百计的用户同时进行访问。

     那么，唯一的好处就是体现在DBA维护数据库方面了。因为，DBA维护数据库的时候，通常都能保证服务器有足够的资源能够使用（CPU，I/O通道等等）。比如，DBA要做一件事情：加载某个数据，重组表，再重建索引。这些操作可以并行执行，最大限度利用硬件的全部能力。以下一些DDL命令允许并行操作：

     1. create index：创建索引

     2. create table as select：select查询可以并行操作，新表插入数据也可以并行

     3. alter index rebuild：重建索引

     4. alter table move：表迁移

     5. alter table split/coalesce partition：单个表分区可以并行地分解或合并

     6. alter index split partition：索引分区可以并行地分解

 

     下面我们以create table as select 为例进行说明

 

      1. 原理介绍

      假设执行一个并行create table as select，并行度为4。那么，每一个并行服务都会分配自己的区段，向其写入数据，写满之后，再分配一个新区段。 每一个并行服务不会使用另外一个并行服务的区段，如下图所示：

 

      P003加载了4个区段，P000加载了5个区段，他们各自互不影响。看上去一切很美好，但是这里面隐含一个表空间“过度浪费”问题。假设我们要加载1010M数据，用10个并行执行服务器来加载这个数据，每一个并行服务器加载101M数据，而每一个区段是100M。那么，每一个并行服务器会分配两个区段，第一个用完，第二个都只用1M空间。也就是总共有990M空间已分配未使用，虽然下次加载的时候会被用到，但是对于现在而言，就有990M的死空间。这个时候，是否可以将这些只用了很小一部分的区段截断开来，取决于表空间的管理机制。

 

     二. 表空间创建

     1. 不支持区段截断表空间

 

create tablespace lmt_uniform
 datafile 'D:\oraclelws\product\10.2.0\oradata\orcl\lmt_uniform.dbf' size 1048640K reuse
 autoextend on next 100m
 extent management local
 uniform size 100m;

 

    2. 支持区段截断表空间

 

create tablespace lmt_auto
 datafile 'D:\oraclelws\product\10.2.0\oradata\orcl\lmt_auto.dbf' size 1048640K reuse
 autoextend on next 100m
 extent management local
autoallocate;

 

     3. 创建big_table 表，参考我的如下一篇文章 http://liwenshui322.iteye.com/blog/1741132

 

 

     三. 并行创建表

     1. 不支持区段截断的表

 

create table uniform_test
 parallel
 tablespace lmt_uniform
 as
 select * from big_table;

 

     2. 支持区段截断的表

 

create table autoallocate_test
 parallel
 tablespace lmt_auto
 as
 select * from big_table;

 

    四. 表空间利用情况

    1. 查看这两个表使用块情况

 

select segment_name, blocks, extents
 from user_segments
 where segment_name in ( 'UNIFORM_TEST', 'AUTOALLOCATE_TEST' );

 

    结果如下图所示：

   

 

    由于我的机器块大小是8kB，二者相差45432个块，也就是45432*8/1024=355M

 

    2. 查看表具体使用空间情况

    首先创建一个存储过程

create or replace procedure show_space   
( p_segname in varchar2,   
  p_owner   in varchar2 default user,   
  p_type    in varchar2 default 'TABLE',   
  p_partition in varchar2 default NULL )   
-- This procedure uses AUTHID CURRENT USER so it can query DBA_*   
-- views using privileges from a ROLE and so it can be installed   
-- once per database, instead of once per user who wanted to use it.   
AUTHID CURRENT_USER   
as   
    l_free_blks                 number;   
    l_total_blocks              number;   
    l_total_bytes               number;   
    l_unused_blocks             number;   
    l_unused_bytes              number;   
    l_LastUsedExtFileId         number;   
    l_LastUsedExtBlockId        number;   
    l_LAST_USED_BLOCK           number;   
    l_segment_space_mgmt        varchar2(255);   
    l_unformatted_blocks number;   
    l_unformatted_bytes number;   
    l_fs1_blocks number; l_fs1_bytes number;   
    l_fs2_blocks number; l_fs2_bytes number;   
    l_fs3_blocks number; l_fs3_bytes number;   
    l_fs4_blocks number; l_fs4_bytes number;   
    l_full_blocks number; l_full_bytes number;   
  
    -- Inline procedure to print out numbers nicely formatted   
    -- with a simple label.   
    procedure p( p_label in varchar2, p_num in number )   
    is   
    begin   
        dbms_output.put_line( rpad(p_label,40,'.') ||   
                              to_char(p_num,'999,999,999,999') );   
    end;   
begin   
   -- This query is executed dynamically in order to allow this procedure   
   -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES   
   -- via a role as is customary.   
   -- NOTE: at runtime, the invoker MUST have access to these two   
   -- views!   
   -- This query determines if the object is an ASSM object or not.   
   begin   
      execute immediate   
          'select ts.segment_space_management   
             from dba_segments seg, dba_tablespaces ts   
            where seg.segment_name      = :p_segname   
              and (:p_partition is null or   
                  seg.partition_name = :p_partition)   
              and seg.owner = :p_owner   
              and seg.tablespace_name = ts.tablespace_name'   
             into l_segment_space_mgmt   
            using p_segname, p_partition, p_partition, p_owner;   
   exception   
       when too_many_rows then   
          dbms_output.put_line   
          ( 'This must be a partitioned table, use p_partition => ');   
          return;   
   end;   
  
  
   -- If the object is in an ASSM tablespace, we must use this API   
   -- call to get space information; else we use the FREE_BLOCKS   
   -- API for the user managed segments.   
   if l_segment_space_mgmt = 'AUTO'   
   then   
     dbms_space.space_usage   
     ( p_owner, p_segname, p_type, l_unformatted_blocks,   
       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,   
       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,   
       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);   
  
     p( 'Unformatted Blocks ', l_unformatted_blocks );   
     p( 'FS1 Blocks (0-25)  ', l_fs1_blocks );   
     p( 'FS2 Blocks (25-50) ', l_fs2_blocks );   
     p( 'FS3 Blocks (50-75) ', l_fs3_blocks );   
     p( 'FS4 Blocks (75-100)', l_fs4_blocks );   
     p( 'Full Blocks        ', l_full_blocks );   
  else   
     dbms_space.free_blocks(   
       segment_owner     => p_owner,   
       segment_name      => p_segname,   
       segment_type      => p_type,   
       freelist_group_id => 0,   
       free_blks         => l_free_blks);   
  
     p( 'Free Blocks', l_free_blks );   
  end if;   
  
  -- And then the unused space API call to get the rest of the   
  -- information.   
  dbms_space.unused_space   
  ( segment_owner     => p_owner,   
    segment_name      => p_segname,   
    segment_type      => p_type,   
    partition_name    => p_partition,   
    total_blocks      => l_total_blocks,   
    total_bytes       => l_total_bytes,   
    unused_blocks     => l_unused_blocks,   
    unused_bytes      => l_unused_bytes,   
    LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,   
    LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,   
    LAST_USED_BLOCK => l_LAST_USED_BLOCK );   
  
    p( 'Total Blocks', l_total_blocks );   
    p( 'Total Bytes', l_total_bytes );   
    p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );   
    p( 'Unused Blocks', l_unused_blocks );   
    p( 'Unused Bytes', l_unused_bytes );   
    p( 'Last Used Ext FileId', l_LastUsedExtFileId );   
    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );   
    p( 'Last Used Block', l_LAST_USED_BLOCK );   
end; 

 

    查看uniform_test表使用空间情况

    先执行这条SQL：set serveroutput on

exec show_space('UNIFORM_TEST');

    结果如下图所示：

   

    对比autoallocate_test表使用空间情况

 

exec show_space('AUTOALLOCATE_TEST' );  

   

 

    我们发现，不使用区段截断的表空间，除开里面unformatted blocks数量，差不多就跟使用区段截断表空间块的数量相等了。这些unformatted blocks就是那些已分配未使用的块。