[20190306]奇怪的查詢結果.txt

--//連結http://www.itpub.net/thread-2108588-1-1.html提到一個非常古怪的問題,我自己重複測試看看:

1.環境:

SCOTT@book> @ ver1

PORT_STRING VERSION BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> select * from dba_tab_modifications where table_name = 'OBJ$';

no rows selected

SYS@book> select * from dba_tab_modifications where table_name = 'OBJ$' and table_owner='SYS';

TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS

----------- ---------- -------------- ----------------- ------- ------- ------- ------------------- --- -------------

SYS OBJ$ 22 65 20 2019-03-05 22:24:14 NO 0

--//加入條件table_owner='SYS';反而查詢到結果.前面加入提示rule,也可以查詢到.明顯出了問題.

SYS@book> select /*+ rule */ * from dba_tab_modifications where table_name = 'OBJ$';

TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS

----------- ---------- -------------- ----------------- ------- ------- ------- ------------------- --- -------------

SYS OBJ$ 22 65 20 2019-03-05 22:24:14 NO 0

SYS@book> ANALYZE TABLE sys.obj$ VALIDATE STRUCTURE CASCADE;

Table analyzed.

--//sys.obj$表以及索引都沒有問題.dba_tab_modifications裡面涉及的表我都分析校驗一次,沒有問題.

2.分析看看:

SYS@book> alter session set statistics_level=all ;

Session altered.

SYS@book> select * from dba_tab_modifications where table_name = 'OBJ$' ;

no rows selected

SYS@book> @ dpc '' outline

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID cb8hkhvh62mpu, child number 0

-------------------------------------

select * from dba_tab_modifications where table_name = 'OBJ$'

Plan hash value: 4248094259

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 104 (100)| | 0 |00:00:00.01 | 121 | | | |

|* 1 | HASH JOIN | | 1 | 5 | 595 | 104 (0)| 00:00:02 | 0 |00:00:00.01 | 121 | 1079K| 1079K| 408K (0)|

|* 2 | HASH JOIN | | 1 | 5 | 470 | 102 (0)| 00:00:02 | 1 |00:00:00.01 | 118 | 1483K| 1483K| 432K (0)|

| 3 | VIEW | VW_JF_SET$35EDC1EA | 1 | 5 | 385 | 99 (0)| 00:00:02 | 1 |00:00:00.01 | 112 | | | |

| 4 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 112 | | | |

| 5 | @NESTED LOOPS | | 1 | 2 | 76 | 32 (0)| 00:00:01 | 1 |00:00:00.01 | 39 | | | |

|* 6 | @ INDEX SKIP SCAN | I_OBJ2 | 1 | 2 | 66 | 31 (0)| 00:00:01 | 1 |00:00:00.01 | 36 | | | |

| 7 | @ TABLE ACCESS CLUSTER | TAB$ | 1 | 1 | 5 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |

|* 8 | @ INDEX UNIQUE SCAN | I_OBJ# | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 | | | |

|* 9 | @INDEX SKIP SCAN | I_OBJ5 | 1 | 1 | 39 | 31 (0)| 00:00:01 | 0 |00:00:00.01 | 36 | | | |

| 10 | @NESTED LOOPS | | 1 | 2 | 100 | 36 (0)| 00:00:01 | 0 |00:00:00.01 | 37 | | | |

| 11 | @ NESTED LOOPS | | 1 | 2 | 100 | 36 (0)| 00:00:01 | 0 |00:00:00.01 | 37 | | | |

| 12 | @ NESTED LOOPS | | 1 | 2 | 86 | 32 (0)| 00:00:01 | 0 |00:00:00.01 | 37 | | | |

|* 13 | @ INDEX SKIP SCAN | I_OBJ2 | 1 | 2 | 70 | 31 (0)| 00:00:01 | 1 |00:00:00.01 | 36 | | | |

| 14 | @ TABLE ACCESS BY INDEX ROWID| TABSUBPART$ | 1 | 1 | 8 | 1 (0)| 00:00:01 | 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 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |

| 18 | TABLE ACCESS FULL | USER$ | 1 | 99 | 1683 | 3 (0)| 00:00:01 | 99 |00:00:00.01 | 6 | | | |

| 19 | TABLE ACCESS FULL | MON_MODS_ALL$ | 1 | 124 | 3100 | 2 (0)| 00:00:01 | 130 |00:00:00.01 | 3 | | | |

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--//說明:執行計劃裡面的@是我人為加入的.VW_JF_SET表示因式分解.

--//我的感覺是執行計劃把sys.mon_mods_all$ m,sys.user$ u拿出來最後連線.

