[20190306]奇怪的查询结果.txt

来源：这里教程网时间：2026-03-03 13:03:12 作者：

[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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 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 ,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 ,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 -------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ @--------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | @Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ @--------- | 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推