Oracle 11.2的BUG？還是我的問題？
環境：
Windows 7
Oracle 11.2.0.1 32位
相同的問題在10g,11g Linux下沒有重現。不知道是不是我這裡的11g Windows有問題？
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 25 10:30:53 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn / as sysdba
已連線。
SQL> select table_name from dba_tab_modifications where rownum<5;
TABLE_NAME
------------------------------
TAB$
SEG$
OBJ$
IND$
SQL> select table_name from dba_tab_modifications where table_name='TAB$';
TABLE_NAME
------------------------------
TAB$
SQL> select * from dba_tab_modifications where table_name='TAB$';
未選定行
SQL> set autot on
SQL> select table_name from dba_tab_modifications where table_name='TAB$';
TABLE_NAME
------------------------------
TAB$
執行計劃
----------------------------------------------------------
Plan hash value: 3635092817
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 68 | 96 (0)| 00:00:02 |
| 1 | VIEW | DBA_TAB_MODIFICATIONS | 4 | 68 | 96 (0)| 00:00:02 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 2 | 92 | 46 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 2 | 82 | 45 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 2 | 74 | 45 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | I_USER2 | 43 | 129 | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I_OBJ2 | 1 | 34 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | I_MON_MODS_ALL$_OBJ | 1 | 4 | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS CLUSTER | TAB$ | 1 | 5 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 45 | 45 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 41 | 45 (0)| 00:00:01 |
| 13 | INDEX FULL SCAN | I_USER2 | 43 | 129 | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | I_OBJ2 | 1 | 38 | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_MON_MODS_ALL$_OBJ | 1 | 4 | 0 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 72 | 5 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 67 | 4 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 63 | 4 (0)| 00:00:01 |
| 19 | MERGE JOIN CARTESIAN | | 1 | 29 | 3 (0)| 00:00:01 |
| 20 | TABLE ACCESS FULL | TABSUBPART$ | 1 | 26 | 2 (0)| 00:00:01 |
| 21 | BUFFER SORT | | 43 | 129 | 1 (0)| 00:00:01 |
| 22 | INDEX FULL SCAN | I_USER2 | 43 | 129 | 1 (0)| 00:00:01 |
|* 23 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 34 | 1 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | I_MON_MODS_ALL$_OBJ | 1 | 4 | 0 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"='TAB$')
8 - access("O"."OBJ#"="M"."OBJ#")
10 - access("O"."OBJ#"="T"."OBJ#")
14 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"='TAB$' AND "O"."TYPE#"=19)
filter("O"."TYPE#"=19)
15 - access("O"."OBJ#"="M"."OBJ#")
23 - filter("O"."NAME"='TAB$')
24 - access("O"."OBJ#"="TSP"."OBJ#" AND "O"."OWNER#"="U"."USER#")
25 - access("O"."OBJ#"="M"."OBJ#")
26 - access("O2"."OBJ#"="TSP"."POBJ#")
統計資訊
----------------------------------------------------------
8 recursive calls
0 db block gets
302 consistent gets
110 physical reads
64 redo size
427 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from dba_tab_modifications where table_name='TAB$';
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 4078897460
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 472| 113 (1)| 00:00:02 |
|* 1 | HASH JOIN | | 4 | 472| 113 (1)| 00:00:02 |
|* 2 | HASH JOIN | | 4 | 380| 111 (1)| 00:00:02 |
| 3 | VIEW | VW_JF_SET$35EDC1EA | 4 | 308| 107 (0)| 00:00:02 |
| 4 | UNION-ALL | | | | | |
| 5 | NESTED LOOPS | | 2 | 78| 52 (0)| 00:00:01 |
|* 6 | INDEX SKIP SCAN | I_OBJ2 | 2 | 68| 51 (0)| 00:00:01 |
| 7 | TABLE ACCESS CLUSTER | TAB$ | 1 | 5| 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
|* 9 | INDEX SKIP SCAN | I_OBJ2 | 1 | 40| 51 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | NESTED LOOPS | | 1 | 69| 4 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 62| 3 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | TABSUBPART$ | 1 | 26| 2 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 36| 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 7| 1 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | USER$ | 43 | 774| 3 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | MON_MODS_ALL$ | 68 | 1564| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_2"="M"."OBJ#")
2 - access("ITEM_1"="U"."USER#")
6 - access("O"."NAME"='TAB$')
filter("O"."NAME"='TAB$')
8 - access("O"."OBJ#"="T"."OBJ#")
9 - access("O"."NAME"='TAB$' AND "O"."TYPE#"=19)
filter("O"."NAME"='TAB$' AND "O"."TYPE#"=19)
14 - filter("O"."NAME"='TAB$')
15 - access("O"."OBJ#"="TSP"."OBJ#")
16 - access("O2"."OBJ#"="TSP"."POBJ#")
統計資訊
----------------------------------------------------------
856 recursive calls
0 db block gets
343 consistent gets
0 physical reads
0 redo size
871 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
0 rows processed[/font]看起來很類似於data block與index block不符的情況，重建一下index I_MON_MODS_ALL$_OBJ試試看
索引已刪除。
--c1001000.sql檔案中有這個索引的定義
SQL> create unique index i_mon_mods_all$_obj on mon_mods_all$(obj#)
2 storage (maxextents unlimited)
3 /
索引已建立。
SQL> set autot off
SQL> select table_name from dba_tab_modifications where table_name='TAB$';
TABLE_NAME
------------------------------
TAB$
SQL> select * from dba_tab_modifications where table_name='TAB$';
未選定行[/font]還是不行
le_name='TAB$';
未選定行
SQL> select * from table(dbms_xplan.display_cursor(null,0,'ALL IOSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID agc1pu4skkrn8, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ * from dba_tab_modificationS where
table_name='TAB$'
Plan hash value: 3953559329
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 161 (100)| | 0 |00:00:00.01 | 55 |
|* 1 | HASH JOIN | | 1 | 4 | 472 | 161 (1)| 00:00:02 | 0 |00:00:00.01 | 55 |
|* 2 | HASH JOIN | | 1 | 4 | 380 | 159 (1)| 00:00:02 | 1 |00:00:00.01 | 52 |
| 3 | VIEW | VW_JF_SET$35EDC1EA | 1 | 4 | 308 | 155 (0)| 00:00:02 | 1 |00:00:00.01 | 47 |
| 4 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 47 |
| 5 | NESTED LOOPS | | 1 | 2 | 78 | 52 (0)| 00:00:01 | 1 |00:00:00.01 | 18 |
|* 6 | INDEX SKIP SCAN | I_OBJ2 | 1 | 2 | 68 | 51 (0)| 00:00:01 | 1 |00:00:00.01 | 14 |
| 7 | TABLE ACCESS CLUSTER | TAB$ | 1 | 1 | 5 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 8 | INDEX UNIQUE SCAN | I_OBJ# | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 |
|* 9 | INDEX SKIP SCAN | I_OBJ2 | 1 | 1 | 40 | 51 (0)| 00:00:01 | 0 |00:00:00.01 | 14 |
| 10 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 15 |
| 11 | NESTED LOOPS | | 1 | 1 | 69 | 52 (0)| 00:00:01 | 0 |00:00:00.01 | 15 |
| 12 | NESTED LOOPS | | 1 | 1 | 62 | 51 (0)| 00:00:01 | 0 |00:00:00.01 | 15 |
|* 13 | INDEX SKIP SCAN | I_OBJ2 | 1 | 2 | 72 | 51 (0)| 00:00:01 | 1 |00:00:00.01 | 14 |
| 14 | TABLE ACCESS BY INDEX ROWID| TABSUBPART$ | 1 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 1 |
|* 15 | INDEX UNIQUE SCAN | I_TABSUBPART$_OBJ$ | 1 | 1 | | 0 (0)| | 0 |00:00:00.01 | 1 |
|* 16 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 17 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 0 | 1 | 7 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 18 | TABLE ACCESS FULL | USER$ | 1 | 43 | 774 | 3 (0)| 00:00:01 | 46 |00:00:00.01 | 5 |
| 19 | TABLE ACCESS FULL | MON_MODS_ALL$ | 1 | 68 | 1564 | 2 (0)| 00:00:01 | 60 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$4CC7D0F8
3 - SET$35EDC1EA / VW_JF_SET$35EDC1EA@SEL$A33807FD
4 - SET$35EDC1EA
5 - SEL$61D13A11
6 - SEL$61D13A11 / O@SEL$2
7 - SEL$61D13A11 / T@SEL$2
8 - SEL$61D13A11 / T@SEL$2
9 - SEL$61BB150F / O@SEL$3
10 - SEL$5962AF70
13 - SEL$5962AF70 / O@SEL$4
14 - SEL$5962AF70 / TSP@SEL$4
15 - SEL$5962AF70 / TSP@SEL$4
16 - SEL$5962AF70 / O2@SEL$4
17 - SEL$5962AF70 / O2@SEL$4
18 - SEL$4CC7D0F8 / U@SEL$2
19 - SEL$4CC7D0F8 / M@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_2"="M"."OBJ#")
2 - access("ITEM_1"="U"."USER#")
6 - access("O"."NAME"='TAB$')
filter("O"."NAME"='TAB$')
8 - access("O"."OBJ#"="T"."OBJ#")
9 - access("O"."NAME"='TAB$' AND "O"."TYPE#"=19)
filter(("O"."NAME"='TAB$' AND "O"."TYPE#"=19))
13 - access("O"."NAME"='TAB$')
filter("O"."NAME"='TAB$')
15 - access("O"."OBJ#"="TSP"."OBJ#")
16 - access("O2"."OBJ#"="TSP"."POBJ#")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "U"."NAME"[VARCHAR2,30], "ITEM_5"[VARCHAR2,30], "ITEM_3"[VARCHAR2,30], "ITEM_4"[VARCHAR2,30],
"M"."DROP_SEGMENTS"[NUMBER,22], "M"."INSERTS"[NUMBER,22], "M"."UPDATES"[NUMBER,22], "M"."DELETES"[NUMBER,22], "M"."TIMESTAMP"[DATE,7],
"M"."FLAGS"[NUMBER,22]
2 - (#keys=1) "ITEM_2"[NUMBER,22], "ITEM_5"[VARCHAR2,30], "ITEM_3"[VARCHAR2,30], "ITEM_4"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30]
3 - "ITEM_2"[NUMBER,22], "ITEM_1"[NUMBER,22], "ITEM_3"[VARCHAR2,30], "ITEM_4"[VARCHAR2,30], "ITEM_5"[VARCHAR2,30]
4 - STRDEF[22], STRDEF[22], STRDEF[30], STRDEF[30], STRDEF[30]
5 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30]
6 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30]
7 - "T".ROWID[ROWID,10], "T"."OBJ#"[NUMBER,22]
8 - "T".ROWID[ROWID,10]
9 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],"O"."SUBNAME"[VARCHAR2,30]
10 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],"O"."SUBNAME"[VARCHAR2,30], "O2"."SUBNAME"[VARCHAR2,30]
11 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],"O"."SUBNAME"[VARCHAR2,30], "O2".ROWID[ROWID,10]
12 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],"O"."SUBNAME"[VARCHAR2,30], "TSP"."POBJ#"[NUMBER,22]
13 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],"O"."SUBNAME"[VARCHAR2,30]
14 - "TSP"."POBJ#"[NUMBER,22]
15 - "TSP".ROWID[ROWID,10]
16 - "O2".ROWID[ROWID,10]
17 - "O2"."SUBNAME"[VARCHAR2,30]
18 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
19 - "M"."OBJ#"[NUMBER,22], "M"."INSERTS"[NUMBER,22], "M"."UPDATES"[NUMBER,22], "M"."DELETES"[NUMBER,22], "M"."TIMESTAMP"[DATE,7],
"M"."FLAGS"[NUMBER,22], "M"."DROP_SEGMENTS"[NUMBER,22]
Note
-----
- cardinality feedback used for this statement
已選擇97行。[/font]可以看出，在步驟1的HASH_JOIN時，資料沒有了，並且應該是MON_MODS_ALL$的全表掃描的問題？
Windows 7
Oracle 11.2.0.1 32位
相同的問題在10g,11g Linux下沒有重現。不知道是不是我這裡的11g Windows有問題？
CODE:
[font=宋體]C:\Users\Administrator>sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 25 10:30:53 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn / as sysdba
已連線。
SQL> select table_name from dba_tab_modifications where rownum<5;
TABLE_NAME
------------------------------
TAB$
SEG$
OBJ$
IND$
SQL> select table_name from dba_tab_modifications where table_name='TAB$';
TABLE_NAME
------------------------------
TAB$
SQL> select * from dba_tab_modifications where table_name='TAB$';
未選定行
SQL> set autot on
SQL> select table_name from dba_tab_modifications where table_name='TAB$';
TABLE_NAME
------------------------------
TAB$
執行計劃
----------------------------------------------------------
Plan hash value: 3635092817
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 68 | 96 (0)| 00:00:02 |
| 1 | VIEW | DBA_TAB_MODIFICATIONS | 4 | 68 | 96 (0)| 00:00:02 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 2 | 92 | 46 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 2 | 82 | 45 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 2 | 74 | 45 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | I_USER2 | 43 | 129 | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I_OBJ2 | 1 | 34 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | I_MON_MODS_ALL$_OBJ | 1 | 4 | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS CLUSTER | TAB$ | 1 | 5 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 45 | 45 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 41 | 45 (0)| 00:00:01 |
| 13 | INDEX FULL SCAN | I_USER2 | 43 | 129 | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | I_OBJ2 | 1 | 38 | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_MON_MODS_ALL$_OBJ | 1 | 4 | 0 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 72 | 5 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 67 | 4 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 63 | 4 (0)| 00:00:01 |
| 19 | MERGE JOIN CARTESIAN | | 1 | 29 | 3 (0)| 00:00:01 |
| 20 | TABLE ACCESS FULL | TABSUBPART$ | 1 | 26 | 2 (0)| 00:00:01 |
| 21 | BUFFER SORT | | 43 | 129 | 1 (0)| 00:00:01 |
| 22 | INDEX FULL SCAN | I_USER2 | 43 | 129 | 1 (0)| 00:00:01 |
|* 23 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 34 | 1 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | I_MON_MODS_ALL$_OBJ | 1 | 4 | 0 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"='TAB$')
8 - access("O"."OBJ#"="M"."OBJ#")
10 - access("O"."OBJ#"="T"."OBJ#")
14 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"='TAB$' AND "O"."TYPE#"=19)
filter("O"."TYPE#"=19)
15 - access("O"."OBJ#"="M"."OBJ#")
23 - filter("O"."NAME"='TAB$')
24 - access("O"."OBJ#"="TSP"."OBJ#" AND "O"."OWNER#"="U"."USER#")
25 - access("O"."OBJ#"="M"."OBJ#")
26 - access("O2"."OBJ#"="TSP"."POBJ#")
統計資訊
----------------------------------------------------------
8 recursive calls
0 db block gets
302 consistent gets
110 physical reads
64 redo size
427 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from dba_tab_modifications where table_name='TAB$';
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 4078897460
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 472| 113 (1)| 00:00:02 |
|* 1 | HASH JOIN | | 4 | 472| 113 (1)| 00:00:02 |
|* 2 | HASH JOIN | | 4 | 380| 111 (1)| 00:00:02 |
| 3 | VIEW | VW_JF_SET$35EDC1EA | 4 | 308| 107 (0)| 00:00:02 |
| 4 | UNION-ALL | | | | | |
| 5 | NESTED LOOPS | | 2 | 78| 52 (0)| 00:00:01 |
|* 6 | INDEX SKIP SCAN | I_OBJ2 | 2 | 68| 51 (0)| 00:00:01 |
| 7 | TABLE ACCESS CLUSTER | TAB$ | 1 | 5| 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
|* 9 | INDEX SKIP SCAN | I_OBJ2 | 1 | 40| 51 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | NESTED LOOPS | | 1 | 69| 4 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 62| 3 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | TABSUBPART$ | 1 | 26| 2 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 36| 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 7| 1 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | USER$ | 43 | 774| 3 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | MON_MODS_ALL$ | 68 | 1564| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_2"="M"."OBJ#")
2 - access("ITEM_1"="U"."USER#")
6 - access("O"."NAME"='TAB$')
filter("O"."NAME"='TAB$')
8 - access("O"."OBJ#"="T"."OBJ#")
9 - access("O"."NAME"='TAB$' AND "O"."TYPE#"=19)
filter("O"."NAME"='TAB$' AND "O"."TYPE#"=19)
14 - filter("O"."NAME"='TAB$')
15 - access("O"."OBJ#"="TSP"."OBJ#")
16 - access("O2"."OBJ#"="TSP"."POBJ#")
統計資訊
----------------------------------------------------------
856 recursive calls
0 db block gets
343 consistent gets
0 physical reads
0 redo size
871 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
0 rows processed[/font]看起來很類似於data block與index block不符的情況，重建一下index I_MON_MODS_ALL$_OBJ試試看
CODE:
[font=宋體]SQL> drop index I_MON_MODS_ALL$_OBJ;索引已刪除。
--c1001000.sql檔案中有這個索引的定義
SQL> create unique index i_mon_mods_all$_obj on mon_mods_all$(obj#)
2 storage (maxextents unlimited)
3 /
索引已建立。
SQL> set autot off
SQL> select table_name from dba_tab_modifications where table_name='TAB$';
TABLE_NAME
------------------------------
TAB$
SQL> select * from dba_tab_modifications where table_name='TAB$';
未選定行[/font]還是不行
CODE:
[font=宋體]SQL> select /*+ gather_plan_statistics */ * from dba_tab_modificationS where table_name='TAB$';
未選定行
SQL> select * from table(dbms_xplan.display_cursor(null,0,'ALL IOSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID agc1pu4skkrn8, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ * from dba_tab_modificationS where
table_name='TAB$'
Plan hash value: 3953559329
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 161 (100)| | 0 |00:00:00.01 | 55 |
|* 1 | HASH JOIN | | 1 | 4 | 472 | 161 (1)| 00:00:02 | 0 |00:00:00.01 | 55 |
|* 2 | HASH JOIN | | 1 | 4 | 380 | 159 (1)| 00:00:02 | 1 |00:00:00.01 | 52 |
| 3 | VIEW | VW_JF_SET$35EDC1EA | 1 | 4 | 308 | 155 (0)| 00:00:02 | 1 |00:00:00.01 | 47 |
| 4 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 47 |
| 5 | NESTED LOOPS | | 1 | 2 | 78 | 52 (0)| 00:00:01 | 1 |00:00:00.01 | 18 |
|* 6 | INDEX SKIP SCAN | I_OBJ2 | 1 | 2 | 68 | 51 (0)| 00:00:01 | 1 |00:00:00.01 | 14 |
| 7 | TABLE ACCESS CLUSTER | TAB$ | 1 | 1 | 5 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 8 | INDEX UNIQUE SCAN | I_OBJ# | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 |
|* 9 | INDEX SKIP SCAN | I_OBJ2 | 1 | 1 | 40 | 51 (0)| 00:00:01 | 0 |00:00:00.01 | 14 |
| 10 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 15 |
| 11 | NESTED LOOPS | | 1 | 1 | 69 | 52 (0)| 00:00:01 | 0 |00:00:00.01 | 15 |
| 12 | NESTED LOOPS | | 1 | 1 | 62 | 51 (0)| 00:00:01 | 0 |00:00:00.01 | 15 |
|* 13 | INDEX SKIP SCAN | I_OBJ2 | 1 | 2 | 72 | 51 (0)| 00:00:01 | 1 |00:00:00.01 | 14 |
| 14 | TABLE ACCESS BY INDEX ROWID| TABSUBPART$ | 1 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 1 |
|* 15 | INDEX UNIQUE SCAN | I_TABSUBPART$_OBJ$ | 1 | 1 | | 0 (0)| | 0 |00:00:00.01 | 1 |
|* 16 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 17 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 0 | 1 | 7 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 18 | TABLE ACCESS FULL | USER$ | 1 | 43 | 774 | 3 (0)| 00:00:01 | 46 |00:00:00.01 | 5 |
| 19 | TABLE ACCESS FULL | MON_MODS_ALL$ | 1 | 68 | 1564 | 2 (0)| 00:00:01 | 60 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$4CC7D0F8
3 - SET$35EDC1EA / VW_JF_SET$35EDC1EA@SEL$A33807FD
4 - SET$35EDC1EA
5 - SEL$61D13A11
6 - SEL$61D13A11 / O@SEL$2
7 - SEL$61D13A11 / T@SEL$2
8 - SEL$61D13A11 / T@SEL$2
9 - SEL$61BB150F / O@SEL$3
10 - SEL$5962AF70
13 - SEL$5962AF70 / O@SEL$4
14 - SEL$5962AF70 / TSP@SEL$4
15 - SEL$5962AF70 / TSP@SEL$4
16 - SEL$5962AF70 / O2@SEL$4
17 - SEL$5962AF70 / O2@SEL$4
18 - SEL$4CC7D0F8 / U@SEL$2
19 - SEL$4CC7D0F8 / M@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_2"="M"."OBJ#")
2 - access("ITEM_1"="U"."USER#")
6 - access("O"."NAME"='TAB$')
filter("O"."NAME"='TAB$')
8 - access("O"."OBJ#"="T"."OBJ#")
9 - access("O"."NAME"='TAB$' AND "O"."TYPE#"=19)
filter(("O"."NAME"='TAB$' AND "O"."TYPE#"=19))
13 - access("O"."NAME"='TAB$')
filter("O"."NAME"='TAB$')
15 - access("O"."OBJ#"="TSP"."OBJ#")
16 - access("O2"."OBJ#"="TSP"."POBJ#")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "U"."NAME"[VARCHAR2,30], "ITEM_5"[VARCHAR2,30], "ITEM_3"[VARCHAR2,30], "ITEM_4"[VARCHAR2,30],
"M"."DROP_SEGMENTS"[NUMBER,22], "M"."INSERTS"[NUMBER,22], "M"."UPDATES"[NUMBER,22], "M"."DELETES"[NUMBER,22], "M"."TIMESTAMP"[DATE,7],
"M"."FLAGS"[NUMBER,22]
2 - (#keys=1) "ITEM_2"[NUMBER,22], "ITEM_5"[VARCHAR2,30], "ITEM_3"[VARCHAR2,30], "ITEM_4"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30]
3 - "ITEM_2"[NUMBER,22], "ITEM_1"[NUMBER,22], "ITEM_3"[VARCHAR2,30], "ITEM_4"[VARCHAR2,30], "ITEM_5"[VARCHAR2,30]
4 - STRDEF[22], STRDEF[22], STRDEF[30], STRDEF[30], STRDEF[30]
5 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30]
6 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30]
7 - "T".ROWID[ROWID,10], "T"."OBJ#"[NUMBER,22]
8 - "T".ROWID[ROWID,10]
9 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],"O"."SUBNAME"[VARCHAR2,30]
10 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],"O"."SUBNAME"[VARCHAR2,30], "O2"."SUBNAME"[VARCHAR2,30]
11 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],"O"."SUBNAME"[VARCHAR2,30], "O2".ROWID[ROWID,10]
12 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],"O"."SUBNAME"[VARCHAR2,30], "TSP"."POBJ#"[NUMBER,22]
13 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],"O"."SUBNAME"[VARCHAR2,30]
14 - "TSP"."POBJ#"[NUMBER,22]
15 - "TSP".ROWID[ROWID,10]
16 - "O2".ROWID[ROWID,10]
17 - "O2"."SUBNAME"[VARCHAR2,30]
18 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
19 - "M"."OBJ#"[NUMBER,22], "M"."INSERTS"[NUMBER,22], "M"."UPDATES"[NUMBER,22], "M"."DELETES"[NUMBER,22], "M"."TIMESTAMP"[DATE,7],
"M"."FLAGS"[NUMBER,22], "M"."DROP_SEGMENTS"[NUMBER,22]
Note
-----
- cardinality feedback used for this statement
已選擇97行。[/font]可以看出，在步驟1的HASH_JOIN時，資料沒有了，並且應該是MON_MODS_ALL$的全表掃描的問題？
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19423/viewspace-663740/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 接上條，問題可能是Oracle新出的BUGOracle
- javascript,還是javascript的問題JavaScript
- Spring的問題，還是Tomcat的問題SpringTomcat
- J道大哥你好！我有個問題是Eclipse debug的問題希望給我解決下！Eclipse
- oracle驅動還是程式有問題？Oracle
- fdisk 更改分割槽容量遇到問題，還以為是oracle asm的問題OracleASM
- 通訊是個大問題，還好我們有方法。
- 這是電腦的問題還是網站有問題，登入2天都說我地址無效，傷心。網站
- oracle11.2 BUG kewastUnPackStats(): bad magic 1OracleAST
- 健康還是工作，這是個問題
- mac11.2安裝air遇到的問題MacAI
- 我的sql沒問題為什麼還是這麼慢｜MySQL加鎖規則MySql
- 轉賬問題是屬於業務問題還是屬於技術問題？
- 大衛談學習3：方法還是問題，這是個問題！
- 【調優】設計問題還是優化問題？優化
- JAVA關於判斷年份是閏年還是平年的問題Java
- 2017開發者盤點：是我在解決AI的問題，不是AI解決我的問題AI
- Fowler：敏捷還是精益？——毫無意義的問題敏捷
- 關於Oracle full outer join 的bug問題分析及處理Oracle
- SOA最核心問題：人還是流程？
- 我討厭智力題，我還是個程式設計師嗎？程式設計師
- 【基礎】EM 還是 REM？這是一個問題！REM
- 我是剛接觸java的，請教大家一問題！Java
- 我是程式猿，我還活著
- 在群裡看到一段程式碼，是記憶體模型的問題還是協程排程的問題呢？記憶體模型
- 【轉載】分享我是如何解決問題的—尊重每一個回答問題的人
- 抽卡遊戲大熱的背後，概率的大小還是主要的問題嗎？遊戲
- 透明還是不透明，是個問題 —— 《你不知道的 JavaScript》書評JavaScript
- Oracle 10.2.0.5 EM 啟動BUG問題解決Oracle
- 關於delete還是update會產生更多日誌的問題delete
- 什麼是Segmentation fault(Core Dump)? + 我遇到的例項問題Segmentation
- 我是如何搞定 NodeJS 記憶體洩漏問題的NodeJS記憶體
- 測試是浪費時間，我的程式肯定沒問題
- Oracle資料庫DDL審計觸發器觸發的bug問題Oracle資料庫觸發器
- 精通Oracle的關鍵是……（Ask Tom上最經常被問到的問題）Oracle
- 各位，問個我昨天面試的問題。面試
- 面對一個Bug，高手程式設計師是如何解決問題的？程式設計師
- 我的JiveJdon安裝問題