[译] oracle database 19c 中的自动索引 (dbms-m6米乐安卓版下载

原创 lucifer三思而后行 2022-01-06

1522

原文地址：
原文作者：tim hall

oracle 数据库 19c 引入了自动索引功能，它可以让您将一些有关索引管理的决策交给数据库。

可以根据数据表中列使用情况识别潜在的自动索引。我们可以称之为 “候选索引”。
将自动索引创建为不可见索引，因此它们不会在执行计划中使用。索引名称包括“sys_ai”前缀。
针对 sql 语句测试不可见的自动索引以确保它们能够提升性能。如果它们确实可以提高性能，就会变成可见索引。反之，如果性能没有得到改善，相关的自动索引会被标记为不可用并随后被删除。针对失败的自动索引测试的 sql 语句被列入阻止列表，因此将来不会考虑将它们用于自动索引。优化器不会在第一次对数据库运行 sql 时考虑自动索引。
删除未使用的索引。

由于从未在 exadata 上使用过此功能，因此无法评论其有效性。

此功能目前仅限于工程系统上的企业版，如此处所述。有一种通过启用 _exadata_feature_on 初始化参数进行测试的m6米乐安卓版下载的解决方案。

export oracle_sid=cdb1
export oraenv_ask=no
. oraenv
export oraenv_ask=yes
sqlplus / as sysdba <set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;
exit;
eof

此方式不建议在生产系统进行使用，仅用于测试所用。

dbms_auto_index 包用于管理自动索引功能，基本管理如下所述。

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>

如果我们切换到用户定义的可插拔数据库，我们就只能查看该容器的值。

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>

关于该参数的详细描述可参考官方文档：！

2、启用/禁用自动索引

我们可以使用 dbms_auto_index 包的 configure 过程配置来自动索引。

自动索引 的开关是使用 auto_index_mode 属性控制的，该属性具有以下几种模式：

implement：打开自动索引，提高性能的新索引变得可见并可供优化器使用。
report 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、自动索引的表空间

默认情况下，在默认永久表空间中创建自动索引。如果想使用新的表空间来创建，可以使用 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);

4、用户级控制

启用自动索引后，在尝试识别候选索引时会考虑所有用户。您可以使用 auto_index_schema 属性更改默认行为，这允许您维护包含/排除列表。

如果 allow 参数设置为 true，则将指定的用户添加到包含列表中。请注意，它构建了一个包含用户的谓词。

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，则将指定的用户添加到排除列表中：

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>

5、其他配置

如果需要了解其他参数，下面详细说明了这些参数：

auto_index_compression ：文档中未说明，大概用于控制压缩级别，默认 off
auto_index_report_retention ：自动索引日志的保留期。报告基于这些日志，默认 31 天。
auto_index_retention_for_auto ：未使用的自动索引的保留期，默认 373 天。
auto_index_retention_for_manual ：未使用的手动创建索引的保留期，当设置为 null 时，不考虑删除手动创建的索引，默认为空。
auto_index_space_budget ：用于自动索引存储的默认永久表空间的百分比，使用 auto_index_default_tablespace 参数指定自定义表空间时，将忽略此参数。

‼️ 做这个之前要仔细考虑，测试，测试，测试！

如果你真的勇气非凡，drop_secondary_indexes 过程将删除除用于约束的索引之外的所有索引。这可以在表、模式或数据库级别完成。

-- 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;

这让您一清二楚，因此自动索引可以为您做出所有索引决策。

drop_auto_indexes 过程允许我们删除自动创建的索引。根据参数值，我们可以删除指定的自动索引，也可以是用户的所有自动索引。

删除指定的自动索引，并确保它不会被重新创建。请注意，索引名称是 双引号 的！

begin
  dbms_auto_index.drop_auto_indexes(
    owner          => 'my_schema',
    index_name     => '"sys_ai_512bd3h5nif1a"',
    allow_recreate => false);
end;
/

删除指定用户下的所有自动索引，但允许重新创建它们：

begin
  dbms_auto_index.drop_auto_indexes(
    owner          => 'my_schema',
    index_name     => null,
    allow_recreate => true);
end;
/

删除当前用户的所有自动索引，但允许重新创建它们：

begin
  dbms_auto_index.drop_auto_indexes(
    owner          => null,
    index_name     => null,
    allow_recreate => true);
end;
/

在此功能的初始版本中，没有一种机制可以删除由自动索引功能创建的特定索引，或者首先阻止创建特定索引。 franck pachot 写了一些可以让你做到这一点的黑客。

有几个与自动索引功能相关的视图，如下所示：

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 列，该列指示索引是否由自动索引功能创建。

以下查询可以使用脚本：


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;

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 ：允许的值（文本、html、xml）。
section：允许值（summary、index_details、verification_details、errors、all）。您还可以使用“ ”和“-”字符的组合来指示是否应该包括或排除某些内容。例如“summary errors”或“all -errors”。
level ：允许值（基本、典型、全部）。

下面显示了从 sql 中使用这些函数的一些示例。注意 level 参数的引用。在 sql 调用中使用 this 时这是必需的，因此它理解这不是对 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>