ORA-20011 ORA-12801 ORA-01722 when gather table statistics

张维照 2019-05-31
9
0 0
摘要:在做一个普通的分区表(HEAP TABLE)收集统计信息时进程报错意外终止,后台日志未出现ora-600等内部错误

问题描述

上周在优化统计信息的收集策略时,在做一个普通的分区表(HEAP TABLE)收集统计信息时进程报错意外终止,后台日志未出现ora-600等内部错误,这是一套11.2.0.3.7 oracle RAC 2-nodes on HPUX-ia 11.31的运行环境,这里简单的记录一下排查过程。

SQL>  exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ANBOB' , tabname => 'TAB_ERROR' , cascade => true, estimate_percent => dbms_stats.auto_sample_size,method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', degree => 8,no_invalidate=>false);

*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-12801: error signaled in parallel query server P001, instance qdim1:im1 (1)
ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_STATS", line 23836
ORA-06512: at "SYS.DBMS_STATS", line 23887
ORA-06512: at line 1


专家解答

Note:
这个错误开始出现的有parallel相关的错误,通常我会逐渐的简化SQL到最后定位问题根源,先取掉并行再试。

SQL> BEGIN dbms_stats.gather_table_stats('ANBOB','TAB_ERROR',method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false); END;
  2  /
BEGIN dbms_stats.gather_table_stats('IM','TAB_ERROR',method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false); END;
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_STATS", line 23836
ORA-06512: at "SYS.DBMS_STATS", line 23887
ORA-06512: at line 1

Note:
基本可以排除parallel引起的错误, “NDV” 通常是统计信息中的唯一值(Number of Distinct Value), 错误与ora-1722有关, 在MOS中未找到任何相似的BUG或案例, 下面是表的创建语句。

