19c Automatic Indexing 自动建索引 控制隐藏参数 _optimizer_auto_index_allow _optimizer_use_auto_indexes

目前 （2019年5月21日)似乎MOS上找不到 控制19c Automatic Indexing 自动建索引特性的Notes，大致可以通过 _optimizer_auto_index_allow 和 _optimizer_use_auto_indexes 2个参数关闭该新特性。

 col name format a40
 SELECT x.ksppinm NAME, y.ksppstvl VALUE
  FROM SYS.x$ksppi x, SYS.x$ksppcv y
  WHERE x.inst_id = USERENV ('Instance')
  AND y.inst_id = USERENV ('Instance')
  AND x.indx = y.indx
 AND x.ksppinm LIKE '%index%';
 
 NAME                                               VALUE
-------------------------------------------------- ------------------------------------------------------------
_optimizer_auto_index_allow                        AUTO
_gc_defer_ping_index_only                          TRUE
_kcl_index_split                                   TRUE
_gc_fast_index_split_wait                          0
_index_partition_large_extents                     FALSE
_index_partition_shrink_opt                        TRUE
_db_index_block_checking                           TRUE
_disable_index_block_prefetching                   FALSE
_index_scan_check_skip_corrupt                     FALSE
_index_scan_check_stopkey                          FALSE
_index_max_inc_trans_pct                           20
_index_split_chk_cancel                            5
_advanced_index_compression_trace                  0
db_index_compression_inheritance                   NONE
_index_alert_key_not_found                         FALSE
_reuse_index_loop                                  5
_reclaim_lob_index_scan_limit                      0
_ignore_desc_in_index                              FALSE
_index_load_buf_oltp_sacrifice_pct                 10
_index_load_buf_oltp_under_pct                     85
_index_load_buf_oltp_over_retry                    0
_index_load_last_leaf_comp                         85
_index_load_min_key_count                          10
_index_load_analysis_frequency                     4
_advanced_index_compression_umem_options           2147483647
_advanced_index_compression_options                0
_advanced_index_compression_cmp_options            0
_advanced_index_compression_tst_options            0
_advanced_index_compression_opt_options            0
_advanced_index_compression_options_value          0
_advanced_index_compression_recmp_cusz             90
_advanced_index_compression_recmp_crsz             10
_advanced_index_compression_recmp_nprg             10
_kdkv_index_lossy                                  TRUE
_kdkv_index_relocate                               FALSE
_kdkv_indexinvalid                                 FALSE
_domain_index_batch_size                           2000
_domain_index_dml_batch_size                       200
_odci_index_pmo_rebuild                            FALSE
_cell_index_scan_enabled                           TRUE
optimizer_index_cost_adj                           100
optimizer_index_caching                            0
_system_index_caching                              0
_index_prefetch_factor                             100
_index_join_enabled                                TRUE
_use_nosegment_indexes                             FALSE
_optimizer_compute_index_stats                     TRUE
skip_unusable_indexes                              TRUE
_delay_index_maintain                              TRUE
_disable_function_based_index                      FALSE
_globalindex_pnum_filter_enabled                   TRUE
_enable_online_index_without_s_locking             TRUE
_optimizer_fkr_index_cost_bias                     10
optimizer_use_invisible_indexes                    FALSE
_noseg_for_unusable_index_enabled                  TRUE
_px_index_sampling_objsize                         TRUE
_part_redef_global_index_update                    TRUE
_fast_index_maintenance                            TRUE
_modify_column_index_unusable                      FALSE
_indexable_con_id                                  TRUE
_optimizer_use_auto_indexes                        AUTO
_optimizer_gather_stats_on_load_index              TRUE

 
 
 
 
 
 _optimizer_auto_index_allow
 _optimizer_use_auto_indexes
 
 
 
 
 SQL> alter system set "_optimizer_auto_index_allow"=0 scope=spfile;
alter system set "_optimizer_auto_index_allow"=0 scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value 0 for parameter _optimizer_auto_index_allow, must be from among ALWAYS, AUTO, NEVER



alter system set "_optimizer_auto_index_allow"=NEVER scope=spfile;


alter system set "_optimizer_use_auto_indexes"=NEVER scope=spfile;
ERROR at line 1:
ORA-00096: invalid value NEVER for parameter _optimizer_use_auto_indexes, must be from among ON, AUTO, OFF


alter system set "_optimizer_use_auto_indexes"=OFF scope=spfile;

19C has a new feature database 19c is automatic indexing. How does it work?

QUESTIONS AND ANSWERS

How does automatic indexing work?
This is fully automated process. Oracle will identify candidate indexes,
verify their effectiveness, perform online validations and then implement the indexes where appropriate.

DBA does not need to do anything. Oracle internally picks the candidate indexes and validates the index or indexes.

Does any parameters need to be set for the automatic indexing to work?
Per documentation:
21.7.3 Enabling Automatic Indexing
Automatic indexing is disabled by default in an Oracle database. To enable
automatic indexing, set the AUTO_IMPLEMENT_INDEXES initialization parameter
to the Oracle database release number, for example, 19.1. You can disable
automatic indexing by setting the AUTO_IMPLEMENT_INDEXES initialization
parameter to NONE.

Database Oracle
Oracle Database Release 19
Database Administrator’s Guide

https://docs-stage.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-6E31777C-3BE3-4510-90D5-C715644E00CB

However, the parameter does not seem to exist though and cannot be used in 19.1

The parameter to control/enable automatic indexing is available in the next
refresh of the RDBMS, 19.2. per following internal bug:

Bug 29001016 – 19CBETA CANNOT USE AUTOMATIC INDEXING FUNCTIONALITY – PARAMETER MISSING