19c自动索引

原创肖杰 2022-12-16

579

新特性描述

自动索引基于手动SQL调优的常用方法，它不断评估执行的SQL和基础表，以确定要创建哪些索引以及可能删除哪些索引。它通过专家系统完成此任务，该系统验证索引可能做出的改进，并在创建之后验证所做的假设。然后它使用强化学习来确保它不再犯同样的错误。最重要的是，随着数据模型和访问路径的变化，Oracle数据库19c能够随着时间的推移进行调整。

Automatic Indexing Process(自动索引过程)以后台服务进程每隔15分钟运行一次，并执行如下任务：

识别自动索引候选项

通过捕获SQL历史到SQL repository（包括SQL，执行计划，绑定变量，统计信息等），识别SQL语句中用到列的使用情况来标识自动索引候选项。
为自动索引候选项创建invisible的自动索引

Invisible Indexes是11g中提出的新功能，缺省对优化器是不可见的，也就是说不会影响到用户既有的SQL语句。
根据SQL语句验证不可见的自动索引

如果通过使用这些自动索引提高了SQL语句的性能，那么会将这些索引配置为可见索引，以便在SQL语句中使用它们。
如果使用这些索引不能提高SQL语句的性能，则将这些索引置为unusable的索引，并将对应的SQL语句列入“黑名单”。unusable的索引稍后会被自动索引过程删除。被列入“黑名单”的SQL语句将来不允许使用自动索引。
清除unused auto indexes

对于长时间不使用的auto indexes会自动进行清除工作。缺省是373天，可以使用DBMS_AUTO_INDEX.CONFIGURE过程来配置数据库中保留未使用的自动索引的时间段。

自动索引相关配置

1、启用和禁用Automatic Indexing特性

启用自动索引，并将任何新的自动索引创建为可见索引(visible indexes)，以便在SQL语句中使用
```
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
复制
```
启用自动索引，但将任何新的自动索引创建为不可见索引(invisible indexes)，所以不能在SQL语句中使用
```
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
复制
```
禁用自动索引，这样就不会创建新的自动索引，并禁用现有的自动索引。这也是缺省模式
```
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
复制
```

2、指定哪些Schemas可以使用Automatic Indexing

当在上一步启用Automatic Indexing特性后，缺省情况下所有的Schemas都可以使用Automatic Indexing。管理员可以根据需要允许哪些schema使用自动索引。

比如：以下语句将SH和HR用户添加到排除列表中，这样SH和HR用户就不能使用自动索引:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', FALSE);
复制

后期如果又想让某个用户使用自动索引，比如HR，可以通过如下语句来设置

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);
复制

恢复到缺省（也就是所有Schema都可以使用自动索引）

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);
复制

3、为unused auto indexes指定保留期

下面的语句将未使用的自动索引的保留期设置为90天。

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', ' 90');
复制

下面的语句将未使用的自动索引的保留期重置为默认值373天。

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);
复制

4、为unused 非自动索引（non-auto indexes）指定保留期

就是为我们手动创建的unused的索引指定一个保留期，缺省情况下，Automatic Indexing Process(自动索引过程)不会删除unused手动创建的索引。

通过如下过程，指定一个unused手动索引保留期，比如60天，那么到期后，将会被Automatic Indexing Process(自动索引过程)清除。

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '60');
复制

恢复到默认值（也就是不给unused 手动索引指定保留期，Automatic Indexing Process不会删除这些索引）

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO',NULL); 
复制

5、为automatic indexing logs指定保留期

缺省是31天，如下示例表示保留60天

```sql
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', '60');
```
复制

恢复到缺省

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', NULL);
复制

*注意*：Automatic indexing reports的生成依赖于automatic indexing logs。所以，当过了automatic indexing logs的保留期后，相应的automatic indexing reports就不能生成了。

6、指定一个表空间存放Auto Indexes

缺省情况下，使用当前数据库的缺省持久表空间。可以通过如果配置指定为其他表空间：

```sql
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'MYAUTOTBS');
```
复制

*说明*：对于使用缺省持久表空间存放Auto Indexes的情况，可以通过类似如下过程来指定一个空间限额（可以使用到20%），缺省是50%：

    ```sql
    SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', '20');
    ```
复制

7、指定一个临时表空间存放临时Auto Indexes结构

缺省情况下，使用当前数据库的缺省TEMP表空间。可以通过如果配置指定为其他表空间：

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TEMP_TABLESPACE', 'MYAUTOTBS_TEMP');
复制

生成自动索引报告

## 生产指定时间段内自动索引情况报告
set serveroutput on

  declare 
       report clob := null;
   begin
       report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(
                   activity_start => TO_TIMESTAMP('2019-02-17', 'YYYY-MM-DD'),
                    activity_end => TO_TIMESTAMP('2019-02-17', 'YYYY-MM-DD'),
                              type => 'HTML',          --支持TEXT、HTML、XML 3中格式，缺省是TEXT
                          section => 'SUMMARY',   
                              level => 'BASIC');
      dbms_output.put_line(report);
   end;
  /
  
## 生成最近一次自动索引情况报告
set serveroutput on
      declare
           report clob := null;
       begin
           report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(
                                   type => 'HTML',
                               section => 'SUMMARY +INDEX_DETAILS +ERRORS',
                                   level => 'BASIC');
          dbms_output.put_line(report);
      end;
     /
复制

自动索引相关数据字典