--//看id=2,A-rows=1,也可以看出有結果的來之union all的第1部分(檢視定義看下面).

--//也就是最後與MON_MODS_ALL$連線時,沒有記錄輸出.

--//看id=1的連線條件是 1 - access("ITEM_2"="M"."OBJ#").

SYS@book> select * from MON_MODS_ALL$ m where M.OBJ#=18;

OBJ# INSERTS UPDATES DELETES TIMESTAMP FLAGS DROP_SEGMENTS

---------- ---------- ---------- ---------- ------------------- ---------- -------------

18 22 65 20 2019-03-05 22:24:14 0 0

--//為什麼最後做hash join(id=1)後,實際行數是0,不理解.

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

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$5962AF70")

OUTLINE_LEAF(@"SEL$61BB150F")

OUTLINE_LEAF(@"SEL$61D13A11")

OUTLINE_LEAF(@"SET$35EDC1EA")

OUTLINE_LEAF(@"SEL$4CC7D0F8")

MERGE(@"SEL$58D8A5DB")

OUTLINE(@"SEL$420E0780")

OUTLINE(@"SEL$73E92AB2")

OUTLINE(@"SEL$A33807FD")

OUTLINE(@"SET$E5581402")

FACTORIZE_JOIN(@"SET$1"("M"@"SEL$2" "M"@"SEL$3" "M"@"SEL$4") ("U"@"SEL$2" "U"@"SEL$3" "U"@"SEL$4"))

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$58D8A5DB")

MERGE(@"SEL$38196F71")

OUTLINE(@"SEL$4")

OUTLINE(@"SEL$3")

OUTLINE(@"SEL$2")

OUTLINE(@"SET$1")

OUTLINE(@"SEL$F9F648E4")

OUTLINE(@"SEL$38196F71")

NO_ACCESS(@"SEL$4CC7D0F8" "VW_JF_SET$35EDC1EA"@"SEL$A33807FD")

FULL(@"SEL$4CC7D0F8" "U"@"SEL$2")

FULL(@"SEL$4CC7D0F8" "M"@"SEL$2")

LEADING(@"SEL$4CC7D0F8" "VW_JF_SET$35EDC1EA"@"SEL$A33807FD" "U"@"SEL$2" "M"@"SEL$2")

USE_HASH(@"SEL$4CC7D0F8" "U"@"SEL$2")

USE_HASH(@"SEL$4CC7D0F8" "M"@"SEL$2")

INDEX_SS(@"SEL$61D13A11" "O"@"SEL$2" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME" "OBJ$"."TYPE#"

"OBJ$"."SPARE3" "OBJ$"."OBJ#"))

INDEX(@"SEL$61D13A11" "T"@"SEL$2" "I_OBJ#")

LEADING(@"SEL$61D13A11" "O"@"SEL$2" "T"@"SEL$2")

USE_NL(@"SEL$61D13A11" "T"@"SEL$2")

INDEX_SS(@"SEL$61BB150F" "O"@"SEL$3" ("OBJ$"."SPARE3" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."TYPE#" "OBJ$"."OWNER#" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME"

"OBJ$"."SUBNAME" "OBJ$"."OBJ#"))

INDEX_SS(@"SEL$5962AF70" "O"@"SEL$4" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME" "OBJ$"."TYPE#"

"OBJ$"."SPARE3" "OBJ$"."OBJ#"))

INDEX_RS_ASC(@"SEL$5962AF70" "TSP"@"SEL$4" ("TABSUBPART$"."OBJ#"))

INDEX(@"SEL$5962AF70" "O2"@"SEL$4" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))

LEADING(@"SEL$5962AF70" "O"@"SEL$4" "TSP"@"SEL$4" "O2"@"SEL$4")

USE_NL(@"SEL$5962AF70" "TSP"@"SEL$4")

USE_NL(@"SEL$5962AF70" "O2"@"SEL$4")

NLJ_BATCHING(@"SEL$5962AF70" "O2"@"SEL$4")

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("ITEM_2"="M"."OBJ#")

2 - access("ITEM_1"="U"."USER#")

6 - access("O"."NAME"='OBJ$')

filter("O"."NAME"='OBJ$')

8 - access("O"."OBJ#"="T"."OBJ#")

9 - access("O"."NAME"='OBJ$' AND "O"."TYPE#"=19)

filter(("O"."NAME"='OBJ$' AND "O"."TYPE#"=19))

13 - access("O"."NAME"='OBJ$')

filter("O"."NAME"='OBJ$')

15 - access("O"."OBJ#"="TSP"."OBJ#")

16 - access("O2"."OBJ#"="TSP"."POBJ#")

119 rows selected.

