---問題
select owner,index_name,DEGREE from DBA_INDEXES where DEGREE>1
*
ERROR at line 1:
ORA-01722: invalid number
--1 獲取執行跟蹤信息
SQL> set autotrace traceonly
SQL> select owner,index_name,DEGREE from DBA_INDEXES where DEGREE=1 and rownum<2;
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 3920975716
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 120 | 18 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 120 | 18 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 117 | 17 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 106 | 16 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 102 | 15 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 85 | 14 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 81 | 13 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 73 | 12 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 65 | 11 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | IND$ | 25 | 750 | 10 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 35 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 0 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 0 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
| 19 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
| 21 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
| 23 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 |
| 25 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
10 - filter(TO_NUMBER(DECODE("I"."DEGREE",32767,'DEFAULT',TO_CHAR(NVL("I"."DEGREE",1))))=1
AND BITAND("I"."FLAGS",4096)=0)
11 - filter(BITAND("O"."FLAGS",128)=0)
12 - access("O"."OBJ#"="I"."OBJ#")
14 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+))
16 - access("I"."BO#"="IO"."OBJ#")
18 - access("ITO"."OWNER#"="ITU"."USER#"(+))
20 - access("U"."USER#"="O"."OWNER#")
22 - access("IO"."OWNER#"="IU"."USER#")
24 - access("I"."TS#"="S"."TS#"(+) AND "I"."FILE#"="S"."FILE#"(+) AND
"I"."BLOCK#"="S"."BLOCK#"(+))
26 - access("I"."TS#"="TS"."TS#"(+))
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
655 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--2
select TO_NUMBER(DECODE("I"."DEGREE",32767,'DEFAULT',TO_CHAR(NVL("I"."DEGREE",1)))) from sys.ind$ i --語句測試
--3 發現對象異常
select TO_NUMBER(DECODE("I"."DEGREE",32767,'DEFAULT',TO_CHAR(NVL("I"."DEGREE",1)))) from sys.ind$ i where obj#<>'55180'
--4 通過幾個庫查詢 11g 沒有這個問題 10g 有這個問題
--10046 跟蹤不正常 沒發現問題根本
--errorstatk 跟蹤裡面太多內部信息，無法確定具體原因
--還是合理猜想+數據驗證獲取結論
