正德厚生,臻于至善

EBS切换职责慢处理及Oracle invisible index妙用

NOTE:1283180.1 – R12.1.2: Very Slow When Switching Responsibilities
NOTE:2000591.1 – R12.2: Switching Responsibilities Is Very Slow After Upgrading From 11i

set lines 160 pages 50000
Select distinct Bugs.Bug_Number as PATCH,
decode(Ad_Patch.Is_Patch_Applied('R12',-1,bugs.bug_Number),'EXPLICIT','APPLIED','NOT_APPLIED') as APPLIED
From 
(
 select '10252312' as bug_number From Dual UNION ALL
 select '19001790' as bug_number From Dual
) Bugs order by 1;
切换职责语句
SELECT R.APPLICATION_ID, R.RESPONSIBILITY_ID, S.SECURITY_GROUP_ID, 
  R.RESPONSIBILITY_NAME, S.SECURITY_GROUP_NAME 
FROM
 FND_RESPONSIBILITY_VL R, FND_USER_RESP_GROUPS U, FND_SECURITY_GROUPS_VL S 
  WHERE U.USER_ID = :A00 AND SYSDATE BETWEEN U.START_DATE AND NVL(U.END_DATE, 
  SYSDATE) AND R.VERSION IN ('4', 'W')  AND R.APPLICATION_ID = 
  U.RESPONSIBILITY_APPLICATION_ID AND R.RESPONSIBILITY_ID = 
  U.RESPONSIBILITY_ID AND SYSDATE BETWEEN R.START_DATE AND NVL(R.END_DATE, 
  SYSDATE) AND U.SECURITY_GROUP_ID = S.SECURITY_GROUP_ID AND 
  S.SECURITY_GROUP_ID >= 0 AND S.SECURITY_GROUP_KEY = NVL(:A01, 
  S.SECURITY_GROUP_KEY) AND R.RESPONSIBILITY_KEY = NVL(:A02, 
  R.RESPONSIBILITY_KEY) AND R.APPLICATION_ID = NVL(:A03, R.APPLICATION_ID) 
  AND NVL(FND_PROFILE.VALUE('NODE_TRUST_LEVEL'), 1) <=  
  NVL(FND_PROFILE.VALUE_SPECIFIC('APPL_SERVER_TRUST_LEVEL', U.USER_ID,
  R.RESPONSIBILITY_ID,R.APPLICATION_ID),1)   ORDER BY RESPONSIBILITY_NAME, 
  SECURITY_GROUP_NAME

从11g开始,Oracle数据库引入了invisible indexes(不可见索引)。Invisible index也是索引的一种,会被优化器忽略,如果没有在session级别或system级别明确设置初始化参数coptimizer_use_invisible_indexes=true。11g之前,我们可能会这样做:先不删除索引,修改其为Unusable。但修改后,索引不能再被使用,也不会随着表数据的更新而更新。当需要重新使用该索引时,需要rebuild、然后收集统计信息等操作。对于一些大表来说,这种负面影响是不可预知的。

创建不可见索引
create index ohs_name on ohs(name) invisible;

修改索引为不可见
alter index <index_name> invisible;

修改索引为可见
alter index <index_name> visible;

可以通过查看user_indexes, all_indexes, or dba_indexes的列visibility来确认索引的情况,Invisible Index和正常的索引一样,当有DML语句发生时会被维护

今天一朋友碰到了EBS中一个性能问题:用户反映在生产系统上切换职责时,系统反应卡顿。但这种情况在测试环境中正常。今天在这只是探讨下解决思路而已

首先通过追踪找到了相应的语句,看了下执行计划

生产环境执行计划
测试环境执行计划

通过比较发现,该语句在生产环境使用WF_USER_ROLE_ASSIGNMENTS_N2索引,在测试环境中使用WF_USER_ROLE_ASSIGNMENTS_N4。到目前为止,基本可以判定是因为索引的问题造成了性能的问题。我们可以通过多种途径解决这个问题。

A.重新收集统计信息(因为这个是已经做过的,但没有效果,所以放在最前面了。这也算是一种解决方案)

统计数据收集模式

这个Request(Gather Schema Statistics – ALL, 10, , NOBACKUP, , LASTRUN, GATHER AUTO, , Y)是EBS中用于收集统计信息的方式。EBS系统中调用的是FND_STATS包,这个包是在DBMS_STATS的基础上做了修改,适用于EBS系统。

SYS@PROD2> exec fnd_stats.gather_table_stats('APPLSYS','WF_USER_ROLE_ASSIGNMENTS',100,degree=>8);

收集后,在生产上仍然有这样的问题。执行计划没有改变。

B.删除生产系统上的WF_USER_ROLE_ASSIGNMENTS_N2索引

从理论上说,删除了这个索引,系统应该会使用WF_USER_ROLE_ASSIGNMENTS_N4的索引。但是我们要考虑到这个Oracle EBS系统,这个索引是系统自带的,我们不能随便删除。这时Invisible Index特性就派上用场了。我们可以把生产环境上的WF_USER_ROLE_ASSIGNMENTS_N2修改为Invisible做个测试。验证下是索引WF_USER_ROLE_ASSIGNMENTS_N2造成问题的猜测是否正确。

改为Invisible之前(由于收集过统计信息,这个执行计划和前面生产系统中执行计划有点区别,但还是都走了N2这个索引)

改为Invisible之前
SYS@PROD2> alter index APPLSYS.WF_USER_ROLE_ASSIGNMENTS_N2 invisible;
Index altered.
改为Invisible之后

把WF_USER_ROLE_ASSIGNMENTS_N2修改为不可见之后,生产库上执行计划和和测试库上执行计划一样,系统恢复正常,性能问题解决,从这可断定是索引的问题。但是我们不能把这个索引修改为不可见,只可以用来测试,确定问题。通过下面的语句改为可见。

SYS@PROD2> alter index APPLSYS.WF_USER_ROLE_ASSIGNMENTS_N2 visible;
Index altered.

修改之后,问题依旧

C.尝试使用dbms_stats收集

exec dbms_stats.gather_table_stats(ownname => 'APPLSYS',tabname => 'WF_USER_ROLE_ASSIGNMENTS',cascade => TRUE) ;

用dbms_stats收集统计信息后,系统恢复正常。看来之前用EBS自带的Request收集信息没有完全发挥作用,这个Request还是存在缺陷的。

D.使用profile

如果C方案仍然没有效果,我们还可以在测试系统上生成该sql的profile,然后在生产环境上实施

可以借助sqlt工具中util文件夹中的sql

E.设置表、索引或列的信息

profile不是万能的,有时即使使用了profile,也不会见得会好。还可以尝试使用dms_stats设置表,列的信息

F.当然设置统计信息也不一定能解决问题。如果买的有Oracle的服务,还可以尝试提交SR,说不定你还真遇到了bug,恭喜你中奖了

Reference

http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes.htm#ADMIN12317

赞(0) 打赏
未经允许不得转载:徐万新之路 » EBS切换职责慢处理及Oracle invisible index妙用
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