--//如果檢視SYS.DBA_TAB_MODIFICATIONS檢視定義:

CREATE OR REPLACE FORCE VIEW SYS.DBA_TAB_MODIFICATIONS

(

TABLE_OWNER

,TABLE_NAME

,PARTITION_NAME

,SUBPARTITION_NAME

,INSERTS

,UPDATES

,DELETES

,TIMESTAMP

,TRUNCATED

,DROP_SEGMENTS

)

AS

SELECT u.name

,o.name

,NULL

,m.inserts

,m.updates

,m.deletes

,m.timestamp

,DECODE (BITAND (m.flags, 1), 1, 'YES', 'NO')

,m.drop_segments

FROM sys.mon_mods_all$ m

,sys.obj$ o

,sys.tab$ t

,sys.user$ u

WHERE o.obj# = m.obj# AND o.obj# = t.obj# AND o.owner# = u.user#

UNION ALL

SELECT u.name

,o.name

,o.subname

,NULL

,m.inserts

,m.updates

,m.deletes

,m.timestamp

,DECODE (BITAND (m.flags, 1), 1, 'YES', 'NO')

,m.drop_segments

FROM sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u

WHERE o.owner# = u.user# AND o.obj# = m.obj# AND o.type# = 19

UNION ALL

SELECT u.name

,o.name

,o2.subname

,o.subname

,m.inserts

,m.updates

,m.deletes

,m.timestamp

,DECODE (BITAND (m.flags, 1), 1, 'YES', 'NO')

,m.drop_segments

FROM sys.mon_mods_all$ m

,sys.obj$ o

,sys.tabsubpart$ tsp

,sys.obj$ o2

,sys.user$ u

WHERE o.obj# = m.obj#

AND o.owner# = u.user#

AND o.obj# = tsp.obj#

AND o2.obj# = tsp.pobj#;

--//分3部分,大概猜測第1部分關於普通表.第2部分是o.type# = 19??.第3部分是有分割槽表的情況.

--//單獨建立檢視SYS.DBA_TAB_MODIFICATIONSx:

CREATE VIEW SYS.DBA_TAB_MODIFICATIONSx

(

TABLE_OWNER

,TABLE_NAME

,PARTITION_NAME

,SUBPARTITION_NAME

,INSERTS

,UPDATES

,DELETES

,TIMESTAMP

,TRUNCATED

,DROP_SEGMENTS

)

AS

SELECT u.name

,o.name

,NULL

,m.inserts

,m.updates

,m.deletes

,m.timestamp

,DECODE (BITAND (m.flags, 1), 1, 'YES', 'NO')

,m.drop_segments

FROM sys.mon_mods_all$ m

,sys.obj$ o

,sys.tab$ t

,sys.user$ u

WHERE o.obj# = m.obj# AND o.obj# = t.obj# AND o.owner# = u.user#;

SYS@book> select * from dba_tab_modificationsx where table_name = 'OBJ$' ;

TABLE_OWNER TABLE_NAME P S INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS

------------------------------ ---------- - - ---------- ---------- ---------- ------------------- --- -------------

SYS OBJ$ 22 65 20 2019-03-05 22:24:14 NO 0

--//明顯顯示資訊來自SYS.DBA_TAB_MODIFICATIONS的union all第1部分.

--//使用提示保證執行計劃一致.

SYS@book> select /*+ full("M"@"SEL$2") full("U"@"SEL$2") LEADING(@"SEL$F5BB74E1" "O"@"SEL$2" "T"@"SEL$2" "U"@"SEL$2" "M"@"SEL$2" ) */ * from dba_tab_modificationsx where table_name = 'OBJ$';

TABLE_OWNER TABLE_NAME P S INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS

------------------------------ ---------- - - ---------- ---------- ---------- ------------------- --- -------------

SYS OBJ$ 22 65 20 2019-03-05 22:24:14 NO 0

--//嗯,有結果輸出,為什麼?

SYS@book> @ dpc '' outline

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID dp6dk67ugzkct, child number 0

-------------------------------------

select /*+ full("M"@"SEL$2") full("U"@"SEL$2") LEADING(@"SEL$F5BB74E1"

"O"@"SEL$2" "T"@"SEL$2" "U"@"SEL$2" "M"@"SEL$2" ) */ * from

dba_tab_modificationsx where table_name = 'OBJ$'

Plan hash value: 1913090444

--------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 37 (100)| | 1 |00:00:00.01 | 49 | | | |

|* 1 | HASH JOIN | | 1 | 2 | 160 | 37 (0)| 00:00:01 | 1 |00:00:00.01 | 49 | 1557K| 1557K| 673K (0)|

