ora-08102 and col_usage$

Roger 2019-06-17
9
0 0
摘要:据说是在分析表时发生的,该网友的提问地址如下:shutdown时出现错误ora-08102,让网友直接drop 该index,然后重建报错

问题描述

今天群里以网友提问说遇到ORA-08102错误,该错误报错如下:
ORA-08102: index key not found, obj# 518, file 1, block 4132 (3)
据说是在分析表时发生的,该网友的提问地址如下:shutdown时出现错误ora-08102
让网友直接drop 该index,然后重建报错:

SQL> create unique index I_COL_USAGE$ on col_usage$(obj#,intcol#) storage(maxextents unlimited);
create unique index I_COL_USAGE$ on col_usage$(obj#,intcol#) storage(maxextents unlimited)
                                    *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

很明显,从上述错误来看,是重复值了,不满足创建unique index的条件。该index和对于的表结构如下:

create table col_usage$
(
  obj#              number,                                 /* object number */
  intcol#           number,                        /* internal column number */
  equality_preds    number,                           /* equality predicates */
  equijoin_preds    number,                           /* equijoin predicates */
  nonequijoin_preds number,                        /* nonequijoin predicates */
  range_preds       number,                              /* range predicates */
  like_preds        number,                         /* (not) like predicates */
  null_preds        number,                         /* (not) null predicates */
  timestamp         date      /* timestamp of last time this row was changed */
)
  storage (initial 200K next 100k maxextents unlimited pctincrease 0) 
/
create unique index i_col_usage$ on col_usage$(obj#,intcol#)
  storage (maxextents unlimited) 
/

网友执行如下sql查询,发现有85条信息:

SQL> select INTCOL#, OBJ# from col_usage$ group by intcol#,obj# having count(*)>1;

      OBJ#    INTCOL#
---------- ----------
4294952044          4
4294952426          4
4294952646         11
4294951384          4
4294951394          5
4294951460         13
.....省略部分信息
4294951460          4
4294951563         13
4294951850          3
4294951981          4
4294952034          3
4294952034          4

85 rows selected.


专家解答

我们知道col_usage$主要是为cbo服务的,换句话说里面存的是跟统计信息相关的,准确一点说是
存的sql查询时where 条件后谓词列相关的列统计信息使用情况,正常情况下,该表的数据在数据
库shutdown immediate 或 shutdown normal模式关闭后会被purge 清空的,正因为会在shutdown
时被清空,这里也就可能会出现一些问题,不过在10g以及以后版本已经没有这个问题了,后面会
详细描述(10g以后开始数据库shutdown 不会清空col_usage$)。

我这里让网友执行SQL 如下清空col_usage$数据,然后再次创建unique index成功:

delete from sys.col_usage$ c
 where not exists (select /*+ unnest */
         1
          from sys.obj$ o
         where o.obj# = c.obj#);

补充一下,也可以用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO 进行对col_usage$的清理。

我在我的10g vm环境中测试,发现:

SQL> select max(obj#) from obj$;     

 MAX(OBJ#)
----------
     56649

SQL> select obj#,INTCOL# from col_usage$  where obj# >56649;

      OBJ#    INTCOL#
---------- ----------
4294950955          2
4294950955          3
4294950957          4
4294950988          2
4294950988          3
4294950989          3
4294950993          4
.....省略部分信息
4294952714          3
4294952714          4
4294952714          6

206 rows selected.
SQL> show user
USER is "SYS"
SQL> delete from sys.col_usage$ c
  2   where not exists (select /*+ unnest */
  3           1
  4            from sys.obj$ o
  5           where o.obj# = c.obj#);

206 rows deleted.

SQL> commit;

Commit complete.

SQL> select obj#,INTCOL# from col_usage$  where obj# >56649;

no rows selected

SQL> 
----这中间间隔几分钟
SQL> select count(*) from col_usage$ where  obj# >56649;

  COUNT(*)
----------
        82

SQL> select count(obj#)  from fixed_obj$;

COUNT(OBJ#)
-----------
        604

我通过查询v$sqlarea 发现了蛛丝马迹,如下了如下sql:

SELECT /*+ ordered use_nl(o c cu h) index(u i_user1) index(o i_obj2)
               index(ci_obj#) index(cu i_col_usage$)
               index(h i_hh_obj#_intcol#) */
 C.NAME COL_NAME,
 C.TYPE# COL_TYPE,
 C.CHARSETFORM COL_CSF,
 C.DEFAULT$ COL_DEF,
 C.NULL$ COL_NULL,
 C.PROPERTY COL_PROP,
 C.COL# COL_UNUM,
 C.INTCOL# COL_INUM,
 C.OBJ# COL_OBJ,
 C.SCALE COL_SCALE,
 H.BUCK ET_CNT H_BCNT,
 (T.ROWCNT - H.NULL_CNT) / GREATEST(H.DISTCNT, 1) H_PFREQ,
 C.LENGTH COL _LEN,
 CU.TIMESTAMP CU_TIME,
 CU.EQUALITY_PREDS CU_EP,
 CU.EQUIJOIN_PREDS CU_EJP,
 C U.RANGE_PREDS CU_RP,
 CU.LIKE_PREDS CU_LP,
 CU.NONEQUIJOIN_PREDS CU_NEJP,
 CU.NULL_ PREDS NP
  FROM SYS.USER$      U,
       SYS.OBJ$       O,
       SYS.TAB$       T,
       SYS.COL$       C,
       SYS.COL_USAGE$ CU,
       SYS.HIST_HEAD$ H
 WHERE :B3 = '0'
   AND U.NAME = :B2
   AND O.OWNER# = U.USER#
   AND O.TYPE# = 2
   AND O.NAME = :B1
   AND O.OBJ# = T.OBJ#
   AND O.OBJ# = C.OBJ#
   AND C.OBJ# = CU.OBJ#(+)
   AND C.INTCOL# = CU.INTCOL#(+)
   AND C.OBJ# = H.OBJ#(+)
   AND C.INTCOL# = H.INTCOL#(+)
UNION ALL
SELECT /*+ ordered use_nl(c) */
 C.KQFCONAM COL_NAME,
 C.K QFCODTY COL_TYPE,
 DECODE(C.KQFCODTY, 1, 1, 0) COL_CSF,
 NULL COL_DEF,
 0 COL_NULL,
 0 COL_PROP,
 C.KQFCOCNO COL_UNUM,
 C.KQFCOCNO COL_INUM,
 O.KQFTAOBJ COL_OBJ,
 DECOD E(C.KQFCODTY, 2, -127, 0) COL_SCALE,
 H.BUCKET_CNT H_BCNT,
 (ST.ROWCNT - NULL_CNT) / G REATEST(H.DISTCNT, 1) H_PFREQ,
 DECODE(C.KQFCODTY, 2, 22, C.KQFCOSIZ) COL_LEN,
 CU. TIMESTAMP CU_TIME,
 CU.EQUALITY_PREDS CU_EP,
 CU.EQUIJOIN_PREDS CU_EJP,
 CU.RANGE_P REDS CU_RP,
 CU.LIKE_PREDS CU_LP,
 CU.NONEQUIJOIN_PREDS CU_NEJP,
 CU.NULL_PREDS NP
  FROM SYS.X$KQFTA    O,
       SYS.TAB_STATS$ ST,
       SYS.X$KQFCO    C,
       SYS.COL_USAGE$ CU,
       SYS.HIS        T_HEAD$ H
 WHERE :B3 != '0'
   AND :B2 = 'SYS'
   AND O.KQFTANAM = :B1
   AND O.KQFTAOBJ = ST.OBJ#(+)
   AND O.KQFTAOBJ = C.KQFCOTOB
   AND C.KQFCOTOB = CU.OBJ#(+)
   AND C.KQFCOC NO = CU.INTCOL#(+)
   AND C.KQFCOTOB = H.OBJ#(+)
   AND C.KQFCOCNO = H.INTCOL#(+)

里面关联了X$KQFTA 和 X$KQFCO,这两个x$ 表都是跟fixed对象有关的,所以我这里断定
col_usage$的obj#非常大的对象是跟fixed对象有关。

SQL> select max(obj#) from obj$;

 MAX(OBJ#)
----------
     56676
SQL> select count(*) from  fixed_obj$;

  COUNT(*)
----------
       604
SQL> select count(*) from  X$KQFTA;

  COUNT(*)
----------
       604

SQL> select count(*) from X$kqfta;

  COUNT(*)
----------
       604

SQL> select count(distinct KQFCOTOB) from X$KQFCO;

COUNT(DISTINCTKQFCOTOB)
-----------------------
                    604

SQL> select count(*) from col_usage$;

  COUNT(*)
----------
      3181
SQL> set pagesize 300
SQL> l
  1  select a.addr,
  2         a.kqftaobj,
  3         a.KQFTAVER,
  4         a.KQFTANAM,
  5         a.KQFTATYP,
  6         a.KQFTACOC,
  7         b.intcol#
  8    from X$KQFTA a, col_usage$ b
  9   where a.kqftaobj = b.obj#
 10*    and b.obj# > 56676
SQL> /

ADDR       KQFTAOBJ   KQFTAVER KQFTANAM                         KQFTATYP   KQFTACOC    INTCOL#
-------- ---------- ---------- ------------------------------ ---------- ---------- ----------
0C943724 4294950955          2 X$KCBWAIT                               1          5          2
0C943724 4294950955          2 X$KCBWAIT                               1          5          3
0C943EA4 4294950957         12 X$KCFIO                                 2         22          4
0C945E84 4294950988          5 X$KGLST                                 1         16          2
0C945E84 4294950988          5 X$KGLST                                 1         16          3
0C945C68 4294950989          4 X$KQRST                                 1         22          3
0C940268 4294950993         13 X$KSLLT                                 4         41          4
0C940394 4294950994          3 X$KSLLD                                 4          7          2
0C940394 4294950994          3 X$KSLLD                                 4          7          3
0C940D6C 4294950995          4 X$KSMSD                                 4          5          3
0C940DA8 4294950997          2 X$KSMSS                                 4          6          4
0C941438 4294950998          5 X$KSPPI                                 4          9          2
0C941438 4294950998          5 X$KSPPI                                 4          9          3
0C941438 4294950998          5 X$KSPPI                                 4          9          4
0C9406DC 4294951004         25 X$KSUSE                                 2         84          2
0C9406DC 4294951004         25 X$KSUSE                                 2         84          3
0C9406DC 4294951004         25 X$KSUSE                                 2         84          4
0C9406DC 4294951004         25 X$KSUSE                                 2         84         23
0C9406DC 4294951004         25 X$KSUSE                                 2         84         24
0C9406DC 4294951004         25 X$KSUSE                                 2         84         27
0C9406DC 4294951004         25 X$KSUSE                                 2         84         31
0C9406DC 4294951004         25 X$KSUSE                                 2         84         73
0C940754 4294951005         14 X$KSUPR                                 2         44          2
0C940754 4294951005         14 X$KSUPR                                 2         44          4
0C940754 4294951005         14 X$KSUPR                                 2         44         25
0C940844 4294951008          6 X$KSUSGSTA                              4          8          3
0C940844 4294951008          6 X$KSUSGSTA                              4          8          6
0C946460 4294951023          2 X$KZDOS                                 1          6          4
0C94649C 4294951024          2 X$KZSRO                                 1          4          4
0C9464D8 4294951025          3 X$KZSPR                                 4          4          3
0C9401F0 4294951036          5 X$KQFCO                                 4         16          1
0C9401F0 4294951036          5 X$KQFCO                                 4         16          5
0C9401F0 4294951036          5 X$KQFCO                                 4         16          6
0C942E3C 4294951037          6 X$KCCFN                                 5         14          3
0C942E3C 4294951037          6 X$KCCFN                                 5         14          4
0C942E3C 4294951037          6 X$KCCFN                                 5         14          5
0C942E3C 4294951037          6 X$KCCFN                                 5         14          6
0C942E3C 4294951037          6 X$KCCFN                                 5         14          9
0C942E3C 4294951037          6 X$KCCFN                                 5         14         10
0C942C5C 4294951038          7 X$KCCDI                                 4         69          3
0C942C5C 4294951038          7 X$KCCDI                                 4         69         39
......省略部分内容
0C948800 4294952567          1 X$KEWSSVCV                              5          8          3
0C948800 4294952567          1 X$KEWSSVCV                              5          8          6
0C948800 4294952567          1 X$KEWSSVCV                              5          8          7
0C941384 4294952646          1 X$KSMPGST                               4         11          6
0C941384 4294952646          1 X$KSMPGST                               4         11          9
0C941384 4294952646          1 X$KSMPGST                               4         11         10
0C941384 4294952646          1 X$KSMPGST                               4         11         11
0C94022C 4294952712          1 X$KQFOPT                                1          5          4
0C94022C 4294952712          1 X$KQFOPT                                1          5          5
0C947054 4294952714          1 X$QKSBGSES                              5         13          3
0C947054 4294952714          1 X$QKSBGSES                              5         13          4
0C947054 4294952714          1 X$QKSBGSES                              5         13          6

217 rows selected.

SQL> select count(*) from col_usage$ where obj# > 56676;

  COUNT(*)
----------
       227
       

SQL> select obj#
  2    from col_usage$
  3   where obj# not in (select kqftaobj from x$kqfta)
  4     and obj# > 56676;

      OBJ#
----------
4294951073
4294951073
4294952680
4294952683
4294952683
4294952683
4294952684
4294952684
4294952684
4294952684

10 rows selected.

我们可以看到col_usage$里面部分obj#非常大的一部分实际上就是x$表的相关信息。
但是上面10条多出来的信息,我还不知道是怎么回事,大家一起研究一下。

另外,10g开始,db shutdown不会purge 清理col_usage$信息,如下测试:

SQL> show user
USER is "SYS"
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size             142607592 bytes
Database Buffers           20971520 bytes
Redo Buffers                2920448 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> 
SQL> select count(*) from col_usage$;

  COUNT(*)
----------
      3184

关于col_usage$的几篇mos文档,大家可以参考一下:
Column usage in Multi Column Index [ID 400214.1]
DBMS_STATS With METHOD_OPT =>’..SIZE auto’ May Not Collect Histograms [ID 557594.1]
Database Shutdown Immediate Takes Forever, Can Only Do Shutdown Abort [ID 332177.1]

「喜欢文章,快来给作者赞赏墨值吧」

评论

0
0
Oracle
订阅
欢迎订阅Oracle频道,订阅之后可以获取最新资讯和更新通知。
墨值排行
今日本周综合
热门文章
近期活动
全部
相关课程
全部