Migrating Oracle Exadata Workloads to Azure- Storage Indexes

I’m about simplifying anything for customers as we bring over complex environments into Azure and Oracle databases running on Exadata is a big part of these challenges.  Decoupling the database from the engineered features is a crucial part of my work and with Oracle 19c, having customers running on the terminal release isn’t the only reason to upgrade if the database is on an earlier release.

As I’ve discussed in other posts, blogs and articles, I have numerous ways to address latency when losing cell node offloading, hybrid columnar compression (HCC), thin cloning with sparse clone, flash cache, flash logging, etc., but storage indexes are unique to Exadata that simply have no comparable work around.

For those who aren’t familiar with this Exadata feature, storage indexes are an automatic feature based on the where clause of SQL statements run in each database.  These indexes are created only in memory on the storage cells only, keeping track of the min and max values of columns within each block of data.

Again, this is all based on usage, so the more the query is run, the more often the index values could be updated and storage indexes, residing in memory, would have to rebuild over time if there was a power cycle of the Exadata or database.  The goal of a storage index is to identify the blocks in range for any given set of values and eliminate unnecessary IO calls to the cell node or disks the data might not exist.

There are some requirements to get the best performance from a storage index, such as well designed where predicates and cleanly sorted data in tables, (think sequence populated columns).

Lost Without You

What can we do when losing this valuable feature coming from an engineered system where the feature no long exists?  You can test the performance beforehand by disabling storage indexes on a staging or test area by altering the spfile value:

SQL>alter system set _KCFIS_STORAGEIDX_DISABLED=TRUE;

This can tell you how the database would perform without storage indexes available to it and can offer some insight, but keep in mind, the IO benefit of cell node offloading will still be enabled at this point, (secondary discussion that we won’t go into here.)

Autonomous/Automatic Indexing in 19c

With the introduction of Oracle release 19c, we now have one more Exadata feature in the Enterprise Edition of Oracle- Autonomous/Automatic indexing (I’ve seen it referred to with both names) This addition to 19c  can help reduce increased IO and performance hits when we migrate from Exadata by automatically creating, testing, turning on good indexes and if not beneficial, dropping them without any human intervention once turned on.  This will assist with:

1. Indexes which may be missing to increase the potential of table scans and offloading to the cell nodes while the database was on Exadata.
2. Create substitute indexes that can supplement performance outside of the benefit of storage indexes no longer available.

This new 19c feature is supported with the DBMS_AUTO_INDEX and is turned off by default in 19c.  First verify the disabled status and then turn it on:

SQL>SELECT con_id, parameter_name, parameter_value
FROM cdb_auto_index_config where parameter_name = 'AUTO_INDEX_MODE';

SQL>EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
SQL>EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

To disable it again, it’s as simple as follows:

SQL>EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

When enabled, the job will identify candidate indexes and will create the indexes in an invisible status and perform some tests from the SQL executed to see if there is a benefit to the index.  If the index fails the tests, the index is dropped in an invisible status, completely transparent to the user or application.

If the index is found to be beneficial, it is turned to a status of visible and made part of the database. Once these indexes are created, you can identify them quickly by their naming convention, which always start with “SYS” or use the following query:

SQL>SELECT owner, index_name, index_type, table_owner, table_name
FROM dba_indexes
WHERE auto = 'YES'
ORDER BY owner, index_name;

These indexes are completely managed by Oracle, so don’t try to move them, rebuild them, etc.  Oracle will manage the lifecycle of these indexes for you, including dropping them if they no longer are worthwhile.

Reality Check

Now I want to be clear- this isn’t a replacement for storage indexes in Exadata, but with the option to offload and use of storage indexes, I consistently experience a considerable amount of indexes missing from migrated databases that could benefit performance- this is just the nature of what happens to an Oracle database on an Exadata.

The greatest opportunity for success in the cloud for Oracle databases is to eliminate excess IO, as they are IO heavy by nature.  In an ideal world, this would be addressed with optimization of SQL, but rarely is their enough time for development or vendors willing to make the changes that would make the largest impact.  Instead, I’m more likely to recommend a significant partitioning strategy or enhance the one in place when leaving Exadata to eliminate more IO.  Those customers that have combined Oracle advanced partitioning, advanced compression and automatic indexing have shown impressive decreases in IO demands of Exadata workloads when migrating to the cloud for incredible success of Oracle on IaaS in Azure.

Hope you and yours are well and have a great weekend!