Oracle 12cR2新特性研究：表分区在线DDL带来的影响

字数 7999阅读 5163评论 0赞 1

作者：贺耀东

背景

Oracle 12cR2提供了表（分区）在线DDL的功能，在执行MOVE、SPLIT操作时可以不影响对相关表（分区）的DML执行。
单独执行一次online的操作当然没问题，Oracle这么大的公司，这样基础的测试肯定是做了的。还需要关注的是如果连续多次执行类似操作，是否会出现内存泄漏等问题，毕竟一次正确执行并不难，难的是持续正确执行。

现象

我们定义了一个分区表，表上有一个全局索引和一个本地索引，在表上并发执行两个session，一个持续执行MOVE操作，一个持续执行insert操作，都在后台执行（具体脚本见附件1）。经过一晚上运行后，查看对比系统运行状态：
Ø SGA。比对运行前后SGA各主要组件是否有剧烈变化，如果有组件暴涨意味着可能会导致ORA-4031内存无法分配的问题。实际比对无异常。
Ø PGA。比对运行前后OS剩余内存和数据库PGA、Session PGA的使用是否有大的变化，如果有意味着可能会有PGA内存泄漏。实际比对无异常。
Ø 空间使用。MOVE过程中占用两份空间是正常的，INSERT也会造成空间使用的不断增加，但是空间使用的异常增加无疑是有问题的。对，你猜对了，空间使用确实异常增加了。

探究

全局索引占用空间达到了10个G，对比本地索引占用200M，分区空间共使用400M。rebuild重建后，全局索引空间使用200M。
进一步的测试发现，只要分区里面有数据，不需要INSERT，只MOVE就可以重现全局索引空间暴涨的现象。
深入研究得知，这是因为12c中对全局索引启用了异步维护机制（Asynchronous Global Index Maintenance）,文档（https://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm#VLDBG14107）中仅提到了DROP、TRUNCATE操作，但MOVE也会造成这个效果。当数据库进行这些操作时，应该是仅仅将全局索引中失效的记录置了标志位，并没有立即处理，因此它们在索引中占据的空间无法被复用。
那何时会被处理呢？Oracle专门有一个自动任务SYS.PMO_DEFERRED_GIND_MAINT_JOB来处理所有的全局索引，该JOB设计为默认在每天凌晨2:00运行。手工可以直接调用DBMS_SCHEDULER.RUN_JOB的方式来启动。

扩展

本着一件事情给N种办法处理的原则，Oracle也提供了各种其他的办法，包括但不局限于：
Ø ALTER INDEX [PARTITION] COALESCE CLEANUP。COALESCE这个办法在对付enq：Index – contention的时候我们在业务低谷期用过，对DML基本没有影响，加个cleanup应该也影响不大。
Ø ALTER INDEX [PARTITION] REBULID。索引重建，最彻底的办法。
Ø DBMS_PART.CLEANUP_GIDX。可以对系统中所有需要清理的全局索引进行清理，也可以指定用户、指定表进行。它会调用前面的COALESCE CLEAN。
Ø DBMS_PART.CLEANUP_ONLINE_OP。
这个存储过程是专治在线MOVE中出现的各种问题，负责扫尾的，顺便也会把全局索引清理一下。

这些办法我们经过测试，把遇到的各种情况给大家一并汇报一下：
Ø DBMS_PART.CLEANUP_GIDX相当贴心，如果它没有发现任何需要清理的全局索引，也会抛出ORA-20000错误来送你个大大的礼包，惊喜不惊喜，意外不意外？
Ø 当执行完了DBMS_PART.CLEANUP_GIDX、COALESCE CLEANUP，多余的空间会释放吗？不会，仍然会维持在原来的大小。通过DBMS_SPACE.SPACE_USAGE（使用办法见附件2）对比执行前后该索引的块空间使用状况可以看到，处理前对象中基本都是Full Blocks，而处理后对象中的Unformatted Blocks和FS2 Blocks(25-50% Free)都有显著增加。此时这些空间才可能被重复利用。
Ø ALL_INDEXES里面有字段ORPHANED_ENTRIES可以展示该索引是否有stale entries，但该字段为字符型（存储值YES、NO），不是数字型。
Ø PMO_DEFERRED_GIND_MAINT_JOB在DBMS_SCHEDULER_JOBS视图中看LAST_START_DATE，确实在每日凌晨2点启动了，查看对象块空间使用情况也改变了，但在DBMS_SCHEDULER_JOB_RUN_DETAILS里面并没有对应记录，因此无法了解是否出现了任务超时等异常。

结论

1.DROP、TRUNCATE、MOVE等分区操作会导致全局索引空间占用增加，这是因为全局索引中有stale entries，这部分空间无法立即被复用。
2.Oracle提供了异步清理机制，根据当前理解Oracle提供信息不够详细，大数据量数据库中无法确认当天JOB是否处理正常完成了。
3.对大表的以上操作，还是建议手工处理，避免后续影响。

相关脚本

1、测试脚本
以下未指明均为非sys用户执行，sys用户不能执行自身表在线move操作，事先创建两个测试用表空间users、dbs2：

