Oracle 19c自动化索引(Automatic Indexing)

MOS概述:https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-19B6C345-FDCA-4D7F-A93A-341555CFFC1A
自动化索引依据应用负载的变化自动/动态地进行索引的管理任务,比如创建(create index)、重构(rebuild index)和删除(drop index),从而提高数据库性能。

一 自动索引特性使Oracle数据库中的索引管理任务自动化。自动索引根据应用程序工作负载的变化自动创建、重新构建和删除数据库中的索引,从而提高数据库性能。自动管理的索引称为自动索引。
索引结构是数据库性能的一个基本特性。索引对于OLTP应用程序至关重要,这些应用程序使用大型数据集,每天运行数百万条SQL语句。索引对于数据仓库应用程序也很关键,这些应用程序通常从非常大的表中查询相对少量的数据。如果在应用程序工作负载发生变化时不更新索引,则现有索引可能会导致数据库性能显著下降。
自动索引通过根据应用程序工作负载的变化自动、动态地管理Oracle数据库中的索引来提高数据库性能。
自动索引提供以下功能:
以预定义的时间间隔周期性地在后台运行自动索引过程。
分析应用程序工作负载,并相应地创建新的索引并删除现有性能不佳的索引,以提高数据库性能。
重新构建由于表分区维护操作(如ALTER table MOVE)而标记为不可用的索引。
提供PL/SQL api,用于配置数据库中的自动索引,生成与自动索引操作相关的报表。

二 自动索引是如何工作的
本节介绍自动索引的工作原理。
自动索引进程每15分钟在后台运行一次,执行如下操作:

1.识别候选的自动索引
根据SQL语句中表列的使用情况确定自动索引候选项。
确保表统计信息是最新的。没有统计信息的表不考虑自动索引。如果实时统计信息不可用,则不考虑使用陈旧统计信息的表进行自动索引。

2.为候选的自动索引创建不可见的自动索引
候选自动索引被创建为不可见的自动索引,也就是说,这些自动索引不能在SQL语句中使用。
自动索引可以是单列的,也可以是多列的。它们被认为有下列用途:
表列(包括虚拟列)
分区表和非分区表

3.对SQL语句验证不可见的自动索引
如果通过使用这些索引来提高SQL语句的性能,则将索引配置为可见索引,以便可以在SQL语句中使用它们。
如果使用这些索引不能提高SQL语句的性能,则将索引配置为不可用索引,并将SQL语句列入黑名单。不可用的索引稍后由自动索引过程删除。被列入黑名单的SQL语句以后不允许使用自动索引。
注意:自动索引不能被数据库中第一次运行的SQL语句使用。

https://www.cndba.cn/hbhe0316/article/22185

4.删除未使用的自动索引
删除长时间不使用的自动索引。

三、开启_exadata_feature_on
[oracle@db01 ~]$ sqlplus c##hbhe/wwwwww

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 16 22:00:30 2021
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Sun Nov 07 2021 14:25:59 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL> alter system set "_exadata_feature_on"=true scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 2516581448 bytes
Fixed Size                  9141320 bytes
Variable Size            1375731712 bytes
Database Buffers         1124073472 bytes
Redo Buffers                7634944 bytes
Database mounted.
Database opened.

自动化索引(Automatic Indexing)是动作通过DBMS_AUTO_INDEX.CONFIGURE过程来完成。 该函数的的说明可以参考MOS:

1 AUTO_INDEX_MODE: 启用、禁用自动索引
2 AUTO_INDEX_SCHEMA: 指定用户启用自动索引
3 AUTO_INDEX_RETENTION_FOR_AUTO: 指定未使用索引的保留时间
4 AUTO_INDEX_DEFAULT_TABLESPACE:指定索引的表空间
5 REPORT_ACTIVITY/REPORT_LAST_ACTIVITY: 生成自动化索引报告
6 DROP_AUTO_INDEXES/DROP_SECONDARY_INDEXES: 删除自动索引
7 AUTO_INDEX_REPORT_RETENTION: 为自动索引logs 指定保留期
8 AUTO_INDEX_COMPRESSION: 索引压缩

查看自动索引的后台任务,这些任务每15分钟运行一次:

启用和禁用Automatic Indexing特性
启用自动索引:https://www.cndba.cn/hbhe0316/article/22185

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’IMPLEMENT’);

启用自动索引,但只作为不可见自动索引,无法被SQL所使用:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’REPORT ONLY’);

禁用自动索引:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’OFF’);

