Oracle 21c中的自动索引技术研究-2023

力哥讲技术

已于 2023-04-18 13:31:32 修改

阅读量194

点赞数

分类专栏： oracle 文章标签： oracle 数据库 sql

于 2023-02-27 19:10:22 首次发布

本文链接：https://blog.csdn.net/m0_37723088/article/details/129247854

版权

oracle 专栏收录该内容

26 篇文章 3 订阅

订阅专栏

1、它能做什么

自动索引功能执行以下操作。

根据表列使用情况确定潜在的自动索引。文档称这些为“候选索引(candidate indexes)”。
将自动索引创建为不可见索引，因此不会在执行计划中使用它们。索引名称包括“SYS_AI”前缀。
根据SQL语句测试不可见的自动索引，以确保它们能提高性能。如果它们导致性能提高，则可以它们可见。如果性能未得到改善，则相关的自动索引将标记为不可用，稍后将被删除。针对失败的自动索引测试的SQL语句被列入黑名单，因此将来不会考虑将它们用于自动索引。第一次对数据库运行SQL时，优化程序不会考虑自动索引。
删除未使用的索引。

2、先决条件

通过设置初始化参数“_exadata_feature_on=true”进行测试。注：请不要在生产系统中测试。

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>

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

ALTER SESSION SET CONTAINER = pdb1;

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
---------- ---------------------------------------- ---------------
         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包的 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.3 自动索引的表空间

默认情况下，自动索引是在默认的永久表空间中创建的。如果这是不可接受的，您可以使用 AUTO_INDEX_DEFAULT_TABLESPACE属性指定一个表空间来保存它们。下面我们创建一个表空间来保存自动索引，并相应地设置属性。

ALTER SESSION SET CONTAINER = pdb1;

CREATE TABLESPACE AUTO_IND_TS DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M;

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_IND_TS');

如果要设置使用默认永久表空间，可以设置为 NULL，如下命令所示：

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

3.4 模式级（Schema-Level）控制

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

如果 ALLOW参数设置为true，则指定的模式(schema)将添加到包含列表中。注意:它构建了一个包含模式的谓词。

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST', allow => TRUE);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'TEST2', allow => TRUE);

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
---------- ---------------------------------------- ----------------------------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_IND_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);

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
---------- ---------------------------------------- ----------------------------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_IND_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);
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
---------- ---------------------------------------- ----------------------------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_IND_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);

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
---------- ---------------------------------------- ----------------------------------------
         3 AUTO_INDEX_COMPRESSION                   OFF
         3 AUTO_INDEX_DEFAULT_TABLESPACE            AUTO_IND_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>

4、其它配置

您可能希望考虑其他参数，这些都在此详细说明。

AUTO_INDEX_COMPRESSION：据推测用于控制压缩程度。默认为“OFF”。
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 参数指定自定义表空间时，将忽略此参数。

5、删除二级索引

在做这个之前，请仔细考虑，测试，测试，测试！

如果您感觉特别勇敢，DROP_SECONDARY_INDEXES过程将删除除用于约束的索引之外的所有索引。这可以在表、模式（Schema）、数据库级别完成。

-- 表级别
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('MY_SCHEMA', 'MY_TABLE');

-- 模式（Schema）级别
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('MY_SCHEMA');

-- 数据库级别
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes;

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

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）。

从SQL中使用这些函数的一些示例如下所示。注意引用LEVEL参数。在SQL调用中使用它时，这是必要的，因此这不是对LEVEL伪列的引用。

SET LONG 1000000 PAGESIZE 0
-- 过去24小时的默认TEXT报告。
SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
-- 最新活动的默认TEXT报告。
SELECT DBMS_AUTO_INDEX.report_last_activity() FROM dual;

-- 前天的HTML报告。
SELECT DBMS_AUTO_INDEX.report_activity(
         activity_start => SYSTIMESTAMP-2,
         activity_end   => SYSTIMESTAMP-1,
         type           => 'HTML')
FROM   dual;

-- 最新活动的HTML报告。
SELECT DBMS_AUTO_INDEX.report_last_activity(
         type => 'HTML')
FROM   dual;
-- 前天的XML报告包含所有信息。
SELECT DBMS_AUTO_INDEX.report_activity(
         activity_start => SYSTIMESTAMP-2,
         activity_end   => SYSTIMESTAMP-1,
         type           => 'XML',
         section        => 'ALL',
         "LEVEL"        => 'ALL')
FROM   dual;
-- 包含所有信息的最新活动的XML报告。
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>

力哥讲技术

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
Oracle 21c中的自动索引技术研究-2023

REPORT_ACTIVITY 函数允许您显示指定时间段内的活动，默认为最后一天。在SQL调用中使用它时，这是必要的，因此这不是对LEVEL伪列的引用。参数设置为true，则指定的模式(schema)将添加到包含列表中。过程将删除除用于约束的索引之外的所有索引。默认情况下，自动索引是在默认的永久表空间中创建的。使用 DBMS_AUTO_INDEX 包来管理自动索引特性。CDB_AUTO_INDEX_CONFIG视图显示当前的自动索引配置。视图包含AUTO列，该列指示索引是否由自动索引功能创建。
复制链接

扫一扫