Oracle 19c: Auto Indexing Feature testing – Onprem

Edited this post on 30-Apr, to correct my command in the test.

Update the post on 22-May, not working in on-prem as expected.

Whilst the documentation say's the auto indexing feature is not available on-prem ones, I tried to test it out.

Command to enable auto index mode is below, we have options "Implement", "Report-Only", "OFF".

exec dbms_auto_index.configure('auto_index_mode','implement'); --> does not work use internal feature. is this something for cloud only or have to verify.

exec dbms_auto_index_internal.configure('auto_index_mode','implement');

We can verify then

select * from dba_auto_index_config order by 1;

And see advisor tasks for auto index is enabled,

select * from dba_advisor_tasks where owner='SYS' order by task_id;

To enable just for a schema,

dbms_auto_index_internal.configure(parameter_name  => 'AUTO_INDEX_SCHEMA',  parameter_value => '&SCHEMANAME',  allow  => FALSE);

Inside in a pdb, just in case.

All it works in this fashion (from juliandontcheff blog)

Capture --> Identify --> Verify --> Decide --> Monitor --> (Cycle repeats)

– The auto index candidates are created as invisible auto indexes
– If the performance of SQL statements is not improved from the auto indexes, then the indexes are marked as unusable and the corresponding SQL statements are blacklisted
– Auto indexes cannot be used for any first time SQL run against the database
– Auto indexes are created as either single, concatenated indexes or function-based indexes and they all use advanced low compression
– The unused auto indexes are deleted after 373 days (can be changed)
– The unused non-auto indexes (manual indexes) are never deleted by the automatic indexing process but can be deleted automatically if needed

Some important dynamic views and queries regarding auto index features. (From Frank Pachot blog)

select * from sys.smb$config where parameter_name like '%AUTO_INDEX%' order by 1;

select * from dba_advisor_tasks where owner='SYS' order by task_id;

select * from dba_advisor_executions where task_name='SYS_AUTO_INDEX_TASK' order by execution_id;

select * from dba_auto_index_executions order by execution_start;

select * from dba_advisor_objects where task_name='SYS_AUTO_INDEX_TASK' and type='AUTO INDEX INFORMATION'order by object_id;

select attr7,sys.dbms_auto_index_internal.finding_name(attr7) from (select rownum attr7 from xmltable('1 to 51')) order by 1;

select * from sys."_auto_index_log" order by log_id;

select * from dba_auto_index_statistics where value>0 order by 1;

select * from sys."_auto_index_ind_objects" order by object_id;

select auto,count(*) from dba_indexes group by auto;

I will post more details once the task running and show recommendations.

Thanks

Suresh