Oracle Database 19c 中的自動索引 (DBMS_AUTO_INDEX)

Oracle 資料庫 19c 引入了自動索引功能，它可以讓您将一些有關索引管理的決策交給資料庫。

針對 SQL 語句測試不可見的自動索引以確定它們能夠提升性能。如果它們确實可以提高性能，就會變成可見索引。反之，如果性能沒有得到改善，相關的自動索引會被标記為不可用并随後被删除。針對失敗的自動索引測試的 SQL 語句被列入阻止清單，是以将來不會考慮将它們用于自動索引。優化器不會在第一次對資料庫運作 SQL 時考慮自動索引。

删除未使用的索引。

由于從未在 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

此方式不建議在生産系統進行使用，僅用于測試所用。

DBMS_AUTO_INDEX

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

CDB_AUTO_INDEX_CONFIG

視圖顯示目前的自動索引配置，以下查詢使用

auto_index_config.sql

腳本。

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>

關于該參數的較長的描述可參考官方文檔： DBMS_AUTO_INDEX ！

我們可以使用

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

預設情況下，在預設永久表空間中建立自動索引。如果想使用新的表空間來建立，可以使用

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

啟用自動索引後，在嘗試識别候選索引時會考慮所有使用者。您可以使用

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>

如果需要了解其他參數，下面詳細說明了這些參數：

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

寫了一些可以讓你做到這一點的黑客。

How to drop an index created by Oracle 19c Auto Indexing?

有幾個與自動索引功能相關的視圖，如下所示：

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;

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 僞列的引用。

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;

以下是在建立任何索引之前預設活動報告的輸出示例：

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>

Oracle Database 19c 中的自動索引 (DBMS_AUTO_INDEX)

目錄

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List