19c自动索引

自动索引是19c中引入的一个新功能，即数据库会自动根据需要来创建和删除索引，帮助系统能更好的运行。

新功能主要做如下事情：

用一张图来表示

img

  • 基于表字段的使用情况，确定可能的自动索引作为候选
  • 创建不可见的索引，并不会被执行计划所引用。索引以SYS_AI前缀命名
  • 测试使用这些不可见索引来执行sql，确保sql的性能得到提升。如果性能确实达到预期，则将这些索引置为可见。如果性能不行，则相关索引会被置为unusable并在之后删除，这些性能未达标的sql之后也不会被考虑使用自动索引
  • 删除unusable的索引

由于这个新特性目前只被限制在exadata上的企业版使用，如果只是为了测试可以开启exadata特性。

1
2
3
alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;

配置

主要是用DBMS_AUTO_INDEX包用来管理自动索引的特性

CDB_AUTO_INDEX_CONFIG视图展示了当前数据库里自动索引的相关配置 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
column parameter_name format a40
column parameter_value format a20

select con_id, parameter_name, parameter_value 
from   cdb_auto_index_config
order by 1, 2;

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ---------------
         1 AUTO_INDEX_COMPRESSION                   OFF
         1 AUTO_INDEX_DEFAULT_TABLESPACE
         1 AUTO_INDEX_MODE                          OFF
         1 AUTO_INDEX_REPORT_RETENTION              373
         1 AUTO_INDEX_RETENTION_FOR_AUTO            373
         1 AUTO_INDEX_RETENTION_FOR_MANUAL
         1 AUTO_INDEX_SCHEMA
         1 AUTO_INDEX_SPACE_BUDGET                  50
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE
         3 AUTO_INDEX_MODE                          OFF
         3 AUTO_INDEX_REPORT_RETENTION              373
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA
         3 AUTO_INDEX_SPACE_BUDGET                  50

如果切换到pdb，则返回的参数略有不同

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> alter session set container=pdb19c;

Session altered.

SQL> select con_id, parameter_name, parameter_value
  2  from   cdb_auto_index_config
  3  order by 1, 2;

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- ---------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE
         3 AUTO_INDEX_MODE                          OFF
         3 AUTO_INDEX_REPORT_RETENTION              373
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA
         3 AUTO_INDEX_SPACE_BUDGET                  50

这里主要介绍部分参数的意思：

  • AUTO_INDEX_MODE ：自动索引的模式

    • IMPLEMENT: 新自动索引会被创建成可见索引，任何已存在的不可见自动索引都会设置成可见的，sql语句在执行的时候会考虑自动索引的情况。
    • REPORT ONLY：新自动索引会被创建成不可见索引，并且不会被sql语句所使用。
    • OFF：会阻止新自动索引的创建和引用，但是这并不会禁用已有的自动索引。

  • AUTO_INDEX_RETENTION_FOR_AUTO： unused的自动索引保存多长时间之后被删除，默认373天。

  • AUTO_INDEX_COMPRESSION：

    • ON：对自动索引开启高级索引压缩
    • OFF：对自动索引关闭压缩

开启/关闭 自动索引

自动索引由DBMS_AUTO_INDEX包的CONFIGURE过程来进行配置。

1
2
3
exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');

自动索引的表空间

默认情况下自动索引都创建在默认永久表空间当中，或者也可以指定某个表空间，通过参数AUTO_INDEX_DEFAULT_TABLESPACE控制

1
2
3
4
5
alter session set container = pdb19c;

create tablespace ts_autoindexs datafile size 100m autoextend on next 100m;

exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','TS_AUTOINDEXES');

还原成默认

1
Exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

用户级别控制

当自动索引启用后，默认所有的schema下的表都会被考虑是否创建自动索引，但是可以通过修改AUTO_INDEX_SCHEMA参数来维护包含或者排除清单，来决定哪些用户适用或者禁止。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE);

PL/SQL procedure successfully completed.


    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- --------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE
         3 AUTO_INDEX_MODE                          OFF
         3 AUTO_INDEX_REPORT_RETENTION              373
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA                        schema IN (TEST)
         3 AUTO_INDEX_SPACE_BUDGET                  50

恢复成默认则使用NULL值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => TRUE);

PL/SQL procedure successfully completed.

SQL> select con_id, parameter_name, parameter_value
  2  from   cdb_auto_index_config
  3  order by 1, 2;

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- --------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE
         3 AUTO_INDEX_MODE                          OFF
         3 AUTO_INDEX_REPORT_RETENTION              373
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA
         3 AUTO_INDEX_SPACE_BUDGET                  50

如果要设置禁用的SCHEMA，则allow置为FALSE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => FALSE);

select con_id, parameter_name, parameter_value
from   cdb_auto_index_config
order by 1, 2;

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- --------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE
         3 AUTO_INDEX_MODE                          OFF
         3 AUTO_INDEX_REPORT_RETENTION              373
         3 AUTO_INDEX_RETENTION_FOR_AUTO            373
         3 AUTO_INDEX_RETENTION_FOR_MANUAL
         3 AUTO_INDEX_SCHEMA                        schema NOT IN (TEST)
         3 AUTO_INDEX_SPACE_BUDGET                  50

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SQL> create table t1 as select object_id,object_name from dba_objects;

Table created.

# 多次通过object_id来进行查询
SQL> select * from t1 where object_id=9;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
         9 I_FILE#_BLOCK#
         
# 需要等待一段时间以后

