[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
推荐阅读
-
Sql_从查询的结果集中分组后取最后有效的数据成新的结果集小记(待优化)
-
在查询结果中添加一列表示记录的行数的sql语句
-
ThinkPHP采用GET方式获取中文参数查询无结果的解决方法
-
mysql导出查询结果到csv的实现方法
-
Oracle列转行函数Listagg()查询结果的去重（去除重复值）的方法详解
-
Oracle将查询的结果放入一张自定义表中并再查询数据
-
php查询mysql数据库并将结果保存到数组的方法
-
PHP使用mysql_fetch_object从查询结果中获取对象集的方法
-
mysql的一个坑。修改或者删除的时候不能直接调用子查询的结果集
-
详解 Mysql查询结果顺序按 in() 中ID 的顺序排列