19c新特性之自动索引

时间: 2020-02-15 17:55:15 | 作者: ohsdba |
如非注明，本站文章皆为原创。欢迎转载，转载时请注明出处和作者信息。

添加索引来解决性能问题是一把双刃剑，它可能会提高某些查询的性能，但肯定会减慢INSERT语句的速度，并可能对UPDATE和DELETE语句产生负面影响，有时足以抵消提升的性能。

因此，创建索引时，要非常仔细地分析对所有查询的影响。想要创建合适的索引，需要我们对数据模型、应用程序、数据的密度，还要和开发人员有深入的沟通，虽然我们可以借助Oracle数据库的SQL Performance Analyze，Access Advisor，Tuning Advisor，Index Advisor, Partition Advisor, In-Memory Advisor等工具来创建，但还要综合分析各项指标，这对DBA来说，是极具挑战的。当使用新索引的SQL语句导致性能问题时，DBA会受到指责，而DBA则反过来责怪开发人员首先编写性能不佳的查询。由于更改查询语句或者表结构变更等，有时不再需要以前创建的索引。DBA需要检查索引的使用情况，如果索引未使用，则将其删除，这对DBA来说也是一项耗时的工作，就更不用说风险很大的工作了。

在数据库19c中，Oracle引入了自动索引。自动索引功能在Oracle数据库中自动实现索引管理任务，她基于应用程序负载的变化，自动索引将自动创建、重建和删除数据库中的索引，从而提高数据库性能。自动管理的索引称为自动索引。对于DBA来说，绝对是个神奇。该特性像数据库的DBA一样，不过她会自动评估新索引和现有索引，如果需要的话，创建新的索引，并且在不再需要时删除它们。该特性同时考虑了单列索引和多列索引，它涵盖了数据库中通常使用的大多数索引，在该版本中，只支持btree索引。重要的是，它一周7天，每天24小时工作，不累，不请病假，也不要求加薪

自动索引提供以下功能
1.在预先定义的时间间隔周期性地在后台运行自动索引过程
2.分析应用程序工作负载，并相应地创建新索引、删除现有的性能表现不好的索引以提高数据库性能
3.重建由于表分区维护操作（如ALTER table MOVE）而标记为不可用的索引
4.提供PL/SQL API，用于配置数据库中的自动索引，并生成与自动索引操作相关的报表
注意：当前版本自动索引支持local B-tree索引，包含分区和非分区表，不支持临时表。其他；类型暂不支持
自动索引在后台每隔15分钟运行一次，并执行如下任务
1.根据表列的使用情况确定潜在的自动索引,这些潜在的索引，我们称之为“候选索引”
2.首先将自动索引(索引名以SYS_AI开头)创建为不可见的索引，因此它们不会在执行计划中立即使用。
3.针对SQL语句测试不可见的自动索引，以确保它们能提供更好的性能。如果它们能提高性能，那么它们就会变得可见。如果性能没有改善，相关的自动索引将被标记为不可用，然后删除。针对失败的自动索引测试的SQL语句将被列入黑名单，以后不会考虑自动索引。优化器在第一次对数据库运行SQL时不会考虑自动索引
4.删除未使用索引
自动索引的参数
select parameter_name,parameter_value from dba_auto_index_config order by 1, 2;

SQL> select parameter_name,parameter_value from dba_auto_index_config order by 1, 2;
PARAMETER_NAME                           PARAMETER_VALUE
---------------------------------------- ---------------
AUTO_INDEX_COMPRESSION                   OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE                          OFF
AUTO_INDEX_REPORT_RETENTION              31
AUTO_INDEX_RETENTION_FOR_AUTO            373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET                  50
8 rows selected.
SQL>

打开自动索引功能
自动索引是通过DBMS_AUTO_INDEX包的存储过程CONFIGURE来配置的，开关是由AUTO_INDEX_MODE的属性控制的，有三种模式：

IMPLEMENT:打开自动索引，对性能有提升的自动索引才可见，然后供优化器使用。

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

