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