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这个索引)

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

把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