Oracle 数据库自动索引的高业务价值

导读：这篇文章要探讨什么？

性能下降始终与功能的不断迭代相互伴随，一直以来都是数据库管理员（DBA）和开发人员之间博弈和争论的焦点。如果你也有这样的困扰，欢迎阅读接下来的内容。我们将介绍Oracle Database 19c的一个重要组件————Auto Index，看看它是如何安全而高效的解决性能问题的。

• 要功能还是要性能？DBA和开发团队之间是否有双赢的解决方案？

• 是更改架构设计增配硬件，还是规范代码质量降低迭代速度？

• 什么是Auto Index？它真的能提升DBA的产出和团队的效率吗？它安全吗？

• 什么情况下该用Auto Index？如何使用？

在这篇文章中，我们将探讨Oracle 19C Auto Index在提升应用开发效率和减轻DBA工作负担方面的应用场景，并分享生产环境中的实际案例。同时，我们还将提供完整的操作步骤。

要功能还是要性能？DBA和开发团队之间是否有双赢的解决方案？

在功能需求日益繁复、代码迭代速度持续加快的今天，开发人员不得不将主要精力投入到满足新的需求上，这使得性能优化经常被暂时搁置。

而生产环境中性能问题导致的运维的压力，迫使DBA将奔跑的开发团队拉回代码优化的轨道上。这不仅增加了开发团队的工作量，也使得项目变得拖沓难以完成。

我们是否可以找到一种方案，让DBA能够帮助开发人员在即使不那么注重代码执行效率的情况下，也能够满足生产环境对于系统性能的要求呢？

今天我们将讨论一个切实可行的新方案：Auto Index。

花钱买硬件还是花时间修代码？

通常的观念中，性能优化的左腿是“硬件、架构和配置”，右腿是“SQL代码的质量”。两条腿需要交替前进，谁都不可或缺。

但受限于更换成本，我们往往每3-5年才有机会升级一次硬件和架构。相比之下，低效的SQL代码可能每天都有机会发布到生产环境中。

这就需要DBA能够在繁忙的生产系统中快速、精准的定位到性能低下代码。DBA的工作犹如私家侦探，他们需要从AWR、SQL Trace等报告提供的线索中敏锐地找出低效代码，并指引团队解决。

从繁杂的线索中抽丝剥茧快速、准确的定位问题所在，并不是一项简单的任务。这需要开发、运维团队的通力协作。而一旦定位错误，不仅会导致整个团队盲目投入精力，更重要的是降低问题处理时效，引起用户不满。

那么有没有什么办法能在降低投入的基础上，快速定位、解决问题，甚至防患于未然呢？还是要回到今天我们要讨论的方案：Auto Index。

什么是Auto Index？

Auto Index，全称 Automatic indexing，顾名思义，数据库自动创建索引。是Oracle Database 19c版本提供的一个新的选项。自动索引实现了根据应用程序工作负载的变化动态在数据库中创建、重构和删除索引，从而达到持续监控、提高数据库性能的目的。

在将Auto Index开启后，它会打开一个15分钟/次的数据库后台任务，不需要用户做任何干预。

它通过引入内部专家系统以及机器学习的方法，不断评估执行的SQL和相关基础表，为可能优化的语句自动创建状态为"invisible"的索引，在验证、判断后进行决策，有利的"invisible"索引将会被置为"visible"状态，而不利的将会在未来被删除。

它能够持续监视、改善SQL的执行性能，同时避免已有SQL的执行计划的退化。

在 dba_advisor_tasks视图中，我们可以看到Auto Index的三个相关任务，分别是：SYS_AI_SPM_EVOLVE_TASK、SYS_AI_VERIFY_TASK、SYS_AUTO_INDEX_TASK。

Auto Index真的能提升团队效率吗？

我们从前文Auto Index的定义中已经知晓，Auto Index不是从AWR中查找Top SQL，而是从SQL列表中查找。换句话说，任何数据库上运行过的查询语句，都有机会被Auto Index发现，无论它是否因为高并发带来了巨大的性能问题。

从上面这张图我们可以看出，Auto Index并不需要等待系统整体出现问题，绝大多数问题都在发生前就被解决了。

