A quick note: This content is a repost from my previous site. I’m excited to bring this valuable workshop back after some unexpected hosting challenges!
This exercise walks through Oracle 19c’s intelligent Automatic Indexing feature. Making a note as how to configure, observe, and implement auto indexes to boost the database performance.
Displaying Current Configuration
First, I always start by checking how Automatic Indexing is currently set up across my database. The CDB_AUTO_INDEX_CONFIG view shows all the key parameters.
SQL
-- Set display formatting for clarity
column parameter_name format a40
column parameter_value format a15
-- Query the auto index configuration for all containers
select con_id, parameter_name, parameter_value
from cdb_auto_index_config
order by 1, 2;
Output:
CON_ID PARAMETER_NAME PARAMETER_VALUE
---------- ---------------------------------------- ---------------
1 AUTO_INDEX_COMPRESSION OFF
1 AUTO_INDEX_DEFAULT_TABLESPACE
1 AUTO_INDEX_MODE OFF
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
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
Understanding Container-Specific Settings:
If I switch to a specific pluggable database, my view will narrow to just that container’s settings.
SQL
-- Change my current working container to pdb1
alter session set container = pdb1;
-- Run the same configuration query
SQL> @auto_index_config.sql
Output:
CON_ID PARAMETER_NAME PARAMETER_VALUE
---------- ---------------------------------------- ---------------
3 AUTO_INDEX_COMPRESSION OFF
3 AUTO_INDEX_DEFAULT_TABLESPACE
3 AUTO_INDEX_MODE OFF
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
Workaround for Non-Engineered Systems
For databases not running on Oracle Engineered Systems like Exadata, I’ve found a simple tweak is needed to enable the Automatic Indexing feature. This sets a hidden parameter to allow the functionality.
SQL
-- Connect as sysdba to perform system-level changes
sqlplus / as sysdba
-- Set the hidden parameter to enable Exadata features (for Auto Indexing)
alter system set "_exadata_feature_on"=true scope=spfile;
-- Shut down the database to apply the parameter change
shutdown immediate;
-- Start the database to bring the new parameter into effect
startup;
Configuring and Observing Automatic Indexing
Now, I configure Automatic Indexing. I like to start in a ‘report-only’ mode. This means it will identify potential indexes but won’t create them yet, allowing me to review its recommendations. I also specify which schema to monitor.
SQL
-- Set the auto index mode to 'REPORT ONLY'.
-- In this mode, Oracle identifies beneficial indexes but doesn't create them,
-- allowing me to review its recommendations.
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
Output:
PL/SQL procedure successfully completed.
SQL
-- Enable automatic indexing for the 'DEMO' schema.
-- This tells Oracle's auto-indexing process to focus its analysis on objects within this schema.
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','DEMO',TRUE);
Output:
PL/SQL procedure successfully completed.
Simulating Workload and Generating Reports
Next, I run a few queries against a large table with no existing indexes. This activity gives Automatic Indexing data to analyze.
SQL
-- Connect as sysdba, then switch to the pluggable database 'pdb1'
sqlplus / as sysdba
SQL> alter session set container=pdb1;
-- Connect to the 'demo' schema, which I configured for auto indexing
SQL> conn demo/demo
Output:
Connected.
SQL
-- I execute a few queries. These queries will be observed by the Automatic Indexing feature.
SQL> select * from mysales where id=4711;
SQL> select * from mysales where id=4713;
SQL> select * from mysales where id=4715;
SQL
-- Set display options for the report
SQL> SET LONG 1000000 PAGESIZE 0
-- I generate a report on the automatic indexing activity.
-- This report shows what indexes Oracle *would* have created or recommended in 'REPORT ONLY' mode.
SQL> SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
Implementing Automatic Indexes
Now, I tell Automatic Indexing to actually create the indexes it recommends. I also specify a default tablespace for these new auto-generated indexes.
SQL
-- I set the default tablespace for newly created automatic indexes.
-- This ensures auto indexes are placed in a designated storage area.
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_IDX');
Output:
PL/SQL procedure successfully completed.
SQL
-- I change the auto index mode to 'IMPLEMENT'.
-- In this mode, Oracle will now automatically create and manage indexes based on observed workload.
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
Output:
PL/SQL procedure successfully completed.
Observing Implemented Indexes
I execute the same queries again. This time, Automatic Indexing should have created new indexes, and I check the execution plan to confirm their use.
SQL
-- I execute the same query. Automatic Indexing should now create and utilize an index.
SQL> select * from mysales where id=4711;
-- I display the execution plan for the last executed SQL statement.
-- This will show if an automatically created index is now being used.
select * from table (dbms_xplan.display_cursor);
Final Activity Report
I generate the Automatic Indexing activity report one last time. Now, I notice that the report shows that indexes have been physically created and are visible.
SQL
-- I generate the comprehensive report on automatic indexing activity.
-- By default, it covers the past 24 hours. This report will confirm
-- that indexes have moved from 'recommended' to 'created' status.
SQL> SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
Quick Heads-Up!
- Images: Some pictures you see here were made with a little help from AI. They’re fun and useful!
- Logos & Brands: Any company names, logos, or brands belong to their rightful owners.
- My Thoughts: What I write here is just my opinion.
- Code Safety: Please test any code you find here! Try it out in a safe, non-live area first. I can’t be responsible if things go wrong without your own checks.
Recent Comments