oracle19c新特性：自动化索引
oracle19c最值得关注十个新特性之自动化索引 ，通过机器学习算法自动创建管理索引，可以明显减轻dba工作负担。
Automatic indexing 提供如下功能
以指定时间间隔定期运行自动化索引特性。
• 分析应用程序工作负载，并相应地创建新索引并删除现有性能不佳的索引以提高数 据库性能。
• 重建由于表分区维护操作而标记为不可用的索引，例如ALTER TABLE MOVE。
• 提供了PL/SQL API接口，用于在数据库中配置自动索引并生成与自动索引操作相 关的报告。
注：
1. 当前版本Auto indexes 是 Local B-tree 索引
2. 支持分区和非分区表(Only Local Index)，不支持临时表
3. 自动创建只针对 = 的查询，不支持 范围、模糊、Min/Max等查询
Automatic indexing支持
• 对于On-Prem环境仅支持 Oracle Exadata 平台
• Oracle Cloud 支持 ExaCS, ADB
自动索引演示
启用exadata特性
[oracle19@GF-qsht1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 8 13:17:39 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.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 5016386992 bytes Fixed Size 9145776 bytes Variable Size 939524096 bytes Database Buffers 4060086272 bytes Redo Buffers 7630848 bytes Database mounted. Database opened.
pdb下查询自动索引是否开启 ：AUTO_INDEX_MODE OFF代表未开
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB READ WRITE NO SQL> SQL> SQL> SQL> COLUMN parameter_name FORMAT A40 COLUMN parameter_value FORMAT A20 SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;SQL> SQL> 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 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 50 8 rows selected.
启用自动化索引
启用： EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
启用，但是只作为不可见自动索引，无法被SQL所使用：EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT'); PL/SQL procedure successfully completed.
也可以指定某个schema启用自动索引，我们这里指定test用户 看到参数 AUTO_INDEX_SCHEMA schema IN (TEST)
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE); PL/SQL procedure successfully completed. SQL> COLUMN parameter_name FORMAT A40 COLUMN parameter_value FORMAT A20 SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config;SQL> SQL> CON_ID PARAMETER_NAME PARAMETER_VALUE ---------- ---------------------------------------- -------------------- 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE TS001 3 AUTO_INDEX_MODE IMPLEMENT 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 50 1 AUTO_INDEX_COMPRESSION OFF 1 AUTO_INDEX_DEFAULT_TABLESPACE 1 AUTO_INDEX_MODE OFF CON_ID PARAMETER_NAME PARAMETER_VALUE ---------- ---------------------------------------- -------------------- 1 AUTO_INDEX_REPORT_RETENTION 31 1 AUTO_INDEX_RETENTION_FOR_AUTO 373 1 AUTO_INDEX_RETENTION_FOR_MANUAL 1 AUTO_INDEX_SCHEMA schema IN (TEST) 1 AUTO_INDEX_SPACE_BUDGET 50 16 rows selected.
创建一个表空间存放自动索引
SQL> create tablespace ts001 datafile '/oracle/app/oracle/oradata/ORCL19C/pdb/ts001.dbf' size 500m; Tablespace created. SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','ts001'); PL/SQL procedure successfully completed. SQL>
创建一张测试表test1 并查询1万次
SQL> create table test.test1 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 test.test1 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
已经创建成功 SYS_AI开头的索引是oracle自动创建的
SQL> COLUMN OWNER FORMAT a10 col INDEX_TYPE format a10 col INDEX_NAME format a20 col TABLE_NAME format a20 col TABLE_OWNER format a10 SELECT OWNER,INDEX_TYPE,INDEX_NAME,TABLE_NAME,TABLE_OWNER FROM DBA_INDEXES WHERE AUTO='YES' ORDER BY OWNER,INDEX_NAME;SQL> SQL> SQL> SQL> SQL> OWNER INDEX_TYPE INDEX_NAME TABLE_NAME TABLE_OWNE ---------- ---------- -------------------- -------------------- ---------- TEST NORMAL SYS_AI_1p2a9sk44mhwy TEST1 TEST TEST NORMAL SYS_AI_5msfmx7p4gkzr TEST1 TEST
执行计划已经走了索引
SQL> set pages 1000 set lines 1000 set autotrace on select object_name from test.test1 where object_id=1000;SQL> SQL> SQL> OBJECT_NAME -------------------------------------------------------------------------------------------------------------------------------- APPLY$_BATCH_SQL_STATS_I Execution Plan ---------------------------------------------------------- Plan hash value: 3029300800 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST1 | 1 | 40 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | SYS_AI_1p2a9sk44mhwy | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=1000) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 575 bytes sent via SQL*Net to client 638 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