REPORT ONLY:打开自动索引，但新创建的自动索引不可见。

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

OFF:关闭自动索引功能。
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
自动索引相关的配置选项

AUTO_INDEX_COMPRESSION：

用于启用和禁用自动索引的高级索引压缩。支持的值包括：OFF和ON，默认值是OFF

AUTO_INDEX_DEFAULT_TABLESPACE：

用来指定自动索引创建时默认使用的表空间，比如通过下面的命令，将自动索引的默认表空间指定为IND_AI_TBS。

EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_DEFAULT_TABLESPACE', 'IND_AI_TBS');

如果我们执行下面的命令，会将自动索引的表空间设置为使用默认的表空间
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

AUTO_INDEX_REPORT_RETENTION：

自动索引日志在数据库中保留的天数。当基于这些日志生成自动索引报告时，自动索引报告不能生成超过留存指定值的周期。默认值为31天。

AUTO_INDEX_RETENTION_FOR_AUTO：

将未使用的自动索引保留在数据库中的天数，之后将其删除。默认值为373天。

AUTO_INDEX_RETENTION_FOR_MANUAL：

将未使用的手动创建的索引（非自动索引）保留在数据库中的天数，之后将其删除。当将其设置为NULL时，手动创建的索引不会被自动索引过程删除。默认值为NULL。

AUTO_INDEX_SPACE_BUDGET：

用于自动索引存储的默认永久表空间的百分比。当使用AUTO_INDEX_DEFAULT_TABLESPACE参数指定自定义表空间时，该参数将被忽略。

AUTO_INDEX_SCHEMA：

默认情况下所有的schema都可以使用自动索引

   下面这两条命令，是不允许HR和SH使用自动索引
      EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', FALSE);
      EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);
   下面这两条命令，是只允许HR和SH使用自动索引
      EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', TRUE);
      EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', TRUE);
   要恢复默认值，请使用下面的命令
      EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'NULL', TRUE);
生成Automatic Indexing报告
可以通过DBMS_AUTO_INDEX包的REPORT_ACTIVITY或REPORT_LAST_ACTIVITY函数生成自动索引的报告，支持TEXT、HTML、XML等3种格式，默认是TEXT
自动索引常用的视图
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

常见错误ORA-40216

请参考ORA-40216 When Using Auto Index Feature (Doc ID 2570076.1)。这个特性仅限于Exadata，在非Exadata环境启用会出现下面的错误。
ORA-40216: feature not supported
ORA-6512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-6512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180
ORA-6512: at "SYS.DBMS_AUTO_INDEX", line 283

ORA-6512: at line 1

测试自动索引
1.由于该特性只能在Exadata上使用，要模拟Exadata的特性，我们可以通过设置参数来实现

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

2.在可插拔数据库ppdb01中创建用户和表空间

  SQL> alter session set container=ppdb01;
  Session altered.
  SQL> alter database open;
  Database altered.
  SQL>
  SQL> show pdbs
      CON_ID CON_NAME                       OPEN MODE  RESTRICTED
  ---------- ------------------------------ ---------- ----------
           3 PPDB01                         READ WRITE NO
  SQL> create tablespace ind_ai_tbs datafile '+DATA' size 200M;
  Tablespace created.
  SQL> create user ohsdba identified by oracle;
  User created.
  SQL> grant dba to ohsdba;
  Grant succeeded.

自动索引默认参数

  SQL> col PARAMETER_NAME for a40
  SQL> col PARAMETER_VALUE for a40
  SQL> select parameter_name,parameter_value from dba_auto_index_config order by 1, 2;
  PARAMETER_NAME                           PARAMETER_VALUE
  ---------------------------------------- ----------------------------------------
  AUTO_INDEX_COMPRESSION                   OFF
  AUTO_INDEX_DEFAULT_TABLESPACE
  AUTO_INDEX_MODE                          OFF
  AUTO_INDEX_REPORT_RETENTION              31
  AUTO_INDEX_RETENTION_FOR_AUTO            373
  AUTO_INDEX_RETENTION_FOR_MANUAL
  AUTO_INDEX_SCHEMA
  AUTO_INDEX_SPACE_BUDGET                  50
  8 rows selected.
  SQL>

