[20201106]11g查询DBA_TAB_MODIFICATIONS无输出.txt

来源：这里教程网时间：2026-03-03 16:16:33 作者：

[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