[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2732568/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