SQL_ID  976r7pmsmvjk5, child number 0
-------------------------------------
select * from t1 where object_id=20

Plan hash value: 2193163512

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | E-Rows |E-Bytes| Cost (%CPU)|
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |        |       |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1                   |      1 |    39 |     1   (0)|
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_5acfcndg6cqp6 |      1 |       |     1   (0)|
--------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=20)

查看生成的自动索引以及状态

1
2
3
4
5
SQL> select index_name,status,visibility from dba_indexes where auto = 'YES';

INDEX_NAME                     STATUS   VISIBILIT
------------------------------ -------- ---------
SYS_AI_5acfcndg6cqp6           VALID    VISIBLE

删除自动索引

根据名称删除，并禁止重新创建。注意名称要用双引号

1
2
3
4
5
6
7
begin
  dbms_auto_index.drop_auto_indexes(
    owner          => 'XB',
    index_name     => '"SYS_AI_5acfcndg6cqp6"',
    allow_recreate => false);
end;
/

删除某个用户下的所有自动索引，但是允许重新被创建

1
2
3
4
5
6
7
begin
  dbms_auto_index.drop_auto_indexes(
    owner          => 'XB',
    index_name     => null,
    allow_recreate => true);
end;
/

相关视图

有好几个自动索引相关的视图

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> select view_name
  2  from   dba_views
  3  where  view_name like 'DBA_AUTO_INDEX%'
  4  order by 1;

VIEW_NAME
----------------------------------------------
DBA_AUTO_INDEX_CONFIG
DBA_AUTO_INDEX_EXECUTIONS
DBA_AUTO_INDEX_IND_ACTIONS
DBA_AUTO_INDEX_SQL_ACTIONS
DBA_AUTO_INDEX_STATISTICS
DBA_AUTO_INDEX_VERIFICATIONS
  • dba_auto_index_executions：执行自动索引任务的历史记录
  • dba_auto_index_statistics：与自动索引有关的统计信息
  • dba_auto_index_ind_actions：在自动索引上执行的操作
  • dba_auto_index_sql_actions：在SQL语句上执行的操作以验证自动索引
  • dba_auto_index_config：自动索引相关配置
  • dba_auto_index_verifications：有关plan_hash_value，auto_index_buffer_gets等的统计信息。

查看所有的自动索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
column owner format a30
column index_name format a30
column table_owner format a30
column table_name format a30

select owner,
       index_name,
       index_type,
       table_owner,
       table_name
       table_type
from   dba_indexes
where  auto = 'YES'
order by owner, index_name;

活动报告

DBMS_AUTO_INDEX包含有两个报告函数

1
2
3
4
5
6
7
8
9
10
11
12
13
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 'TEXT',
   section         IN  VARCHAR2 DEFAULT 'ALL',
   level           IN  VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;

REPORT_ACTIVITY函数主要展示一段时间内的活动情况，默认是最近一天内。

REPORT_LAST_ACTIVITY函数报告最近一个自动索引的操作。

两者都可以通过一些参数来调整输出

  • TYPE:可选格式TEXT, HTML, XML
  • SECTION：可选项（SUMMARY, INDEX_DETAILS, VERIFICATION_DETAILS, ERRORS, ALL），也可以用+和-符号来进行连接组合表示哪些需要哪些排除，比如’SUMMARY +ERRORS’或者’ALL -ERRORS’
  • LEVEL：可选项（BASIC, TYPICAL, ALL）

比如查看我之前生成的自动索引相关报告：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
SQL> select dbms_auto_index.report_activity() from dual;

DBMS_AUTO_INDEX.REPORT_ACTIVITY()
-------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 29-AUG-2024 14:39:03
 Activity end                 : 30-AUG-2024 14:39:03
 Executions completed         : 5
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 1
 Indexes created (visible / invisible)         : 1 (1 / 0)
 Space used (visible / invisible)              : 2.1 MB (2.1 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 8
 SQL statements improved (improvement factor)  : 8 (452.4x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 452.4x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0
 Space used        : 0 B
 Unusable indexes  : 0
-------------------------------------------------------------------------------

INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
--------------------------------------------------------------------------
| Owner | Table | Index                | Key       | Type   | Properties |
--------------------------------------------------------------------------
| XB    | T1    | SYS_AI_5acfcndg6cqp6 | OBJECT_ID | B-TREE | NONE       |
--------------------------------------------------------------------------
-------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : XB
 SQL ID               : 0y6kdntmqahh8
 SQL Text             : select * from t1 where object_id=15
 Improvement Factor   : 452x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  9470                          276
 CPU Time (s):      6299                          223
 Buffer Gets:       1808                          3
 Optimizer Cost:    128                           1
 Disk Reads:        0                             4
 Direct Writes:     0                             0
 Rows Processed:    4                             1
 Executions:        4                             1


PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
 Plan Hash Value  : 3617692013

--------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------
|  0 | SELECT STATEMENT    |      |      |       |  128 |          |
|  1 |   TABLE ACCESS FULL | T1   |    1 |    39 |  128 | 00:00:01 |
--------------------------------------------------------------------

- With Auto Indexes
-----------------------------
 Plan Hash Value  : 2193163512

-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |    1 |    39 |    1 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | T1                   |    1 |    39 |    1 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_5acfcndg6cqp6 |    1 |       |    1 | 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("OBJECT_ID"=15)


Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
Oracle
#19c
19c自动索引
https://www.xbdba.com/2024/08/29/19c-automatic-indexing/
作者
xbdba
发布于
2024年8月29日
许可协议