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

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