Oracle 19c AutoIndex Tips
Env:
Exa/EE 19c
Prepare
auto index只有云产品和ExadataEE才能使用，测试各种命令可以使用模拟exadata环境去实践这些相关命令
alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;
auto_index的相关parameter
[oracle@db1 ~](KKB)$ ora params auto_index
NAME VALUE DESCRIPTION
--------------------------------------------- -------------------- ----------------------------------------------------------------------
_optimizer_auto_index_allow AUTO Controls Auto Index
_optimizer_use_auto_indexes AUTO Use Auto Index
[oracle@db1 ~](KKB)$
SYS@KKB>
col parameter_name for a40
col PARAMETER_VALUE for 999999
col LAST_UPDATED for a30
col UPDATED_BY for a10
col PARAMETER_DATA for a30
set lines 200 pagesize 1000
SYS@KKB> select * from SMB$CONFIG;
PARAMETER_NAME PARAMETER_VALUE LAST_UPDATED UPDATED_BY PARAMETER_DATA
---------------------------------------- --------------- ------------------------------ ---------- ------------------------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
SPM_TRACING 0
AUTO_CAPTURE_PARSING_SCHEMA_NAME 0 <filters></filters>
AUTO_CAPTURE_MODULE 0 <filters></filters>
AUTO_CAPTURE_ACTION 0 <filters></filters>
AUTO_CAPTURE_SQL_TEXT 0 <filters></filters>
AUTO_INDEX_SCHEMA 0 <filters></filters>
AUTO_INDEX_DEFAULT_TABLESPACE 0
AUTO_INDEX_SPACE_BUDGET 50
AUTO_INDEX_REPORT_RETENTION 31
AUTO_INDEX_RETENTION_FOR_AUTO 0 373
AUTO_INDEX_RETENTION_FOR_MANUAL 0
AUTO_INDEX_MODE 0 19-06-18 14:22:40.000000 SYS OFF
_AUTO_INDEX_TRACE 0
_AUTO_INDEX_TASK_INTERVAL 900
_AUTO_INDEX_TASK_MAX_RUNTIME 3600
_AUTO_INDEX_IMPROVEMENT_THRESHOLD 20
_AUTO_INDEX_REGRESSION_THRESHOLD 10
_AUTO_INDEX_ABSDIFF_THRESHOLD 100
_AUTO_INDEX_STS_CAPTURE_TASK 0 19-06-18 13:29:46.000000 LIN ON
_AUTO_INDEX_CONTROL 0
_AUTO_INDEX_DERIVE_STATISTICS 0 ON
_AUTO_INDEX_CONCURRENCY 1
_AUTO_INDEX_SPA_CONCURRENCY 1
_AUTO_INDEX_REBUILD_TIME_LIMIT 30
_AUTO_INDEX_REBUILD_COUNT_LIMIT 5
_AUTO_INDEX_REVERIFY_TIME 30
AUTO_INDEX_COMPRESSION 0 OFF
AUTO_SPM_EVOLVE_TASK 0 OFF
AUTO_SPM_EVOLVE_TASK_INTERVAL 3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
32 rows selected.
SYS@KKB>
可以看到 _AUTO_INDEX_TASK_INTERVAL = 900 是15分钟捕获auto index
auto index的缺省设置： 根据名称即可大概了解参数的功能,这些参数都需要通过DBMS_AUTO_INDEX去修改
SYS@KKB> select * from dba_auto_index_config;
PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY
---------------------------------------- -------------------- -------------------- --------------------
AUTO_INDEX_COMPRESSION OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE OFF
AUTO_INDEX_REPORT_RETENTION 31
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 50
8 rows selected.
SYS@KKB>
Intro
[设置为ON]
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
[设置为OFF]
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
[仅生成报告]
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
另外需要注意的是通过Automatic Indexing生成的索引，通过命令drop文是删除不了，这就是为什么有上面auto_index的配置参数去删除，如保留多少天（默认 AUTO_INDEX_RETENTION_FOR_AUTO 是373天）
如下： Can not Drop index sys_ai_xxxxxxxx
LIN@KKB> select index_name from user_indexes;
INDEX_NAME
--------------------------------------------------------------------------------
SYS_AI_djph6g9pxbyxf
SYS_AI_829s8wj1jb4y4
LIN@KKB> drop index SYS_AI_829s8wj1jb4y4;
drop index SYS_AI_829s8wj1jb4y4
*
ERROR at line 1:
ORA-01418: specified index does not exist
LIN@KKB>
从官方宣传Oracle Auto Index 达到自治功能，也是可以了解到为什么不能手动drop了
The new Automatic Indexing feature in Oracle Database 19c detects the need for indexes, creates them, and drops them automatically—without DBA intervention.
为什么是373天？ 不清楚……
wiki 解释如下： （这个跟Oracle有啥关系？不懂…