Oracle 19C 新特性之自动索引(Automatic Indexing)测试-Part.1

在eygle的文章《Oracle 19c 十大新特性一览》中,曾经提到 Oracle 19c的一个重要新特性,就是:自动化索引创建和实施

对于关系型数据库来说,索引是使得查询加速的重要手段,而如何设计和创建有效的索引,长期以来是一项复杂的任务。
在 Oracle 19c 中,自动化索引创建和实施技术被引入进来,Oracle 通过模拟人工索引的思路,建立了内置的专家系统。
数据库内置的算法将会通过捕获、识别、验证、决策、在线验证、监控的全流程管控索引自动化的过程。
这一特性将会自动帮助用户创建有效的索引,并通过提前验证确保其性能和有效性,并且在实施之后进行监控,这一特效将极大缓解数据库索引维护工作。
自动化还将删除由新创建的索引(逻辑合并)废弃的索引,并删除自动创建但长时间未使用的索引。

来自原文的引用
引用eygle的PPT,侵删

在多处有文提到,Automatic indexing特性对于on-prem环境仅支持Oracle Exadata平台,对于Oracle Cloud各个Cloud Edition都支持。所以on-prem环境的用户,是不能使用这个特性的,仅仅是使用了exadata或者O云的环境才可以。为了测试用途,我们打开on-prem的exadata特性之后开始测试。否则将会遇到下面的报错。

此仅为测试用途,测试完了我就删了,不然晚上害怕。

首先,打开自动索引这个特性,并且设置自动索引的表空间。

打开特性,设置为AI创建好索引就自动应用
当前的配置

测试用户为HR,基于DBA_OBJECTS创建一个大表,并且将OBJECT_ID列更新为ROWNUM。

这个表是有统计信息的,统计信息是CTAS时候的行数,后面几次insert的没有收集。统计信息是不准确的,不过此时表上没有索引,但是这个数量级也足够CBO跟AUTO INDEXING特性在几次全扫之后来给OBJECT_ID加索引了,因为。。。如果这个都实现不了那还玩儿啥。

这个执行计划里提示到的dynamic statistics used好像也是新特性?下次研究下。

这时候是没有索引的,默认需要等待15分钟才能看到下次任务启动,为了快速,我把间隔时间修改成3分钟。

查看任务,这个自动索引任务是每三分钟启动一次。(默认15分钟)

任务出现

Oracle自信的给这个任务名字加上了AI。

看一下这个任务的详情:

任务详情

从任务相信可以看到,有一个候选INDEX,创建了一个visible的索引,使用了45088768个字节的空间,以及其他等等。

查看表上的索引,也加上了,还是AI。

AI索引名

查看trace,能在trace中发现完整的Automatic Indexing过程:这里摘录几段说明下:

随后也能验证,CBO开始使用这个INDEX了。

我这个库有BUG,不能使用如下方式出报告:

好不容易把报告搞出来,简单截图几张。

我当时测试的时候,表上是没有正确的统计信息的,假设表上没有统计信息,或者统计信息跟实际差别巨大,那么Automatic Indexing会不会误判?

当我尝试清掉环境,删除这个自动索引的时候:

我即使关闭了这个特性,这个索引同样不能被alter或者drop,假如因这个索引导致性能问题,DBA将束手无策?在没找到删除办法之前,这意味着Oracle的AI系统足够相信自己创建的索引,它来创建,测试和决策,并且绝对不让人的DBA来操作它的索引。但是它却可以删人工创建的未使用的索引。

接着说统计信息的事情,表上无统计信息或者表上统计信息跟实际差别巨大的情况下Automatic Indexing会如何工作:(为了测试,我只好drop purge了表,索引当然跟着没有了,这里可以10046跟一下索引是怎么没的,update ind$的auto标志位,然后delete这一条?)

重建环境:

清空表的统计信息:

打开自动索引特性。重复执行SQL。观察是否新生成索引,是否重新收集了统计信息。

根据测试,统计信息缺失的情况下,Automatic Indexing不会工作。在重新收集统计信息之后,Automatic Indexing开始工作。此外,如果统计信息不准确也不见得肯定可以触发AutoMatic Indexing。所以,表统计信息在Automatic indexing中作用也是非常被依赖的指标。

三步

最后,我找到一个办法,可以一次把AI做的一切事情全部清空,dbms_auto_index_internal.AI_CLEAR,但是这个影响范围太大了。还是没能找到怎么删掉某一个索引的办法。

One thought on “Oracle 19C 新特性之自动索引(Automatic Indexing)测试-Part.1

  1. 最后我发现是可以删除的。哭了。
    SQL> drop index HR.”SYS_AI_6ua5p8jqsj6ts”;
    drop index HR.”SYS_AI_6ua5p8jqsj6ts”
    *
    ERROR at line 1:
    ORA-65532: cannot alter or drop automatically created indexes

    SQL> update ind$ set PROPERTY=0 where obj#=73205;

    1 row updated.

    SQL> drop index HR.”SYS_AI_6ua5p8jqsj6ts”;

    Index dropped.

发表评论