Oracle 10g 10.2.0.5 Patch

最近客戶一直要求將資料庫由10.2.0.4升級至10.2.0.5

為了讓他了解到升級的風險，因此上網找了升級後出問題的例子：

案例1  post on 11/08/2010

簡述一下內容：此事件的資料庫由10.2.0.1升級至10.2.0.5後(11/10/2010)，原本2分鐘的sql 指令，跑了18小時才完成。

Oracle Support的建議是請他將相容性降至10.2.0.4，才能達到4分鐘(2X時間)完成。

案例二 post on  06/04/2010 相似的bug post on 11/03/2010

這個案例造成hash_Join出錯

案例三 post on 10/26/2010

在 ANSI join時出錯，原文提出的徵況與解法如下：

The pattern that I spotted was:

• in all cases complex views are involved in the queries (outer joins, nested selects, unions...you named it),
• roughly half of the views were written using ANSI join syntax, the other half used old-style join syntax,
• performance problem was reported only on queries that selected data from the views having ANSI join syntax, old-style views performed well
• if the problematic view with ANSI join was rewritten to old-style, it performed well

Workarounds that we found:

• rewrite views containing ANSI joins to use old style join syntax,
• hint the views with /*+ NO_QUERY_TRANSFORMATION */ hint,
• disable CBO query transformations at the session level with logon trigger such as:

查了一下Metalink 有關10.2.0.5的相關問題時發現一個清單(如下)

Issues introduced in 10.2.0.5

This section lists bugs introduced in 10.2.0.5 (if any). Such issues may be either serious or trivial but the aim is to list them all to help customers assess the risk of applying the Patch Set on top of 10.2.0.4

Bug/Doc	Fixed in PSU	Description	Updated
Note:1229669.1*		OERI [kddummy_blkchk] / OERI [5467] for an aborted transaction of allocating extents	18/Nov/2010
10158493		Wrong Results when fix for bug 6082605 is present	08/Nov/2010
10017048		Parallel query / DML does not run in parallel in deep nesting of a DBMS_JOB and PLSQL procedure	09/Sep/2010
10010310		ORA-27300 / ORA-27302 killing a non existing session	15/Nov/2010
9981011		Oracle processes consume large numbers of file descriptors	11/Nov/2010
9980062		Dump [kkocxj] from cost based query transformation	18/Oct/2010
9949948P		Linux: Process spin under ksfdrwat0 if OS Async IO not configured high enough	03/Aug/2010
9928290		The fix for UTL_FILE memory leak bug 7197637 impacts performance	20/Nov/2010
9871430		ORA-31011 / LPX-230 converting CLOB to XMLTYPE	15/Sep/2010
9726739		ORA-997 from SQL with CONNECT BY and UNION	18/Nov/2010
9360157		Wrong results using ORA_ROWSCN pseudocolumn with ANSI join	27/Aug/2010
9020537		DBMS_LOB.SUBSTR truncates LOBs > 8191 in multibyte	02/Sep/2010
7612454		More "direct path read" operations / OERI:kcblasm_1	15/Jul/2010
7361418		ORA-600 querying V$LOGMNR_CONTENTS for logs without supplemental logging	18/Nov/2010
Note:1229669.1*	10.2.0.5.1	ORA-600 [ktsptrn_fix-extmap] during extent allocation caused by bug 8198906	18/Nov/2010

DBA介傳說的偶數版本是較穩定的版本，看來有其道理。