oracle中less than,解决Oracle中Split Partition缓慢的问题

有这样一个case ， 用户的10g产品数据库中有一张按照月份划分的RANGE分区表 ， 其范围为Less than (maxvalue)的最后一个Partition分区总是为空Empty的， 用户每隔半年会对这个MaxValue Partition 执行Split Partition 操作， 以便存放后续月份的数据， 同时这个表上还有一个非分区索引Nonpartitioned indexes。

满以为Split 这样一个Empry Partition会是一个回车就结束的任务， 但是Performance issue就在这平淡无奇的分区维护工作中出现了， 实际在执行"Alter Table Split partition Pn at (value) into ..." 的DDL语句时，发现需要花费超过十多分钟才能完成一次Split。问题就在于，如果是有大量数据的Partition分区 ， Split 操作慢一些也是正常的(expected预期内的) ， 但是实际这里的Max Partition总是为空的，没有任何一条数据， 为什么Split 一个空的分区也会是这种蜗牛的速度呢？

我们来模拟这个现象， 首先创建一张分区表，Maxvalue的Partition是Empty的，且有一个普通索引:

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME

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

www.oracledatabase12g.com & www.askmaclean.com

SQL> CREATE TABLE Maclean

2 ( "OWNER" VARCHAR2(30),

3 "OBJECT_NAME" VARCHAR2(128),

4 "SUBOBJECT_NAME" VARCHAR2(30),

5 "OBJECT_ID" NUMBER,

6 "DATA_OBJECT_ID" NUMBER,

7 "OBJECT_TYPE" VARCHAR2(19),

8 "CREATED" DATE,

9 "LAST_DDL_TIME" DATE,

10 "TIMESTAMP" VARCHAR2(19),

11 "STATUS" VARCHAR2(7),

12 "TEMPORARY" VARCHAR2(1),

13 "GENERATED" VARCHAR2(1),

14 "SECONDARY" VARCHAR2(1)

15 ) nologging

16 partition by range(object_id)

17 (partition p1 values less than (99999) tablespace users,

18 partition p2 values less than (maxvalue) tablespace users);

Table created.

SQL> insert /*+ append */ into maclean select * from maclean1;

38736384 rows created.

SQL> commit;

Commit complete.

SQL> create index ind_obj on maclean(DATA_OBJECT_ID,OBJECT_ID,LAST_DDL_TIME,TIMESTAMP,object_type,owner,status)

nologging parallel

2 ;

Index created.

SQL> alter index ind_obj noparallel;

Index altered.

SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN',cascade=>true,degree=>2);

PL/SQL procedure successfully completed.

SQL> select num_rows from dba_tables where table_name='MACLEAN';

NUM_ROWS

----------

38818438

SQL> select * from maclean partition (p2);

no rows selected

/* Maclean表有大量的数据 ，但是都在p1分区中， p2分区没有任何数据 */

我们执行Split partition 的DDL 语句，并使用10046 level 12 event监控该过程：

oradebug setmypid;

oradebug event 10046 trace name context forever,level 12;

SQL> alter table maclean split partition p2 at (100001)

2 into (partition p3, partition p4);

Table altered.

[oracle@vrh8 ~]$ tkprof /s01/admin/G10R21/udump/g10r21_ora_4896.trc g10r21_ora_4896.tkf

TKPROF: Release 10.2.0.1.0 - Production on Thu Nov 17 23:42:48 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

从tkf 文件中可以找出以下内容：

alter table maclean split partition p2 at (100001)

into (partition p3, partition p4)

call count cpu elapsed disk query current rows

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

Parse 1 0.13 0.30 20 1139 0 0

Execute 1 0.01 0.18 3 6 33 0

Fetch 0 0.00 0.00 0 0 0 0

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

total 2 0.14 0.48 23 1145 33 0

select /*+ FIRST_ROWS PARALLEL("MACLEAN", 1) */ 1

from