如何安装Oracle one off临时小补丁及注意事项
View more documents from Maclean Liu
Waiting For KKSFBC CHILD COMPLETION?
2011/05/09 by 4 Comments
客户有一套AIX 5.3上的10.2.0.4.5生产库系统，最近频繁出现”KKSFBC CHILD COMPLETION”等待，同时导致session不断spin消耗CPU并hang住，从表象看这似乎是由bug引起的。以KKSFBC CHILD COMPLETION为关键字到MOS查询可以找到<Bug 6795880 – Session spins / OERI after ‘kksfbc child completion’ wait – superceded [ID 6795880.8]>,该Bug的症状为进程不断spin且hang住、出现’KKSFBC CHILD COMPLETION’等待事件、还可能伴有’Waits for “cursor: pin S”‘等待事件，直接影响的版本有11.1.0.6、10.2.0.3和10.2.0.4。
对于该Bug的描述是在发生’kksfbc child completion’等待事件后会话陷入无休止的自旋(spins)中，这种自旋(spins)发生在由堆栈调用(stack call)kksSearchChildList->kkshgnc陷入对kksSearchChildList函数的无限循环中。
就当前用户提供的版本号及等待事件信息仍不足以定位到该Bug，我们需要更详细的stack call。所幸的是这个trouble是可以重现的(reproduceable),在之后的一次案发现场我们得到了必要的信息:
Name PID CPU% PgSp Owner
oracle 3723390 10.0 7.0 oracle
SQL> oradebug setospid 3723390
Oracle pid: 155, Unix process pid: 3723390, image: oracle@lmsdb1
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug short_stack;
ksdxfstk+002c<-ksdxcb+04e4<-sspuser+0074<-000044F0
<-kksfbc+0b9c<-kkspsc0+0ffc<-kksParseCursor+00d4
<-opiosq0+0ae0<-kpooprx+0168<-kpoal8+0400<-opiodr+0ae0<-ttcpip+1020<-opitsk+1124
<-opiino+0990<-opiodr+0ae0<-opidrv+0484<-sou2o+0090<-opimai_real+01bc<-main+0098<-__start+0098
SQL> oradebug dump processstate 10;
Statement processed.
SQL> oradebug dump systemstate 266;
Statement processed.
=========================process state dump=========================
SO: 7000003f72e3378, type: 4, owner: 7000003f225c798, flag: INIT/-/-/0x00
(session) sid: 270 trans: 0, creator: 7000003f225c798, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-009B-0000017F, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 0, prv: 0, sql: 700000243ef9540, psql: 7000002fe76d9c0, user: 28/OLSUSER
service name: SYS$USERS
O/S info: user: newprepay, term: unknown, ospid: 1234, machine: newprepayC
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
last wait for 'kksfbc child completion' blocking sess=0x0 seq=4397 wait_time=48840 seconds since wait started=144918
=0, =0, =0
Dumping Session Wait History
for 'kksfbc child completion' count=1 wait_time=48840
=0, =0, =0
可以从以上trace中看到会话确实曾长时间处于’kksfbc child completion’等待中，之后陷入无限自旋(spins)中消耗了大量CPU时间。但这里实际的表现又存有差异，引发无限循环的函数是kksfbc而不是kksSearchChildList(常规的调用序列是:kksParseCursor->kkspsc0->kksfbc ->kksSearchChildList->kkshgnc)。
kksfbc意为K[Kernel]K[Kompile]S[Shared]F[Find]B[Best]C[Child]该函数用以在软解析时找寻合适的子游标，在10.2.0.2以后引入了mutex互斥体来取代原有的Cursor Pin机制，Mutex较Latch更为轻量级。著名的Tanel Poder指出虽然mutex的引入改变了众多cursor pin的内部机制，但kksfbc仍需要持有library cache latches才能扫描library cache hash chains(However the traversing of library cache hash chains (the right child cursor lookup using kksfbc()) was still protected by library cache latches)。另一方面当kksfbc函数针对某个parent cursor找到合适child cursor后，可能使用KKSCHLPINx方法将该child cursor pin住，这个时候就需要exclusive地持有该child cursor相应的mutex，如:
SQL> select mutex_addr,requesting_session rs,blocking_session bs,location from x$mutex_sleep_history; MUTEX_ADDR RS BS LOCATION ---------------- ---------- ---------- ---------------------------------------- 00000000A3CF8928 159 148 kksfbc [KKSCHLPIN1] 00000000A3CEA8A8 159 0 kksfbc [KKSCHLPIN1] 00000000A3CF5508 159 0 kksfbc [KKSCHLPIN1] 00000000A3CF10E8 148 159 kksLockDelete [KKSCHLPIN6]
当有一个进程执行kksfbc，而其他进程可能需要陷入’kksfbc child completion’等待中(更多的是cursor:pin S等待事件)，但这种等待一般是十分轻微的，你很难从某个”仪表”上观察到这一事件。因为一系列kksfbc相关的bug仅发生在10.2.0.2以后，可以猜测是由于mutex的引入引起的。
打破’kksfbc child completion’异常等待的一种行之有效的workaround方法是设置隐藏参数_use_kks_mutex_pin为false,即使用传统的由latch保护的Cursor pin；设置该hidden parameter需要重启实例，具体的设置方法如下:
alter system set "_kks_use_mutex_pin"=false scope=spfile; restart instance...........
但这种方法也仅仅是行之有效，而非万试万灵。从消极的角度来说，因为放弃了mutex保护cursor pin的机制，在解析频繁的系统中library cache latch的争用将白热化，有可能成为Top 5等待事件。
另一种值得推荐的workaround是减少硬编码的SQL语句，使用session_cached_cursor减少实际的软解析操作也有助于避免引发该Bug；当然这要求我们修改代码，但从长远来看这种改良是值得的。
最后Oracle在10.2.0.4上提供了该Bug的one-off Patch 8575528,其在10.2.0.4 psu4以后的等价补丁为(Equivalent patch)为merge patch 9696904：
| 8557428 | 9696904 7527908 | Both fixes are needed. 6795880 superceded by 8575528 in 9696904 which includes extra files so may cause new conflicts |
但merge patch 9696904目前仅有Linux x86/64平台上的版本，而问题数据库所在平台为IBM AIX on POWER Systems (64-bit)。如果要通过补丁来fix这个问题的话，AIX平台的用户可以要求Oracle development开发部门build一个Power版的9696904 patch，也可以升级到10.2.0.5上去；
注意Patch 8575528: MISSING ENTRIES IN V$MUTEX_SLEEP.LOCATION目前有IBM AIX on POWER Systems (64-bit)平台上10.2.0.4.4的版本，经过验证该one-off patch可以在10.2.0.4 PSU4以后的版本上实施(包括10.2.0.4.5/10.2.0.4.6等)，不需要如以上描述地去apply 9696904这个merge patch。当然升级到10.2.0.5/11.1.0.7或以上版本依然有效。
此外apply以上8575528:后需要修改隐藏参数”_cursor_features_enabled”并重启才能使fix生效，具体设置防范如下:
ALTER SYSTEM SET "_cursor_features_enabled"=10 scope=spfile; restart instance.............
注意以上参数仅在使用one-off patch时需要设置，而当通过升级到10.2.0.5/11.1.0.7来修复问题时无需设置上述”_cursor_features_enabled”参数。
如何确定所打Patch是否需要停机
2010/12/28 by 1 Comment
Oracle在11g中提出了online patching(也可以叫做hot patch)的概念，有效减少了因实施one-off patch而导致的系统停机时间。但我们如何得知哪些Patch是可以online apply的，而哪些Patch是必须关闭实例(shutdown instance)后应用的呢？
下面我们就介绍一种简单有效地方法来识别这2种Patch:
1. 从MOS下载所需要的Patch文件 2. 解压该Patch文件(一般为zip压缩包)，cd到/$PATCH_ID/etc/config目录，打开inventory.xml文件 3. 检查xml文件中的instance_shutdown选项，如 <instance_shutdown>true</instance_shutdown> 则说明该one-patch要求offline apply,而不能在线实施
ora-00600[kkocxj:pjpCtx]内部错误一例
2010/11/29 by Leave a Comment
一套HP-UX上的10.2.0.4系统在运行某条 select查询语句时出现ORA-00600[kkocxj:pjpCtx]内部错误，TRACE文件信息如下：
FILE VERSION
------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/10.2
System name: HP-UX
Node name: crmdb1
Release: B.11.31
Version: U
Machine: ia64
Instance name: cbssnm
Redo thread mounted by this instance: 1
TRACE FILE
---------------
Filename = cbssnm_ora_29061.trc
*** ACTION NAME:(SQL 窗口 - 新建) 2010-07-02 15:59:46.238
*** MODULE NAME:(PL/SQL Developer) 2010-07-02 15:59:46.238
*** SERVICE NAME:(SYS$USERS) 2010-07-02 15:59:46.238
*** SESSION ID:(770.4341) 2010-07-02 15:59:46.237
*** 2010-07-02 15:59:46.237
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kkocxj : pjpCtx], [], [], [], [], [], [], []
Current SQL statement for this session:
select p.access_number, aa.name
from crm.product p,
(select aa.prod_id, os.name, os.staff_number
from (select *
from (select prod_id,
party_id,
row_number() over(partition by prod_id order by start_dt desc) num
from crm.party_2_prod
where end_dt > sysdate
and party_product_rela_role_cd = 3)
where num = 1) aa,
crm.our_staff os
where aa.party_id = os.staff_id) aa
where p.prod_id = aa.prod_id(+)
and p.access_number = '15335581126'
----- Call Stack Trace -----
ksedst <- ksedmp <- ksfdmp <- kgerinv <- kgeasnmierr
<- $cold_kkocxj <- kkoiqb <- kkooqb <- kkoqbc <- apakkoqb
<- apaqbdDescendents <- apaqbd <- kkqctCostTransfQB <- kkqctdrvJP
<- kkqjpdttr <- kkqctdrvTD <- kkqjpddrv <- kkqdrv <- kkqctdrvIT
<- apadrv <- opitca <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild
<- kxsGetRuntimeLock <- kksfbc <- kkspsc0 <- kksParseCursor
<- opiosq0 <- kpooprx <- kpoal8 <- opiodr <- ttcpip <- opitsk
<- opiino <- opiodr <- opidrv <- sou2o <- opimai_real <- main
<- main_opd_entry
根据错误代码和stack trace可以在metalink上匹配到如下Bug:
Bug:7014646
Abstract: ORA-600: INTERNAL ERROR CODE, ARGUMENTS: [KKOCXJ : PJPCTX], [], [], [], [], []
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11.2
Versions confirmed as being affected
* 10.2.0.4
* 11.1.0.6
Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in
* 10.2.0.4 Patch 7 on Windows Platforms
* 10.2.0.5 (Server Patch Set)
* 11.1.0.7 (Server Patch Set)
* 11.2 (Future Release)
Symptoms:
* Internal Error May Occur (ORA-600)
* ORA-600 [kkocxj : pjpCtx]
Related To:
* Optimizer
* _OPTIMIZER_PUSH_PRED_COST_BASED
Description
A complex query can fail during parse with
ORA-600 [kkocxj : pjpCtx]
Workaround
Set "_optimizer_push_pred_cost_based"=false
该bug可以通过实施one off Patch 7014646修复，也可以尝试通过修改隐式参数_optimizer_push_pred_cost_based禁用基于成本的谓词前置特性(WORKAROUND: disable cost based push predicate)来规避该[KKOCXJ:PJPCTX]内部错误发生，具体的修改方法:
SQL> conn / as sysdba SQL> alter system set "_optimizer_push_pred_cost_based"=false; SQL> exit /* 设置该隐式参数无需重启实例 */
Oracle GCS更推荐通过应用补丁7014646的方法来解决问题，而修改以上隐式参数则不一定百分之百能解决问题。
