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 

相关文章

关于Java的那些事
Vagrant with oracle
Install Oracle Database 19c 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
Top