1. 实现方式
自动索引功能能够完成以下内容:
-
根据表列使用情况识别潜在的自动索引。文档中成为"候选索引"（candidate indexes）
-
创建自动索引为不可见索引，这样他们就无法被执行计划使用。索引名使用SYS_AI的前缀
-
在SQL语句上测试不可见的自动索引。如果能够提升性能，将其变成可用。如果性能没有提升，相关的自动索引将会被标记为unusable，后续会被删除。在自动索引上测试失败的SQL会被阻止，这样他们在后续是不会进行自动索引测试。自动索引在SQL语句第一次在数据库中运行时优化器是不会考虑自动索引的。
-
删除不可用的索引
2. 前提
该功能当前仅限于exadata上的企业版数据库。可以临时打开初始化参数"_exadata_feature_on"进行临时测试。
export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES
sqlplus / as sysdba <<EOF
alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;
exit;
EOF不支持这么设置，在实际生产系统中不能这么设置。
3. 配置
dbms_auto_index包用于管理自动索引功能。基本的管理如后续描述。
3.1. 查看配置
视图cdb_auto_index_config显示当前自动索引配置。
column parameter_name format a40
column parameter_value format a15
select con_id, parameter_name, parameter_value
from cdb_auto_index_config
order by 1, 2;
CON_ID PARAMETER_NAME PARAMETER_VALUE
---------- ---------------------------------------- ---------------
1 AUTO_INDEX_COMPRESSION OFF
1 AUTO_INDEX_DEFAULT_TABLESPACE
1 AUTO_INDEX_MODE OFF
1 AUTO_INDEX_REPORT_RETENTION 31
1 AUTO_INDEX_RETENTION_FOR_AUTO 373
1 AUTO_INDEX_RETENTION_FOR_MANUAL
1 AUTO_INDEX_SCHEMA
1 AUTO_INDEX_SPACE_BUDGET 50
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
SQL>该语句后续就是文件auto_index_config.sql。
如果切换到用户定义的PDB中，只能获取当前容器的对应值。
alter session set container = pdb1;
SQL> @auto_index_config.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
SQL>对应参数的讲解见这里
3.2. 启用/禁用自动索引
自动索引使用dbms_auto_index包进行配置。
使用auto_index_mode属性控制自动索引配置的开关，对应设置如下:
-
implement： 打开自动索引。能够提升性能的索引会被变成可见，并且对优化器可用
-
read only: 打开自动索引，但是新索引保持不可见状态
-
off: 关闭自动索引
在不同模式下切换
exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');3.3. 自动索引的表空间
默认情况下，自动索引是在用户的默认表空间中。如果不使用默认表空间，可以使用auto_index_default_tablespace属性来设置使用的表空间。以下是创建表空间来存放自动索引，并进行设置的过程。
alter session set container = pdb1;
create tablespace auto_indexes_ts datafile size 100m autoextend on next 100m;
exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS');将该属性设置为null，就是自动索引使用默认表空间
Exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);3.4. scheme级别的控制
一旦自动索引启用，在尝试识别候选索引时是会涉及所有schema的。当然可以使用使用auto_index_schema属性来修改该设置，这能够设置包含或排除列表。
当allow参数设置为true时，指定的schema将会被加入到包含列表中。注意它构建的谓词中包含schema。
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE);
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST2', allow => TRUE);
SQL> @auto_index_config.sql
CON_ID PARAMETER_NAME PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
3 AUTO_INDEX_COMPRESSION OFF
3 AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS
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 schema IN (TEST, TEST2)
3 AUTO_INDEX_SPACE_BUDGET 50
SQL>使用NULL可以清空包含列表
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => TRUE);
SQL> @auto_index_config.sql
CON_ID PARAMETER_NAME PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
3 AUTO_INDEX_COMPRESSION OFF
3 AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS
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
SQL>当allow参数设置为false，指定的schema将会在排除列表中
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => FALSE);
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST2', allow => FALSE);
SQL> @auto_index_config.sql
CON_ID PARAMETER_NAME PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
3 AUTO_INDEX_COMPRESSION OFF
3 AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS
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 schema NOT IN (TEST, TEST2)
3 AUTO_INDEX_SPACE_BUDGET 50
SQL>使用NULL可以清空排除列表
exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => FALSE);
SQL> @auto_index_config.sql
CON_ID PARAMETER_NAME PARAMETER_VALUE
---------- ---------------------------------------- ----------------------------------------
3 AUTO_INDEX_COMPRESSION OFF
3 AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS
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
SQL>3.5. 其他配置
还可以使用其他参数进行配置，具体见这里
-
auto_index_compression: 未记录。可能是用于控制压缩级别。默认为OFF
-
auto_index_report_retention: 自动索引日志的保留时间。报告就是基于这些日志的。默认是31天
-
auto_idnex_retention_for_auto: 未使用的自动索引保留期限。默认为373天
-
auto_index_retention_for_manual: 对于未使用手动创建的索引的保留期限。当设置为NULL时，手动创建的索引将不会被删除。默认为NULL
-
auto_index_space_budget: 默认表空间用于自动索引存储空间的空间百分比。如果使用auto_index_default_tablespace参数指定其他表空间，该参数将会被忽略。
4. 删除secodnary索引
WARNING
做之前一定要三思，并且测试、测试、测试！！！
drop_secondary_indexes程序会删除除了正被约束使用的索引之外的所有其他索引。这可以在表、schema、数据库级别进行。
-- Table-level
exec dbms_auto_index.drop_secondary_indexes('MY_SCHEMA', 'MY_TABLE');
-- Schema-level
exec dbms_auto_index.drop_secondary_indexes('MY_SCHEMA');
-- Database-level
exec dbms_auto_index.drop_secondary_indexes;这能够让保证数据库索引处于初始化的状态，自动索引将会做所有的索引决策。
5. 删除自动索引
drop_auto_indexes程序能够删除自动创建的索引。根据使用的参数，可以是命名的搜音或者schema下的所有自动索引。
删除指定索引，并确保其不会被重建。注意名称是由双引号括起来的
begin
dbms_auto_index.drop_auto_indexes(
owner => 'MY_SCHEMA',
index_name => '"SYS_AI_512bd3h5nif1a"',
allow_recreate => false);
end;
/删除指定schema下的所有自动索引，但是允许他们被重建
begin
dbms_auto_index.drop_auto_indexes(
owner => 'MY_SCHEMA',
index_name => null,
allow_recreate => true);
end;
/删除当前schema的所有自动索引，但是允许他们被重建
begin
dbms_auto_index.drop_auto_indexes(
owner => null,
index_name => null,
allow_recreate => true);
end;
/在该功能的初始版本中，没有一种机制来删除由自动索引功能创建的特定索引，或首先阻止创建特定索引。
6. 视图
有很多关于自动索引功能的视图，如下所示。只有第一个视图是在参考手册中有记录。
select view_name
from dba_views
where view_name like 'DBA_AUTO_INDEX%'
order by 1;
VIEW_NAME
--------------------------------------------------------------------------------
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
SQL>除此之外，{CDB|DBA|ALL|USER}_INDEXES视图包含AUTO列，它用来显示一个索引是否时自动索引功能创建的。以下查询会当成auto_indexes.sql脚本进行运行
column owner format a30
column index_name format a30
column table_owner format a30
column table_name format a30
select owner,
index_name,
index_type,
table_owner,
table_name
table_type
from dba_indexes
where auto = 'YES'
order by owner, index_name;7. 活动报告
dbms_auto_index程序包含两个报告函数
DBMS_AUTO_INDEX.REPORT_ACTIVITY (
activity_start IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP - 1,
activity_end IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
type IN VARCHAR2 DEFAULT 'TEXT',
section IN VARCHAR2 DEFAULT 'ALL',
level IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;
DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY (
type IN VARCHAR2 DEFAULT 'TEXT',
section IN VARCHAR2 DEFAULT 'ALL',
level IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;report_activity函数能够显示特定时间段内到活动，默认是一天。report_last_activity函数报告最后一次自动索引操作。这两个剋用使用以下参数进行格式化输出。
-
TYPE可使用的值有TEXT、HTML、 XML
-
SECTION可使用的值有SUMMARY、INDEX_DETAILS、VERIFICATION_DETAILS、ERRORS、ALL。同样可以使用"+"和"-"来按需加减显示章节。例如"SUMMARY+ERRORS"或"ALL-ERRORS"
-
LEVEL可使用值包括BASIC、TYPICAL、ALL
以下是使用这些函数。注意LEVEL参数使用引号括起来的。当使用SQL调用时，这是必要的，这样他才能不会把level当成伪列。
set long 1000000 pagesize 0
-- Default TEXT report for the last 24 hours.
select dbms_auto_index.report_activity() from dual;
-- Default TEXT report for the latest activity.
select dbms_auto_index.report_last_activity() from dual;
-- HTML Report for the day before yesterday.
select dbms_auto_index.report_activity(
activity_start => systimestamp-2,
activity_end => systimestamp-1,
type => 'HTML')
from dual;
-- HTML report for the latest activity.
select dbms_auto_index.report_last_activity(
type => 'HTML')
from dual;
-- XML Report for the day before yesterday with all information.
select dbms_auto_index.report_activity(
activity_start => systimestamp-2,
activity_end => systimestamp-1,
type => 'XML',
section => 'ALL',
"LEVEL" => 'ALL')
from dual;
-- XML report for the latest activity with all information.
select dbms_auto_index.report_last_activity(
type => 'HTML',
section => 'ALL',
"LEVEL" => 'ALL')
from dual;
set pagesize 14以下是未创建任意索引之前的默认活动报告状态。
select dbms_auto_index.report_activity() from dual;
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 03-JUN-2019 21:59:21
Activity end : 04-JUN-2019 21:59:21
Executions completed : 2
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 0
Indexes created : 0
Space used : 0 B
Indexes dropped : 0
SQL statements verified : 0
SQL statements improved : 0
SQL plan baselines created : 0
Overall improvement factor : 0x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
ERRORS
---------------------------------------------------------------------------------------------
No errors found.
---------------------------------------------------------------------------------------------
SQL>译者碎碎念：非常现代的功能，但是我们也要看到他的实现不是一蹴而就的，是在隐藏索引、sql tuning advisory等特性的基础上逐步发展的产物。仅在exadata支持是一个不小的遗憾。
325
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
