Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching, Zero-Downtime Upgrade, GoldenGate

Oracle Exadata ,Oracle ODA, Oracle ZDLRA


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

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 

相关文章

OGG from MySQL to Oracle
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)

推荐文章

联系我们

Oracle Software Download Index

Oracle Database Upgrade

Oracle SQLMaria

Oracle Database Document

Oracle Cloud Document

Oracle Cloud中文链接

eDelivery

GoldenGate

My Oracle Support

Exadata Document

Oracle Linux

Oracle Fusion Middleware

MySQL Document

PostgreSQL

Python

Apache

Github

Sourceforge

CNCF

Ruby

Go

ipcheck

ipaddressguide

db-engines

Linux Kernel

Top