|* 2 | HASH JOIN | | 1 | 2 | 110 | 35 (0)| 00:00:01 | 1 |00:00:00.01 | 45 | 1645K| 1645K| 737K (0)|

| 3 | NESTED LOOPS | | 1 | 2 | 76 | 32 (0)| 00:00:01 | 1 |00:00:00.01 | 39 | | | |

|* 4 | INDEX SKIP SCAN | I_OBJ2 | 1 | 2 | 66 | 31 (0)| 00:00:01 | 1 |00:00:00.01 | 36 | | | |

| 5 | TABLE ACCESS CLUSTER| TAB$ | 1 | 1 | 5 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |

|* 6 | INDEX UNIQUE SCAN | I_OBJ# | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 | | | |

| 7 | TABLE ACCESS FULL | USER$ | 1 | 99 | 1683 | 3 (0)| 00:00:01 | 99 |00:00:00.01 | 6 | | | |

| 8 | TABLE ACCESS FULL | MON_MODS_ALL$ | 1 | 124 | 3100 | 2 (0)| 00:00:01 | 130 |00:00:00.01 | 4 | | | |

--------------------------------------------------------------------------------------------------------------------------------------------------------------

--//執行計劃與上面一致.

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$F5BB74E1

4 - SEL$F5BB74E1 / O@SEL$2

5 - SEL$F5BB74E1 / T@SEL$2

6 - SEL$F5BB74E1 / T@SEL$2

7 - SEL$F5BB74E1 / U@SEL$2

8 - SEL$F5BB74E1 / M@SEL$2

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$F5BB74E1")

MERGE(@"SEL$2")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$2")

INDEX_SS(@"SEL$F5BB74E1" "O"@"SEL$2" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME"

"OBJ$"."TYPE#" "OBJ$"."SPARE3" "OBJ$"."OBJ#"))

INDEX(@"SEL$F5BB74E1" "T"@"SEL$2" "I_OBJ#")

FULL(@"SEL$F5BB74E1" "U"@"SEL$2")

FULL(@"SEL$F5BB74E1" "M"@"SEL$2")

LEADING(@"SEL$F5BB74E1" "O"@"SEL$2" "T"@"SEL$2" "U"@"SEL$2" "M"@"SEL$2")

USE_NL(@"SEL$F5BB74E1" "T"@"SEL$2")

USE_HASH(@"SEL$F5BB74E1" "U"@"SEL$2")

USE_HASH(@"SEL$F5BB74E1" "M"@"SEL$2")

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("O"."OBJ#"="M"."OBJ#")

2 - access("O"."OWNER#"="U"."USER#")

4 - access("O"."NAME"='OBJ$')

filter("O"."NAME"='OBJ$')

6 - access("O"."OBJ#"="T"."OBJ#")

--//我僅僅能估計Oracle 連線因式分解有bug,在連線時報錯.

3.做一個10053跟蹤分析看看:

SYS@book> @ 10053x cb8hkhvh62mpu 0

PL/SQL procedure successfully completed.

Final query after transformations:******* UNPARSED QUERY IS *******

--//如下:我做了格式化處理:

SELECT "U"."NAME" "TABLE_OWNER"

,"VW_JF_SET$35EDC1EA"."ITEM_3" "TABLE_NAME"

,"VW_JF_SET$35EDC1EA"."ITEM_4" "PARTITION_NAME"

,"VW_JF_SET$35EDC1EA"."ITEM_5" "SUBPARTITION_NAME"

,"M"."INSERTS" "INSERTS"

,"M"."UPDATES" "UPDATES"

,"M"."DELETES" "DELETES"

,"M"."TIMESTAMP" "TIMESTAMP"

,DECODE (BITAND ("M"."FLAGS", 1), 1, 'YES', 'NO') "TRUNCATED"

,"M"."DROP_SEGMENTS" "DROP_SEGMENTS"

FROM ( (SELECT "O"."OWNER#" "ITEM_1"

,"O"."OBJ#" "ITEM_2"

,"O"."NAME" "ITEM_3"

,NULL "ITEM_4"

,NULL "ITEM_5"

FROM "SYS"."TAB$" "T", "SYS"."OBJ$" "O"

WHERE "O"."NAME" = 'OBJ$' AND "O"."OBJ#" = "T"."OBJ#")

UNION ALL

( (SELECT "O"."OWNER#" "ITEM_2"

,"O"."OBJ#" "ITEM_1"

,"O"."NAME" "ITEM_3"

,"O"."SUBNAME" "ITEM_4"

,NULL "ITEM_5"

FROM "SYS"."OBJ$" "O"

WHERE "O"."NAME" = 'OBJ$' AND "O"."TYPE#" = 19)

UNION ALL

(SELECT "O"."OWNER#" "ITEM_1"

,"O"."OBJ#" "ITEM_2"

,"O"."NAME" "ITEM_3"

,"O2"."SUBNAME" "ITEM_4"

,"O"."SUBNAME" "ITEM_5"

FROM "SYS"."OBJ$" "O"

,"SYS"."OBJ$" "O2"

,"SYS"."TABSUBPART$" "TSP"

WHERE "O"."NAME" = 'OBJ$'

AND "O2"."OBJ#" = "TSP"."POBJ#"

AND "O"."OBJ#" = "TSP"."OBJ#"))) "VW_JF_SET$35EDC1EA"

