OS: HP
DB: 10204升级至10205
症状：
升级完成后，客户开始抱怨数据库跑的很慢，以前几个小时能跑完的job现在需要10几个小时，一天能搞定的现在则需要好几天。
经过好几次沟通后，通过AWR确认了一个bad sql，据说升级前需要6个小时，现在都运行70个小时了还没结束。
Sql如下
SELECT /*+ parallel_index(t,"INDEX",8) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t,"INDEX") */
COUNT(*)
AS nrw,
COUNT (DISTINCT sys_op_lbid (4756197, 'L', t.ROWID)) AS nlb,
COUNT (
DISTINCT HEXTORAW( sys_op_descend ("SYS_NC00089$")
|| sys_op_descend ("SYS_NC00090$")
|| sys_op_descend ("SYS_NC00091$"))
)
AS ndk,
sys_op_countchg (SUBSTRB (t.ROWID, 1, 15), 1) AS clf
FROM "JUSTIN"."TABLE" SAMPLE BLOCK (15.0000000000) t
WHERE "SYS_NC00089$" IS NOT NULL
OR "SYS_NC00090$" IS NOT NULL
OR "SYS_NC00091$" IS NOT NULL
其执行计划如下
----------------------------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
----------------------------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 42M | | | |
| 1 | SORT GROUP BY | | 1 | 30 | | | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID | TABLE| 45M | 1349M | 42M | | ROW LOCATION| ROW LOCATION|
| 3 | INDEX FULL SCAN | INDEX | 45M | | 2052K | | | |
----------------------------------------------------------------------+-----------------------------------+---------------+
刚开始比较好奇怎么没有用到parallel，index_ffs也没有起作用，而且还用到了回表操作。
开了SR，最后MOS确认此为10205 bug 6980350,而此sql为DBMS_STATS.GATHER_TABLE_STATS('JUSTIN', 'TABLE', CASCADE=>TRUE, ESTIMATE_PERCENT=>15, DEGREE=>8)的衍生物，不能人工干预。
第三条跟目前情形比较接近，该索引为function-based且用到了sample;
Description
This problem is introduced in 10.2.0.5
This Fix addresses multiple issues :
1 - ANALYZE statement choosing inappropriate fast full scan plans.
2 - When Gathering with sample, a fast full scan for a bitmap index
may drop the sample clause, making it slow.
3- DBMS_STATS compute an inaccurate number of leaf block statistic on
function based indexes when sampling is used, potentially generating
suboptimal execution plans.
4 - When Gathering Stats on an index the query might do an unnecessary "TABLE ACCESS BY" .
Workaround
specify estimate_percent => 100
HOOKS PACKAGE:DBMS_STATS SQL:ANALYZE LIKELYAFFECTS XAFFECTS_10.2.0.5 XAFFECTS_V10020005 AFFECTS=10.2.0.5 XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_CBO TAG_DBMSPKG TAG_FUNCINDEX TAG_QPERF TAG_RA205 TAG_REGRESSION CBO DBMSPKG FUNCINDEX QPERF RA205 REGRESSION FIXED_11.2.0.1
Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.
最后下载one-off patch 6980350，应用之后该sql执行计划正常如下
Id
Operation
Name
Rows
Bytes
Cost
TQ
IN-OUT
PQ Distrib
0
SELECT STATEMENT
21688
1
SORT GROUP BY
1
53
2
PX COORDINATOR
3
PX SEND QC (RANDOM)
:TQ10001
1
53
Q1,01
P->S
QC (RAND)
4
SORT GROUP BY
1
53
Q1,01
PCWP
5
PX RECEIVE
1
53
Q1,01
PCWP
6
PX SEND HASH
:TQ10000
1
53
Q1,00
P->P
HASH
7
SORT GROUP BY
1
53
Q1,00
PCWP
8
PX BLOCK ITERATOR
47M
2395M
21688
Q1,00
PCWC
9
INDEX SAMPLE FAST FULL SCAN
INDEX
47M
2395M
21688
Q1,00
PCWP