DBA_AUTO_INDEX_CONFIG                     --19.1新增视图，描述当前自动索引的配置
DBA_INDEXES/ALL_INDEXES/USER_INDEXES      --新增加的AUTO列标识是自动索引(YES)还是手动索引(NO)
DBA_AUTO_INDEX_EXECUTIONS                 --显示历史自动索引任务执行
DBA_AUTO_INDEX_STATISTICS                 --显示与自动索引相关的统计信息
DBA_AUTO_INDEX_IND_ACTIONS                --显示在自动索引上执行的操作
DBA_AUTO_INDEX_SQL_ACTIONS                --显示在SQL上执行的验证自动索引的操作
复制

新特性测试

###################################################################################################
## 由于自动索引是exadata功能，普通环境修改"_exadata_feature_on"为true可以达到测试目的，但别在生产环境使用。####
###################################################################################################
SQL> alter system set "_exadata_feature_on"=true scope=spfile;

System altered.

SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1795159104 bytes
Fixed Size                  8897600 bytes
Variable Size             436207616 bytes
Database Buffers         1342177280 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.


## 构建测试表
SQL> create table auto_index_tab as select * from dba_objects;

Table created.

## 启用自动索引，并将任何新的自动索引创建为**可见**索引(*visible* indexes)，以便在SQL语句中使用
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

PL/SQL procedure successfully completed.

SQL> select * from dba_auto_index_config;

PARAMETER_NAME                                     PARAMETER_VALUE LAST_MODIFIED                  MODIFIED_B
-------------------------------------------------- --------------- ------------------------------ ------
AUTO_INDEX_COMPRESSION                             OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE                                    IMPLEMENT       06-JAN-22 03.16.00.000000 PM   DEVIN
AUTO_INDEX_REPORT_RETENTION                        31
AUTO_INDEX_RETENTION_FOR_AUTO                      373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET                            50

## SQL测试
SQL> select count(*) from auto_index_tab where object_id=1;
  COUNT(*)
----------
         0

SQL> select count(*) from auto_index_tab where object_id=10;
  COUNT(*)
----------
         1

SQL> select count(*) from auto_index_tab where object_id=11;
  COUNT(*)
----------
         1

....
..
.

## 检查自动索引执行情况（15分钟后，因为自动索引进程15分钟运行一次）
SQL> select * from dba_auto_index_executions;

EXECUTION_NAME                 EXECUTION EXECUTION ERROR_MESS STATUS
------------------------------ --------- --------- ---------- -----------
SYS_AI_2022-01-06/15:47:01     06-JAN-22 06-JAN-22            COMPLETED
  ### 可以看到自动索引任务已经完成

## 检查自动索引相关统计信息
SQL> select * from dba_auto_index_statistics where execution_name='SYS_AI_2022-01-06/15:47:01';

EXECUTION_NAME                 STAT_NAME                          VALUE
------------------------------ ----------------------------- ----------
SYS_AI_2022-01-06/15:47:01     Index candidates                       1
SYS_AI_2022-01-06/15:47:01     Indexes created (visible)              1
SYS_AI_2022-01-06/15:47:01     Indexes created (invisible)            0
SYS_AI_2022-01-06/15:47:01     Indexes dropped                        0
SYS_AI_2022-01-06/15:47:01     Space used in bytes              2097152
SYS_AI_2022-01-06/15:47:01     Space reclaimed in bytes               0
SYS_AI_2022-01-06/15:47:01     SQL statements verified                3
SYS_AI_2022-01-06/15:47:01     SQL statements improved                3
SYS_AI_2022-01-06/15:47:01     SQL statements managed by SPM          0
SYS_AI_2022-01-06/15:47:01     SQL plan baselines created             0
SYS_AI_2022-01-06/15:47:01     Improvement percentage             99.93

11 rows selected.

## 检查自动索引任务执行的具体内容
SQL> select execution_name,index_name,index_owner,table_name,command from dba_auto_index_ind_actions where execution_name='SYS_AI_2022-01-06/15:47:01';

EXECUTION_NAME                 INDEX_NAME           INDEX_OWNE TABLE_NAME           COMMAND
------------------------------ -------------------- ---------- -------------------- ----------------------------------------------------------------
SYS_AI_2022-01-06/15:47:01     SYS_AI_5h1zfmmwwchjt DEVIN      AUTO_INDEX_TAB       CREATE INDEX
SYS_AI_2022-01-06/15:47:01     SYS_AI_5h1zfmmwwchjt DEVIN      AUTO_INDEX_TAB       REBUILD INDEX
SYS_AI_2022-01-06/15:47:01     SYS_AI_5h1zfmmwwchjt DEVIN      AUTO_INDEX_TAB       ALTER INDEX VISIBLE

## 检查测试表上是否真的创建了索引
SQL> select OWNER,INDEX_NAME,TABLE_NAME,VISIBILITY from dba_indexes where table_name='AUTO_INDEX_TAB';

OWNER      INDEX_NAME           TABLE_NAME           VISIBILIT
---------- -------------------- -------------------- ---------
DEVIN      SYS_AI_5h1zfmmwwchjt AUTO_INDEX_TAB       VISIBLE

## 可以看到确实已经自动创建了一个索引，再次检查SQL执行计划
SQL> set autot trace
SQL> select count(*) from auto_index_tab where object_id=10;


Execution Plan
----------------------------------------------------------
Plan hash value: 2233821123

------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                      |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| SYS_AI_5h1zfmmwwchjt |     1 |     5 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=10)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        550  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

## 生成html报告
略
复制

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者