,"SYS"."MON_MODS_ALL$" "M"

,"SYS"."USER$" "U"

WHERE "VW_JF_SET$35EDC1EA"."ITEM_2" = "M"."OBJ#"

AND "VW_JF_SET$35EDC1EA"."ITEM_1" = "U"."USER#";

--//我直接執行OK.

TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS

----------- ---------- -------------- ----------------- ------- ---------- ---------- ------------------- --- -------------

SYS OBJ$ 22 65 20 2019-03-05 22:24:14 NO 0

--//執行計劃如下:

Plan hash value: 1913316274

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ @---------

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ @---------

| 0 | SELECT STATEMENT | | 1 | | | 104 (100)| | 1 |00:00:00.01 | 122 | | | | @ 121 |

|* 1 | HASH JOIN | | 1 | 5 | 595 | 104 (0)| 00:00:02 | 1 |00:00:00.01 | 122 | 1421K| 1421K| 652K (0)| @ 121 |

|* 2 | HASH JOIN | | 1 | 5 | 470 | 102 (0)| 00:00:02 | 1 |00:00:00.01 | 118 | 1483K| 1483K| 740K (0)| @ 118 |

| 3 | VIEW | | 1 | 5 | 385 | 99 (0)| 00:00:02 | 1 |00:00:00.01 | 112 | | | | @ 112 |

| 4 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 112 | | | | @ 112 |

| 5 | NESTED LOOPS | | 1 | 2 | 76 | 32 (0)| 00:00:01 | 1 |00:00:00.01 | 39 | | | | @ 39 |

|* 6 | INDEX SKIP SCAN | I_OBJ2 | 1 | 2 | 66 | 31 (0)| 00:00:01 | 1 |00:00:00.01 | 36 | | | | @ 36 |

| 7 | TABLE ACCESS CLUSTER | TAB$ | 1 | 1 | 5 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | | @ 3 |

|* 8 | INDEX UNIQUE SCAN | I_OBJ# | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 | | | | @ 2 |

|* 9 | INDEX SKIP SCAN | I_OBJ5 | 1 | 1 | 39 | 31 (0)| 00:00:01 | 0 |00:00:00.01 | 36 | | | | @ 36 |

| 10 | NESTED LOOPS | | 1 | 2 | 100 | 36 (0)| 00:00:01 | 0 |00:00:00.01 | 37 | | | | @ 37 |

| 11 | NESTED LOOPS | | 1 | 2 | 100 | 36 (0)| 00:00:01 | 0 |00:00:00.01 | 37 | | | | @ 37 |

| 12 | NESTED LOOPS | | 1 | 2 | 86 | 32 (0)| 00:00:01 | 0 |00:00:00.01 | 37 | | | | @ 37 |

|* 13 | INDEX SKIP SCAN | I_OBJ2 | 1 | 2 | 70 | 31 (0)| 00:00:01 | 1 |00:00:00.01 | 36 | | | | @ 36 |

| 14 | TABLE ACCESS BY INDEX ROWID| TABSUBPART$ | 1 | 1 | 8 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 | | | | @ 1 |

|* 15 | INDEX UNIQUE SCAN | I_TABSUBPART$_OBJ$ | 1 | 1 | | 0 (0)| | 0 |00:00:00.01 | 1 | | | | @ 1 |

|* 16 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | | @ 0 |

| 17 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 0 | 1 | 7 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | | @ 0 |

| 18 | TABLE ACCESS FULL | USER$ | 1 | 99 | 1683 | 3 (0)| 00:00:01 | 99 |00:00:00.01 | 6 | | | | @ 6 |

| 19 | TABLE ACCESS FULL | MON_MODS_ALL$ | 1 | 124 | 3100 | 2 (0)| 00:00:01 | 130 |00:00:00.01 | 4 | | | | @ 3 |

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ @---------

--//執行計劃與上面完成一致.

--//奇怪的地方id=19,buffers=4,前面是3?

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1

