Exadata优化误区2——Exadata无需索引

个人简介:石云华,Exadata中国用户组联合创始人,2019年被ORACLE官方授予ACE称号。毕业后一直从事Oracle数据库第三方运维服务工作,拥有十余年电信运营商、保险、税务、电力行业核心系统数据库运维经验。现就职于北京海天起点技术服务股份有限公司,oracle数据库专家组成员,Exadata部门负责人。个人著作有《Exadata实施运维指南》,另外一本《Oracle Exadata性能优化》即将面世。

曾几何时,有大量的人认为如果想要SQL语句在Exadata上跑得快,就应该删除表上的索引,让SQL语句走智能扫描,更有甚者,在将数据库迁移至Exadata平台时,直接不迁移索引。这种做法真的合适吗?Exadata上真的不需要索引吗?下面,通过一个案例来说话吧。

某客户的一套业务系统迁移到Exadata上之后,出现大面积的业务超时故障。业务运维人员检查发现,其中有一条SQL语句反而比迁移之前更慢,正是这条SQL语句导致了业务超时故障,具体SQL语句文本如下。

既然已经定位到具体SQL语句,则可以收集该SQL语句在当前Exadata环境中的执行计划和SQL运行信息情况,如图8.1所示。


图8.1 SQL语句在Exadata环境中的运行情况

可以看出,该SQL语句进行了全表扫描,平均每次运行的物理读和逻辑读都在50多万,平均执行时间在8747ms左右。

最关键的是这个SQL语句在短短的时间(1个小时)内执行了12514次,从这个SQL语句的执行频率也可以看出,这不是一个报表类查询语句,而是一个OLTP类型的SQL语句,语句本身也非常简单,当ACCESS_NUMBER字段为某个值时,查询PROD_ID和A.LATN_ID字段的最大值。

类似这种SQL语句,走索引扫描的效率应该更高一些,询问业务运维人员得知,在迁移前的环境中,该SQL语句涉及的表对象上存在索引,但该业务系统迁移到Exadata环境时,仅仅保留了主键索引,其他的所有普通索引都未进行迁移。

下面我们看看对SERV_INCR这张表的ACCESS_NUMBER字段创建索引之后的执行计划和SQL运行信息情况,具体如图8.2所示。


图8.2 SQL语句在创建索引之后的运行情况

在创建索引并运行一段时间之后,可以看出,相同的该SQL语句进行了索引的范围扫描,平均每次运行的物理读和逻辑读在10以内,每一次的执行时间才8ms左右。创建完索引后,极大地提升了运行速度,同时也减少了物理IO扫描。

从上面的案例我们可以看出,对于纯OLTP类型的SQL语句,我们仍然需要使用索引,索引扫描的运行时间大大低于智能扫描的运行时间。在某些情况下,智能扫描可能并不是数据访问的最有效方式。

Exadata上的数据库,如何对待数据库上的索引呢?通常建议使用以下方式正确的评估办法索引。

  • 第一步,开启索引监控。

当一套新的数据库迁移到Exadata上之后,最好将所有业务系统相关的索引开启索引监控,数据库运行一个业务周期(也即基本的业务都会跑完一遍)之后,基本可以定位出哪些索引是未被使用过的,这类未被使用过的索引,可以先置于invisible状态,然后关闭索引监控功能。

如果想监控整个用户下的索引,可以采用如下的脚本,具体见代码清单8.1

代码清单8.1 生成索引监控的SQL脚本

代码清单8.1中的SQL脚本会生成一个名为start_index_monitor.sql的脚本,生成的这个脚本才是开启对该用户下所有索引进行监控的具体语句。同样,如果需要关闭监控功能,只需要对以上脚本稍作改动即可。

需要注意,由于v$object_usage视图限制了只显示当前用户下被监控的索引的情况,如果需要查看所有用户下的被监控的索引的情况,使用如下SQL语句,具体见代码清单8.2

代码清单8.2监控索引的使用情况

建议至少经过一个业务周期的持续监控,基本上可以确定哪些索引是从未使用过的,这一类索引则可以置于invisible状态。然后关闭索引监控功能。

  • 第二步:优化使用了索引的TOP SQL语句。

经过第一步的处理,数据库中从未被使用的索引已经全部置于invisible状态,此时我们需要进一步优先数据库中的TOP SQL语句,查看已经使用的索引是否合理。

我们可以通过AWR报告、sql monitor报告或EM12c等方式来获取TOP SQL语句,如果这些SQL语句通过索引扫描的方式花费比较多的时间,那么我们可以考虑强制使用智能扫描的方式来执行这些SQL语句,对比哪种扫描方式的效率更高,如果使用智能扫描方式的效率更高,则考虑将相关的索引置于invisible状态。

Oracle 11g之前的版本,长时间运行的SQL语句可以通过监控v$session_longops性能视图,而在Oracle 11g中,当SQL消耗超过5秒的CPUIO时间,它也会被监控到,监控数据被记录在v$sql_monitor性能视图中,将v$sql_monitor的内容格式化输出,具体命令如下。

  • 第三步:删除不必要的索引。

经过第一步和第二步的处理,一些不必要的索引已经被置于invisible状态,如果这些索引在比较长的时间内未被使用,同时数据库系统的性能也未受影响,那么最终的目的是删除掉这些invisible的索引。

未经允许不得转载:Oracle一体机用户组 » Exadata优化误区2——Exadata无需索引

相关推荐