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

时间:2022-02-02 20:10:42

[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验证问题.