ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分区索引重建后引发的性能问题,疑似10.2.0.5的bug

分区索引重建后引发的性能问题,疑似10.2.0.5的bug

原创 Linux操作系统 作者:myownstars 时间:2011-04-20 15:58:28 0 删除 编辑

报表数据库出现了好几次类似的问题,大致症状为:
分区表上的某个分区索引失效,脚本自动重建,然后其统计信息丢失,导致接下来的job运行的超慢,这里有个疑点,就是每个分区表的统计信息都是锁定的,且参数_optimizer_compute_index_stats也为true,不管从哪个角度来看,都不该出现这种状况。
其中数据库版本为10.2.0.5
先在报表数据库上出问题的表BAOBIAO上重现一把
查看分区P110218的索引统计信息,
SQL> select index_name,partition_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS from user_ind_partitions where partition_name in('P110218');
 
INDEX_NAME                     PARTITION_NAME                     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ----------
IDX_BAOBIAO_ID           P110218                                 2       11474        294003                       1                       5           1698781    1709562

且其统计信息被锁定
SQL> select stattype_locked from user_ind_statistics u where u.INDEX_NAME ='IDX_BAOBIAO_ID' and partition_name ='P110218';
 
STATTYPE_LOCKED
---------------
ALL
而且参数设为true
sys@std> select ksppinm name, ksppstvl value, ksppdesc description from x$ksppi x, x$ksppcv y
  2  where (x.indx = y.indx) and ( ksppinm like '%_optimizer_compute_index_stats%' or ksppdesc like '%_optimizer_compute_index_stats%' ) order by name;

NAME                           VALUE      DESCRIPTION
------------------------------ ---------- ----------------------------------------------------------------------------------------------------
_optimizer_compute_index_stats TRUE       force index stats collection on index creation/rebuild

现在重新创建该索引
SQL> alter index IDX_BAOBIAO_ID rebuild partition P110218 online;
 
Index altered
发现统计信息居然丢失了
SQL> select index_name,partition_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS from user_ind_partitions where partition_name in('P110218');
 
INDEX_NAME                     PARTITION_NAME                     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ----------
IDX_BAOBIAO_ID           P110218                            
 

尝试先解锁该分区统计信息 
SQL> exec dbms_stats.unlock_partition_stats(ownname => user,tabname => 'BAOBIAO',partname => 'P110218');
 
PL/SQL procedure successfully completed
 
SQL> select stattype_locked from user_ind_statistics u where u.INDEX_NAME ='IDX_BAOBIAO_ID' and partition_name ='P110218';
 
STATTYPE_LOCKED
---------------
ALL
执行后该分区统计信息状态依然为锁定,只有对整个表执行unlock,才可以解锁统计信息
SQL> exec dbms_stats.unlock_table_stats(ownname => user,tabname => 'BAOBIAO');
 
PL/SQL procedure successfully completed
 
SQL> select stattype_locked from user_ind_statistics u where u.INDEX_NAME ='IDX_BAOBIAO_ID' and partition_name ='P110218';
 
STATTYPE_LOCKED
---------------

考虑是生产库,不宜过多测试,将该表的统计信息Lock,然后转到测试环境进行测试,同样为10.2.0.5

SQL> create table justin (id number(10));
 
Table created
 
SQL> declare
  2    v_c number := 0;
  3  begin
  4    for i in 1..10000 loop
  5      insert into justin values(i);
  6    end loop;
  7    commit;
  8  end;
  9  /
 
PL/SQL procedure successfully completed
 
SQL> create index idx_justin on justin(id);
 
Index created

sys@std> select ksppinm name, ksppstvl value, ksppdesc description from x$ksppi x, x$ksppcv y
  2  where (x.indx = y.indx) and ( ksppinm like '%_optimizer_compute_index_stats%' or ksppdesc like '%_optimizer_compute_index_stats%' ) order by name;

NAME                           VALUE      DESCRIPTION
------------------------------ ---------- ----------------------------------------------------------------------------------------------------
_optimizer_compute_index_stats TRUE       force index stats collection on index creation/rebuild

SQL> select index_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS from user_indexes where index_name ='IDX_justin';
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ----------
IDX_justin                              1          21         10000                       1                       1                16      10000
 
SQL> exec dbms_stats.lock_table_stats(ownname => user,tabname => 'justin');
 
PL/SQL procedure successfully completed
 
SQL> select stattype_locked from user_tab_statistics where table_name ='justin';
 
STATTYPE_LOCKED
---------------
ALL
 
SQL> alter index idx_justin rebuild online;
 
Index altered
 
SQL> select index_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS from user_indexes where index_name ='IDX_justin';
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ----------
IDX_justin                              1          21         10000                       1                       1                16      10000
发现针对非分区表,索引信息锁定的情况下重建索引,而索引统计信息依然存在且未变动,换成一个分区表试一下,环境同样为10.2.0.5
SQL> select index_name,partition_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS from user_ind_partitions where partition_name in('P1010') and index_name ='IDX_PART_ACCOUNT_ORDER_CODE';
 
INDEX_NAME                     PARTITION_NAME                     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ----------
IDX_PART_ACCOUNT_ORDER_CODE    P1010                                   1           2           104                       1                       1               110        499

