[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驗證問題.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2637657/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql查詢結果多列拼接查詢MySql
- navicat 匯出查詢結果
- mysql匯出查詢結果MySql
- 使用查詢結果更新表的方法
- oracle 查詢結果的各種格式Oracle
- xsl中，對xml文件查詢的結果再次查詢XML
- Mybatis模糊查詢結果為空MyBatis
- ps aux查詢結果解釋UX
- MySQL查詢結果匯出方式總結MySql
- Mybatis 查詢語句結果集總結MyBatis
- [Mysql 查詢語句]——對查詢結果進一步的操作MySql
- 等於NULL的查詢條件導致查詢結果不正確Null
- PLSQL Developer 複製查詢結果 卡頓SQLDeveloper
- ORDER對查詢結果進行排序排序
- oracle查詢結果外面新增引號Oracle
- Oracle查詢結果 儲存為XMLOracleXML
- MySQL 查詢結果取交集的實現方法MySql
- 為 MySQL 的查詢結果新增排名欄位MySql
- UPDATE查詢結果範圍內的資料
- 備忘：laravel 對查詢結果集可以迴圈where查詢Laravel
- ES查詢之查詢屬性過濾、結果高亮顯示
- PostgreSQL函式：返回表查詢結果集SQL函式
- MySQL 將查詢結果匯出到檔案MySql
- DBeaver 匯出多個查詢結果集
- 使用ROWNUM將導致查詢結果集的固化
- sqlserver查詢結果中新增自動編號SQLServer
- SQL 把查詢結果當作"表"來使用SQL
- SQL Server對組合查詢結果排序方法SQLServer排序
- 利用資料泵匯出查詢結果（二）
- 利用資料泵匯出查詢結果（一）
- hibernate查詢結果Logic:iterate問題
- 動態sql查詢結果多行的處理情況SQL
- mysql中的多行查詢結果合併成一個MySql
- 關於分頁查詢結果的快取問題快取
- SQL查詢結果集對注入的影響及利用SQL
- Laravel 關聯查詢限制條數和分組查詢顯示為零的結果Laravel
- 在HQL select查詢語句中自定義查詢結果返回的資料型別資料型別
- 極兔快遞怎麼查詢物流資訊 支援匯出查詢結果嗎？