记一次增加分区需要两个小时的优化案例

最新推荐文章于 2022-03-13 19:26:41 发布

coak086064

最新推荐文章于 2022-03-13 19:26:41 发布

阅读量234

点赞数

最近在给生产上一些表增加分区时，遇到了一个很奇怪的问题：
一共5个表，三个表很快，二个表慢，其中有一个表增加一个分区需要3分钟左右，另一个表在交易库上需要16分钟，而在查询库（通过goldengate同步）同步DDL过来，则需要两个小时！慢就慢了，关键的一点是，期间其他应用没法查询这个表，会报“ORA-04021: 等待对象锁超时 ”错误,影响业务了，必须找出解决办法；
首先是怀疑GG的DDL复制会不会带来一些特别的影响，因为DDL复制很多公司是不开的，据说问题多；于是过滤了这个表的DDL复制，改成分别在两个库增加分区，但是，时间还是一样的：交易库16分钟左右，查询库2个小时，很稳定啊；
既然不是GG的原因，那么问题在哪里呢？套用现在一种DBA的分类，我就是“百度DBA”（因为Google用不了啊），但是，百度一番也没有发现类似的案例，有truncate慢的案例但套不上，难道别人都没有遇到就我遇到了？人品就是这么“SO BAD”？
BS了一番百度后，我还是决定做一个“ThinkDBA”，Ycc陷入了深深的思索：这个表有什么特点？很大，一天3千万，一共60亿，可是加分区跟记录数有关吗？又不需要维护索引，而且其他三个表也在10-20个亿，却秒级完成，这说不过去；还有一个特点，这个表的分区字段类型”char(8)",实际保存的是日期转化的字符，如“20171001”，这的源于糟糕的设计，是历史问题，但是，另一个表也是同样的设计，却是非常快的，这个理由被推翻；
换一个角度，增加分区会做一些什么操作呢？具体的不知道，但总体上只是改一下数据字典吧，那我收集一下数据字典的统计信息会不会有改变呢？于是在查询库做了一个收集系统、固定对象、字典统计信息的操作，然后在第二天，惊奇地发现，查询库上竟然一秒不到就完成了增加（任务放在晚上调度），心中大喜，以为找到了解决之道，立即在交易库也做了同样的操作，然后第二天的结果是，交易库依旧需要16分钟多，奇迹没有发生，同时，查询库也出现了反复，又有需要两个小时的情况了，从现象上初步总结，貌似周一到周五是慢的，而周末则是快的；
趁着周末，一次性给查询库加了一年的分区，缓解了一个库的”分区荒“问题，但交易库的，依然没有思路；
因为国庆迁移项目加班，在项目快完成之际，有了一点空闲，加上现在正是业务的低峰，决定深入跟一下这个问题，于是祭出了非常规武器：10046，加了10046之后，发现这个SQL执行更慢了，原来16分钟，现在运行了20多分钟还没完成，慢有什么影响，只好暂停了；不过跟踪文件已经有内容了；
用TKprof格式化trace文件之后，我不断地翻看着里面的SQL，都是很小的很快的递归SQL，正当在我以为没什么希望的时候，突然一个特别耗时的SQL出现在我的眼帘：
SELECT KEY.KEY_NAME INDEX_NAME, KEY.COLUMN_NAME, KEY.DESCEND
FROM (SELECT C.CONSTRAINT_NAME KEY_NAME,
C.COLUMN_NAME COLUMN_NAME,
C.POSITION POSITION,
'ASC' DESCEND
FROM DBA_CONS_COLUMNS C
WHERE C.OWNER = :B2
AND C.TABLE_NAME = :B1
AND C.CONSTRAINT_NAME IN
(SELECT CON1.NAME
FROM SYS.USER$ USER1,
SYS.USER$ USER2,
SYS.CDEF$ CDEF,
SYS.CON$ CON1,
SYS.CON$ CON2,
SYS.OBJ$ OBJ1,
SYS.OBJ$ OBJ2
WHERE USER1.NAME = :B2
AND OBJ1.NAME = :B1
AND CDEF.TYPE# = 3
AND BITAND(CDEF.DEFER, 36) = 4
AND CDEF.ENABLED IS NOT NULL
AND CON2.OWNER# = USER2.USER#(+)
AND CDEF.ROBJ# = OBJ2.OBJ#(+)
AND CDEF.RCON# = CON2.CON#(+)
AND OBJ1.OWNER# = USER1.USER#
AND CDEF.CON# = CON1.CON#
AND CDEF.OBJ# = OBJ1.OBJ#)
AND EXISTS (SELECT 'x'
FROM DBA_TAB_COLUMNS T
WHERE T.OWNER = C.OWNER
AND T.TABLE_NAME = C.TABLE_NAME
AND T.COLUMN_NAME = C.COLUMN_NAME)
UNION
SELECT I.INDEX_NAME KEY_NAME,
C.COLUMN_NAME COLUMN_NAME,
C.COLUMN_POSITION POSITION,
C.DESCEND DESCEND
FROM DBA_INDEXES I, DBA_IND_COLUMNS C
WHERE I.TABLE_OWNER = :B2
AND I.TABLE_NAME = :B1
AND I.UNIQUENESS = 'UNIQUE'
AND I.OWNER = C.INDEX_OWNER
AND I.INDEX_NAME = C.INDEX_NAME
AND :B1 = C.TABLE_NAME
AND :B2 = C.TABLE_OWNER
AND I.INDEX_NAME IN
(SELECT INDEX_NAME
FROM DBA_INDEXES
WHERE TABLE_OWNER = :B2
AND TABLE_NAME = :B1
AND (VISIBILITY != 'INVISIBLE' OR 'FALSE' = 'TRUE')
AND UNIQUENESS = 'UNIQUE')
AND I.INDEX_NAME NOT IN
(SELECT C.CONSTRAINT_NAME
FROM DBA_CONS_COLUMNS C
WHERE C.OWNER = :B2
AND C.TABLE_NAME = :B1
AND C.CONSTRAINT_NAME IN
(SELECT C1.NAME
FROM SYS.USER$ U1,
SYS.USER$ U2,
SYS.CDEF$ D,
SYS.CON$ C1,
SYS.CON$ C2,
SYS.OBJ$ O1,
SYS.OBJ$ O2
WHERE U1.NAME = :B2
AND O1.NAME = :B1
AND D.TYPE# IN (2, 3)
AND 1 =
DECODE(1, 1, DECODE(D.ENABLED, NULL, 0, 1), 1)
AND (D.ENABLED IS NULL OR D.DEFER IS NULL OR
BITAND(D.DEFER, 32) IN (0, 32))
AND C2.OWNER# = U2.USER#(+)
AND D.ROBJ# = O2.OBJ#(+)
AND D.RCON# = C2.CON#(+)
AND O1.OWNER# = U1.USER#
AND D.CON# = C1.CON#
AND D.OBJ# = O1.OBJ#)
AND EXISTS
(SELECT 'X'
FROM DBA_TAB_COLUMNS T
WHERE T.OWNER = C.OWNER
AND T.TABLE_NAME = C.TABLE_NAME
AND T.COLUMN_NAME = C.COLUMN_NAME))
AND EXISTS (SELECT 'x'
FROM DBA_TAB_COLUMNS T
WHERE T.OWNER = :B2
AND T.TABLE_NAME = :B1
AND T.COLUMN_NAME = C.COLUMN_NAME)) KEY
ORDER BY KEY.KEY_NAME, KEY.POSITION

找到问题SQL那就好办了，SQL的执行路径虽然漫长，但紧要处也就几步，一步走错了，整个计划就变了,我不清楚为什么搞出了这么多记录：
SQL Plan Monitoring Details (Plan Hash Value=1366467067)
==============================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
==============================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +1013 | 1 | 2 | | | | | |
| 1 | SORT ORDER BY | | 2 | 125 | 1 | +1013 | 1 | 2 | | | 2048 | | |
| 2 | VIEW | | 2 | 124 | 1 | +1013 | 1 | 2 | | | | | |
| 3 | SORT UNIQUE | | 2 | 124 | 404 | +610 | 1 | 2 | | | 2048 | | |
| 4 | UNION-ALL | | | | 203 | +610 | 1 | 2 | | | | | |
| 5 | FILTER | | | | | | 1 | | | | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 33 | | | 1 | | | | | | |
| 7 | NESTED LOOPS OUTER | | 1 | 31 | | | 1 | | | | | | |
| 8 | NESTED LOOPS OUTER | | 1 | 30 | | | 1 | | | | | | |
| 9 | NESTED LOOPS OUTER | | 1 | 26 | | | 1 | | | | | | |
| 10 | NESTED LOOPS OUTER | | 1 | 24 | | | 1 | | | | | | |
| 11 | NESTED LOOPS | | 1 | 23 | | | 1 | | | | | | |
| 12 | NESTED LOOPS | | 1 | 22 | | | 1 | | | | | | |
| 13 | NESTED LOOPS | | 1 | 21 | | | 1 | | | | | | |
| 14 | NESTED LOOPS | | 1 | 19 | | | 1 | | | | | | |
| 15 | NESTED LOOPS | | 1 | 18 | | | 1 | | | | | | |
| 16 | NESTED LOOPS OUTER | | 1 | 15 | | | 1 | | | | | | |
| 17 | NESTED LOOPS | | 1 | 14 | | | 1 | | | | | | |
| 18 | NESTED LOOPS | | 1 | 13 | | | 1 | | | | | | |
| 19 | NESTED LOOPS | | 1 | 11 | | | 1 | | | | | | |
| 20 | NESTED LOOPS | | 1 | 10 | | | 1 | | | | | | |
| 21 | NESTED LOOPS | | 1 | 9 | | | 1 | | | | | | |
| 22 | NESTED LOOPS OUTER | | 1 | 8 | | | 1 | | | | | | |
| 23 | NESTED LOOPS OUTER | | 1 | 7 | | | 1 | | | | | | |
| 24 | NESTED LOOPS | | 1 | 6 | 1 | +10 | 1 | 0 | | | | | |
| 25 | NESTED LOOPS | | 1 | 5 | 1 | +10 | 1 | 662 | | | | | |
| 26 | NESTED LOOPS | | 1 | 3 | 1 | +10 | 1 | 1 | | | | | |
| 27 | NESTED LOOPS | | 1 | 2 | 1 | +10 | 1 | 1 | | | | | |
| 28 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 | +10 | 1 | 1 | | | | | |
| 29 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1 | +10 | 1 | 1 | | | | | |
| 30 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 | +10 | 1 | 1 | | | | | |
| 31 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1 | +10 | 1 | 1 | | | | | |
| 32 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 | +10 | 1 | 1 | | | | | |
| 33 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1 | +10 | 1 | 1 | | | | | |
| 34 | INDEX RANGE SCAN | I_OBJ2 | 1 | 2 | 1 | +10 | 1 | 662 | | | | | |
| 35 | TABLE ACCESS CLUSTER | CDEF$ | 1 | 1 | 1 | +10 | 662 | 0 | | | | | |
| 36 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | 1 | +10 | 662 | 1 | | | | | |
| 37 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 1 | | | | | | | | | |
| 38 | INDEX UNIQUE SCAN | I_CON2 | 1 | | | | | | | | | | |
| 39 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 40 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 1 | | | | | | | | | |
| 41 | INDEX UNIQUE SCAN | I_CON2 | 1 | | | | | | | | | | |
| 42 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 1 | | | | | | | | | |
| 43 | INDEX UNIQUE SCAN | I_CON1 | 1 | | | | | | | | | | |
| 44 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 1 | | | | | | | | | |
| 45 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | | | | | | | | | |
| 46 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 2 | | | | | | | | | |
| 47 | INDEX RANGE SCAN | I_CCOL1 | 1 | 1 | | | | | | | | | |
| 48 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 1 | | | | | | | | | |
| 49 | INDEX UNIQUE SCAN | I_COL3 | 1 | | | | | | | | | | |
| 50 | TABLE ACCESS CLUSTER | ATTRCOL$ | 1 | 1 | | | | | | | | | |
| 51 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 3 | | | | | | | | | |
| 52 | INDEX RANGE SCAN | I_OBJ1 | 1 | 2 | | | | | | | | | |
| 53 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 54 | INDEX RANGE SCAN | I_OBJ5 | 1 | 2 | | | | | | | | | |
| 55 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 56 | TABLE ACCESS CLUSTER | COL$ | 1 | 1 | | | | | | | | | |
| 57 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | | | | | | | | |
| 58 | TABLE ACCESS CLUSTER | COLTYPE$ | 1 | 1 | | | | | | | | | |
| 59 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 1 | 2 | | | | | | | | | |
| 60 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 4 | | | | | | | | | |
| 61 | INDEX RANGE SCAN | I_OBJ3 | 38 | 1 | | | | | | | | | |
| 62 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 63 | INDEX RANGE SCAN | I_OBJ1 | 1 | 2 | | | | | | | | | |
| 64 | TABLE ACCESS CLUSTER | TAB$ | 1 | 2 | | | | | | | | | |
| 65 | INDEX UNIQUE SCAN | I_OBJ# | 1 | 1 | | | | | | | | | |
| 66 | NESTED LOOPS | | 1 | 3 | | | | | | | | | |
| 67 | INDEX SKIP SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 68 | INDEX RANGE SCAN | I_OBJ4 | 1 | 2 | | | | | | | | | |
| 69 | NESTED LOOPS | | 1 | 3 | | | | | | | | | |
| 70 | INDEX SKIP SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 71 | INDEX RANGE SCAN | I_OBJ4 | 1 | 2 | | | | | | | | | |
| 72 | NESTED LOOPS OUTER | | 1 | 3 | | | | | | | | | |
| 73 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 2 | | | | | | | | | |
| 74 | INDEX UNIQUE SCAN | I_COL3 | 1 | 1 | | | | | | | | | |
| 75 | TABLE ACCESS CLUSTER | ATTRCOL$ | 1 | 1 | | | | | | | | | |
| 76 | FILTER | | | | 203 | +610 | 1 | 2 | | | | | |
| 77 | NESTED LOOPS | | 1 | 56 | 803 | +10 | 1 | 2 | | | | | |
| 78 | NESTED LOOPS | | 1 | 56 | 803 | +10 | 1 | 5 | | | | | |
| 79 | NESTED LOOPS OUTER | | 1 | 53 | 803 | +10 | 1 | 5 | | | | | |
| 80 | NESTED LOOPS OUTER | | 1 | 51 | 803 | +10 | 1 | 5 | | | | | |
| 81 | NESTED LOOPS OUTER | | 1 | 50 | 803 | +10 | 1 | 5 | | | | | |
| 82 | NESTED LOOPS OUTER | | 1 | 48 | 803 | +10 | 1 | 5 | | | | | |
| 83 | NESTED LOOPS OUTER | | 1 | 47 | 803 | +10 | 1 | 5 | | | | | |
| 84 | NESTED LOOPS | | 1 | 45 | 803 | +10 | 1 | 5 | | | | | |
| 85 | NESTED LOOPS | | 1 | 44 | 803 | +10 | 1 | 5 | | | | | |
| 86 | NESTED LOOPS OUTER | | 1 | 41 | 803 | +10 | 1 | 5 | | | | | |
| 87 | NESTED LOOPS OUTER | | 1 | 40 | 803 | +10 | 1 | 5 | | | | | |
| 88 | NESTED LOOPS OUTER | | 1 | 39 | 803 | +10 | 1 | 5 | | | | | |
| 89 | NESTED LOOPS OUTER | | 1 | 38 | 803 | +10 | 1 | 5 | | | | | |
| 90 | NESTED LOOPS OUTER | | 1 | 34 | 803 | +10 | 1 | 5 | | | | | |
| 91 | NESTED LOOPS | | 1 | 33 | 1004 | +10 | 1 | 5 | | | | | |
| 92 | NESTED LOOPS | | 1 | 32 | 1004 | +10 | 1 | 3310 | | | | | |
| 93 | NESTED LOOPS | | 1 | 31 | 1004 | +10 | 1 | 3310 | | | | | |
| 94 | NESTED LOOPS OUTER | | 1 | 30 | 1004 | +10 | 1 | 3310 | | | | | |
| 95 | NESTED LOOPS | | 1 | 29 | 1004 | +10 | 1 | 3310 | | | | | |
| 96 | NESTED LOOPS OUTER | | 1 | 28 | 1004 | +10 | 1 | 2M | | | | | |
| 97 | NESTED LOOPS | | 1 | 27 | 1004 | +10 | 1 | 2M | | | | 0.20 | Cpu (2) |
| 98 | NESTED LOOPS | | 1 | 26 | 1004 | +10 | 1 | 1G | | | | 2.38 | Cpu (24) |
| 99 | NESTED LOOPS | | 1 | 24 | 1004 | +10 | 1 | 2M | | | | | |
| 100 | NESTED LOOPS | | 1 | 22 | 1004 | +10 | 1 | 3310 | | | | | |
| 101 | NESTED LOOPS | | 1 | 20 | 803 | +10 | 1 | 5 | | | | | |
| 102 | NESTED LOOPS | | 1 | 3 | 1 | +10 | 1 | 1 | | | | | |
| 103 | NESTED LOOPS | | 1 | 2 | 1 | +10 | 1 | 1 | | | | | |
| 104 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 | +10 | 1 | 1 | | | | | |
| 105 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1003 | +10 | 1 | 1 | | | | | |
| 106 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 | +10 | 1 | 1 | | | | | |
| 107 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1003 | +10 | 1 | 1 | | | | | |
| 108 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 | +10 | 1 | 1 | | | | | |
| 109 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1003 | +10 | 1 | 1 | | | | | |
| 110 | VIEW | DBA_IND_COLUMNS | 1 | 17 | 803 | +10 | 1 | 5 | | | | | |
| 111 | NESTED LOOPS OUTER | | 1 | 17 | 803 | +10 | 1 | 5 | | | | | |
| 112 | NESTED LOOPS | | 1 | 16 | 803 | +10 | 1 | 5 | | | | | |
| 113 | NESTED LOOPS | | 1 | 15 | 803 | +10 | 1 | 5 | | | | | |
| 114 | NESTED LOOPS | | 1 | 14 | 803 | +10 | 1 | 5 | | | | | |
| 115 | NESTED LOOPS | | 3 | 13 | 803 | +10 | 1 | 5 | | | | | |
| 116 | NESTED LOOPS | | 3 | 4 | 803 | +10 | 1 | 5 | | | | | |
| 117 | NESTED LOOPS | | 1 | 3 | 1 | +10 | 1 | 662 | | | | | |
| 118 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 | +10 | 1 | 1 | | | | | |
| 119 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1003 | +10 | 1 | 1 | | | | | |
| 120 | INDEX RANGE SCAN | I_OBJ2 | 1 | 2 | 1003 | +10 | 1 | 662 | | | | | |
| 121 | TABLE ACCESS CLUSTER | ICOL$ | 3 | 1 | 803 | +10 | 662 | 5 | | | | | |
| 122 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1003 | +10 | 662 | 1 | | | | | |
| 123 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 3 | 803 | +10 | 5 | 5 | | | | | |
| 124 | INDEX RANGE SCAN | I_OBJ1 | 1 | 2 | 1003 | +10 | 5 | 5 | 2 | 16384 | | | |
| 125 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 1 | 803 | +10 | 5 | 5 | | | | | |
| 126 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1003 | +10 | 5 | 5 | | | | | |
| 127 | TABLE ACCESS CLUSTER | USER$ | 1 | 1 | 803 | +10 | 5 | 5 | | | | | |
| 128 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1003 | +10 | 5 | 5 | | | | | |
| 129 | TABLE ACCESS CLUSTER | COL$ | 1 | 1 | 1003 | +10 | 5 | 5 | | | | | |
| 130 | TABLE ACCESS CLUSTER | ATTRCOL$ | 1 | 1 | | | 5 | | | | | | |
| 131 | INDEX RANGE SCAN | I_OBJ5 | 1 | 2 | 1004 | +10 | 5 | 3310 | | | | | |
| 132 | INDEX RANGE SCAN | I_OBJ2 | 1 | 2 | 1004 | +10 | 3310 | 2M | | | | | |
| 133 | INDEX RANGE SCAN | I_OBJ2 | 1 | 2 | 1013 | +1 | 2M | 1G | | | | 18.75 | Cpu (189) |
| 134 | TABLE ACCESS CLUSTER | IND$ | 1 | 1 | 1006 | +8 | 1G | 2M | | | | 20.54 | Cpu (207) |
| 135 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1012 | +2 | 1G | 2M | | | | 56.85 | Cpu (573) |
| 136 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | 1004 | +10 | 2M | 2M | | | | 0.60 | Cpu (6) |
| 137 | INDEX UNIQUE SCAN | I_TS# | 1 | | 1004 | +10 | 2M | 2M | | | | 0.20 | Cpu (2) |
| 138 | TABLE ACCESS CLUSTER | IND$ | 1 | 1 | 1004 | +10 | 2M | 3310 | | | | | |
| 139 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1004 | +10 | 2M | 3310 | | | | 0.50 | Cpu (5) |
| 140 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | 1004 | +10 | 3310 | 3310 | | | | | |
| 141 | INDEX UNIQUE SCAN | I_TS# | 1 | | 1004 | +10 | 3310 | 3310 | | | | | |
| 142 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | 1004 | +10 | 3310 | 3310 | | | | | |
| 143 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1004 | +10 | 3310 | 3310 | | | | | |
| 144 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1004 | +10 | 3310 | 3310 | | | | | |
| 145 | TABLE ACCESS CLUSTER | COL$ | 1 | 1 | 803 | +10 | 3310 | 5 | | | | | |
| 146 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 803 | +10 | 3310 | 5 | | | | | |
| 147 | TABLE ACCESS CLUSTER | COLTYPE$ | 1 | 1 | | | 5 | | | | | | |
| 148 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 4 | | | 5 | | | | | | |
| 149 | INDEX RANGE SCAN | I_OBJ3 | 38 | 1 | | | 5 | | | | | | |
| 150 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | 5 | | | | | | |
| 151 | TABLE ACCESS CLUSTER | SEG$ | 1 | 1 | | | 5 | | | | | | |
| 152 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | | | 5 | | | | | | |
| 153 | TABLE ACCESS CLUSTER | SEG$ | 1 | 1 | | | 5 | | | | | | |
| 154 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | | | 5 | | | | | | |
| 155 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 3 | 803 | +10 | 5 | 5 | | | | | |
| 156 | INDEX RANGE SCAN | I_OBJ1 | 1 | 2 | 803 | +10 | 5 | 5 | | | | | |
| 157 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | 803 | +10 | 5 | 5 | | | | | |
| 158 | INDEX RANGE SCAN | I_OBJ1 | 1 | 2 | | | 5 | | | | | | |
| 159 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | 5 | | | | | | |
| 160 | INDEX RANGE SCAN | I_OBJ1 | 1 | 2 | | | 5 | | | | | | |
| 161 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | 5 | | | | | | |
| 162 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 1 | 2 | 803 | +10 | 5 | 5 | | | | | |
| 163 | INDEX RANGE SCAN | I_OBJ1 | 1 | 2 | 803 | +10 | 5 | 5 | | | | | |
| 164 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 3 | 203 | +610 | 5 | 2 | | | | | |
| 165 | FILTER | | | | | | 1 | | | | | | |
| 166 | NESTED LOOPS | | 1 | 33 | | | 1 | | | | | | |
| 167 | FILTER | | | | | | 1 | | | | | | |
| 168 | NESTED LOOPS OUTER | | 1 | 32 | | | 1 | | | | | | |
| 169 | NESTED LOOPS | | 1 | 31 | | | 1 | | | | | | |
| 170 | NESTED LOOPS | | 1 | 30 | | | 1 | | | | | | |
| 171 | NESTED LOOPS | | 1 | 27 | | | 1 | | | | | | |
| 172 | NESTED LOOPS | | 1 | 25 | | | 1 | | | | | | |
| 173 | NESTED LOOPS OUTER | | 1 | 24 | | | 1 | | | | | | |
| 174 | NESTED LOOPS OUTER | | 1 | 23 | | | 1 | | | | | | |
| 175 | NESTED LOOPS OUTER | | 1 | 21 | | | 1 | | | | | | |
| 176 | NESTED LOOPS OUTER | | 1 | 17 | | | 1 | | | | | | |
| 177 | NESTED LOOPS | | 1 | 16 | | | 1 | | | | | | |
| 178 | NESTED LOOPS | | 1 | 15 | | | 1 | | | | | | |
| 179 | NESTED LOOPS OUTER | | 1 | 13 | | | 1 | | | | | | |
| 180 | NESTED LOOPS OUTER | | 1 | 12 | | | 1 | | | | | | |
| 181 | NESTED LOOPS OUTER | | 1 | 10 | | | 1 | | | | | | |
| 182 | NESTED LOOPS | | 1 | 9 | | | 1 | | | | | | |
| 183 | NESTED LOOPS | | 1 | 8 | | | 1 | | | | | | |
| 184 | NESTED LOOPS | | 1 | 7 | | | 1 | | | | | | |
| 185 | NESTED LOOPS | | 1 | 5 | | | 1 | | | | | | |
| 186 | NESTED LOOPS | | 1 | 4 | 1 | +610 | 1 | 0 | | | | | |
| 187 | NESTED LOOPS | | 1 | 3 | 1 | +610 | 1 | 1 | | | | | |
| 188 | NESTED LOOPS | | 1 | 2 | 1 | +610 | 1 | 1 | | | | | |
| 189 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 | +610 | 1 | 1 | | | | | |
| 190 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1 | +610 | 1 | 1 | | | | | |
| 191 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 | +610 | 1 | 1 | | | | | |
| 192 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1 | +610 | 1 | 1 | | | | | |
| 193 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 | +610 | 1 | 1 | | | | | |
| 194 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1 | +610 | 1 | 1 | | | | | |
| 195 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 1 | | | 1 | | | | | | |
| 196 | INDEX UNIQUE SCAN | I_CON1 | 1 | | | | 1 | | | | | | |
| 197 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 1 | | | | | | | | | |
| 198 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | | | | | | | | | |
| 199 | INDEX RANGE SCAN | I_OBJ2 | 1 | 2 | | | | | | | | | |
| 200 | TABLE ACCESS CLUSTER | CDEF$ | 1 | 1 | | | | | | | | | |
| 201 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | | | | | | | | | |
| 202 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 1 | | | | | | | | | |
| 203 | INDEX UNIQUE SCAN | I_CON2 | 1 | | | | | | | | | | |
| 204 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 1 | | | | | | | | | |
| 205 | INDEX UNIQUE SCAN | I_CON2 | 1 | | | | | | | | | | |
| 206 | INDEX RANGE SCAN | I_OBJ1 | 1 | 2 | | | | | | | | | |
| 207 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 208 | INDEX RANGE SCAN | I_OBJ5 | 1 | 2 | | | | | | | | | |
| 209 | TABLE ACCESS CLUSTER | COL$ | 1 | 1 | | | | | | | | | |
| 210 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | | | | | | | | |
| 211 | TABLE ACCESS CLUSTER | COLTYPE$ | 1 | 1 | | | | | | | | | |
| 212 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 4 | | | | | | | | | |
| 213 | INDEX RANGE SCAN | I_OBJ3 | 38 | 1 | | | | | | | | | |
| 214 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 1 | 2 | | | | | | | | | |
| 215 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 216 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 217 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 2 | | | | | | | | | |
| 218 | INDEX RANGE SCAN | I_CCOL1 | 1 | 1 | | | | | | | | | |
| 219 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 3 | | | | | | | | | |
| 220 | INDEX RANGE SCAN | I_OBJ1 | 1 | 2 | | | | | | | | | |
| 221 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 1 | | | | | | | | | |
| 222 | INDEX UNIQUE SCAN | I_COL3 | 1 | | | | | | | | | | |
| 223 | TABLE ACCESS CLUSTER | ATTRCOL$ | 1 | 1 | | | | | | | | | |
| 224 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 225 | TABLE ACCESS CLUSTER | TAB$ | 1 | 2 | | | | | | | | | |
| 226 | INDEX UNIQUE SCAN | I_OBJ# | 1 | 1 | | | | | | | | | |
| 227 | NESTED LOOPS | | 1 | 3 | | | | | | | | | |
| 228 | INDEX SKIP SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 229 | INDEX RANGE SCAN | I_OBJ4 | 1 | 2 | | | | | | | | | |
| 230 | NESTED LOOPS | | 1 | 3 | | | | | | | | | |
| 231 | INDEX SKIP SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 232 | INDEX RANGE SCAN | I_OBJ4 | 1 | 2 | | | | | | | | | |
| 233 | TABLE ACCESS CLUSTER | TAB$ | 1 | 2 | 1 | +610 | 1 | 0 | | | | | |
| 234 | INDEX UNIQUE SCAN | I_OBJ# | 1 | 1 | 1 | +610 | 1 | 1 | | | | | |
| 235 | NESTED LOOPS | | 1 | 3 | | | | | | | | | |
| 236 | INDEX SKIP SCAN | I_USER2 | 1 | 1 | | | | | | | | | |
| 237 | INDEX RANGE SCAN | I_OBJ4 | 1 | 2 | | | | | | | | | |
==============================================================================================================================================================================================================

大致的定位，是这个子查询被合并了：
SELECT INDEX_NAME
FROM DBA_INDEXES
WHERE TABLE_OWNER = 'DM_ACT'
AND TABLE_NAME = 'ACCOUNT_DAILY'
AND (VISIBILITY != 'INVISIBLE' OR 'FALSE' = 'TRUE')
AND UNIQUENESS = 'UNIQUE'
所以，我的解决方案是固化执行计划，让这个视图”no_merge",固定后，效果果然是杠杠的：
不仅这个表是秒级，另一个要3分钟的表也是秒级；

增加分区终于不再成功一种负担，yeah! 这真是中秋节最好的礼物了^_^

20180901更新：

近日又遇上了一个问题，在改索引的并行度时（其实任意DDL都这样），久久无法完成，但也没有阻塞，等待事件是Gc current grant，表面看起来正常，但就是一动不动；经10046跟踪之后，发现又是这个SQL惹的祸，问题应该是一样的问题，视图合并的原因，或者union改成union all也可，但我用no_merge固化，竟然没生效，采用autotune的sqlprofile也没有生效，奇了怪了，沉下心来，好好地重新分析与固化了一次（用了rule),这一次生效了，再运行ddl就闪电一般了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13365316/viewspace-2145708/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13365316/viewspace-2145708/