• oracle 自动索引

    备注：

    oracle的自动索引，只能在一体机上实现。非一体机无法使用，会报错

    1. SYS@test>EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION', 'ON');
    2. BEGIN DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION', 'ON'); END;
    3.  
    4. *
    5. ERROR at line 1:
    6. ORA-40216: feature not supported
    7. ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
    8. ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 10888
    9. ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 301
    10. ORA-06512: at line 1
    11.  
    12.  
    13. SYS@test>

    参考文档：

    Database Administrator’s Guide
    21 Managing Indexes
    21.7 Managing Auto Indexes

    PL/SQL Packages and Types Reference
    31 DBMS_AUTO_INDEX
    19c自动创建index功能 (Doc ID 2533822.1)
    [Automatic indexing] How To Enable AUTO_INDEX_COMPRESSION on Exadata (Doc ID 2610685.1)
    ORA-40216 When Using Auto Index Feature (Doc ID 2570076.1)
    Automatic Indexing - Autonomous Database (Shared) (Doc ID 2764331.1)

    自动索引如何工作：

    This section describes how automatic indexing works.

    The automatic indexing process runs in the background every 15 minutes and performs the following operations:

    1. Identifies auto index candidates

      Auto index candidates are identified based on the usage of table columns in SQL statements.

      Ensure that table statistics are up to date. Tables without statistics are not considered for auto indexing. Tables with stale statistics are not considered for auto indexing, if real-time statistics are not available.

    2. Creates invisible auto indexes for the auto index candidates

      The auto index candidates are created as invisible auto indexes, that is, these auto indexes cannot be used in SQL statements.

      Automatic indexes can be single-column or multi-column. They are considered for the following:

      • Table columns (including virtual columns)
      • Partitioned and non-partitioned tables

    3. Verifies invisible auto indexes against SQL statements

      The invisible auto indexes are validated against SQL statements.

      If the performance of SQL statements is improved by using these indexes, then the indexes are configured as visible indexes, so that they can be used in SQL statements.

      If the performance of SQL statements is not improved by using these indexes, then the indexes are configured as unusable indexes and the SQL statements are blacklisted. The unusable indexes are later deleted by the automatic indexing process. The blacklisted SQL statements are not allowed to use auto indexes in future.

      Note:

      Auto indexes cannot be used by SQL statements that are running for the first time in a database.

    4. Deletes the unused auto indexes

      The auto indexes that are not used for a long period are deleted.

      Note:

      By default, the unused auto indexes are deleted after 373 days. The period for retaining the unused auto indexes in a database can be configured using the DBMS_AUTO_INDEX.CONFIGURE procedure.

    配置自动索引

    You can configure automatic indexing in an Oracle database using the DBMS_AUTO_INDEX.CONFIGURE procedure.

    exec dbms_auto_index.configure('AUTO_INDEX_MODE','');

    AUTO_INDEX_MODE: Modes of operation of auto indexes. It can have one of the following values:

    IMPLEMENT: In this mode, new auto indexes are created as visible indexes and any existing invisible auto indexes are also set to visible indexes. In this mode, auto indexes are available to be used in SQL statements.
    REPORT ONLY: In this mode, new auto indexes are created as invisible indexes and are not available to be used in SQL statements.
    OFF: Setting the mode to OFF prevents new auto indexes from being considered and created. However, it does not disable existing auto indexes.


    DBA_AUTO_INDEX_CONFIG displays the current configuration parameter settings for automatic indexing

    SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_MODE';

    PARAMETER_NAME PARAMETER_VALUE
    ------------------------------ ------------------------------
    AUTO_INDEX_MODE OFF

    To enable automatic indexing in a database and creating any new auto indexes as visible indexes:

    1. SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
    2. SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_MODE';
    3. PARAMETER_NAME PARAMETER_VALUE
    4. ------------------------------
    5. AUTO_INDEX_MODE IMPLEMENT

     To enable automatic indexing in a database and creating any new auto indexes as invisible indexes:

    1. SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
    2. SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_MODE';
    3.  
    4. PARAMETER_NAME PARAMETER_VALUE
    5. ------------------------------
    6. AUTO_INDEX_MODE REPORT ONLY

    To disable automatic indexing in a database so that no new auto indexes are created (existing auto indexes remain enabled):

    1. SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF')
    2. SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_MODE';
    3.  
    4. PARAMETER_NAME PARAMETER_VALUE
    5. ------------------------------
    6. AUTO_INDEX_MODE OFF

    在schema级别启动自动索引

    You can use the AUTO_INDEX_SCHEMA configuration setting to specify schemas that can use auto indexes.
    When automatic indexing is enabled in a database, all the schemas in the database can use auto indexes by default.

    The automatic indexing process manages two schema lists – the inclusion list and the exclusion list. The inclusion list contains the schemas that can use auto indexes. The exclusion list contains the schemas that cannot use auto indexes. Initially, both these lists are empty and all the schemas in the database can use auto indexes when automatic indexing is enabled for a database.

    execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','',);

    TRUE: Add the specified schema to the inclusion list.
    FALSE: Add the specified schema to the exclusion list.
    NULL: Remove the specified schema from the list to which it is currently added

    If the inclusion list contains at least one schema, then only the schemas listed in the inclusion list can use auto indexes:

    1. SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA';
    2.  
    3. PARAMETER_NAME PARAMETER_VALUE
    4. ------------------------------
    5. AUTO_INDEX_SCHEMA
    6.  
    7. SQL> execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','ADMIN',TRUE);
    8. SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA';
    9.  
    10. PARAMETER_NAME PARAMETER_VALUE
    11. ------------------------------
    12. AUTO_INDEX_SCHEMA schema IN (ADMIN)
    13.  
    14. In this case ADMIN schema only enabled with auto indexes and all other schemas excluded.

     If the inclusion list is empty and the exclusion list contains at least one schema, then all the schemas can use auto indexes, except the schemas listed in the exclusion list:

    1. SQL> execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','ADMIN',FALSE);
    2. SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA';
    3.  
    4. PARAMETER_NAME PARAMETER_VALUE
    5. ------------------------------
    6. AUTO_INDEX_SCHEMA schema NOT IN (ADMIN)
    7.  
    8. In this case ADMIN schema excluded from auto indexes feature and all other schemas included.

    If both the lists (the inclusion list and the exclusion list) contain at least one schema, then all the schemas can use auto indexes, except the schemas listed in the exclusion list:

    1. SQL> execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','ADMIN',TRUE);
    2. SQL> execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','TCUSER',FALSE);
    3.  
    4. SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA';
    5.  
    6. PARAMETER_NAME PARAMETER_VALUE
    7. ------------------------------ ------------------------------------------------------------
    8. AUTO_INDEX_SCHEMA schema IN (ADMIN) AND schema NOT IN (TCUSER)
    9.  
    10. In this case, TCUSER is excluded from auto indexes feature and all other schemas included.

    产生自动索引report

    You can generate reports related to automatic indexing operations in an Oracle database using the REPORT_ACTIVITY and REPORT_LAST_ACTIVITY functions of the DBMS_AUTO_INDEX package.

    Generating a report of automatic indexing operations for last 24 hours

    1. SQL> set long 1000000
    2. SQL> select dbms_auto_index.report_activity from dual;
    3.  
    4. REPORT_ACTIVITY
    5. --------------------------------------------------------------------------------
    6. GENERAL INFORMATION
    7. -------------------------------------------------------------------------------
    8. Activity start : 10-APR-2021 20:14:05
    9. Activity end  : 11-APR-2021 20:14:05
    10. Executions completed : 95
    11. Executions interrupted : 0
    12. Executions with fatal error : 0
    13. -------------------------------------------------------------------------------
    14.  
    15.  SUMMARY (AUTO INDEXES)
    16.  
    17. -------------------------------------------------------------------------------
    18. REPORT_ACTIVITY
    19. --------------------------------------------------------------------------------
    20. Index candidates : 0
    21. Indexes created : 0
    22. Space used : 0 B
    23. Indexes dropped : 0
    24. SQL statements verified : 0
    25. SQL statements improved : 0
    26. SQL plan baselines created : 0
    27. Overall improvement factor : 0x
    28. -------------------------------------------------------------------------------
    29.  
    30.  SUMMARY (MANUAL INDEXES)
    31.  
    32.  REPORT_ACTIVITY
    33.  
    34. --------------------------------------------------------------------------------
    35. -------------------------------------------------------------------------------
    36. Unused indexes : 0
    37. Space used : 0 B
    38. Unusable indexes : 0
    39. -------------------------------------------------------------------------------
    40.  
    41.  ERRORS
    42.  
    43. --------------------------------------------------------------------------------
    44. No errors found.
    45. --------------------------------------------------------------------------------

    Generating a report of automatic indexing operations for a specific period

    1. SQL> set long 1000000
    2. SQL> select dbms_auto_index.report_activity(activity_start => TO_TIMESTAMP('2021-04-01', 'YYYY-MM-DD'), activity_end => TO_TIMESTAMP('2021-04-11', 'YYYY-MM-DD')) from dual;
    3.  
    4. DBMS_AUTO_INDEX.REPORT_ACTIVITY(ACTIVITY_START=>TO_TIMESTAMP('2021-04-01','YYYY-
    5. --------------------------------------------------------------------------------
    6. GENERAL INFORMATION
    7. -------------------------------------------------------------------------------
    8. Activity start : 01-APR-2021 00:00:00
    9. Activity end : 11-APR-2021 00:00:00
    10. Executions completed : 951
    11. Executions interrupted : 0
    12. Executions with fatal error : 0
    13. -------------------------------------------------------------------------------
    15.  SUMMARY (AUTO INDEXES)
    17. -------------------------------------------------------------------------------
    19.  DBMS_AUTO_INDEX.REPORT_ACTIVITY(ACTIVITY_START=>TO_TIMESTAMP('2021-04-01','YYYY-
    20.  
    21. --------------------------------------------------------------------------------
    22. Index candidates : 0
    23. Indexes created : 0
    24. Space used : 0 B
    25. Indexes dropped : 0
    26. SQL statements verified : 0
    27. SQL statements improved : 0
    28. SQL plan baselines created : 0
    29. Overall improvement factor : 0x
    30. -------------------------------------------------------------------------------
    31.  
    32.  SUMMARY (MANUAL INDEXES)
    33.  
    34.  DBMS_AUTO_INDEX.REPORT_ACTIVITY(ACTIVITY_START=>TO_TIMESTAMP('2021-04-01','YYYY-
    36. --------------------------------------------------------------------------------
    37. -------------------------------------------------------------------------------
    38. Unused indexes : 0
    39. Space used : 0 B
    40. Unusable indexes : 0
    41. -------------------------------------------------------------------------------
    43.  ERRORS
    45. --------------------------------------------------------------------------------
    46. -------------
    47. No errors found.
    48. --------------------------------------------------------------------------------

    END 

  • 相关阅读:
    特征提取 - 骨架、中轴和距离变换
    kettle spoon连接MySQL8.0数据库报错解决方法
    Ubuntu安装VM TOOLS解决虚拟机无法和WINDOWS粘贴复制问题
    cmake是什么，为什么现在都用cmake,cmake编译原理和跨平台示例
    图解 cv2.HoughLines & cv2.line 参数原理
    【算法】PTA刷题记录
    【MySQL】解决在join表时一对多的情况下重复数据的问题
    【基础架构】part-1 高可用策略和知识点总结
    使用 PyQT 和 Qt 设计器进行 Python GUI 开发
    ubuntu22.04@laptop OpenCV Get Started: 013_contour_detection
  • 原文地址：https://blog.csdn.net/xxzhaobb/article/details/133313684