CREATE TABLE "ANBOB"."TAB_ERROR"
   (    "REGION" NUMBER(6,0) NOT NULL ENABLE,
        "INV_ID" VARCHAR2(32) NOT NULL ENABLE,
        "RES_TYPE_ID" VARCHAR2(32),
        "INV_STATUS" VARCHAR2(32) NOT NULL ENABLE,
        "STATUS_DATE" DATE,
        "STATUS_REASON" VARCHAR2(32),
        "LDSTORE_BUSI_ID" VARCHAR2(32),
        "LAST_BUSI_ID" VARCHAR2(32) NOT NULL ENABLE,
        "STORE_ID" VARCHAR2(32),
        "ORG_ID" VARCHAR2(32),
        "PHYSICAL_STATUS" VARCHAR2(32) NOT NULL ENABLE,
        "BUSI_STATUS" VARCHAR2(32) NOT NULL ENABLE,
        "HLR_STATUS" VARCHAR2(32),
        "HLR_ID" VARCHAR2(32) DEFAULT 'HLR001',
        "GROUP_ID" VARCHAR2(32),
        "USE_CNT" NUMBER(6,0) DEFAULT 0,
        "PRE_PRICE" NUMBER(8,0) DEFAULT 0,
        "BUNDLE_IMSI" VARCHAR2(32),
        "OPER_ID" VARCHAR2(32),
        "USE_ORG_ID" VARCHAR2(32),
        "BRAND_ID" VARCHAR2(32),
        "PRODUCT_ID" VARCHAR2(256),
        "TELNUM_GRADE" VARCHAR2(32),
        "CREATE_DATE" DATE,
        "MEMO" VARCHAR2(500),
        "USER_PASSWORD" VARCHAR2(32),
        "ADDDATA1" VARCHAR2(32),
        "ADDDATA2" VARCHAR2(32),
        "RECYCLE_DATE" DATE,
        "LDSTORE_DATE" DATE DEFAULT SYSDATE,
        "TELNUM_CLASS" VARCHAR2(32) DEFAULT '0' NOT NULL ENABLE,
        "LOW_CONSUM_FEE" NUMBER(32,0) DEFAULT 0,
        "LOW_CONSUM_PRE" NUMBER(32,0) DEFAULT 0,
        "LOW_INSERVICE_TIME" NUMBER(10,0) DEFAULT 0,
        "TELNUMTAIL8" VARCHAR2(32),
        "TELNUMTAIL7" VARCHAR2(32),
        "TELNUMTAIL6" VARCHAR2(32),
        "TELNUMTAIL5" VARCHAR2(32),
        "TELNUMTAIL4" VARCHAR2(32),
        "TELNUMTAIL3" VARCHAR2(32),
        "TELNUM_CLASSNAME" VARCHAR2(32),
        "MATCH_PATTERN" VARCHAR2(32),
        "NEXT_CALCGRADE_DATE" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 40 MAXTRANS 255
 NOCOMPRESS  LOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA999"
  PARTITION BY RANGE ("REGION")
 (PARTITION "PART_310"  VALUES LESS THAN (311) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 40 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA310" ,
 PARTITION "PART_311"  VALUES LESS THAN (312) SEGMENT CREATION IMMEDIATE
...

使用10046 or errorstack 都是可以的, 这里trace一下出错信息.

SQL> alter session set events '10046 trace name context forever, level 12:1722 trace name errorstack level 3';
Session altered.


SQL>  exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ANBOB' , tabname => 'TAB_ERROR' , 
cascade => true, estimate_percent => dbms_stats.auto_sample_size,
method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT',  no_invalidate=>false);

*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_STATS", line 23836
ORA-06512: at "SYS.DBMS_STATS", line 23887
ORA-06512: at line 1

-- == 10046 trace
WAIT #11529215043894653336: nam='library cache pin' ela= 337 handle address=13835058131968295736 pin address=13835058130966052184 100*mode+namespace=25589415280642 obj#=15388 tim=37250183459212
LOBGETLEN: c=0,e=6,p=0,cr=0,cu=0,tim=37250183460205
LOBREAD: c=0,e=31,p=0,cr=1,cu=0,tim=37250183460276
LOBREAD: c=0,e=13,p=0,cr=1,cu=0,tim=37250183460318
LOBGETLEN: c=0,e=3,p=0,cr=0,cu=0,tim=37250183460407
LOBREAD: c=0,e=12,p=0,cr=1,cu=0,tim=37250183460447
LOBREAD: c=0,e=11,p=0,cr=1,cu=0,tim=37250183460485
LOBGETLEN: c=0,e=5,p=0,cr=0,cu=0,tim=37250183461458
LOBREAD: c=0,e=21,p=0,cr=1,cu=0,tim=37250183461518
=====================
PARSING IN CURSOR #11529215043892965104 len=7279 dep=1 uid=32 oct=3 lid=0 tim=37250183466741 hv=988474521 ad='c0000011fa02b6c8' sqlid='68kd0nhxfpv4t'
:q

-- == errorstack trace
*** 2016-04-05 17:46:21.679
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-01722: invalid number
----- Current SQL Statement for this session (sql_id=68kd0nhxfpv4t) -----


SQL> select sql_fulltext from v$sqlarea where sql_id='&sqlid';
Enter value for sqlid: 68kd0nhxfpv4t

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* SQL Analyze(0) */ select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad  */to_char(count("LOW_INSERVICE_TIME")),to_char(substrb(dump(min("LOW_INSERVICE_TIME"),16,0,32),1,120)),to_char(substrb(dump(max("LOW_INSERVICE_TIME"),16,0,32),1,120)),to_char(count("TELNUMTAIL8")),to_char(substrb(dump(min("TELNUMTAIL8"),16,0,32),1,120)),to_char(substrb(dump(max("TELNUMTAIL8"),16,0,32),1,120)),to_char(count("TELNUMTAIL7")),to_char(substrb(dump(min("TELNUMTAIL7"),16,0,32),1,120)),to_char(substrb(dump(max("TELNUMTAIL7"),16,0,32),1,120)),to_char(count("TELNUMTAIL6")),to_char(substrb(dump(min("TELNUMTAIL6"),16,0,32),1,120)),to_char(substrb(dump(max("TELNUMTAIL6"),16,0,32),1,120)),to_char(count("TELNUMTAIL5")),to_char(substrb(dump(min("TELNUMTAIL5"),16,0,32),1,120)),to_char(substrb(dump(max("TELNUMTAIL5"),16,0,32),1,120)),to_char(count("TELNUMTAIL4")),to_char(substrb(dump(min("TELNUMTAIL4"),16,0,
32),1,120)),to_char(substrb(dump(max("TELNUMTAIL4"),16,0,32),1,120)),to_char(count("TELNUMTAIL3")),to_char(substrb(dump(min("TELNUMTAIL3"),16,0,32),1,120)),to_char(substrb(dump(max("TELNUMTAIL3"),16,0,32),1,120)),to_char(count("TELNUM_CLASSNAME")),to_char(substrb(dump(min("TELNUM_CLASSNAME"),16,0,32),1,120)),to_char(substrb(dump(max("TELNUM_CLASSNAME"),16,0,32),1,120)),to_char(count("MATCH_PATTERN")),to_char(substrb(dump(min("MATCH_PATTERN"),16,0,32),1,120)),to_char(substrb(dump(max("MATCH_PATTERN"),16,0,32),1,120)),to_char(count("NEXT_CALCGRADE_DATE")),to_char(substrb(dump(min("NEXT_CALCGRADE_DATE"),16,0,32),1,120)),to_char(substrb(dump(max("NEXT_CALCGRADE_DATE"),16,0,32),1,120)),to_char(count("SYS_NC00044$")),to_char(substrb(dump(min("SYS_NC00044$"),16,0,32),1,120)),to_char(substrb(dump(max("SYS_NC00044$"),16,0,32),1,120)),to_char(count("SYS_NC00045$")),to_char(substrb(dump(min("SYS_NC00045$"),16,0,32),1,120)),to_char(substrb(dump(max("SYS_NC00045$"),16,0,32),1,120)),to_char(count("
BRAND_ID")),to_char(substrb(dump(min("BRAND_ID"),16,0,32),1,120)),to_char(substrb(dump(max("BRAND_ID"),16,0,32),1,120)),to_char(count("PRODUCT_ID")),to_char(substrb(dump(min(
...
 from "ANBOB"."TAB_ERROR" t  where TBL$OR$IDX$PART$NUM("ANBOB"."TAB_ERROR",0,4,0,"ROWID") = :objn /* 
 
 
 SQL> select column_name,column_id,hidden_column,virtual_column,segment_column_id,internal_column_id
  2       from dba_tab_cols where owner='ANBOB' and table_name='TAB_ERROR' ORDER BY 2;

COLUMN_NAME                COLUMN_ID HIDDEN VIRTUA SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
------------------------- ---------- ------ ------ ----------------- ------------------
REGION                             1 NO     NO                     1                  1
INV_ID                             2 NO     NO                     2                  2
RES_TYPE_ID                        3 NO     NO                     3                  3
INV_STATUS                         4 NO     NO                     4                  4
STATUS_DATE                        5 NO     NO                     5                  5
STATUS_REASON                      6 NO     NO                     6                  6
LDSTORE_BUSI_ID                    7 NO     NO                     7                  7
LAST_BUSI_ID                       8 NO     NO                     8                  8
STORE_ID                           9 NO     NO                     9                  9
ORG_ID                            10 NO     NO                    10                 10
PHYSICAL_STATUS                   11 NO     NO                    11                 11
BUSI_STATUS                       12 NO     NO                    12                 12
HLR_STATUS                        13 NO     NO                    13                 13
HLR_ID                            14 NO     NO                    14                 14
...
TELNUM_CLASS                      31 NO     NO                    31                 31
LOW_CONSUM_FEE                    32 NO     NO                    32                 32
LOW_CONSUM_PRE                    33 NO     NO                    33                 33
LOW_INSERVICE_TIME                34 NO     NO                    34                 34
TELNUMTAIL8                       35 NO     NO                    35                 35
TELNUMTAIL7                       36 NO     NO                    36                 36
TELNUMTAIL6                       37 NO     NO                    37                 37
TELNUMTAIL5                       38 NO     NO                    38                 38
TELNUMTAIL4                       39 NO     NO                    39                 39
TELNUMTAIL3                       40 NO     NO                    40                 40
TELNUM_CLASSNAME                  41 NO     NO                    41                 41
MATCH_PATTERN                     42 NO     NO                    42                 42
NEXT_CALCGRADE_DATE               43 NO     NO                    43                 43
SYS_NC00044$                         YES    YES                                      44
SYS_NC00045$                         YES    YES                                      45

SQL>   select col#,name,TYPE#,DEFAULT$ ,property from col$ where OBJ#=15386
       order by 1
      COL# NAME                        TYPE# DEFAULT$                              PROPERTY
---------- ---------------------- ---------- ----------------------------------- ----------
         0 SYS_NC00045$                    2 "PRE_PRICE"+NVL("LOW_CONSUM_PRE",0)      65832
         0 SYS_NC00044$                    2 TO_NUMBER("TELNUM_CLASS")               327976
         1 REGION                          2                                              0
         2 INV_ID                          1                                              0
         3 RES_TYPE_ID                     1                                              0
         4 INV_STATUS                      1                                              0
         5 STATUS_DATE                    12                                              0
         6 STATUS_REASON                   1                                              0
         7 LDSTORE_BUSI_ID                 1                                              0
         8 LAST_BUSI_ID                    1                                              0
         9 STORE_ID                        1                                              0
        10 ORG_ID                          1                                              0
        11 PHYSICAL_STATUS                 1                                              0
        12 BUSI_STATUS                     1                                              0
        13 HLR_STATUS                      1                                              0
        14 HLR_ID                          1 'HLR001'                                     0
        15 GROUP_ID                        1                                              0
        16 USE_CNT                         2 0                                            0
        17 PRE_PRICE                       2 0                                            0
        18 BUNDLE_IMSI                     1                                              0
        19 OPER_ID                         1                                              0
        20 USE_ORG_ID                      1                                              0
        21 BRAND_ID                        1                                              0
        22 PRODUCT_ID                      1                                              0
        23 TELNUM_GRADE                    1                                              0
        24 CREATE_DATE                    12                                              0
        25 MEMO                            1                                              0
        26 USER_PASSWORD                   1                                              0
        27 ADDDATA1                        1                                              0
        28 ADDDATA2                        1                                              0
        29 RECYCLE_DATE                   12                                              0
        30 LDSTORE_DATE                   12 SYSDATE                                      0
        31 TELNUM_CLASS                    1 '0'                                          0
        32 LOW_CONSUM_FEE                  2 0                                            0
        33 LOW_CONSUM_PRE                  2 0                                            0
        34 LOW_INSERVICE_TIME              2 0                                            0
        35 TELNUMTAIL8                     1                                              0
        36 TELNUMTAIL7                     1                                              0
        37 TELNUMTAIL6                     1                                              0
        38 TELNUMTAIL5                     1                                              0
        39 TELNUMTAIL4                     1                                              0
        40 TELNUMTAIL3                     1                                              0
        41 TELNUM_CLASSNAME                1                                              0
        42 MATCH_PATTERN                   1                                              0
        43 NEXT_CALCGRADE_DATE            12                                              0

45 rows selected.

TIP:
 property      number not null,           /* column properties (bit flags): */
                /* 0x0001 =       1 = ADT attribute column                   */
                /* 0x0002 =       2 = OID column                             */
                /* 0x0004 =       4 = nested table column                    */
                /* 0x0008 =       8 = virtual column                         */
                /* 0x0010 =      16 = nested table's SETID$ column           */
                /* 0x0020 =      32 = hidden column                          */
                /* 0x0040 =      64 = primary-key based OID column           */
                /* 0x0080 =     128 = column is stored in a lob              */
                /* 0x0100 =     256 = system-generated column                */  ---
                /* 0x0200 =     512 = rowinfo column of typed table/view     */
                /* 0x0400 =    1024 = nested table columns setid             */
                /* 0x0800 =    2048 = column not insertable                  */
                /* 0x1000 =    4096 = column not updatable                   */
                /* 0x2000 =    8192 = column not deletable                   */
                /* 0x4000 =   16384 = dropped column                         */
                /* 0x8000 =   32768 = unused column - data still in row      */
            /* 0x00010000 =   65536 = virtual column                         */   ---
            /* 0x00020000 =  131072 = place DESCEND operator on top          */
            /* 0x00040000 =  262144 = virtual column is NLS dependent        */
            /* 0x00080000 =  524288 = ref column (present as oid col)        */
            /* 0x00100000 = 1048576 = hidden snapshot base table column      */
            /* 0x00200000 = 2097152 = attribute column of a user-defined ref */
            /* 0x00400000 = 4194304 = export hidden column,RLS on hidden col */
            /* 0x00800000 = 8388608 = string column measured in characters   */
           /* 0x01000000 = 16777216 = virtual column expression specified    */
           /* 0x02000000 = 33554432 = typeid column                          */
           /* 0x04000000 = 67108864 = Column is encrypted                    */
          /* 0x20000000 = 536870912 = Column is encrypted without salt       */
65832=65536 + 256 + 32 + 8
327976 = 262144 + 65536 + 256 + 32 + 8

Note:
SQL中含有该表上两个系统生成的SYS_NC开头的列, SYS_NC开头的通常是创建表上的函数索引时自动增加的虚拟列。

SQL> select INDEX_NAME,INDEX_TYPE from dba_indexes where TABLE_NAME='TAB_ERROR' AND TABLE_OWNER='ANBOB';

INDEX_NAME                               INDEX_TYPE
---------------------------------------- ------------------------
IDX_IMTELNUMIMSI                         NORMAL
IDX_IMTELNUMTYPE                         NORMAL
IDX_IMTELNUM_INV_ORG                     NORMAL
IDX_IMTELNUM_ORG_INV                     NORMAL
PK_IMINVTELNUM                           NORMAL
IDX_IMINVTELNUM_LASTBUSIID               NORMAL
IDX_IMINVTELNUM_LDBUSIID                 NORMAL
IDX_IMTELNUMTAIL8                        NORMAL
IDX_IMTELNUMTAIL7                        NORMAL
IDX_IMTELNUMTAIL6                        NORMAL
IDX_IMTELNUMTAIL5                        NORMAL
IDX_IMTELNUMTAIL4                        NORMAL
IDX_IMTELNUMTAIL3                        NORMAL
IDX_IMTELNUMORGID                        NORMAL
IDX_IMTELNUMINVIDORGID                   NORMAL

SQL> explain plan for
  2  select * from ANBOB.TAB_ERROR where "PRE_PRICE"+NVL("LOW_CONSUM_PRE",0)=10;
Explained.

SQL> @x2
PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 1631671213

-----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |   500K|   117M|   576K  (1)| 01:55:18 |       |       |
|   1 |  PARTITION RANGE ALL|               |   500K|   117M|   576K  (1)| 01:55:18 |     1 |    12 |
|*  2 |   TABLE ACCESS FULL |     TAB_ERROR |   500K|   117M|   576K  (1)| 01:55:18 |     1 |    12 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("PRE_PRICE"+NVL("LOW_CONSUM_PRE",0)=10)

14 rows selected.

Note:
如果列上有函数索引,根据列上的DEFAULT 值,上面的SQL 应该可以使用函数索引,但是还是全表扫描。确认了表上并未包含函数索引。 如果这样的话就说明有数据字典不一致的情况, 可以使用Hcheck.sql 进行扫描,该脚本MOS上有提供。

SQL> @hcheck3
Package created.
Package body created.

H.Check Version 9i+/hc3.50
---------------------------------------
Catalog Version 11.2.0.3.0 (1102000300)
---------------------------------------

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release      Run
------------------------------ ... ---------- -- ----------   ---
.- SynLastDDLTim               ... 1102000300 >  1001000200 : n/a
.- LobNotInObj                 ... 1102000300 >  1000000200 : n/a
.- MissingOIDOnObjCol          ... 1102000300 <= *All Rel* : Ok .- SourceNotInObj ... 1102000300 >  1002000100 : n/a
.- IndIndparMismatch           ... 1102000300 >  1102000100 : n/a
.- InvCorrAudit                ... 1102000300 >  1102000100 : n/a
.- OversizedFiles              ... 1102000300 <= *All Rel* : Ok .- TinyFiles ... 1102000300 >   900010000 : n/a
.- PoorDefaultStorage          ... 1102000300 <=  *All Rel* : Ok
.- PoorStorage                 ... 1102000300 <= *All Rel* : Ok .- MissTabSubPart ... 1102000300 >   900010000 : n/a
.- PartSubPartMismatch         ... 1102000300 >  1102000100 : n/a
.- TabPartCountMismatch        ... 1102000300 <= *All Rel* : Ok .- OrphanedTabComPart ... 1102000300 >   900010000 : n/a
.- ZeroTabSubPart              ... 1102000300 >   902000100 : n/a
.- MissingSum$                 ... 1102000300 <=  *All Rel* : Ok
.- MissingDir$                 ... 1102000300 <=  *All Rel* : Ok
.- DuplicateDataobj            ... 1102000300 <=  *All Rel* : Ok
.- ObjSynMissing               ... 1102000300 <=  *All Rel* : Ok
.- ObjSeqMissing               ... 1102000300 <=  *All Rel* : Ok
.- OrphanedUndo                ... 1102000300 <=  *All Rel* : Ok
.- OrphanedIndex               ... 1102000300 <=  *All Rel* : Ok
.- OrphanedIndexPartition      ... 1102000300 <=  *All Rel* : Ok
.- OrphanedIndexSubPartition   ... 1102000300 <=  *All Rel* : Ok
.- OrphanedTable               ... 1102000300 <=  *All Rel* : Ok
.- OrphanedTablePartition      ... 1102000300 <=  *All Rel* : Ok
.- OrphanedTableSubPartition   ... 1102000300 <=  *All Rel* : Ok
.- MissingPartCol              ... 1102000300 <=  *All Rel* : Ok
.- OrphanedSeg$                ... 1102000300 <= *All Rel* : Ok .- OrphanedIndPartObj# ... 1102000300 >  1101000600 : n/a
.- DuplicateBlockUse           ... 1102000300 <= *All Rel* : Ok .- HighObjectIds ... 1102000300 >   801060000 : n/a
.- PQsequence                  ... 1102000300 >   800060000 : n/a
.- TruncatedCluster            ... 1102000300 >   801070000 : n/a
.- FetUet                      ... 1102000300 <=  *All Rel* : Ok
.- Uet0Check                   ... 1102000300 <=  *All Rel* : Ok
.- ExtentlessSeg               ... 1102000300 <=  *All Rel* : Ok
.- SeglessUET                  ... 1102000300 <=  *All Rel* : Ok
.- BadInd$                     ... 1102000300 <=  *All Rel* : Ok
.- BadTab$                     ... 1102000300 <= *All Rel* : Ok .- BadIcolDepCnt ... 1102000300 >  1101000700 : n/a
.- WarnIcolDep                 ... 1102000300 >  1101000700 : n/a
.- OnlineRebuild$              ... 1102000300 <= *All Rel* : Ok .- DropForceType ... 1102000300 >  1001000200 : n/a
.- TrgAfterUpgrade             ... 1102000300 <=  *All Rel* : Ok
.- FailedInitJVMRun            ... 1102000300 <= *All Rel* : Ok .- TypeReusedAfterDrop ... 1102000300 >   900010000 : n/a
.- Idgen1$TTS                  ... 1102000300 >   900010000 : n/a
.- DroppedFuncIdx              ... 1102000300 >   902000100 : n/a
.- BadOwner                    ... 1102000300 >   900010000 : n/a
.- UpgCheckc0801070            ... 1102000300 <=  *All Rel* : Ok
.- BadPublicObjects            ... 1102000300 <=  *All Rel* : Ok
.- BadSegFreelist              ... 1102000300 <= *All Rel* : Ok .- BadCol# ... 1102000300 >  1001000200 : n/a
.- BadDepends                  ... 1102000300 <=  *All Rel* : Ok
.- CheckDual                   ... 1102000300 <=  *All Rel* : Ok
.- ObjectNames                 ... 1102000300 <=  *All Rel* : Ok
.- BadCboHiLo                  ... 1102000300 <=  *All Rel* : Ok
.- ChkIotTs                    ... 1102000300 <=  *All Rel* : Ok
.- NoSegmentIndex              ... 1102000300 <=  *All Rel* : Ok
.- BadNextObject               ... 1102000300 <= *All Rel* : Ok .- OrphanIndopt ... 1102000300 >   902000800 : n/a
.- UpgFlgBitTmp                ... 1102000300 >  1001000100 : n/a
.- RenCharView                 ... 1102000300 >  1001000100 : n/a
.- Upg9iTab$                   ... 1102000300 >   902000400 : n/a
.- Upg9iTsInd                  ... 1102000300 >   902000500 : n/a
.- Upg10gInd$                  ... 1102000300 >  1002000000 : n/a
.- DroppedROTS                 ... 1102000300 <= *All Rel* : Ok .- ChrLenSmtcs ... 1102000300 >  1101000600 : n/a
.- FilBlkZero                  ... 1102000300 <=  *All Rel* : Ok
.- DbmsSchemaCopy              ... 1102000300 <=  *All Rel* : Ok

Found 0 potential problem(s) and 0 warning(s)
Contact Oracle Support with the output
to check if the above needs attention or not

note:
Hcheck 未明确存在错误,实际col$确实多出两列函数虚拟列的记录,在基表ind$ / obj$ / icol$ / indpart$ 中未找到对应记录。下面分析这两个虚拟列与上面的报错有什么关系?

select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad  */
to_char(count("SYS_NC00044$")),to_char(substrb(dump(min("SYS_NC00044$"),16,0,32),1,120)),   <<<<<<<<<<<< to_char(substrb(dump(max("SYS_NC00044$"),16,0,32),1,120)),to_char(count("SYS_NC00045$")), to_char(substrb(dump(min("SYS_NC00045$"),16,0,32),1,120)),to_char(substrb(dump(max("SYS_NC00045$"),16,0,32),1,120)) ... from "ANBOB"."TAB_ERROR" t where TBL$OR$IDX$PART$NUM("ANBOB"."TAB_ERROR",0,4,0,"ROWID") = :objn SQL>   select col#,name,TYPE#,DEFAULT$  from col$ where OBJ#=15386 order by 1

     COL# NAME                                TYPE# DEFAULT$
---------- ------------------------------ ---------- --------------------------------------------------
        0 SYS_NC00045$                            2 "PRE_PRICE"+NVL("LOW_CONSUM_PRE",0)
        0 SYS_NC00044$                            2 TO_NUMBER("TELNUM_CLASS")  <<<<<<<<<<<<<<<<<

CREATE TABLE "ANBOB"."TAB_ERROR"
       ......
       "PRE_PRICE" NUMBER(8,0) DEFAULT 0,
       "LOW_CONSUM_PRE" NUMBER(32,0) DEFAULT 0,
       "TELNUM_CLASS" VARCHAR2(32) DEFAULT '0' NOT NULL ENABLE,    <<<<<<<<<<<<<<<<
      ......

Note:
统计信息中SQL 中用到了两个虚拟列, 虚拟列是基于表上的另外三个列,对于number列的做计算不会有问题,因为 SYS_NC00044$= to_number(telnum_class), to_char(count(“SYS_NC00044$”)) 可以换算成to_char(count(to_number(telnum_class))),又因为telnum_class为varchar2类型,所以如果telnum_class列存在非数值类型格式的值,就会出现ORA-01722: invalid number错误。

SQL> select to_char(count("SYS_NC00044$")) from anbob.TAB_ERROR; 
select to_char(count("SYS_NC00044$")) from anbob.TAB_ERROR
*
ERROR at line 1:
ORA-01722: invalid number


SQL> select to_char(count(to_number(telnum_class))) from anbob.TAB_ERROR;
select to_char(count(to_number(telnum_class))) from anbob.TAB_ERROR
                     *
ERROR at line 1:
ORA-01722: invalid number

create or replace function isnumber(p_in varchar2) return number as
  i number;
begin
  i := to_number(p_in);
  return 1;
exception
  when others then
    return 0;
end;

SQL>  select rowid rid,telnum_class from anbob.TAB_ERROR where isnumber(telnum_class)=0;

RID                TELNUM_CLASS
------------------ ----------------------------------------------------------------
AACjsZABrAACN/gAAQ 2008-6-21 5:49:54
AACjsZABrAACN/gAAR 2008-9-28 14:56:56
AACjsZABrAACN/gAAY 2009-4-13 9:44:39
AACjsZABtAAAnhxAAC 2009-5-12 9:23:40
AACjsZABtAAAnhxAAP 2009-5-12 9:23:45
AACjsZABuAAB5l9AAT 2008-6-21 5:54:14
AACjsZABuAAB6aCAAB 2008-6-21 5:58:04
...

NOTE:
找出telnum_class列中非数字格式的值, 首先可以创建一个简单的函数。到这里整个问题就清晰了,因为数据字典中列上存在两个虚拟列,其中SYS_NC00044$虚拟列是基于telnum_class字符列的to_number, telnum_class上存在非数字的值,在收集列上的统计信息时如NDV报ora-1722错误。

解决方法:
1, 常规保守型

1) Create table as select * from ....;
2) Recreate the indexes on the new table
3) Run dbms_stats on the new table to see if it works
4) Drop the old table and rename the new table 

OR  using Exchange partition to save time
1) Create a new partitioned table with the same structure with the old table.
2) Create a temp table (non-partitioned) with the same structure with the old table:
3) Exchange the old table's partition (anbob.TAB_ERROR) with the temp table(anbob.tmp):
4) Exchange the new table s partition(anbob.TAB_ERROR_new) with the temp table(anbob.tmp):
5) Exchange all of the partitions in the old table as step 3&4.
6) Recreate the indexes on the new table.
7) Run dbms_stats on the new table to see if it works.
8) Drop the old table and rename the new table.

2, 简单粗暴型
The operation below is in test DB env of store device synchronous(Note do not test the steps below in a production database)

SQL> delete from col$ where OBJ#=15386 and col#=0;
 rows deleted.

SQL> commit;
Commit complete.

SQL> alter system flush shared_pool;
System altered.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'anbob' , tabname => 'TAB_ERROR' , 
cascade => true, estimate_percent => dbms_stats.auto_sample_size,
method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', degree => 8,no_invalidate=>false);

PL/SQL procedure successfully completed.


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

评论

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