自动索引技术实操

数据库中哪些索引需要创建、哪些索引可以删除？ 利用19c特性auto index来告诉你答案

--查看当前有哪些advisor任务 set lin 200 col task_name for a32 col DESCRIPTION for a32 col advisor_name for a32 select task_name,DESCRIPTION,advisor_name,status,to_char(EXECUTION_START,'yyyy-mm-dd hh24:mi:ss')EXECUTION_START from dba_advisor_tasks where task_name not like 'ADDM%'; --看看这些任务状态是否激活 col task_name for a32 col last_run for a22 select task_name,status,enabled, to_char(last_schedule_time,'yyyy-mm-dd hh24:mi:ss')last_run from dba_autotask_schedule_control; --非一体机环境需要设置隐含参数，23ai就默认打开 alter system set "_exadata_feature_on"=true scope=spfile; shu immediate startup --查看当前auto index配置项 col PARAMETER_NAME for a35 col PARAMETER_VALUE for a35 col LAST_MODIFIED for a22 col MODIFIED_BY for a12 select PARAMETER_NAME,PARAMETER_VALUE,to_char(LAST_MODIFIED,'yyyy-mm-dd hh24:mi:ss') modified,MODIFIED_BY from dba_auto_index_config; --运行ai功能模式：只提供报表、自动添加删除（慎用）、关闭 EXEC dbms_auto_index.configure('AUTO_INDEX_MODE', 'REPORT ONLY') --EXEC dbms_auto_index.configure('AUTO_INDEX_MODE', 'IMPLEMENT') --exec dbms_auto_index.configure('auto_index_mode','off'); exec dbms_auto_index.configure('auto_index_schema','test',allow=>true); --只有test可用 用户名null就是全部 --exec dbms_auto_index.configure('auto_index_schema','test',allow=>false); --除去test都可用 exec dbms_auto_index.configure('auto_index_default_tablespace','users') --自动创建的索引必须用drop_auto_indexes方法删 exec dbms_auto_index.drop_auto_indexes(owner=>'test', index_name=>'SYS_AI_001', allow_recreate=>false); --运行一段时间后看报告 set lines 300 trims on pages 1000 long 10000000 col report_hist for a120 var rep CLOB exec :rep:=dbms_auto_index.report_activity(activity_start=>sysdate -2, section=>'ALL', level=>'ALL') spo report_hist.txt select :rep report from dual; spo OFF exec :rep:=dbms_auto_index.report_activity(activity_start=>sysdate -2, section=>'ALL', level=>'ALL', type=>'HTML') select dbms_auto_index.report_last_activity() report from dual; select TABLE_NAME,index_name,index_type,status,auto,VISIBILITY,last_analyzed from dba_indexes where owner='TEST'; --哪些索引是自动创建的 COL TABLE_NAME FOR A22 COL INDEX_NAME FOR A22 select TABLE_NAME,index_name,index_type,status,auto,VISIBILITY,last_analyzed from dba_indexes where auto='YES'; --决策结果 col EXEC_NAME for a30 select EXECUTION_NAME exec_name,SQL_ID, ORIGINAL_PLAN_HASH_VALUE ori_plan, AUTO_INDEX_PLAN_HASH_VALUE auto_plan, ORIGINAL_BUFFER_GETS ori_buffget, AUTO_INDEX_BUFFER_GETS auto_buffget, round(ORIGINAL_CPU_TIME) ori_cpu, round(AUTO_INDEX_CPU_TIME) auto_cpu, STATUS from DBA_AUTO_INDEX_VERIFICATIONS; --自动索引处置命令：新建、重建、设置unusable set long 99999 longc 99999 col command for a30 col STATEMENT for a65 COL INDEX_NAME FOR A22 col EXECUTION_NAME for a28 select EXECUTION_NAME ,INDEX_NAME,TABLE_NAME,COMMAND,STATEMENT,error# from DBA_AUTO_INDEX_IND_ACTIONS; --特性相关数据字典 col OBJECT_NAME for a32 select distinct object_name from dba_objects where object_name like 'DBA_AUTO_INDEX%'; select * from DBA_AUTO_INDEX_STATISTICS where rownum<11; col sql_id for a13 select EXECUTION_NAME,ACTION_ID,SQL_ID,PLAN_HASH_VALUE,COMMAND,STATEMENT,START_TIME from DBA_AUTO_INDEX_SQL_ACTIONS; col ERROR_MESSAGE for a20 select * from (select * from DBA_AUTO_INDEX_EXECUTIONS order by EXECUTION_START) where rownum<11;