目前 (2019年5月21日)似乎MOS上找不到 控制19c Automatic Indexing 自动建索引特性的Notes,大致可以通过 _optimizer_auto_index_allow 和 _optimizer_use_auto_indexes 2个参数关闭该新特性。
col name format a40SELECT x.ksppinm NAME, y.ksppstvl VALUEFROM SYS.x$ksppi x, SYS.x$ksppcv yWHERE x.inst_id = USERENV ('Instance')AND y.inst_id = USERENV ('Instance')AND x.indx = y.indxAND 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 0db_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 TRUEoptimizer_index_cost_adj 100optimizer_index_caching 0_system_index_caching 0_index_prefetch_factor 100_index_join_enabled TRUE_use_nosegment_indexes FALSE_optimizer_compute_index_stats TRUEskip_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 10optimizer_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_indexesSQL> 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, NEVERalter 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, OFFalter 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