指定特定用户启用Automatic Indexing
缺省情况下所有的Schemas都可以使用Automatic Indexing。也可以使用如下参数指定特定用户。

https://www.cndba.cn/hbhe0316/article/22185

除了c##hbhe用户之外,其他用户都可以使用自动索引:https://www.cndba.cn/hbhe0316/article/22185

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'hbhe', FALSE);

恢复hbhe用户使用自动索引:https://www.cndba.cn/hbhe0316/article/22185

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, ‘hbhe’, NULL);

只有hbhe用户可以使用自动索引:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, ‘hbhe’, TRUE);

所有用户都可以使用自动索引:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, NULL, TRUE);

配置手工创建的索引,未使用60天时删除(未配置时默认永不删除)https://www.cndba.cn/hbhe0316/article/22185

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_MANUAL’, ‘ 60’);

配置手工创建的索引永不删除(未配置时默认永不删除)

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_MANUAL’, NULL);

配置自动索引,在未使用60天后删除(默认373天)

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO',  '60');

将未使用的自动索引的保留期重置为默认值373天。https://www.cndba.cn/hbhe0316/article/22185

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

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

对于使用缺省持久表空间存放Auto Indexes的情况,可以通过类似如下过程来指定一个空间限额(可以使用到20%),缺省是50%:https://www.cndba.cn/hbhe0316/article/22185https://www.cndba.cn/hbhe0316/article/22185

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', '60');

四、启用自动化索引

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'c##hbhe', allow => TRUE);

PL/SQL procedure successfully completed.

SQL> COLUMN parameter_name FORMAT A40
SQL> COLUMN parameter_value FORMAT A20
SQL> SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;

    CON_ID PARAMETER_NAME                           PARAMETER_VALUE
---------- ---------------------------------------- --------------------
         1 AUTO_INDEX_SCHEMA                        schema IN (C##HBHE)
         1 AUTO_INDEX_DEFAULT_TABLESPACE            TBS02
         1 AUTO_INDEX_SPACE_BUDGET                  50
         1 AUTO_INDEX_RETENTION_FOR_AUTO            373
         1 AUTO_INDEX_RETENTION_FOR_MANUAL
         1 AUTO_INDEX_MODE                          IMPLEMENT
         1 AUTO_INDEX_COMPRESSION                   OFF
         1 AUTO_INDEX_REPORT_RETENTION              373

8 rows selected.

SQL> create tablespace TBS02 datafile '/oradata/ORCL/TBS02.dbf' size 10240m;

Tablespace created.

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'TBS02');

PL/SQL procedure successfully completed.

SQL> create table c##hbhe.test_auto_index as select rownum id,t.* from dba_objects t;

Table created.

SQL> declare
a varchar2(2000) := '';
begin
for x in 1.. 10000 loop
select object_name into a from c##hbhe.test_auto_index where id=x;
end loop;
end;  
/

PL/SQL procedure successfully completed.

自动索引的周期默认是15分钟,15分钟之后我们查看是否创建成功

SQL> select parameter_name,PARAMETER_VALUE from SYS.SMB$CONFIG where parameter_name like '%AUTO_INDEX_TASK_INTERVAL%';

PARAMETER_NAME                           PARAMETER_VALUE
---------------------------------------- ---------------
_AUTO_INDEX_TASK_INTERVAL                900
SQL> select parameter_name,PARAMETER_VALUE from SYS.SMB$CONFIG where parameter_name like '%AUTO_INDEX_TASK_INTERVAL%';

PARAMETER_NAME                           PARAMETER_VALUE
---------------------------------------- ---------------
_AUTO_INDEX_TASK_INTERVAL                900

查看是否走自动创建索引,其中 SYSAI为自动创建的索引。

https://www.cndba.cn/hbhe0316/article/22185
SQL> SELECT OWNER,INDEX_TYPE,INDEX_NAME,TABLE_NAME,TABLE_OWNER FROM DBA_INDEXES WHERE AUTO='YES' ORDER BY OWNER,INDEX_NAME;

OWNER                                                                                                                                                                                                                                       INDEX_TYPE                                                                         INDEX_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME                                                                                                                                                                                                                                  TABLE_OWNER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C##HBHE                                                                                                                                                                                                                                     NORMAL                                                                     SYS_AI_7pmgd01b25cr1
TEST_AUTO_INDEX                                                                                                                                                                                                                             C##HBHE



Execution Plan
----------------------------------------------------------
Plan hash value: 1171994700

