[20201106]11g查詢DBA_TAB_MODIFICATIONS無輸出.txt
[20201106]11g查詢DBA_TAB_MODIFICATIONS無輸出.txt
--//以前遇到的問題，連結：http://blog.itpub.net/267265/viewspace-2637657/==>[20190306]奇怪的查詢結果.txt
--//實際上11.2.0.4的bug。
In the DBA_TAB_MODIFICATIONS table, change statistics for all changed tables in the database are stored. The same
information can also be accessed from the ALL_TAB_MODIFICATIONS table.
Because of the bug related to UNION ALL numbered 13984324, the records are not returned from these tables. The related
bug is seen in the databases 11.2.0.4, 11.2.0.3 and 11.2.0.2. This bug has been fixed in versions 12.1.0.1 and above.
This problem can be fixed by applying a patch to the bug. Also, if we set the value of "_optimizer_join_factorization"
parameter to "false" as workaround, the problem will be solved.
--//重複測試看看：
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
SCOTT@book> select * from dba_tab_modifications where table_name = 'OBJ$';
no rows selected
SCOTT@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$ 445 4772 438 2020-11-06 10:20:52 NO 0
--//加入table_name = 'OBJ$'有輸出。
2.加入提示：
SYS@book> @ hide _optimizer_join_factorization
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
----------------------------- ------------------------------------- ------------- ------------- ------------ ----- ---------
_optimizer_join_factorization use join factorization transformation TRUE TRUE TRUE TRUE IMMEDIATE
SCOTT@book> select /*+ OPT_PARAM('optimizer_join_factorization' false) */ * from dba_tab_modifications where table_name = 'OBJ$';
no rows selected
--//寫錯，false也要加引號。
SCOTT@book> select /*+ OPT_PARAM('optimizer_join_factorization' 'false') */ * 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$ 445 4772 438 2020-11-06 10:20:52 NO 0
SCOTT@book> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID ayj9ww9s78jxr, child number 0
-------------------------------------
select /*+ OPT_PARAM('optimizer_join_factorization' 'false') */ * from
dba_tab_modifications where table_name = 'OBJ$'
Plan hash value: 1174053892
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 107 (100)| |
| 1 | VIEW | DBA_TAB_MODIFICATIONS | 5 | 660 | 107 (0)| 00:00:02 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 2 | 158 | 35 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 2 | 124 | 33 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 2 | 114 | 32 (0)| 00:00:01 |
|* 6 | INDEX SKIP SCAN | I_OBJ2 | 2 | 66 | 31 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | I_MON_MODS_ALL$_OBJ | 1 | | 0 (0)| |
| 9 | TABLE ACCESS CLUSTER | TAB$ | 1 | 5 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| |
| 11 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
| 13 | NESTED LOOPS | | 1 | 80 | 33 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 80 | 33 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 56 | 32 (0)| 00:00:01 |
|* 16 | INDEX SKIP SCAN | I_OBJ5 | 1 | 39 | 31 (0)| 00:00:01 |
| 17 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
|* 19 | INDEX UNIQUE SCAN | I_MON_MODS_ALL$_OBJ | 1 | | 0 (0)| |
| 20 | TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 1 | 24 | 1 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 2 | 182 | 39 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 2 | 182 | 39 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | 2 | 168 | 35 (0)| 00:00:01 |
| 24 | NESTED LOOPS | | 2 | 134 | 33 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | 2 | 86 | 32 (0)| 00:00:01 |
|* 26 | INDEX SKIP SCAN | I_OBJ2 | 2 | 70 | 31 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID| TABSUBPART$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | I_TABSUBPART$_OBJ$ | 1 | | 0 (0)| |
| 29 | TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 1 | 24 | 1 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | I_MON_MODS_ALL$_OBJ | 1 | | 0 (0)| |
| 31 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
|* 33 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 7 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
--//你可以發現執行計劃取消了因式分解.沒有VW_JF_SET。
--//實際上當時已經猜到因式分解有問題，沒有繼續深究。
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / DBA_TAB_MODIFICATIONS@SEL$1
2 - SET$1
3 - SEL$2
6 - SEL$2 / O@SEL$2
7 - SEL$2 / M@SEL$2
8 - SEL$2 / M@SEL$2
9 - SEL$2 / T@SEL$2
10 - SEL$2 / T@SEL$2
11 - SEL$2 / U@SEL$2
12 - SEL$2 / U@SEL$2
13 - SEL$3
16 - SEL$3 / O@SEL$3
17 - SEL$3 / U@SEL$3
18 - SEL$3 / U@SEL$3
19 - SEL$3 / M@SEL$3
20 - SEL$3 / M@SEL$3
21 - SEL$4
26 - SEL$4 / O@SEL$4
27 - SEL$4 / TSP@SEL$4
28 - SEL$4 / TSP@SEL$4
29 - SEL$4 / M@SEL$4
30 - SEL$4 / M@SEL$4
31 - SEL$4 / U@SEL$4
32 - SEL$4 / U@SEL$4
33 - SEL$4 / O2@SEL$4
34 - SEL$4 / O2@SEL$4
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('_optimizer_join_factorization' 'false')
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" "DBA_TAB_MODIFICATIONS"@"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_RS_ASC(@"SEL$4" "M"@"SEL$4" ("MON_MODS_ALL$"."OBJ#"))
INDEX(@"SEL$4" "U"@"SEL$4" "I_USER#")
INDEX(@"SEL$4" "O2"@"SEL$4" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
LEADING(@"SEL$4" "O"@"SEL$4" "TSP"@"SEL$4" "M"@"SEL$4" "U"@"SEL$4" "O2"@"SEL$4")
USE_NL(@"SEL$4" "TSP"@"SEL$4")
USE_NL(@"SEL$4" "M"@"SEL$4")
USE_NL(@"SEL$4" "U"@"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(@"SEL$3" "U"@"SEL$3" "I_USER#")
INDEX(@"SEL$3" "M"@"SEL$3" ("MON_MODS_ALL$"."OBJ#"))
LEADING(@"SEL$3" "O"@"SEL$3" "U"@"SEL$3" "M"@"SEL$3")
USE_NL(@"SEL$3" "U"@"SEL$3")
USE_NL(@"SEL$3" "M"@"SEL$3")
NLJ_BATCHING(@"SEL$3" "M"@"SEL$3")
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_RS_ASC(@"SEL$2" "M"@"SEL$2" ("MON_MODS_ALL$"."OBJ#"))
INDEX(@"SEL$2" "T"@"SEL$2" "I_OBJ#")
INDEX(@"SEL$2" "U"@"SEL$2" "I_USER#")
LEADING(@"SEL$2" "O"@"SEL$2" "M"@"SEL$2" "T"@"SEL$2" "U"@"SEL$2")
USE_NL(@"SEL$2" "M"@"SEL$2")
USE_NL(@"SEL$2" "T"@"SEL$2")
USE_NL(@"SEL$2" "U"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("O"."NAME"='OBJ$')
filter("O"."NAME"='OBJ$')
8 - access("O"."OBJ#"="M"."OBJ#")
10 - access("O"."OBJ#"="T"."OBJ#")
12 - access("O"."OWNER#"="U"."USER#")
16 - access("O"."NAME"='OBJ$' AND "O"."TYPE#"=19)
filter(("O"."NAME"='OBJ$' AND "O"."TYPE#"=19))
18 - access("O"."OWNER#"="U"."USER#")
19 - access("O"."OBJ#"="M"."OBJ#")
26 - access("O"."NAME"='OBJ$')
filter("O"."NAME"='OBJ$')
28 - access("O"."OBJ#"="TSP"."OBJ#")
30 - access("O"."OBJ#"="M"."OBJ#")
32 - access("O"."OWNER#"="U"."USER#")
33 - access("O2"."OBJ#"="TSP"."POBJ#")
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2732568/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20201106]11g修改表無需修改許可權.txt
- [20201106]奇怪的awr報表.txt
- [20241112]無法理解sqlplus的輸出.txtSQL
- [20181112]11g 日誌傳輸壓縮模式.txt模式
- [20180926]查詢相似索引.txt索引
- [20190524]sqlplus 與輸出&.txtSQL
- MongoDB查詢如何只輸出部分欄位內容MongoDB
- [20200325]慎用標量子查詢.txt
- [20190524]淺談模糊查詢.txt
- [20201106]瞭解oracle資料庫啟動時間.txtOracle資料庫
- [20201218]dbms_output.put_line無法輸出前面的空格.txt
- Oracle 11G 閃回技術 閃回版本查詢和閃回事務查詢Oracle
- Java 中如何使用 SQL 查詢 TXTJavaSQL
- [20190306]奇怪的查詢結果.txt
- [20220822]奇怪的ashtop輸出.txt
- [20211111]奇怪的ashtop輸出.txt
- [20210924]awk奇怪的輸出.txt
- [20191119]探究ipcs命令輸出.txt
- [20200317]NULL與排序輸出.txtNull排序
- [20201201]約束大寫與查詢.txt
- [20210418]查詢v$檢視問題.txt
- [20210902]cut使用輸出問題.txt
- [20200306]expand格式化輸出.txt
- [20190720]sqlplus 與輸出& 2.txtSQL
- [20210301]延遲顯示輸出.txt
- [20210419]避免冗餘的輸出.txt
- 如何跟蹤11g vip及SCN IP的輸出
- [20220308]查詢x$ksmmem遇到的疑問.txt
- [20211220]關於標量子查詢問題.txt
- [20190502]查詢條件不等於測試.txt
- [20231103]輸出倒數第2行.txt
- [20210924]awk奇怪的輸出2.txt
- [20191106]善用column格式化輸出.txt
- [20191119]探究ipcs命令輸出2.txt
- [20180312]iostat顯示輸出問題.txtiOS
- [20201130]11g or_expand提示.txt
- [20190306]11g health monitor.txt
- [20190219]那個更快(11g).txt