grant select any table to scott;
create table ins_test tablespace users PARTITION by range(object_id)( 
PARTITION part1 values less than (20000) tablespace users , 
PARTITION part2 values less than (40000) tablespace users , 
PARTITION part3 values less than (60000) tablespace users , 
PARTITION part4 values less than (80000) tablespace users , 
PARTITION partmax values less than (maxvalue) tablespace users 
) 
as select * from  dba_objects where object_id > 0; 
create index ind_g on ins_test(object_id,object_name) tablespace users; 
create index ind_l on ins_test(object_name,object_id) local; 
create sequence seqT cache 20; 
Alter system set enable_ddl_logging = true scope=both;

在一个session执行：

declare 
        i number; 
        n number;     
begin 
        n:= 0; 
        dbms_output.enable(1000000); 
        for i in 1..10000 
        loop            
                if(n < 1) then 
                        --dbms_lock.sleep(1); 
                          execute immediate 'Alter table ins_test move partition part1 tablespace dbs2 update indexes online'; 
                          execute immediate 'Alter table ins_test move partition part2 tablespace dbs2 update indexes online'; 
                          execute immediate 'Alter table ins_test move partition part3 tablespace dbs2 update indexes online'; 
                          execute immediate 'Alter table ins_test move partition part4 tablespace dbs2 update indexes online'; 
                          execute immediate 'Alter table ins_test move partition partmax tablespace dbs2 update indexes online'; 
                          n :=  1; 
                else 
                          execute immediate 'Alter table ins_test move partition part1 tablespace users update indexes online'; 
                          execute immediate 'Alter table ins_test move partition part2 tablespace users update indexes online'; 
                          execute immediate 'Alter table ins_test move partition part3 tablespace users update indexes online'; 
                          execute immediate 'Alter table ins_test move partition part4 tablespace users update indexes online'; 
                          execute immediate 'Alter table ins_test move partition partmax tablespace users update indexes online'; 
                                n := 0; 
                end if; 
        dbms_output.put_line(i||to_char(sysdate,' dd hh24:mi:ss')|| 'seq:'|| seqT.nextval);                     
        end loop; 
  EXCEPTION 
  WHEN OTHERS THEN 
          dbms_output.put_line(i||to_char(sysdate,' dd hh24:mi:ss'));         
    DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);              
end; 
/

在另一个session执行：

create table ins_test2 tablespace users  as select * from dba_objects ; 
declare 
        i number; 
        n number;     
begin 
        n:= 0; 
        dbms_output.enable(1000000); 
        for i in 1..10000 
        loop 
                --tmp_tables(i) := lpad('a',1000,'a'); 
                insert into ins_test  select * from ins_test2; 
                commit; 
                dbms_lock.sleep(1);           
        dbms_output.put_line(i||to_char(sysdate,' dd hh24:mi:ss'));                    
        end loop; 
  EXCEPTION 
  WHEN OTHERS THEN 
          dbms_output.put_line(i||to_char(sysdate,' dd hh24:mi:ss'));         
    DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);            
end; 
/

2、查看对象空间使用脚本

/*****************************/
/* File: segspace.sql               */
/* Author: Wuduogang           */
/* Description:                       */
/* Script to show segment space usage */
/* Usage:                              */
/* @segspace                        */
/* input the segment owner     */
/* input the segment name      */
/******************************/
set echo off feedback off timing off pause off verify off
set pages 100 lines 132 trimspool on trimout on space 1 recsep off
set linesize 155 pagesize 99 verify off feedback off echo off
set serveroutput on size 10000;
accept segment_owner prompt "Enter the Segment Owner: "
accept segment_name  prompt "Enter the Segment Name: "
col owner new_value v_owner format a20
col name  new_value v_name  format a30
col type  new_value v_type  format a12
prompt
prompt Check the segment information
prompt ====================
select owner
      ,segment_name name
      ,segment_type type
from dba_segments
where upper(owner)=upper('&&segment_owner')
and   upper(segment_name)=upper('&&segment_name')
;
prompt
pause "Press Enter to continue,Press Ctrl+C to quit"
declare
  v_unformatted_blocks number;
  v_unformatted_bytes number;
  v_fs1_blocks number;
  v_fs1_bytes number;
  v_fs2_blocks number;
  v_fs2_bytes number;
  v_fs3_blocks number;
  v_fs3_bytes number;
  v_fs4_blocks number;
  v_fs4_bytes number;
  v_full_blocks number;
  v_full_bytes number;
begin
  dbms_space.space_usage('&&v_owner', '&&v_name', '&&v_type',v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks,v_fs1_bytes,v_fs2_blocks, v_fs2_bytes,v_fs3_blocks,
v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks,v_full_bytes);
  dbms_output.put_line('&&v_type &&v_owner'||'.'||'&&v_name Space Usage');
  dbms_output.put_line('========');
  dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
  dbms_output.put_line('FS1 Blocks(0-25% Free) = '||v_fs1_blocks);
  dbms_output.put_line('FS2 Blocks(25-50% Free) = '||v_fs2_blocks);
  dbms_output.put_line('FS3 Blocks(50-75% Free) = '||v_fs3_blocks);
  dbms_output.put_line('FS4 Blocks(75-100% Free) = '||v_fs4_blocks);
  dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/
prompt
set verify on
undefine segment_owner
undefine segment_name
undefine v_owner
undefine v_name
undefine v_type

oracle表分区 ddl Oracle 12c

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！