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

最新推荐文章于 2021-04-04 05:57:12 发布
最新推荐文章于 2021-04-04 05:57:12 发布
阅读量103 收藏
点赞数

报表数据库出现了好几次类似的问题，大致症状为：
分区表上的某个分区索引失效，脚本自动重建，然后其统计信息丢失，导致接下来的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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-692971/

congdiku5262
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
ojdbc14-10.2.0.5 .jar (oracle驱动 10.2.0.5)
12-17
ojdbc14-10.2.0.5.jar是Oracle公司发布的针对Oracle数据库10g Release 2 (10.2.0.5)的一个特定版本的JDBC驱动程序。这个驱动程序允许Java开发者在他们的应用程序中执行SQL查询，进行数据操作，以及实现数据库事务...
_optimizer_compute_index_stats
cod42960的博客
06-09 157
_optimizer_compute_index_stats[@more@]COMPUTE STATISTICS In earlier releases, you could use this clause to start ...
Oracle隐含参数强制创建索引时自动收集统计信息
lk的窝
03-29 632
隐含参数强制创建索引时自动收集统计信息NAME VALUE PDESC------------------------------ ---------- --------------------------------------------------_optimizer_compute_index_stats TRUE ...
获得执行计划方法五-10053事件
道行尚浅（老道）的博客
06-30 1144
_optimizer_dim_subq_join_sel        = true   _optimizer_disable_strans_sanity_checks = 0   _optimizer_compute_index_stats      = true   _push_join_union_view2              = true   _optimizer_igno
oracle 创建或重建索引时收集统计信息不自动收集（_optimizer_compute_index_stats）
cuiwuzha3492的博客
06-05 181
如下隐含参数强制创建过引时自动收集统计信息 NAME VALUE PDESC ------------------------------ ---------...
10053诊断事件——深入分析CBO内容！
事后诸葛亮的程序人生（微信：zq9017197）
12-17 5766
我们在查看一条sql语句的执行计划时，只看到了CBO最终告诉我们的执行计划结果，但是却不知道CBO为什么要这么做。特别是当执行计划明显失真时，我们一定非常想搞清楚到底是什么地方导致CBO作出了这样一个错误的执行计划。10053事件给我们提供了一种这样的方式，它允许我们深入到CBO的内部，去看看CBO是如何工作的，它究竟是按照什么样的依据得出最终的执行计划的。 SQL> conn u1/u1 已
Oracle 10.2.0.5补丁windows版32位及64位
10-02
Oracle 10.2.0.5 补丁是针对Oracle数据库系统的一个重要更新，它提供了对Oracle 10.2.0.4版本的增强和修复，旨在提高系统的稳定性和安全性。此补丁适用于Windows操作系统，同时支持32位和64位环境，这意味着无论是...
Oracle Instant Client for Microsoft Windows (x64) 10.2.0.5
12-14
instantclient-basic-win64-10.2.0.5.zip instantclient-basiclite-win64-10.2.0.5.zip instantclient-jdbc-win64-10.2.0.5.zip instantclient-sqlplus-win64-10.2.0.5.zip instantclient-sdk-win64-10.2.0.5.zip ...
instantclient-basic-win32-10.2.0.5+Network.zip
07-11
1. 解压instantclient-basic-win32-10.2.0.5+Network.zip，得到Network文件夹和instantclient-basic-win32-10.2.0.5.zip，再次解压后者得到instantclient_10_2文件夹。 2. 在创建D:\oracle文件夹，将instantclient_...
oracle10G 10.2.0.5.19 补丁集 p20299014 linux hp-ux aix
11-06
oracle10G 10.2.0.5.19 补丁集 p20299014 linux hp-ux aix linux 64 10.2.0.5.19补丁集，4个包。 http://download.csdn.net/detail/iaihah/9545156 http://download.csdn.net/detail/iaihah/9545166 ...
查询初始化参数的方法
冰刀(skate)
08-05 3983
author：skatetime：2009/08/04 今天闲逛时，发现yangtingkun的关于查看初始化参数的介绍，觉得很好，很详细特别记录   查询初始化参数的方法（一）  出处: http://yangtingkun.itpub.net/post/468/484669-------------------------------------------
统计信息DBMS_STATS包的一些参数解释
cjh81378066的博客
10-17 432
DBMS_STATS.SET_GLOBAL_PREFS ( pname IN VARCHAR2, pvalue IN VARCHAR2); DBMS_STATS.SET_DATAB...
oracle查询初始化参数,oracle技术之查询初始化参数的方法（六）
weixin_29305313的博客
04-04 842
查询初始化参数的方法很多，比如SHOW PARAMETER，或查询V$PARAMETER等，这里简单总结一下。这一篇描述CREATE PFILE的方法检查初始化参数。前面介绍了很多种方法，这些方法都是在数据库中查询初始化参数的设置。其实还有一种方法更加的一目了然，就是CREATE PFILE的方式：SQL> show parameter spfileNAME ...
自动重建失效普通索引和分区索引
csdn426523的博客
05-04 178
只为了做的更好 创建一个JOB调度下面过程就可以自动把失效索引重建 create or replace procedure re_index Authid Current_User is name...
三十二、索引导致分区表变慢的解析
基督山伯爵的博客
12-09 1806
1、索引导致分区表变慢 分区表上建索引，相当于每个分区建了一个小索引。 drop table part_tab purge; --创建分区表，id列分区 create table part_tab (id int,col2 int,col3 int) partition by range (id) ( partition p1 values less...
oracle 全局并行,记一次Oracle分区表全局索引重建的过程
weixin_33834241的博客
04-02 255
1、查询数据库各个表空间利用率：SELECT Upper(F.TABLESPACE_NAME) "表空间名",D.TOT_GROOTTE_MB "表空间大小(M)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",To_char(Round((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100...
oracle10.2.0.5包下载
最新发布
01-21
你好，对于Oracle 10.2.0.5包的下载，首先需要确保你有合法的许可和权限来下载该软件包。一般来说，你可以通过Oracle官方网站或者官方授权的渠道来获取下载链接。 其次，你需要登录到Oracle的官方网站，并在官方网站的下载页面中寻找适用于Oracle 10.2.0.5的软件包。在下载之前，你可能需要先注册一个Oracle账号，并接受相关的许可协议。 一旦你找到了合适的软件包，点击下载链接开始下载该软件包。下载完成后，你需要按照Oracle官方的安装指南来安装该软件包。在安装过程中可能需要输入一些许可信息或者进行其他设置。 需要注意的是，Oracle 10.2.0.5已经是一个较旧的版本，建议你在使用之前先了解该版本的系统要求、安装步骤以及可能遇到的问题。同时，考虑到安全性和稳定性，建议你升级至最新版本的Oracle数据库，以获得更好的支持和功能。 总之，下载Oracle 10.2.0.5包的过程涉及到合法许可和权限、官方渠道的下载、安装前的准备工作等多个方面，希望上述回答对你有所帮助。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值