SQL> select stattype_locked from user_ind_statistics where index_name ='IDX_PART_ACCOUNT_ORDER_CODE' and partition_name ='P1010';
 
STATTYPE_LOCKED
---------------

锁定该表的分区统计信息,并不起效果
SQL> exec dbms_stats.lock_partition_stats(ownname => user,tabname => 'FIN_ACCOUNTS_RECEIVABLE_MANAGE',partname => 'P1010');
 
PL/SQL procedure successfully completed
 
SQL> select stattype_locked from user_ind_statistics where index_name ='IDX_PART_ACCOUNT_ORDER_CODE' and partition_name ='P1010';
 
STATTYPE_LOCKED
---------------

在分区统计信息不锁定的情况下,重建该分区索引,发现统计信息不受影响
SQL> alter index IDX_PART_ACCOUNT_ORDER_CODE rebuild partition P1010 online;
 
Index altered
 
SQL> select index_name,partition_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS from user_ind_partitions where partition_name in('P1010') and index_name ='IDX_PART_ACCOUNT_ORDER_CODE';
 
INDEX_NAME                     PARTITION_NAME                     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ----------
IDX_PART_ACCOUNT_ORDER_CODE    P1010                                   1           2           104                       1                       1               110        499

在表级别进行锁定,锁定成功
SQL> exec dbms_stats.lock_table_stats(ownname => user,tabname => 'FIN_ACCOUNTS_RECEIVABLE_MANAGE');
 
PL/SQL procedure successfully completed
 
SQL> select stattype_locked from user_ind_statistics where index_name ='IDX_PART_ACCOUNT_ORDER_CODE' and partition_name ='P1010';
 
STATTYPE_LOCKED
---------------
ALL

此时重建该索引,
SQL> alter index IDX_PART_ACCOUNT_ORDER_CODE rebuild partition P1010 online;
 
Index altered
现象得到重现,该分区的统计信息全部为空
SQL> select index_name,partition_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS from user_ind_partitions where partition_name in('P1010') and index_name ='IDX_PART_ACCOUNT_ORDER_CODE';
 
INDEX_NAME                     PARTITION_NAME                     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ----------
IDX_PART_ACCOUNT_ORDER_CODE    P1010                                                                                                                                


现在再用另一个环境进行测试,11.2.0.2的环境,应该不至于再这样了
[oracle@testdb ~]$ ora params index | grep comp
_optimizer_compute_index_stats                TRUE                 force index stats collection on index creation/rebuild

SQL> select index_name,partition_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS from user_ind_partitions where partition_name in('P1010') and index_name ='IDX_CESHI';
 
INDEX_NAME                     PARTITION_NAME                     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ----------
IDX_CESHI          P1010                                   2        2878            12                     239                       3             21585     732451
 
SQL> select stattype_locked from user_ind_statistics where index_name ='IDX_CESHI' and partition_name ='P1010';
 
STATTYPE_LOCKED
---------------
 
SQL> alter index IDX_CESHI rebuild partition P1010 online;
 
Index altered
 
SQL> select index_name,partition_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS from user_ind_partitions where partition_name in('P1010') and index_name ='IDX_CESHI';
 
INDEX_NAME                     PARTITION_NAME                     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ----------
IDX_CESHI          P1010                                   2        1978             6                     329                    3362             20175     745518

将该分区的统计信息lock,可以成功锁定,而在10.2.0.5的环境中该API就不好使
SQL> exec dbms_stats.lock_partition_stats(ownname => user,tabname => 'CESHI',partname => 'P1010');
 
PL/SQL procedure successfully completed
 
SQL> select stattype_locked from user_ind_statistics where index_name ='IDX_CESHI' and partition_name ='P1010';
 
STATTYPE_LOCKED
---------------
ALL
 
SQL> select index_name,partition_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS from user_ind_partitions where partition_name in('P1010') and index_name ='IDX_CESHI';
 
INDEX_NAME                     PARTITION_NAME                     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ----------
IDX_CESHI          P1010                                   2        1978             6                     329                    3362             20175     745518
 
SQL> alter index IDX_CESHI rebuild partition P1010 online;
 
Index altered
 
SQL> select index_name,partition_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS from user_ind_partitions where partition_name in('P1010') and index_name ='IDX_CESHI';
 
INDEX_NAME                     PARTITION_NAME                     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ----------
IDX_CESHI          P1010                                   2        1978             6                     329                    3362             20175     745518
可以看到,即使该分区的统计信息被锁,重建索引后,其统计信息依然存在且没有改动
同时,针对分区信息的lock/unlock也生效
SQL> exec dbms_stats.unlock_partition_stats(ownname => user,tabname => 'CESHI',partname => 'P1010');
 
PL/SQL procedure successfully completed
 
SQL> select stattype_locked from user_ind_statistics where index_name ='IDX_CESHI' and partition_name ='P1010';
 
STATTYPE_LOCKED
---------------

综上所述:初步得出结论 10.2.0.5存在bug 导致分区索引统计信息被锁的情况下 再次重建的时候 其统计信息会丢失;另外dbms_stats.unlock_partition_stats/dbms_stats.lock_partition_stats似乎也不太灵光


下周就要将其升级到11.2.0.2,以后就不会再受到此问题的困扰了

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-692971/,如需转载,请注明出处,否则将追究法律责任。

上一篇: linux安装mysql dbi
请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3111691