oracle 19c虚拟要下自动索引测试

2025-04-13 技术教程

1.关于测试

最近oracle举办了一系列线上分享，其中一个19c的新特性是自动索引。自己在docker中测试了一下，失败。

[oracle@8aa96a41b58b ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 20 09:44:52 2020Version 19.3.0.0.0SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');BEGIN DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT'); END;*ERROR at line 1:ORA-40216: feature not supportedORA-06512: at "SYS.DBMS_SYS_ERROR", line 79ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 283ORA-06512: at line 1自动索引只能在exadata机器上配置。Auto indexing will be allowed only in EXADATA systems, if not it will raise ORA-40216: feature not supported when we try it turn it on in non exadata systems.ORA-40216 When Using Auto Index Feature (Doc ID 2570076.1)

2.检查系统隐含参数

SELECT i.ksppinm name, i.ksppdesc description, CV.ksppstvl VALUE, CV.ksppstdf isdefault, DECODE(BITAND(CV.ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE') ismodified, DECODE(BITAND(CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted FROM sys.x$ksppi i, sys.x$ksppcv CV WHERE i.inst_id = USERENV('Instance') AND CV.inst_id = USERENV('Instance') AND i.indx = CV.indx AND i.ksppinm LIKE '%exadata%'/' ORDER BY REPLACE(i.ksppinm, '_', '');其中有一项为：NAME DESCRIPTION VALUE ISDEFAULT ISMODIFIED ISADJ------------------------ --------------------- ---------- --------- ---------- -----_exadata_feature_on Exadata Feature On FALSE TRUE FALSE FALSE

3.打开隐含参数

SQL> alter system set "_exadata_feature_on"=true scope=spfile;SQL> shut immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 1610609888 bytesFixed Size 9135328 bytesVariable Size 385875968 bytesDatabase Buffers 1207959552 bytesRedo Buffers 7639040 bytesDatabase mounted.Database opened.

4.重新测试
注：以下测试脚本来自oracle在线分享。

重新打开自动索引配置，成功。SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');PL/SQL procedure successfully completed.启用自动索引有三个参数：EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT|REPORT ONLY|OFF');IMPLEMENT：启用自动索引并将创建后的索引设置为可见状态，优化器可使用该索引。REPORT ONLY：启用，索引是不可见自动索引OFF:关闭自动索引cdb自动索引已启用：SQL> COLUMN parameter_name FORMAT A40SQL> COLUMN parameter_value FORMAT A20SQL> SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config; CON_ID PARAMETER_NAME PARAMETER_VALUE---------- ---------------------------------------- -------------------- 1 AUTO_INDEX_COMPRESSION OFF 1 AUTO_INDEX_DEFAULT_TABLESPACE** 1 AUTO_INDEX_MODE IMPLEMENT** 1 AUTO_INDEX_REPORT_RETENTION 31 1 AUTO_INDEX_RETENTION_FOR_AUTO 373 1 AUTO_INDEX_RETENTION_FOR_MANUAL 1 AUTO_INDEX_SCHEMA 1 AUTO_INDEX_SPACE_BUDGET 50 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE 3 AUTO_INDEX_MODE OFF CON_ID PARAMETER_NAME PARAMETER_VALUE---------- ---------------------------------------- -------------------- 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 50

启用pdb自动索引：

SQL> alter session set container=orclpdb;Session altered.SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');PL/SQL procedure successfully completed.SQL> sELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config; CON_ID PARAMETER_NAME PARAMETER_VALUE---------- ---------------------------------------- -------------------- 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE ** 3 AUTO_INDEX_MODE IMPLEMENT** 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 508 rows selected.

创建自动索引的存储空间

默认情况下，在数据库创建期间指定的永久表空间用于存储自动索引。是否配置可检查AUTO_INDEX_DEFAULT_TABLESPACE参数。SQL> create tablespace tbs_auto datafile '/opt/oracle/oradata/ORCL/ORCLPDB/tbs_auto_ind01.dbf' size 100M;Tablespace created.

设置自动索引使用的默认表空间SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','tbs_auto'); PL/SQL procedure successfully completed.SQL> SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config; CON_ID PARAMETER_NAME PARAMETER_VALUE---------- ---------------------------------------- -------------------- 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE TBS_AUTO 3 AUTO_INDEX_MODE IMPLEMENT 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 50保留系统默认使用以下存储过程EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

指定用户启用自动索引

在数据库中启用自动索引后，默认情况下，数据库中的所有用户都可以使用自动索引。可以查看cdb_auto_index_config.AUTO_INDEX_SCHEMA字段查看不同的配置。SH不能使用自动索引(AUTO_INDEX_SCHEMA=schema NOT IN (SH))：EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);仅SH可以使用自动索引(AUTO_INDEX_SCHEMA=schema IN (SH))：EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', TRUE);数据库中的所有用户都可以使用自动索引：EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);

