Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Exadata

19c新特性之自动索引

添加索引来解决性能问题是一把双刃剑,它可能会提高某些查询的性能,但肯定会减慢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 

相关文章

Oracle Database 19c with UCP(Universal Connection Pool)
19c中新增dbms_xplan.compare_plans
Oracle Database 19c在优化方面有哪些新特性
Oracle 19c新特性之Automatic Flashback
Install Oracle RAC Database 19c Step by Step
19c新特性之Automatic In-memory
19c新特性之SQL语句隔离
19c新特性之实时统计信息收集
19c新特性之自动索引
Oracle事务卫士(Transaction Guard)和应用连续性(Application Continuity)
在Oracle RAC ADG中通过opatchauto应用补丁(19.5.0-->19.5.1)
Upgrade to Oracle Database 19c Hands-on
Top