开启自动索引功能

  SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
  PL/SQL procedure successfully completed.
  SQL>
  SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','IND_AI_TBS');
  PL/SQL procedure successfully completed.
  SQL> select parameter_name,parameter_value from dba_auto_index_config order by 1, 2;

  PARAMETER_NAME                           PARAMETER_VALUE
  ---------------------------------------- ----------------------------------------
  AUTO_INDEX_COMPRESSION                   OFF
  AUTO_INDEX_DEFAULT_TABLESPACE            IND_AI_TBS
  AUTO_INDEX_MODE                          IMPLEMENT
  AUTO_INDEX_REPORT_RETENTION              31
  AUTO_INDEX_RETENTION_FOR_AUTO            373
  AUTO_INDEX_RETENTION_FOR_MANUAL
  AUTO_INDEX_SCHEMA
  AUTO_INDEX_SPACE_BUDGET                  50
  8 rows selected.

创建模拟数据

  SQL> conn ohsdba/oracle@192.168.10.51/ppdb01
  Connected.
  SQL>
  SQL> create table ohsdba_objects as select * from dba_objects;
  Table created.
  SQL> insert into ohsdba_objects select * from ohsdba_objects;
  72368 rows created.
  SQL> insert into ohsdba_objects select * from ohsdba_objects;
  144736 rows created.
  SQL> commit;
  Commit complete.
  SQL> update ohsdba_objects set object_id=rownum;
  289472 rows updated.
  SQL> commit;
  Commit complete.
  SQL>

模拟测试

  declare
    a varchar2(2000) := '';
  begin
    for x in 1.. 10000 loop
    select object_name into a from ohsdba_objects where object_id=x;
    end loop;
  end;
  /

查看自动索引的执行情况

自动索引在后头默认的执行时间是15分钟，也就是900秒，如果想早些看到测试效果，可以通过下面的方式把自动索引的运行间隔调整为180秒，甚至更短。如果在Exadata的生产环境中，这个间隔建议调大。因为运行自动索引也是要耗费资源的。

SQL> exec dbms_auto_index_internal.configure('_AUTO_INDEX_TASK_INTERVAL', '180', allow_internal => true);

  col error_message for a20
  col execution_name for a60
  set lines 156
  SQL> select * from dba_auto_index_executions;
  EXECUTION_NAME                EXECUTION EXECUTION ERROR_MESSAGE        STATUS
  ----------------------------- --------- --------- -------------------- -----------
  SYS_AI_2019-02-19/08:50:56    19-FEB-19 19-FEB-19                      COMPLETED
  SYS_AI_2019-02-19/09:06:34    19-FEB-19 19-FEB-19                      COMPLETED

  SQL> select * from dba_auto_index_statistics;
  EXECUTION_NAME              STAT_NAME                          VALUE
  --------------------------------------------------------- ----------
  SYS_AI_2019-02-19/08:50:56  Index candidates                       0
  SYS_AI_2019-02-19/08:50:56  Indexes created (visible)              0
  SYS_AI_2019-02-19/08:50:56  Indexes created (invisible)            0
  SYS_AI_2019-02-19/08:50:56  Indexes dropped                        0
  SYS_AI_2019-02-19/08:50:56  Space used in bytes                    0
  SYS_AI_2019-02-19/08:50:56  Space reclaimed in bytes               0
  SYS_AI_2019-02-19/08:50:56  SQL statements verified                0
  SYS_AI_2019-02-19/08:50:56  SQL statements improved                0
  SYS_AI_2019-02-19/08:50:56  SQL statements managed by SPM          0
  SYS_AI_2019-02-19/08:50:56  SQL plan baselines created             0
  SYS_AI_2019-02-19/08:50:56  Improvement percentage                 0                                                                  
  EXECUTION_NAME              STAT_NAME                          VALUE
  --------------------------------------------------------- ----------
  SYS_AI_2019-02-19/09:06:34  Index candidates                       1
  SYS_AI_2019-02-19/09:06:34  Indexes created (visible)              1
  SYS_AI_2019-02-19/09:06:34  Indexes created (invisible)            0
  SYS_AI_2019-02-19/09:06:34  Indexes dropped                        0
  SYS_AI_2019-02-19/09:06:34  Space used in bytes              6291456
  SYS_AI_2019-02-19/09:06:34  Space reclaimed in bytes               0
  SYS_AI_2019-02-19/09:06:34  SQL statements verified                8
  SYS_AI_2019-02-19/09:06:34  SQL statements improved                8
  SYS_AI_2019-02-19/09:06:34  SQL statements managed by SPM          0
  SYS_AI_2019-02-19/09:06:34  SQL plan baselines created             0
  SYS_AI_2019-02-19/09:06:34  Improvement percentage             99.98                                                                                                    
  22 rows selected.                                                                                                                                                                                       
  SQL>
