目前 （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
Comment