通过实际环境验证，90%以上的问题都止步于Auto Index，整个团队不需要过多的为性能优化陷入内耗。这极大的加快了开发部门的交付速度，减少了DBA优化的工作量，与此同时系统性能也得到了极大的提高。

Auto Index 安全吗？

尽管很多文章都详细解释了Auto Index的功能，但对于其能否安全稳定地应用于生产环境，却缺乏深入的探讨。

索引在提高查询效率的同时，也可能带来一些负面效应。

过多的索引除了可能导致DML性能下降的问题之外，危害更大的是可能导致执行计划发生改变，进而引发性能大幅下降。这种潜在危害往往难以预测和准确评估。

那么Auto Index是否能够准确地创建合适的索引？其行为是否审慎且稳定？此外，它是否能避免因新建索引而可能导致的执行计划不稳定问题？

所以我们做了进一步的研究。

风险有底线，提升无上限

准确的统计信息、invisible状态的索引和SQL Plan Baseline是Auto Index确保安全的三大工具。

Auto Index不会考虑缺乏统计信息或统计信息陈旧的表，这就大幅降低因统计信息缺失引发的性能退化风险。

在数据库中，Auto Index首先尝试创建invisible状态的索引。invisible状态的使数据库有机会比较不同的执行计划并审慎做出选择，合适的索引将会被置位visible状态并被优化器使用。如果这个索引确实不合适，则会被Auto Index的后台任务删除。实验中我们发现，Auto Index甚至比大多数DBA更为严谨。

自动创建SQL Plan Baseline是Auto Index确保执行计划不退化至关重要的组成部分。Auto Index后台任务会为SQL语句创建SQL Plan Baseline，以阻止执行计划在索引从invisible状态变为visible状态时出现退化。这一点真正保障了数据库的整体性能在Auto Index开启后做到稳中有升。如此重要的特性，在国内外的文档中却鲜少有被提及。Auto Index开始运行后，我们可以在dba_sql_plan_baselines视图中找到它们。

上述三个工具，确保了Auto Index开启后SQL执行计划不会变得更糟糕，仅仅只有DML语句受到轻微影响。对于Auto Index底线的充分了解，也是我们敢于使用Auto Index的底气所在。

底线无忧之后，Auto Index强大、持久的优化能力，则会全面提升系统的性能。

案例分享

我们面向互联网终端用户的系统长期以来面临这样的窘境：由于对功能迭代速度要求极高，开发团队专注于满足用户需求，性能问题常常被忽视。这导致运维团队长期承受性能问题困扰，开发人员也被迫投入大量时间和精力处理这些问题。

为解决这一问题，我们首先想到的是升级硬件。我们近几年硬件的升级情况如下：

硬件的提升帮助我们短期的缓解了性能问题，但是随着代码的迭代，我们不得不每隔3年时间就再次进行配置的提升。

直到我们更换了Exadata。与单纯升级硬件设备提升IO速度不同，在闪存特性的基础上，Exadata提供了Smart Scan技术。

Smart Scan可以通过字段投影、谓词过滤、存储索引三个核心技术帮助SQL语句在存储节点上进行初步的数据处理，它常常能过滤掉大量的无用的数据。这一特性不仅仅是提升了IO的速度，而是事实上降低了数据库调用数据块的数量。

美中不足的是，Smart Scan的触发条件太苛刻了（这也是为什么你已经有了一台Exadata，但还是需要Auto Index的原因），它只有在Full table, Fast full index, 或 Full bitmap index scans才会被触发[引用自Oracle Document《Oracle Exadata Database Machine Smart Scan》]。并非所有场景都能有效使用到这一特性。

四年过去了，这台Exadata的负载也逐渐升高。日均达到15%的CPU利用率，高峰时段甚至有40%以上。

但是面对我们当前的业务，在性能上，能够相对于Exadata有质的超越的设备似乎已经很难再找到了。而立刻替换一台新的Exadata设备，在经济上又难以承受。

于是我们想到了另一条路，继续优化代码。

可是之前的经验已经告诉我们，面对几十名开发人员的快速迭代，我们要么逼迫开发人员降低开发时效陪我们一起优化代码、关注代码质量，要么就是把DBA的工作量增加十倍————事实上我们也购买了大量的人天试图让整个系统有脱胎换骨的提升，但是效果却只是差强人意。一方面工作量实在太大，另一方面新的问题语句仍然不断被发布到生产。这似乎成了一个永无止尽的问题。

