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
最后下载one-off patch 6980350，应用之后该sql执行计划正常如下