col index_name  for a20                                                                                              
col index_owner for a10
col table_name  for a20
col table_owner for a10
col command     for a40
col execution_name for a40
SQL> select index_owner,index_name,table_name,table_owner,command from dba_auto_index_ind_actions;
  INDEX_OWNE INDEX_NAME           TABLE_NAME           TABLE_OWNE COMMAND
  ---------- -------------------- -------------------- ---------- ----------------------------------------------------------------
  OHSDBA     SYS_AI_a2j3tnkn13znr OHSDBA_OBJECTS       OHSDBA     CREATE INDEX
  OHSDBA     SYS_AI_a2j3tnkn13znr OHSDBA_OBJECTS       OHSDBA     REBUILD INDEX
  OHSDBA     SYS_AI_a2j3tnkn13znr OHSDBA_OBJECTS       OHSDBA     ALTER INDEX VISIBLE
SQL> set long 5000
SQL> col statement for a5000
SQL> select statement from dba_auto_index_ind_actions;
  STATEMENT
  -------------------------------------------------------------------------------------------------------------------------------------------------
  CREATE INDEX "OHSDBA"."SYS_AI_a2j3tnkn13znr"   ON "OHSDBA"."OHSDBA_OBJECTS"("OBJECT_ID") TABLESPACE "IND_AI_TBS" UNUSABLE INVISIBLE AUTO  ONLINE
  ALTER INDEX "OHSDBA"."SYS_AI_a2j3tnkn13znr"   REBUILD  ONLINE
  ALTER INDEX "OHSDBA"."SYS_AI_a2j3tnkn13znr"   VISIBLE
SQL>

查看并确认自动索引(AUTO字段)

SQL> col owner for a10
SQL> select owner,index_name,table_name,table_owner,auto from dba_indexes where index_name='SYS_AI_a2j3tnkn13znr';
OWNER      INDEX_NAME           TABLE_NAME           TABLE_OWNE AUT
---------- -------------------- -------------------- ---------- ---
OHSDBA     SYS_AI_a2j3tnkn13znr OHSDBA_OBJECTS       OHSDBA     YES

SQL>

测试完毕，删除表，关闭自动索引功能

SQL> drop table ohsdba_objects purge;
Table dropped.
SQL> select owner,index_name,table_name,table_owner,auto from dba_indexes where index_name='SYS_AI_a2j3tnkn13znr';
no rows selected
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);
PL/SQL procedure successfully completed.
SQL>
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
PL/SQL procedure successfully completed.

SQL>

Reference

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-D1285CD5-95C0-4E74-8F26-A02018EA7999
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_AUTO_INDEX.html#GUID-78C59A20-2F92-448F-96F7-6C4FBB355E42

https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87

https://blogs.oracle.com/oraclemagazine/autonomous-indexing

关键词:autoindex 19c

19c新特性之自动索引

相关文章

推荐文章

联系我们