Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » 性能优化

Oracle Invisible Index的妙用

从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系统。

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


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.


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



关键词:perf oracle 

相关文章

vagrant with oracle
Install Oracle Database on X86
Install oracle products on docker
一图了解Oracle GoldenGate实现Oracle到Oracle复制的前世今生
Exadata最权威最完整的学习资料
Oracle数据库C函数解析
Exadata上收集Cell节点的日志
MySQL Cookbook for Oracle DBA
Full Transportable Export/Import(fxtts)
Oracle自治数据仓库云
expdp on physical standby
Oracle Database 12c之后DataGuard环境中的PDB操作
Top