3 - SET$1 / VW_JF_SET$35EDC1EA@SEL$1

4 - SET$1

5 - SEL$2

6 - SEL$2 / O@SEL$2

7 - SEL$2 / T@SEL$2

8 - SEL$2 / T@SEL$2

9 - SEL$3 / O@SEL$3

10 - SEL$4

13 - SEL$4 / O@SEL$4

14 - SEL$4 / TSP@SEL$4

15 - SEL$4 / TSP@SEL$4

16 - SEL$4 / O2@SEL$4

17 - SEL$4 / O2@SEL$4

18 - SEL$1 / U@SEL$1

19 - SEL$1 / M@SEL$1

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$2")

OUTLINE_LEAF(@"SEL$3")

OUTLINE_LEAF(@"SEL$4")

OUTLINE_LEAF(@"SET$1")

OUTLINE_LEAF(@"SEL$1")

NO_ACCESS(@"SEL$1" "VW_JF_SET$35EDC1EA"@"SEL$1")

FULL(@"SEL$1" "U"@"SEL$1")

FULL(@"SEL$1" "M"@"SEL$1")

LEADING(@"SEL$1" "VW_JF_SET$35EDC1EA"@"SEL$1" "U"@"SEL$1" "M"@"SEL$1")

USE_HASH(@"SEL$1" "U"@"SEL$1")

USE_HASH(@"SEL$1" "M"@"SEL$1")

INDEX_SS(@"SEL$4" "O"@"SEL$4" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME" "OBJ$"."TYPE#"

"OBJ$"."SPARE3" "OBJ$"."OBJ#"))

INDEX_RS_ASC(@"SEL$4" "TSP"@"SEL$4" ("TABSUBPART$"."OBJ#"))

INDEX(@"SEL$4" "O2"@"SEL$4" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))

LEADING(@"SEL$4" "O"@"SEL$4" "TSP"@"SEL$4" "O2"@"SEL$4")

USE_NL(@"SEL$4" "TSP"@"SEL$4")

USE_NL(@"SEL$4" "O2"@"SEL$4")

NLJ_BATCHING(@"SEL$4" "O2"@"SEL$4")

INDEX_SS(@"SEL$3" "O"@"SEL$3" ("OBJ$"."SPARE3" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."TYPE#" "OBJ$"."OWNER#" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME"

"OBJ$"."SUBNAME" "OBJ$"."OBJ#"))

INDEX_SS(@"SEL$2" "O"@"SEL$2" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME" "OBJ$"."TYPE#"

"OBJ$"."SPARE3" "OBJ$"."OBJ#"))

INDEX(@"SEL$2" "T"@"SEL$2" "I_OBJ#")

LEADING(@"SEL$2" "O"@"SEL$2" "T"@"SEL$2")

USE_NL(@"SEL$2" "T"@"SEL$2")

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("VW_JF_SET$35EDC1EA"."ITEM_2"="M"."OBJ#")

2 - access("VW_JF_SET$35EDC1EA"."ITEM_1"="U"."USER#")

6 - access("O"."NAME"='OBJ$')

filter("O"."NAME"='OBJ$')

8 - access("O"."OBJ#"="T"."OBJ#")

9 - access("O"."NAME"='OBJ$' AND "O"."TYPE#"=19)

filter(("O"."NAME"='OBJ$' AND "O"."TYPE#"=19))

13 - access("O"."NAME"='OBJ$')

filter("O"."NAME"='OBJ$')

15 - access("O"."OBJ#"="TSP"."OBJ#")

16 - access("O2"."OBJ#"="TSP"."POBJ#")

4.貼一個有結果的執行計劃:

Plan hash value: 712189870

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 130 (100)| | 1 |00:00:00.01 | 148 | | | |

|* 1 | HASH JOIN | | 1 | 5 | 720 | 130 (0)| 00:00:02 | 1 |00:00:00.01 | 148 | 2211K| 2211K| 444K (0)|

| 2 | VIEW | VW_JF_SET$52E8A812 | 1 | 5 | 640 | 126 (0)| 00:00:02 | 1 |00:00:00.01 | 139 | | | |

| 3 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 139 | | | |

| 4 | NESTED LOOPS | | 1 | 2 | 130 | 41 (0)| 00:00:01 | 1 |00:00:00.01 | 50 | | | |

| 5 | NESTED LOOPS | | 1 | 2 | 130 | 41 (0)| 00:00:01 | 1 |00:00:00.01 | 49 | | | |

| 6 | NESTED LOOPS | | 1 | 2 | 76 | 40 (0)| 00:00:01 | 1 |00:00:00.01 | 47 | | | |

