ITPub博客

首页 > 数据库 > Oracle > split分区操作导致的librarycachelock

split分区操作导致的librarycachelock

原创 Oracle 作者:redhouser 时间:2014-11-03 11:29:39 0 删除 编辑


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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    807245