10205 bug之6980350， dbms_stats巨慢无比

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

 

 

 

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

转载于:http://blog.itpub.net/15480802/viewspace-733533/