模拟自动索引创建：

SQL> create table hr.tab_auto as select rownum id,t.* from dba_objects t;Table created.SQL> declarea varchar2(2000) := ''; 2 3 begin 4 for x in 1.. 10000 loop 5 select object_name into a from tab_auto where id=x; 6 end loop; 7 end; 8 /PL/SQL procedure successfully completed.SQL> COLUMN task_name FORMAT A30SQL> COLUMN advisor_name FORMAT A30SQL> select task_name,advisor_name from dba_advisor_tasks where owner='SYS' ORDER BY TASK_ID;TASK_NAME ADVISOR_NAME------------------------------ ------------------------------SYS_AUTO_SPM_EVOLVE_TASK SPM Evolve AdvisorSYS_AI_SPM_EVOLVE_TASK SPM Evolve Advisor <<<SYS_AI_VERIFY_TASK SQL Performance Analyzer <<<与自动索引相关SYS_AUTO_INDEX_TASK SQL Access Advisor <<<AUTO_STATS_ADVISOR_TASK Statistics AdvisorINDIVIDUAL_STATS_ADVISOR_TASK Statistics Advisor

过15分钟后查看索引

COLUMN OWNER FORMAT a10col INDEX_TYPE format a10col INDEX_NAME format a20col TABLE_NAME format a20col TABLE_OWNER format a10SELECT OWNER,INDEX_TYPE,INDEX_NAME,TABLE_NAME,TABLE_OWNER FROM DBA_INDEXES WHERE AUTO='YES' ORDER BY OWNER,INDEX_NAME;OWNER INDEX_TYPE INDEX_NAME TABLE_NAME TABLE_OWNE---------- ---------- -------------------- -------------------- ----------HR NORMAL SYS_AI_8abjpspc3b08n TAB_AUTO HR

查看执行计划

SQL> select count(*) from tab_auto where id=100; COUNT(*)---------- 1SQL> select * from table(dbms_xplan.display_cursor(format=>'TYPICAL'));PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------SQL_ID 8fp2w8rwapnbz, child number 0-------------------------------------select count(*) from tab_auto where id=100Plan hash value: 896819007------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 1 (100)| || 1 | SORT AGGREGATE | | 1 | 5 | | ||* 2 | INDEX RANGE SCAN| SYS_AI_8abjpspc3b08n | 1 | 5 | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID"=100)使用了新创建的自动索引。

指定未使用的自动索引的保留期限

使用AUTO_INDEX_RETENTION_FOR_AUTO配置未使用的自动索引保留在数据库中的期限。在指定的保留期限后，将删除未使用的自动索引。注意：默认情况下，未使用的自动索引将在373天后删除。以下语句将未使用的自动索引的保留期限设置为90天。EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '90');以下语句将自动索引的保留期重置为默认值373天。EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);

生成自动索引报告

您可以使用软件包的REPORT_ACTIVITY和REPORT_LAST_ACTIVITY功能在Oracle数据库中生成与自动索引操作有关的报告DBMS_AUTO_INDEX。-- 过去24小时内自动索引操作的典型信息，文本。SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;-- 最新活动的默认TEXT报告。SELECT DBMS_AUTO_INDEX.report_last_activity() FROM dual;-- 指定时间段的HTML报告。SELECT DBMS_AUTO_INDEX.report_activity(activity_start =>TO_TIMESTAMP('2018-11-01', 'YYYY-MM-DD'),activity_end => TO_TIMESTAMP('2018-11-05', 'YYYY-MM-DD'),,type => 'HTML') FROM dual;也可以使用activity_start =>TIMESTAMP - N-- 最新活动的HTML报告。SELECT DBMS_AUTO_INDEX.report_last_activity( type => 'HTML') FROM dual;---最新活动的HTML报告。包括最新活动自动索引操作的摘要，索引详细信息和错误信息DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(type => 'HTML',section => 'SUMMARY +INDEX_DETAILS +ERRORS',level => 'BASIC');