---------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |    28 |  5376 |   684   (1)| 00:00:01 |
|   1 |  SORT ORDER BY                         |                |    28 |  5376 |   684   (1)| 00:00:01 |
|*  2 |   FILTER                               |                |       |       |            |          |
|*  3 |    HASH JOIN                           |                |    29 |  5568 |   683   (1)| 00:00:01 |
|   4 |     NESTED LOOPS                       |                |    29 |  5046 |   679   (1)| 00:00:01 |
|   5 |      NESTED LOOPS                      |                |    29 |  5046 |   679   (1)| 00:00:01 |
|*  6 |       HASH JOIN OUTER                  |                |    29 |  3683 |   621   (1)| 00:00:01 |
|   7 |        NESTED LOOPS OUTER              |                |    29 |  3567 |   619   (0)| 00:00:01 |
|*  8 |         HASH JOIN OUTER                |                |    29 |  3335 |   590   (0)| 00:00:01 |
|   9 |          NESTED LOOPS OUTER            |                |    29 |  3248 |   583   (0)| 00:00:01 |
|* 10 |           HASH JOIN                    |                |    29 |  2929 |   554   (0)| 00:00:01 |
|  11 |            NESTED LOOPS                |                |    29 |  2407 |   550   (0)| 00:00:01 |
|  12 |             NESTED LOOPS               |                |    29 |  2407 |   550   (0)| 00:00:01 |
|* 13 |              TABLE ACCESS FULL         | IND$           |    29 |   986 |   492   (0)| 00:00:01 |
|* 14 |              INDEX RANGE SCAN          | I_OBJ1         |     1 |       |     1   (0)| 00:00:01 |
|* 15 |             TABLE ACCESS BY INDEX ROWID| OBJ$           |     1 |    49 |     2   (0)| 00:00:01 |
|  16 |            TABLE ACCESS FULL           | USER$          |   130 |  2340 |     4   (0)| 00:00:01 |
|  17 |           TABLE ACCESS CLUSTER         | SEG$           |     1 |    11 |     1   (0)| 00:00:01 |
|* 18 |            INDEX UNIQUE SCAN           | I_FILE#_BLOCK# |     1 |       |     0   (0)| 00:00:01 |
|  19 |          TABLE ACCESS FULL             | TS$            |    20 |    60 |     7   (0)| 00:00:01 |
|* 20 |         INDEX RANGE SCAN               | I_OBJ1         |     1 |     8 |     1   (0)| 00:00:01 |
|  21 |        INDEX FULL SCAN                 | I_USER2        |   130 |   520 |     1   (0)| 00:00:01 |
|* 22 |       INDEX RANGE SCAN                 | I_OBJ1         |     1 |       |     1   (0)| 00:00:01 |
|  23 |      TABLE ACCESS BY INDEX ROWID       | OBJ$           |     1 |    47 |     2   (0)| 00:00:01 |
|  24 |     TABLE ACCESS FULL                  | USER$          |   130 |  2340 |     4   (0)| 00:00:01 |
|* 25 |    TABLE ACCESS CLUSTER                | TAB$           |     1 |    13 |     2   (0)| 00:00:01 |
|* 26 |     INDEX UNIQUE SCAN                  | I_OBJ#         |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   2 - filter("IO"."TYPE#"<>2 OR  (SELECT 1 FROM "SYS"."TAB$" "T" WHERE "T"."OBJ#"=:B1 AND
              BITAND("T"."PROPERTY",36893488147419103232)=0)=1)
   3 - access("IO"."OWNER#"="IU"."USER#")
   6 - access("ITO"."OWNER#"="ITU"."USER#"(+))
   8 - access("I"."TS#"="TS"."TS#"(+))
  10 - access("U"."USER#"="O"."OWNER#")
  13 - filter(DECODE(BITAND("I"."PROPERTY",8),8,'YES','NO')='YES' AND BITAND("I"."FLAGS",4096)=0)
  14 - access("O"."OBJ#"="I"."OBJ#")
  15 - filter(BITAND("O"."FLAGS",128)=0)
  18 - access("I"."TS#"="S"."TS#"(+) AND "I"."FILE#"="S"."FILE#"(+) AND
              "I"."BLOCK#"="S"."BLOCK#"(+))
  20 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+))
  22 - access("I"."BO#"="IO"."OBJ#")
  25 - filter(BITAND("T"."PROPERTY",36893488147419103232)=0)
  26 - access("T"."OBJ#"=:B1)

Note
-----
   - this is an adaptive plan


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

版权声明:本文为博主原创文章,未经博主允许不得转载。

Linux,oracle

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值