目录
需要注意的是,Auto Index目前非云端仅建议在Exadata上使用(因为在Exadata上实践过了)
一、探讨内容
性能下降始终与功能的不断迭代相互伴随,一直以来都是数据库管理员(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?
1.创建表空间,并作为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');
2.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功能。
3.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')
4.删除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再次创建这条索引。
5.查看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;
6.查看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上使用。