10205 bug之6980350， dbms_stats巨慢无比

OS: HPDB: 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 clfFROM "JUSTIN"."TABLE" SAMPLE BLOCK （15.0000000000） tWHERE "SYS_NC00089$" IS NOT NULLOR "SYS_NC00090$" IS NOT NULLOR "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;DescriptionThis 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 => 100HOOKS 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.1Please 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<？xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />	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

Unix/Linux搜寻tnsnames.ora规则升级10205遭遇bug222316 之 ora-1031 VS dba_role_privs.default_role =N相关资讯 Oracle数据库基础教程

在Oracle数据库中插入含有&符号的（03/06/2013 09:20:14）
Oracle 执行计划更改导致数据加工（02/13/2013 14:45:04）
判断Oracle Sequence是否存在（02/13/2013 14:32:26）

Oracle数据库中无法对数据表进行（02/26/2013 14:24:58）
Oracle 在同一台主机上建立用户管（02/13/2013 14:40:58）
Oracle em 无法启动,报not found错（02/13/2013 14:29:48）

本文评论查看全部评论（0）