|* 7 | INDEX SKIP SCAN | I_OBJ2 | 1 | 2 | 66 | 39 (0)| 00:00:01 | 1 |00:00:00.01 | 44 | 1025K| 1025K| |

| 8 | TABLE ACCESS CLUSTER | TAB$ | 1 | 1 | 5 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |

|* 9 | INDEX UNIQUE SCAN | I_OBJ# | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 | 1025K| 1025K| |

|* 10 | INDEX UNIQUE SCAN | I_MON_MODS_ALL$_OBJ | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 | 1025K| 1025K| |

| 11 | TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 1 | 1 | 27 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |

| 12 | NESTED LOOPS | | 1 | 1 | 66 | 40 (0)| 00:00:01 | 0 |00:00:00.01 | 44 | | | |

| 13 | NESTED LOOPS | | 1 | 1 | 66 | 40 (0)| 00:00:01 | 0 |00:00:00.01 | 44 | | | |

|* 14 | INDEX SKIP SCAN | I_OBJ5 | 1 | 1 | 39 | 39 (0)| 00:00:01 | 0 |00:00:00.01 | 44 | 1025K| 1025K| |

|* 15 | INDEX UNIQUE SCAN | I_MON_MODS_ALL$_OBJ | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | 0 | 1025K| 1025K| |

| 16 | TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 0 | 1 | 27 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |

| 17 | NESTED LOOPS | | 1 | 2 | 154 | 45 (0)| 00:00:01 | 0 |00:00:00.01 | 45 | | | |

| 18 | NESTED LOOPS | | 1 | 2 | 154 | 45 (0)| 00:00:01 | 0 |00:00:00.01 | 45 | | | |

| 19 | NESTED LOOPS | | 1 | 2 | 140 | 41 (0)| 00:00:01 | 0 |00:00:00.01 | 45 | | | |

| 20 | NESTED LOOPS | | 1 | 2 | 86 | 40 (0)| 00:00:01 | 0 |00:00:00.01 | 45 | | | |

|* 21 | INDEX SKIP SCAN | I_OBJ2 | 1 | 2 | 70 | 39 (0)| 00:00:01 | 1 |00:00:00.01 | 44 | 1025K| 1025K| |

| 22 | TABLE ACCESS BY INDEX ROWID| TABSUBPART$ | 1 | 1 | 8 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 | | | |

|* 23 | INDEX UNIQUE SCAN | I_TABSUBPART$_OBJ$ | 1 | 1 | | 0 (0)| | 0 |00:00:00.01 | 1 | 1025K| 1025K| |

| 24 | TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 0 | 1 | 27 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |

|* 25 | INDEX UNIQUE SCAN | I_MON_MODS_ALL$_OBJ | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | 0 | 1025K| 1025K| |

|* 26 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |

| 27 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 0 | 1 | 7 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |

| 28 | TABLE ACCESS STORAGE FULL | USER$ | 1 | 121 | 1936 | 4 (0)| 00:00:01 | 127 |00:00:00.01 | 9 | 1025K| 1025K| |

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--//可以看出拆分與前面的不一樣.

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$5F81645F

2 - SET$52E8A812 / VW_JF_SET$52E8A812@SEL$5DEDEEEF

3 - SET$52E8A812

4 - SEL$33370C19

7 - SEL$33370C19 / O@SEL$2

8 - SEL$33370C19 / T@SEL$2

9 - SEL$33370C19 / T@SEL$2

10 - SEL$33370C19 / M@SEL$2

11 - SEL$33370C19 / M@SEL$2

12 - SEL$18F9F943

14 - SEL$18F9F943 / O@SEL$3

15 - SEL$18F9F943 / M@SEL$3

16 - SEL$18F9F943 / M@SEL$3

17 - SEL$570A9A3E

21 - SEL$570A9A3E / O@SEL$4

22 - SEL$570A9A3E / TSP@SEL$4

23 - SEL$570A9A3E / TSP@SEL$4

24 - SEL$570A9A3E / M@SEL$4

25 - SEL$570A9A3E / M@SEL$4

26 - SEL$570A9A3E / O2@SEL$4

27 - SEL$570A9A3E / O2@SEL$4

28 - SEL$5F81645F / U@SEL$2

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$570A9A3E")

OUTLINE_LEAF(@"SEL$18F9F943")

OUTLINE_LEAF(@"SEL$33370C19")

OUTLINE_LEAF(@"SET$52E8A812")

OUTLINE_LEAF(@"SEL$5F81645F")

MERGE(@"SEL$EF00E7F8")

OUTLINE(@"SEL$D84A1568")

OUTLINE(@"SEL$6AD81F3B")

