有这样一个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
本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1278156