oracle21c自动化索引功能实践脚本记录-2023

一、开启自动化索引--隐藏参数

sqlplus / as sysdba <<EOF
alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;
exit;
EOF

二、创建自动化索引专用表空间-- AUTO_IND_TS

create tablespace AUTO_IND_TS
datafile '/u01/oradata/MDD/auto_ind_ts01.dbf' size 32767M;
alter tablespace AUTO_IND_TS

三、配置默认表空间为上面创建的表空间

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_IND_TS');

四、指定需要启用自动化索引的shema

#!/bin/sh
list="
pdb1
pdb2
"
for one in $list
do
sqlplus -S "/ as sysdba"<<EOF
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', '${one}', allow => TRUE);
exit
EOF
done
​

五、启用自动化索引功能

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
其他相关操作

#EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO','150'); --设置清除unused auto indexes时间周期

#EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY'); --报告模式
#EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF'); -- 关闭自动化索引功能

六、查看确认配置是否生效

COLUMN parameter_name FORMAT A40
COLUMN parameter_value FORMAT A15

SELECT con_id, parameter_name, parameter_value
FROM cdb_auto_index_config
ORDER BY 1, 2;

七、获取自动化索引相关的report

-- 前天的HTML报告。
SELECT DBMS_AUTO_INDEX.report_activity(
 activity_start => SYSTIMESTAMP-1,
 activity_end => SYSTIMESTAMP,
 type => 'HTML')
FROM dual;

-- 最新活动的HTML报告。
SELECT DBMS_AUTO_INDEX.report_last_activity(
 type => 'HTML')
FROM dual;