ITPub博客

首页 > 数据库 > Oracle > Oracle Database 19c中的自动索引

Oracle Database 19c中的自动索引

原创 Oracle 作者:jelephant 时间:2019-08-20 13:45:18 0 删除 编辑

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_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  模式级( 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_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);
    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_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);
    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_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);
    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_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>

    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       : 
     Executions 
    with fatal 
    error  : 
    -------------------------------------------------------------------------------
    SUMMARY (AUTO INDEXES)
    -------------------------------------------------------------------------------
     Index candidates            : 
     Indexes created             : 
     Space used                  : 
    0 B
     Indexes dropped             : 
     SQL statements verified     : 
     SQL statements improved     : 
     SQL plan baselines created  : 
     Overall improvement factor  : 
    0x
    -------------------------------------------------------------------------------
    SUMMARY (MANUAL INDEXES)
    -------------------------------------------------------------------------------
     Unused indexes    : 
     Space used        : 
    0 B
     Unusable indexes  : 
    -------------------------------------------------------------------------------
    ERRORS
    ---------------------------------------------------------------------------------------------
    No errors found.
    ---------------------------------------------------------------------------------------------
    SQL>


    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29337971/viewspace-2654266/,如需转载,请注明出处,否则将追究法律责任。

    请登录后发表评论 登录
    全部评论

    注册时间:2013-12-07

    • 博文量
      143
    • 访问量
      626791