1. 问题
1.1 问题
在Oracle10.2.0.5.0版本中，使用split新增分区时，由于被split分区缺少统计信息，split操作中特定递归sql对存在全局索引的表进行索引范围扫描，导致大量逻辑IO，执行时间变长，进而导致大量insert会话等待事件library cache lock。
1.2 解决思路
思路1：每次对分区P_CSTTRANMONI_DEFAULT做split前收集该分区的统计信息：
exec dbms_stats.gather_table_stats('MCIS','CSTTRANMONI',partname=>'P_CSTTRANMONI_DEFAULT ');
==>可行
思路2：将全局索引设置为invisible，避免递归sql使用索引
==>测试表明：技术上可行
思路3：将CSTTRANMONI表上的Global索引改成local索引。
==>仅技术上可行，调整索引涉及应用SQL调整和验证
思路4：收集并锁定缺省分区统计信息
==>测试表明：split操作后，缺省分区统计信息丢失
思路5：会话级设置参数_optimizer_ignore_hints=true
经网友测试验证，对于客户提交的sql，该参数有效；但对递归sql，该参数无效。后又通过在会话级设置optimizer_feature_enable='8.0.0'实现。
---参考：http://www.oracledatabase12g.com/archives/resolve-split-partition-recursive-sql-hint-performance-issue.html
2. Oracle11.2.0.1问题重现
2.1 版本
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
2.2 创建测试表
create table tmp_part(x int,y int)
partition by range
(x)
(
partition p_1 values less than (1),
partition p_2 values less than (2),
partition p_3 values less than (3),
partition p_4 values less than (4),
partition p_default values less than (maxvalue)
);
create index idx_tmp_part on tmp_part(x);
insert into tmp_part(x,y)
select 1,rownum from dual connect by level<100000;
insert into tmp_part(x,y)
select 2,rownum from dual connect by level<100000;
insert into tmp_part(x,y)
select 3,rownum from dual connect by level<100000;
commit;
begin
dbms_stats.gather_table_stats(user,'TMP_PART');
end;
/
select index_name,status from user_indexes where index_name='IDX_TMP_PART';
INDEX_NAME STATUS
------------------------------ --------
IDX_TMP_PART VALID
select count(*) from tmp_part partition(p_default);
COUNT(*)
----------
0
2.3 P_DEFAULT分区有统计信息情况
--split分区前
select table_name,
partition_name,
to_char(last_analyzed,'YYYYMMDD HH24MI'),
num_rows,
stattype_locked,
global_stats,
user_stats
from user_tab_statistics
where table_name = 'TMP_PART';
TABLE_NAME PARTITION_ TO_CHAR(LAST_ NUM_ROWS STATT GLO USE
---------- ---------- ------------- ---------- ----- --- ---
TMP_PART 20141102 0314 299997 YES NO
TMP_PART P_1 20141102 0314 0 YES NO
TMP_PART P_2 20141102 0314 99999 YES NO
TMP_PART P_3 20141102 0314 99999 YES NO
TMP_PART P_4 20141102 0314 99999 YES NO
TMP_PART P_DEFAULT 20141102 0314 0 YES NO
set timing on
oradebug setmypid
oradebug event 10046 trace name context forever,level 12;
alter table mh.tmp_part
split partition p_default at (5) into (partition p_5,partition p_default) update indexes;
Elapsed: 00:00:01.21
--分析跟踪文件
tkprof orcl_ora_5466.trc orcl_ora_5466.tkf
--获取分区排他锁
SQL ID: bgy16fj5td0yz
Plan Hash: 0
LOCK TABLE "MH"."TMP_PART" PARTITION ("P_DEFAULT") IN EXCLUSIVE MODE NOWAIT
--原始SQL
alter table mh.tmp_part
split partition p_default at (5) into (partition p_5,partition p_default)
update indexes
--递归SQL1
SQL ID: 85698vrqj1mh6
Plan Hash: 1105471716
select /*+ FIRST_ROWS(1) PARALLEL("TMP_PART", 1) */ 1
from
"MH"."TMP_PART" PARTITION ("P_DEFAULT") where ( ( ( ( "X" < 5 ) ) ) )
and rownum < 2
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=3 pr=0 pw=0 time=0 us)
0 PARTITION RANGE SINGLE PARTITION: 5 5 (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
0 TABLE ACCESS FULL TMP_PART PARTITION: 5 5 (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
==》待split分区有统计信息时，使用了分区全表扫描，逻辑读3很少
--递归SQL2
SQL ID: 2uvud5a8z8dpg
Plan Hash: 1105471716
select /*+ FIRST_ROWS(1) PARALLEL("TMP_PART", 1) */ 1
from
"MH"."TMP_PART" PARTITION ("P_DEFAULT") where ( ( ( ( "X" >= 5 OR "X" IS
NULL ) ) ) ) and rownum < 2
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=3 pr=0 pw=0 time=0 us)
0 PARTITION RANGE SINGLE PARTITION: 5 5 (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
0 TABLE ACCESS FULL TMP_PART PARTITION: 5 5 (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
2.4 P_DEFAULT分区无统计信息情况
--split分区前
select table_name,
partition_name,
to_char(last_analyzed,'YYYYMMDD HH24MI'),
num_rows,
stattype_locked,
global_stats,
user_stats
from user_tab_statistics
where table_name = 'TMP_PART';
TABLE_NAME PARTITION_ TO_CHAR(LAST_ NUM_ROWS STATT GLO USE
---------- ---------- ------------- ---------- ----- --- ---
TMP_PART 20141102 0314 299997 YES NO
TMP_PART P_1 20141102 0314 0 YES NO
TMP_PART P_2 20141102 0314 99999 YES NO
TMP_PART P_3 20141102 0314 99999 YES NO
TMP_PART P_4 20141102 0314 99999 YES NO
TMP_PART P_5 NO NO
TMP_PART P_DEFAULT NO NO
set timing on
oradebug setmypid
oradebug event 10046 trace name context forever,level 12;
alter table mh.tmp_part
split partition p_default at (6) into (partition p_6,partition p_default) update indexes;
--分析跟踪文件
--获取分区排他锁
SQL ID: bgy16fj5td0yz
Plan Hash: 0
LOCK TABLE "MH"."TMP_PART" PARTITION ("P_DEFAULT") IN EXCLUSIVE MODE NOWAIT
--原始SQL
alter table mh.tmp_part
split partition p_default at (6) into (partition p_6,partition p_default)
update indexes
--递归SQL1
SQL ID: 4hfsvkycsgxwd
Plan Hash: 82649502
select /*+ FIRST_ROWS(1) PARALLEL("TMP_PART", 1) */ 1
from
"MH"."TMP_PART" PARTITION ("P_DEFAULT") where ( ( ( ( "X" < 6 ) ) ) )
and rownum < 2
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=1206 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN IDX_TMP_PART (cr=1206 pr=0 pw=0 time=0 us cost=3 size=3 card=1)(object id 74930)
==》待split分区无统计信息时，使用了索引范围扫描，逻辑读1206较高
--递归SQL2
SQL ID: 1vy0ywnpz3r0r
Plan Hash: 1105471716
select /*+ FIRST_ROWS(1) PARALLEL("TMP_PART", 1) */ 1
from
"MH"."TMP_PART" PARTITION ("P_DEFAULT") where ( ( ( ( "X" >= 6 OR "X" IS
NULL ) ) ) ) and rownum < 2
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=3 pr=0 pw=0 time=0 us)
0 PARTITION RANGE SINGLE PARTITION: 6 6 (cr=3 pr=0 pw=0 time=0 us cost=35 size=3 card=1)
0 TABLE ACCESS FULL TMP_PART PARTITION: 6 6 (cr=3 pr=0 pw=0 time=0 us cost=35 size=3 card=1)
2.5 通过锁定分区统计信息，试图避免split操作后统计信息缺失，是否可行?
==>split操作后P_DEFAULT分区统计信息丢失，不可行
--当前分区统计信息
select table_name,
partition_name,
to_char(last_analyzed,'YYYYMMDD HH24MI'),
num_rows,
stattype_locked,
global_stats,
user_stats
from user_tab_statistics
where table_name = 'TMP_PART';
TABLE_NAME PARTITION_ TO_CHAR(LAST_ NUM_ROWS STATT GLO USE
---------- ---------- ------------- ---------- ----- --- ---
TMP_PART 20141102 0314 299997 YES NO
TMP_PART P_1 20141102 0314 0 YES NO
TMP_PART P_2 20141102 0314 99999 YES NO
TMP_PART P_3 20141102 0314 99999 YES NO
TMP_PART P_4 20141102 0314 99999 YES NO
TMP_PART P_5 NO NO
TMP_PART P_6 NO NO
TMP_PART P_DEFAULT NO NO
--复制并锁定P_DEFAULT分区统计信息
begin
dbms_stats.copy_table_stats(user,'TMP_PART','P_1','P_DEFAULT');
dbms_stats.lock_partition_stats(user,'TMP_PART','P_DEFAULT');
end;
/
--当前分区统计信息
select table_name,
partition_name,
to_char(last_analyzed,'YYYYMMDD HH24MI'),
num_rows,
stattype_locked,
global_stats,
user_stats
from user_tab_statistics
where table_name = 'TMP_PART';
TABLE_NAME PARTITION_ TO_CHAR(LAST_ NUM_ROWS STATT GLO USE
---------- ---------- ------------- ---------- ----- --- ---
TMP_PART 20141102 0314 299997 YES NO
TMP_PART P_1 20141102 0314 0 YES NO
TMP_PART P_2 20141102 0314 99999 YES NO
TMP_PART P_3 20141102 0314 99999 YES NO
TMP_PART P_4 20141102 0314 99999 YES NO
TMP_PART P_5 NO NO
TMP_PART P_6 NO NO
TMP_PART P_DEFAULT 20141102 0314 0 ALL YES NO
==>P_DEFAULT分区统计信息被锁定，行数为0
alter system flush shared_pool;
set timing on
oradebug setmypid
oradebug event 10046 trace name context forever,level 12;
alter table mh.tmp_part
split partition p_default at (7) into (partition p_7,partition p_default) update indexes;
--split分区操作后
select table_name,
partition_name,
to_char(last_analyzed,'YYYYMMDD HH24MI'),
num_rows,
stattype_locked,
global_stats,
user_stats
from user_tab_statistics
where table_name = 'TMP_PART';
TABLE_NAME PARTITION_ TO_CHAR(LAST_ NUM_ROWS STATT GLO USE
---------- ---------- ------------- ---------- ----- --- ---
TMP_PART 20141102 0314 299997 YES NO
TMP_PART P_1 20141102 0314 0 YES NO
TMP_PART P_2 20141102 0314 99999 YES NO
TMP_PART P_3 20141102 0314 99999 YES NO
TMP_PART P_4 20141102 0314 99999 YES NO
TMP_PART P_5 NO NO
TMP_PART P_6 NO NO
TMP_PART P_7 NO NO
TMP_PART P_DEFAULT ALL NO NO
==>split分区后，P_DEFAULT上的统计信息没有了,虽然还处于锁定状态
2.6 通过设置全局索引为不可见，可以避免全局索引扫描
--设置全局索引invisible
alter index idx_tmp_part invisible;
--索引状态
select index_name,status,VISIBILITY from user_indexes where index_name='IDX_TMP_PART';
INDEX_NAME STATUS VISIBILIT
------------------------------ -------- ---------
IDX_TMP_PART VALID INVISIBLE
show parameter invisible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
begin
dbms_stats.unlock_partition_stats(user,'TMP_PART','P_DEFAULT');
end;
/
--分区当前统计信息，P_DEFAULT分区无统计信息
select table_name,
partition_name,
to_char(last_analyzed,'YYYYMMDD HH24MI'),
num_rows,
stattype_locked,
global_stats,
user_stats
from user_tab_statistics
where table_name = 'TMP_PART';
TABLE_NAME PARTITION_ TO_CHAR(LAST_ NUM_ROWS STATT GLO USE
---------- ---------- ------------- ---------- ----- --- ---
TMP_PART 20141102 0314 299997 YES NO
TMP_PART P_1 20141102 0314 0 YES NO
TMP_PART P_2 20141102 0314 99999 YES NO
TMP_PART P_3 20141102 0314 99999 YES NO
TMP_PART P_4 20141102 0314 99999 YES NO
TMP_PART P_5 20141102 1920 0 YES NO
TMP_PART P_6 20141102 1920 0 YES NO
TMP_PART P_7 20141102 1920 0 YES NO
TMP_PART P_DEFAULT NO NO
--split 分区
alter system flush shared_pool;
set timing on
oradebug setmypid
oradebug event 10046 trace name context forever,level 12;
alter table mh.tmp_part
split partition p_default at (8) into (partition p_8,partition p_default) update indexes;
--分析跟踪文件
--获取分区排他锁
SQL ID: bgy16fj5td0yz
Plan Hash: 0
LOCK TABLE "MH"."TMP_PART" PARTITION ("P_DEFAULT") IN EXCLUSIVE MODE NOWAIT
--原始SQL
alter table mh.tmp_part
split partition p_default at (8) into (partition p_8,partition p_default) update indexes
--递归SQL1
SQL ID: bhbt564jk3dct
Plan Hash: 1105471716
select /*+ FIRST_ROWS(1) PARALLEL("TMP_PART", 1) */ 1
from
"MH"."TMP_PART" PARTITION ("P_DEFAULT") where ( ( ( ( "X" < 8 ) ) ) )
and rownum < 2
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=3 pr=1 pw=0 time=0 us)
0 PARTITION RANGE SINGLE PARTITION: 8 8 (cr=3 pr=1 pw=0 time=0 us cost=2 size=3 card=1)
0 TABLE ACCESS FULL TMP_PART PARTITION: 8 8 (cr=3 pr=1 pw=0 time=0 us cost=2 size=3 card=1)
--递归SQL2
SQL ID: 99t3mrypzgbaf
Plan Hash: 1105471716
select /*+ FIRST_ROWS(1) PARALLEL("TMP_PART", 1) */ 1
from
"MH"."TMP_PART" PARTITION ("P_DEFAULT") where ( ( ( ( "X" >= 8 OR "X" IS
NULL ) ) ) ) and rownum < 2
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=3 pr=0 pw=0 time=0 us)
0 PARTITION RANGE SINGLE PARTITION: 8 8 (cr=3 pr=0 pw=0 time=0 us cost=27 size=3 card=1)
0 TABLE ACCESS FULL TMP_PART PARTITION: 8 8 (cr=3 pr=0 pw=0 time=0 us cost=27 size=3 card=1)
3，Oracle10.2.0.1测试
在此版本中，无论P_DEFAULT是否有统计信息，split操作的递归SQL都通过索引范围扫描执行，即使收集P_DEFAULT统计信息还会存在性能问题。
3.1 版本
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
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
3.2 创建分区表
create table tmp_part(x int,y int)
partition by range(x)
(
partition p_1 values less than (1),
partition p_2 values less than (2),
partition p_3 values less than (3),
partition p_4 values less than (4),
partition p_default values less than (maxvalue)
);
create index idx_tmp_part on tmp_part(x);
insert into tmp_part(x,y)
select 1,rownum from dual connect by level<100000;
insert into tmp_part(x,y)
select 2,rownum from dual connect by level<100000;
insert into tmp_part(x,y)
select 3,rownum from dual connect by level<100000;
commit;
begin
dbms_stats.gather_table_stats(user,'TMP_PART');
end;
/
select index_name,status from user_indexes where index_name='IDX_TMP_PART';
INDEX_NAME STATUS
------------------------------ --------
IDX_TMP_PART VALID
select table_name,
partition_name,
to_char(last_analyzed, 'YYYYMMDD HH24MI'),
num_rows,
stattype_locked,
global_stats,
user_stats
from user_tab_statistics
where table_name = 'TMP_PART';
TABLE_NAME PARTITION_NAME TO_CHAR(LAST_ANALYZED,'YYYYMMD NUM_ROWS STATTYPE_LOCKED GLOBAL_STATS USER_STATS
------------ --------------- ------------------------------ ---------- --------------- ------------ ----------
TMP_PART 20141102 1413 299997 YES NO
TMP_PART P_1 20141102 1413 0 YES NO
TMP_PART P_2 20141102 1413 99999 YES NO
TMP_PART P_3 20141102 1413 99999 YES NO
TMP_PART P_4 20141102 1413 99999 YES NO
TMP_PART P_DEFAULT 20141102 1413 0 YES NO
select count(*) from tmp_part partition(p_default);
COUNT(*)
----------
0
3.3 P_DEFAULT分区有统计信息情况
oradebug setmypid;
oradebug event 10046 trace name context forever,level 12;
alter table bnet.tmp_part
split partition p_default at (5) into (partition p_5,partition p_default) update indexes;
Elapsed: 00:00:09.55
--分析跟踪文件
tkprof bnet_ora_14014.trc bnet_ora_14014.tkf
--原始SQL
alter table bnet.tmp_part
split partition p_default at (5) into (partition p_5,partition p_default) update indexes;
--递归SQL1：
select /*+ FIRST_ROWS PARALLEL("TMP_PART", 1) */ 1
from
"bnet"."TMP_PART" PARTITION ("P_DEFAULT") where ( ( ( ( "X" < 5 ) ) )
) and rownum < 2
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=1019 pr=1019 pw=0 time=5297158 us)
0 TABLE ACCESS BY GLOBAL INDEX ROWID TMP_PART PARTITION: 5 5 (cr=1019 pr=1019 pw=0 time=5297147 us)
299997 INDEX RANGE SCAN IDX_TMP_PART (cr=1019 pr=1019 pw=0 time=2873717 us)(object id 257942)
==>该SQL使用了索引扫描，即使P_DEFAULT分区上有统计信息，逻辑读1019成本较高，读取299997行后发现没有符合条件的数据
--递归SQL2：
select /*+ FIRST_ROWS PARALLEL("TMP_PART", 1) */ 1
from
"bnet"."TMP_PART" PARTITION ("P_DEFAULT") where ( ( ( ( "X" >= 5 OR "X"
IS NULL ) ) ) ) and rownum < 2
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=3 pr=1 pw=0 time=6413 us)
0 PARTITION RANGE SINGLE PARTITION: 5 5 (cr=3 pr=1 pw=0 time=6405 us)
0 TABLE ACCESS FULL TMP_PART PARTITION: 5 5 (cr=3 pr=1 pw=0 time=6379 us)
3.4 P_DEFAULT分区无统计信息情况
--split操作前
select table_name,
partition_name,
to_char(last_analyzed, 'YYYYMMDD HH24MI'),
num_rows,
stattype_locked,
global_stats,
user_stats
from user_tab_statistics
where table_name = 'TMP_PART';
TABLE_NAME PARTITION_NAME TO_CHAR(LAST_ANALYZED,'YYYYMMD NUM_ROWS STATTYPE_LOCKED GLOBAL_STATS USER_STATS
----------- ---------------- ------------------------------ ---------- --------------- ------------ ----------
TMP_PART 20141102 1413 299997 YES NO
TMP_PART P_1 20141102 1413 0 YES NO
TMP_PART P_2 20141102 1413 99999 YES NO
TMP_PART P_3 20141102 1413 99999 YES NO
TMP_PART P_4 20141102 1413 99999 YES NO
TMP_PART P_5 NO NO
TMP_PART P_DEFAULT NO NO
==> P_5、P_DEFAULT缺少统计信息
--为避免前面执行计划的影响：
alter system flush shared_pool;
set timing on
oradebug setmypid;
oradebug event 10046 trace name context forever,level 12;
alter table bnet.tmp_part
split partition p_default at (6) into (partition p_6,partition p_default) update indexes;
Elapsed: 00:00:02.29
--跟踪文件
tkprof bnet_ora_14391.trc bnet_ora_14391.tkf
--原始SQL
alter table bnet.tmp_part
split partition p_default at (6) into (partition p_6,partition p_default) update indexes
--递归SQL1：
select /*+ FIRST_ROWS PARALLEL("TMP_PART", 1) */ 1
from
"bnet"."TMP_PART" PARTITION ("P_DEFAULT") where ( ( ( ( "X" < 6 ) ) )
) and rownum < 2
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=1021 pr=1 pw=0 time=227276 us)
0 TABLE ACCESS BY GLOBAL INDEX ROWID TMP_PART PARTITION: 6 6 (cr=1021 pr=1 pw=0 time=227267 us)
299997 INDEX RANGE SCAN IDX_TMP_PART (cr=1021 pr=1 pw=0 time=1500093 us)(object id 257942)
--递归SQL2：
select /*+ FIRST_ROWS PARALLEL("TMP_PART", 1) */ 1
from
"bnet"."TMP_PART" PARTITION ("P_DEFAULT") where ( ( ( ( "X" >= 6 OR "X"
IS NULL ) ) ) ) and rownum < 2
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=3 pr=1 pw=0 time=235 us)
0 PARTITION RANGE SINGLE PARTITION: 6 6 (cr=3 pr=1 pw=0 time=226 us)
0 TABLE ACCESS FULL TMP_PART PARTITION: 6 6 (cr=3 pr=1 pw=0 time=203 us)
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-1318531/，如需转载，请注明出处，否则将追究法律责任。
