这周遇到的2个问题

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

昨天在青海某客户计费数据库反应入库很慢，通过查询发现所有的program都在等待reliable message。对于该等待事件几年前遇到过。通过如下脚本很容易判断产生该等待事件的channel是什么：

SELECT c.usernane,
c.sid,
c.program,
c.sql_id,
b.addr "Channel context",
b.totpub_ksrcctx,
a.name_ksrcdes
FROM X$KSRCDES a, X$KSRCCTX b, v$session c
WHERE b.name_ksrcctx = a.indx
AND b.addr = c.p1raw
and c.event='reliable message'

SELECT c.usernane,

c.sid,

c.program,

c.sql_id,

b.addr "Channel context",

b.totpub_ksrcctx,

a.name_ksrcdes

FROM X$KSRCDES a, X$KSRCCTX b, v$session c

WHERE b.name_ksrcctx = a.indx

AND b.addr = c.p1raw

and c.event='reliable message'

通过查询发现均为Result Cache:channel ；由此可见跟11gR2引入的Result cache新特性有关；但是询问客户之后，发现业务sql并没有使用/*+result_cache ＊／之类的hint。

那么为什么会产生这个问题呢？通过模糊查询v$sqlarea／v$session发现 oracle在对table进行统计信息动态采样；而动态采样的sql语句中使用来诸如result_cache(snapshot=360)之类的hint。经查发现这是12c针对动态采样的增强，通过调整如下的几个参数可以屏蔽该问题：

alter system set "_optimizer_dsdir_usage_control"=0	  	  sid ='*' scope=both;     
alter system set "_sql_plan_directive_mgmt_control"=0	  sid ='*' scope=both; 
alter system set "_optimizer_ads_use_result_cache" = FALSE  sid ='*' scope=both;

alter system set "_optimizer_dsdir_usage_control"=0 sid ='*' scope=both;

alter system set "_sql_plan_directive_mgmt_control"=0 sid ='*' scope=both;

alter system set "_optimizer_ads_use_result_cache" = FALSE sid ='*' scope=both;

今天另外一个客户也遇到一个奇怪的问题,insert 语句报错ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn]。

这个问题我还是第一次遇见，从mos文档来看该问题应该是bug，如下：Bug 22241601 – ORA-600 [kdsgrp1] ORA-1555 / ORA-600 [ktbdchk1: bad dscn] due to Invalid Commit SCN in INDEX block (Doc ID 22241601.8)

解决方案是通过重建index或者设置_ktb_debug_flags参数，不过该参数的设置并不会对已经损坏对block起任何作用，仍然建议设置该参数或者打相应打patch。

如下是trace文件的过滤内容：

rishinasahis-MacBook-Pro:Desktop Roger$ cat ora333a3_ora_20280_i410029.trc |grep 'seg/obj'
seg/obj: 0x143e2  csc: 0x05.6aa2b48b  itc: 120  flg: E  typ: 2 - INDEX
seg/obj: 0x143e1  csc: 0x05.82491401  itc: 92  flg: E  typ: 2 - INDEX
seg/obj: 0x143e2  csc: 0x05.425f5889  itc: 1  flg: E  typ: 2 - INDEX
seg/obj: 0x143e2  csc: 0x05.8478edd7  itc: 1  flg: E  typ: 2 - INDEX
seg/obj: 0x143e2  csc: 0x05.84638453  itc: 1  flg: E  typ: 2 - INDEX
seg/obj: 0x143e2  csc: 0x05.84638453  itc: 1  flg: E  typ: 2 - INDEX
seg/obj: 0x143e2  csc: 0x05.83fbc1b0  itc: 1  flg: E  typ: 2 - INDEX
seg/obj: 0x143e2  csc: 0x05.83fbc1b0  itc: 121  flg: E  typ: 2 - INDEX
seg/obj: 0x143e2  csc: 0x05.844ed914  itc: 120  flg: E  typ: 2 - INDEX
seg/obj: 0x143e2  csc: 0x05.6aa2b48b  itc: 120  flg: E  typ: 2 - INDEX
seg/obj: 0x143e2  csc: 0x05.6aa2b48b  itc: 120  flg: E  typ: 2 - INDEX
seg/obj: 0x143e2  csc: 0x05.6aa2b48b  itc: 120  flg: E  typ: 2 - INDEX

rishinasahis-MacBook-Pro:Desktop Roger$ cat ora333a3_ora_20280_i410029.trc |grep 'seg/obj'

seg/obj: 0x143e2 csc: 0x05.6aa2b48b itc: 120 flg: E typ: 2 - INDEX

seg/obj: 0x143e1 csc: 0x05.82491401 itc: 92 flg: E typ: 2 - INDEX

seg/obj: 0x143e2 csc: 0x05.425f5889 itc: 1 flg: E typ: 2 - INDEX

seg/obj: 0x143e2 csc: 0x05.8478edd7 itc: 1 flg: E typ: 2 - INDEX

seg/obj: 0x143e2 csc: 0x05.84638453 itc: 1 flg: E typ: 2 - INDEX

seg/obj: 0x143e2 csc: 0x05.83fbc1b0 itc: 1 flg: E typ: 2 - INDEX

seg/obj: 0x143e2 csc: 0x05.83fbc1b0 itc: 121 flg: E typ: 2 - INDEX

seg/obj: 0x143e2 csc: 0x05.844ed914 itc: 120 flg: E typ: 2 - INDEX

seg/obj: 0x143e2 csc: 0x05.6aa2b48b itc: 120 flg: E typ: 2 - INDEX

Posted in Oracle rdbms, troubleshooting on 6月 20, 2018

One Response to “这周遇到的2个问题”

罗拉 Says:

10月 1, 2018 at 上午 6:35

呵呵。学习了。感触良多！

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

这周遇到的2个问题

One Response to “这周遇到的2个问题”

Leave a Reply