1. About testing
Recently oracle held a series of online sharing, one of the new features of 19c is automatic indexing. I tested it in docker and failed.
[oracle@8aa96a41b58b ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 20 09:44:52 2020
Version 19.3.0.0.0
SQL> 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 supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 283
ORA-06512: at line 1
//Autoindex can only be configured on exadata machines.
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. Check the hidden parameters of the system
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, '_', '');
//One of them is:
NAME DESCRIPTION VALUE ISDEFAULT ISMODIFIED ISADJ
------------------------ --------------------- ---------- --------- ---------- -----
_exadata_feature_on Exadata Feature On FALSE TRUE FALSE FALSE3. Open hidden parameters
SQL> alter system set "_exadata_feature_on"=true scope=spfile; SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1610609888 bytes Fixed Size 9135328 bytes Variable Size 385875968 bytes Database Buffers 1207959552 bytes Redo Buffers 7639040 bytes Database mounted. Database opened.
4. Retest
Note: the following test script is from oracle online sharing.
Reopen auto index configuration, successful.
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
PL/SQL procedure successfully completed.
//There are three parameters to enable automatic indexing:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT|REPORT ONLY|OFF');
IMPLEMENT: Enable automatic indexing and set the created index to visible, which can be used by the optimizer.
REPORT ONLY: Enabled, index is invisible auto index
OFF:Turn off automatic indexing
cdb Auto index enabled:
SQL> COLUMN parameter_name FORMAT A40
SQL> COLUMN parameter_value FORMAT A20
SQL> 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 50Enable pdb auto indexing:
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 50
8 rows selected.Create storage for automatic indexing
By default, the persistent tablespace specified during database creation is used to store automatic indexes. Configure to check the auto? Index? Default? Tablespace parameter. SQL> create tablespace tbs_auto datafile '/opt/oracle/oradata/ORCL/ORCLPDB/tbs_auto_ind01.dbf' size 100M; Tablespace created.
Set the default tablespace for automatic indexing
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
//Keep the system using the following stored procedures by default
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);
Specify that users enable automatic indexing
When automatic indexing is enabled in the database, by default, automatic indexing is available to all users in the database.
You can view the CDB auto index config.auto index schema field to see the different configurations.
SH cannot use auto index (schema = schema not in (SH)):
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);
Only sh can use auto index (schema = schema in (SH)):
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', TRUE);
Automatic indexing is available to all users in the database:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);Simulate automatic index creation:
SQL> create table hr.tab_auto as select rownum id,t.* from dba_objects t; Table created. SQL> declare a 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 A30 SQL> COLUMN advisor_name FORMAT A30 SQL> 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 Advisor SYS_AI_SPM_EVOLVE_TASK SPM Evolve Advisor <<< SYS_AI_VERIFY_TASK SQL Performance Analyzer <<<Related to automatic indexing SYS_AUTO_INDEX_TASK SQL Access Advisor <<< AUTO_STATS_ADVISOR_TASK Statistics Advisor INDIVIDUAL_STATS_ADVISOR_TASK Statistics Advisor
Check index in 15 minutes
COLUMN OWNER FORMAT a10 col INDEX_TYPE format a10 col INDEX_NAME format a20 col TABLE_NAME format a20 col TABLE_OWNER format a10 SELECT 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
View execution plan
SQL> select count(*) from tab_auto where id=100;
COUNT(*)
----------
1
SQL> 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=100
Plan 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)
//The newly created auto index is used.Specifies the retention period for unused auto indexes
Use auto? Index? Retention? For? Auto to configure how long unused auto indexes remain in the database. After the specified retention period, unused auto indexes are removed.
Note: by default, unused auto indexes are removed after 373 days.
The following statement sets the retention period for unused auto indexes to 90 days.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '90');
The following statement resets the retention period of the auto index to the default value of 373 days.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);Generate auto index report
You can use the package's REPORT ACTIVITY and REPORT LAST ACTIVITY functions to generate a report, DBMS AUTO INDEX, in an Oracle database related to automatic indexing operations.
--Typical information of automatic index operation in the past 24 hours, text.
SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
--Default TEXT report for the latest activity.
SELECT DBMS_AUTO_INDEX.report_last_activity() FROM dual;
--HTML report for the specified time period.
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;
You can also use activity_start = > timestamp - n
--HTML report of the latest activity.
SELECT DBMS_AUTO_INDEX.report_last_activity( type => 'HTML') FROM dual;
---HTML report of the latest activity. Includes a summary of the latest active auto index operations, index details, and error information
DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(type => 'HTML',section => 'SUMMARY +INDEX_DETAILS +ERRORS',level => 'BASIC');Related view
Displays the current configuration settings for automatic indexing. DBA_AUTO_INDEX_CONFIG The AUTO column in some views indicates whether the index is YES or NO. DBA_INDEXES ALL_INDEXES USER_INDEXES