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

[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 |     &nb

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