OUTLINE(@"SEL$5DEDEEEF")

OUTLINE(@"SET$20D5428A")

FACTORIZE_JOIN(@"SET$1"("U"@"SEL$2" "U"@"SEL$3" "U"@"SEL$4"))

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$EF00E7F8")

MERGE(@"SEL$7557EA6E")

OUTLINE(@"SEL$4")

OUTLINE(@"SEL$3")

OUTLINE(@"SEL$2")

OUTLINE(@"SET$1")

OUTLINE(@"SEL$7EA21606")

OUTLINE(@"SEL$7557EA6E")

NO_ACCESS(@"SEL$5F81645F" "VW_JF_SET$52E8A812"@"SEL$5DEDEEEF")

FULL(@"SEL$5F81645F" "U"@"SEL$2")

LEADING(@"SEL$5F81645F" "VW_JF_SET$52E8A812"@"SEL$5DEDEEEF" "U"@"SEL$2")

USE_HASH(@"SEL$5F81645F" "U"@"SEL$2")

INDEX_SS(@"SEL$33370C19" "O"@"SEL$2" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME" "OBJ$"."TYPE#"

"OBJ$"."SPARE3" "OBJ$"."OBJ#"))

INDEX(@"SEL$33370C19" "T"@"SEL$2" "I_OBJ#")

INDEX(@"SEL$33370C19" "M"@"SEL$2" ("MON_MODS_ALL$"."OBJ#"))

LEADING(@"SEL$33370C19" "O"@"SEL$2" "T"@"SEL$2" "M"@"SEL$2")

USE_NL(@"SEL$33370C19" "T"@"SEL$2")

USE_NL(@"SEL$33370C19" "M"@"SEL$2")

NLJ_BATCHING(@"SEL$33370C19" "M"@"SEL$2")

INDEX_SS(@"SEL$18F9F943" "O"@"SEL$3" ("OBJ$"."SPARE3" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."TYPE#" "OBJ$"."OWNER#" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME"

"OBJ$"."SUBNAME" "OBJ$"."OBJ#"))

INDEX(@"SEL$18F9F943" "M"@"SEL$3" ("MON_MODS_ALL$"."OBJ#"))

LEADING(@"SEL$18F9F943" "O"@"SEL$3" "M"@"SEL$3")

USE_NL(@"SEL$18F9F943" "M"@"SEL$3")

NLJ_BATCHING(@"SEL$18F9F943" "M"@"SEL$3")

INDEX_SS(@"SEL$570A9A3E" "O"@"SEL$4" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME" "OBJ$"."TYPE#"

"OBJ$"."SPARE3" "OBJ$"."OBJ#"))

INDEX_RS_ASC(@"SEL$570A9A3E" "TSP"@"SEL$4" ("TABSUBPART$"."OBJ#"))

INDEX_RS_ASC(@"SEL$570A9A3E" "M"@"SEL$4" ("MON_MODS_ALL$"."OBJ#"))

INDEX(@"SEL$570A9A3E" "O2"@"SEL$4" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))

LEADING(@"SEL$570A9A3E" "O"@"SEL$4" "TSP"@"SEL$4" "M"@"SEL$4" "O2"@"SEL$4")

USE_NL(@"SEL$570A9A3E" "TSP"@"SEL$4")

USE_NL(@"SEL$570A9A3E" "M"@"SEL$4")

USE_NL(@"SEL$570A9A3E" "O2"@"SEL$4")

NLJ_BATCHING(@"SEL$570A9A3E" "O2"@"SEL$4")

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("ITEM_1"="U"."USER#")

7 - access("O"."NAME"='OBJ$')

filter("O"."NAME"='OBJ$')

9 - access("O"."OBJ#"="T"."OBJ#")

10 - access("O"."OBJ#"="M"."OBJ#")

14 - access("O"."NAME"='OBJ$' AND "O"."TYPE#"=19)

filter(("O"."NAME"='OBJ$' AND "O"."TYPE#"=19))

15 - access("O"."OBJ#"="M"."OBJ#")

21 - access("O"."NAME"='OBJ$')

filter("O"."NAME"='OBJ$')

23 - access("O"."OBJ#"="TSP"."OBJ#")

25 - access("O"."OBJ#"="M"."OBJ#")

26 - access("O2"."OBJ#"="TSP"."POBJ#")

143 rows selected.

--//附上10053x.sql指令碼:

$ cat 10053x.sql

execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1');

總結:

--//能力有限,無法定位問題!!不知道那位知道,寫的有點長,大家可以使用前面的outline驗證問題.

[20190306]奇怪的查詢結果.txt

相關文章