但当我们把Oracle Database升级到19c之后，更好的解决方案出现了，那就是Auto Index。

在经过审慎的论证和测试后，我们决定在生产环境采用该功能。

自7月13日我们启用Auto Index功能以来，该设备日均CPU负载在短短两周之内从15%迅速降至8%左右，下降幅度将近50%。

另一方面，该系统全部接口的平均响应时效，从18ms提升到9ms，提升了整整一倍！

根据Auto Index的报告，数百条SQL的执行计划得到了优化，而且还在持续增加。如果DBA手动进行SQL查找、分析、测试、优化和上线，以实现类似的效果，需要多长时间？也许是一年？也许是三年？

下表记录了在我们真实环境中，未开启Auto Index之前和开启Auto Index大约一个月之后，调用频繁的前16名接口响应时效的对比。结果显示整体响应时效大幅提升。

什么情况下适用Auto Index？

从前面的描述中，我们可以知道：Auto Index是一个稳定的优化工具，你可以信任它对于目标语句的优化效果，同时不必担心它造成当前SQL语句和其他SQL语句的性能衰退。

但是Auto Index不能够规避对于DML性能造成的损失，同时它对于SQL语句中的逻辑错误也同样无能为力。对于DML语句性能要求较高的系统，则不适用该功能。Auto Index在评估索引是否应当被使用时，并不会考虑DML语句的性能。

因此，Auto Index适用于开发过程中对性能不是特别重视的、以查询为主的系统。事实上绝大多数系统都是以查询为主。而开发人员原先在性能方面投入的精力越少，优化效果也就越是明显。

同样的道理，Auto Index也能够让开发人员解放出大量的时间，把注意力更多的聚焦在功能而非性能上。整个团队的开发效率必然将有质的飞跃。

如何使用Auto Index？

一、创建表空间，并作为Auto Index的表空间使用

create bigfile tablespace auto_indexes_ts datafile autoextend on maxsize 128000m;
exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS');

二、Auto Index的范围控制

1）允许某个schema使用Auto Index：

exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE);

2）禁止某个schema使用Auto Index：

exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST2', allow =>FALSE);

3）将Auto Index的schema限制清空

exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow=>FALSE);

若不做设置，则默认对全库所有的schema开启Auto Index功能。

三、Auto Index的启停

1）打开Auto Index，使其可用：

exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');

2）打开Auto Index，并使其创建的所有索引都是invisble：

exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');

3）关闭Auto Index：

exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF')

四、删除Auto Index创建的索引

1）删除某条Auto Index创建的索引

begin
dbms_auto_index.drop_auto_indexes(
owner =>'MY_SCHEMA',
index_name =>'"SYS_AI_512bd3h5nif1a"',
allow_recreate =>false);
end;
/

2）删除某个schema下的所有Auto Index创建的索引

begin
dbms_auto_index.drop_auto_indexes(
owner =>'MY_SCHEMA',
index_name =>null,
allow_recreate =>true);
end;
/

注意，上文中allow recreate为是否允许Auto Index再次创建这条索引。

五、查看Auto Index的报告：

DBMS_AUTO_INDEX.REPORT_ACTIVITY (
activity_start IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP -1,
activity_end IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
type IN VARCHAR2 DEFAULT 'TEXT',
section IN VARCHAR2 DEFAULT 'ALL',
level IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;
DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY (
type IN VARCHAR2 DEFAULT 'HTML',
section IN VARCHAR2 DEFAULT 'ALL',
level IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;

六、查看Auto Index的配置信息：

你可以在DBA_AUTO_INDEX_CONFIG视图中查看到Auto Index的配置信息。如下的视图提供更多的Auto Index信息。

DBA_AUTO_INDEX_EXECUTIONS
DBA_AUTO_INDEX_IND_ACTIONS
DBA_AUTO_INDEX_SQL_ACTIONS
DBA_AUTO_INDEX_STATISTICS
DBA_AUTO_INDEX_VERIFICATIONS

最后需要注意的是，Auto Index目前非云端仅建议在Exadata上使用。

编辑：赵靖宇