saup007

133 积分	0 好友	17 主题

发消息

[SQL调优] 索引与表数据不同步导致，第三回出现，难道没解决根源？

1^#

发表于 2013-9-5 10:20:08 | 查看: 3724| 回复: 7

本帖最后由 saup007 于 2013-9-5 10:42 编辑

Oracle 版本 11.2.0.2

SQL> select action,comments from sys.registry$history;

ACTION                      COMMENTS
------------------------------ ------------
APPLY                         Patchset 11.2.0.2.0
APPLY                         PSU 11.2.0.2.3

alter 日志报错如下：Trace文件在附件，谢谢！

Wed Sep 04 20:26:00 2013
Errors in file /u01/app/oracle/diag/rdbms/daxiao/DAXIAO/trace/DAXIAO_ora_21527.trc:
Wed Sep 04 20:26:10 2013
Dumping diagnostic data in directory=[cdmp_20130904202610], requested by (instance=1, osid=21527), summary=[abnormal process termination].
Wed Sep 04 20:27:19 2013
Errors in file /u01/app/oracle/diag/rdbms/daxiao/DAXIAO/trace/DAXIAO_ora_21509.trc:
Wed Sep 04 20:27:22 2013
Dumping diagnostic data in directory=[cdmp_20130904202722], requested by (instance=1, osid=21509), summary=[abnormal process termination].
Wed Sep 04 20:29:50 2013
LGWR: Standby redo logfile selected to archive thread 1 sequence 10764
LGWR: Standby redo logfile selected for thread 1 sequence 10764 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 10764 (LGWR switch)
Current log# 1 seq# 10764 mem# 0: /u01/oradata/DAXIAO/redo01.log
Wed Sep 04 20:29:52 2013
Archived Log entry 24581 added for thread 1 sequence 10763 ID 0x57285b76 dest 1:
Wed Sep 04 20:43:42 2013
Errors in file /u01/app/oracle/diag/rdbms/daxiao/DAXIAO/trace/DAXIAO_ora_21509.trc:
Wed Sep 04 20:43:45 2013
Dumping diagnostic data in directory=[cdmp_20130904204345], requested by (instance=1, osid=21509), summary=[abnormal process termination].
Wed Sep 04 22:02:05 2013
Errors in file /u01/app/oracle/diag/rdbms/daxiao/DAXIAO/trace/DAXIAO_ora_21509.trc:
Wed Sep 04 22:02:08 2013
Dumping diagnostic data in directory=[cdmp_20130904220208], requested by (instance=1, osid=21509), summary=[abnormal process termination].

复制代码

DAXIAO_ora_21509.rar

2.51 MB, 下载次数: 814

DAXIAO_ora_21527.rar

1.84 MB, 下载次数: 910

分享0

收藏0 回复只看该作者道具举报

saup007

2^#

发表于 2013-9-5 10:41:50

本帖最后由 saup007 于 2013-9-5 10:52 编辑

--根据trace文件 oer 8102.2 - obj# 151536, rdba: 0x04b0c98b(afn 18, blk# 3197323)
select owner,object_name,subobject_name,object_id,object_type from dba_objects where object_id=151536;
--P_LOG_ID_184 这是一张分区表的分区索引，前面的值是分区名
select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_owner='OWNER' and partition_name='P_LOG_ID_184'
--184000000 找到所在分区对应的值，即存放小于184000000并且大于183000000
select max(login_id) from OWNER.LOG_RECORD_DETAIL where login_id >=183000000;
--183623608 找到最新最大的值，好做比较
--全表扫描，与索引数据对比，明显少一条记录
SELECT /*+ FULL(t1) */ count(1)
FROM OWNER.LOG_RECORD_DETAIL t1 where login_id >=183000000 and login_id <=183623608
-- 623609
SELECT /*+ index(t LOG_RECORD_DETAIL_IX2) */ count(1)
FROM OWNER.LOG_RECORD_DETAIL t where login_id >=183000000 and login_id <=183623608;
-- 623608
--LOG_RECORD_DETAIL表的结构介绍下
以login_id为主键索引（序列值递增），也是以login_id做分区的。
LOG_RECORD_DETAIL_IX2 是三个列的分区索引LOGOUT_TIME、GAME_ID、GATEWAY_ID
第二次遇到的时候dbv检查这个索引存在的数据文件，看结果是没有坏块
dbv file=
1>、文件1
DBVERIFY - Verification complete
Total Pages Examined : 4193792
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 4178689
Total Pages Failing (Index): 0
Total Pages Processed (Other): 15058
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 45
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3849499 (9.3849499)
2>、文件2
DBVERIFY - Verification complete
Total Pages Examined : 2818560
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2557153
Total Pages Failing (Index): 0
Total Pages Processed (Other): 13542
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 247865
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 4022558 (9.4022558)
3>、文件3
DBVERIFY - Verification complete
Total Pages Examined : 3151360
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2958776
Total Pages Failing (Index): 0
Total Pages Processed (Other): 13473
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 179111
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 4052885 (9.4052885)
我想原因是这样：
trace文件的Current SQL 根据login_id给update LOGOUT_TIME、ROLE_LEVEL、MONEY 等等字段（此步执行计划走主键索引），因为logout_time是另一索引字段，维护索引LOG_RECORD_DETAIL_IX2，但LOG_RECORD_DETAIL_IX2索引中无此记录，就报错了
我以前的解决方式是
alter index owner.LOG_RECORD_DETAIL_IX2 REBUILD PARTITION P_LOG_ID_184 online
begin
dbms_stats.gather_index_stats( 'owner',indname => 'LOG_RECORD_DETAIL_IX2' ,partname => 'P_LOG_ID_184');
end;
/
最近已经发生3回了，会不会有更深入的原因没有找到？

复制代码

回复只看该作者道具举报

saup007

3^#

发表于 2013-9-5 10:59:49

SQL> @?/rdbms/admin/utlvalid.sql
SQL> analyze table owner.LOG_RECORD_DETAIL partition (P_LOG_ID_184) validate structure online into invalid_rows ;
SQL> select * from invalid_rows;
no rows selected

复制代码

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2013-9-5 15:31:52

未必就一定是索引的问题啊，考虑把表移动一下吧

回复只看该作者道具举报

psufnxk2000

5^#

发表于 2013-9-5 16:21:12

Table/Index row count mismatch (Doc ID 563070.1)

回复只看该作者道具举报

saup007

6^#

发表于 2013-9-6 10:26:11

Maclean Liu(刘相兵发表于 2013-9-5 15:31
未必就一定是索引的问题啊，考虑把表移动一下吧

表有点大，快2亿的数据了，分区表貌似不能move，估计找时间在线重定义一下。

回复只看该作者道具举报

saup007

7^#

发表于 2013-9-6 10:45:28

psufnxk2000 发表于 2013-9-5 16:21
Table/Index row count mismatch (Doc ID 563070.1)

Thanks！

回复只看该作者道具举报

saup007

8^#

发表于 2013-9-6 11:19:56

扩展阅读哈，这位童鞋最后也是通过move解决的。

如果索引是有效的
alter index rebuild online ，是基于表数据重建索引的

如果索引是无效的
alter index rebuild online ，是基于索引数据重建索引的

如果索引是无效，就不能用online，需要offline 方式rebuild索引
alter index rebuild ;

http://hourim.wordpress.com/2012/02/06/how-to-solve-ora-08102-index-key-not-found/
http://hourim.wordpress.com/2013/06/14/ora-08102-index-key-not-found-part-ii/

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册