By Arup Nanda
May 15, 2019
John, the chief architect of Acme Travels, walks into the office of the CTO, Tanya, and he can almost smell the frostiness in the air. Acme, a travel products company, has been operating for more than 20 years, and during that time, the company’s data model has evolved—as any 20-year-old company’s data model would. The developers create new tables, alter the existing tables, write new SQL statements, and modify the existing SQL constantly. To improve performance, they create indexes on the columns proactively, but more often they add indexes later—after a performance issue has been detected.
The DBA manager, Betty, points out that adding indexes to address performance issues is a double-edged sword. It may improve performance for some queries, but it will definitely slow down INSERT statements and may negatively impact UPDATE and DELETE statements, often enough to negate any perceived performance gains elsewhere. Therefore Betty’s team doesn’t create indexes willy-nilly but only after a very careful analysis of the impact on all queries, sometimes with the use of Oracle Database’s SQL Performance Analyzer feature, and even then, those indexes are almost always created after the SQL statements have been issued against the database. When the SQL statements that use the new indexes cause performance issues, DBAs are blamed, and DBAs, in turn, blame the developers for writing the poorly performing queries in the first place. Similarly, as a result of changing queries, indexes created earlier are sometimes no longer needed. The DBAs check for usage of indexes and drop them if they’re unused, which is also a time-consuming—not to mention risky—exercise for the DBAs.
Debbie, the development manager, expects the DBAs to perform this analysis 24 hours a day, seven days a week, along with everything else they are doing.
“You’ve got to be kidding to suggest something that preposterous, especially with our lean staff!” says an enraged Betty.
“Well, we certainly can’t do it!” responds Debbie. “We follow Agile methodology for development. We make small changes, perhaps 100 times a week. There is no way for us to slow down and check the impact of each change line by line. The analysis and changes must come from your team.”
The tension inside the office is so thick you could cut it with a knife. Obviously, Tanya doesn’t like it and wants to end it with a solution acceptable to everyone and also beneficial to Acme. She does agree that with Agile development practices and the continuous improvement/ continuous deployment (CI/CD) philosophy, it’s important to maintain the velocity of development. So she also understands that these indexing issues must be dealt with after development, not before. But at the same time, she understands that expecting the DBAs to do the time-consuming analysis and make the risky changes 24/7 is impractical unless she quadruples the DBA staff, which she can’t do.
She wonders out loud if the teams should slow down development considerably and include a thorough analysis before deploying applications. No way, Debbie responds, because that would be catastrophic for application delivery. And this time Betty agrees with her, saying that it would not solve the problem entirely, because there will still be indexes to be analyzed—just more in a bunch rather than spread out through the releases. So, although the original problem would still exist, Acme would lose the benefit of the CI/CD approach. It’s a lose-lose for everyone.
“Well, so much for solutions,” sighs Tanya. “That’s why I asked John to be here,” she announces. “I’m hoping he knows if there’s a way to get us out of this bind.”
All eyes turn to John. “Yes, there is,” smiles John, trying to warm the chill.
John, who until recently was the lead DBA at Acme, reminds everyone how indexes are being managed now. Most of them are created as a part of the performance issues captured and solutions recommended by the advisors that come with Oracle Database, such as Index Advisor, Partition Advisor, and In-Memory Advisor. These advisors alert the DBA to the potential issues and advise if a new index would actually help. However, the responsibility for determining the overall impact and implementing the indexes—both fairly onerous tasks—still lies with the DBAs. For example, the DBAs must determine
The last part is particularly tricky, John points out. The table and columns, and even the SQL statements, may not have changed, but if the data pattern has changed, the once-useful indexes may not be useful anymore while also being a drag on the performance of DML statements. Similarly, some indexes not needed earlier could actually help after data pattern changes. And because data pattern changes are almost impossible to track down, the battle for the ideal indexes is usually a losing one.
This is where a new feature in Oracle Database 19c, Automatic Indexing, comes to the rescue, continues John. The feature acts like a DBA inside the database that evaluates the need for new indexes and the need for the existing indexes, creates new ones if they’re needed, and drops them when they are no longer needed. Both single-column and concatenated indexes are considered by the feature, so it covers a majority of the indexes typically used in a database. But the best part, John reminds everyone, is that it’s software: It works 24 hours a day, seven days a week, and does not get tired, does not take sick leave, and does not ask for raises. It will do everything Tanya is asking for.
Everyone is listening.
Connected to Oracle Database 19c, John starts his demo by executing this SQL statement to enable Automatic Indexing.
begin
dbms_auto_index.configure ('AUTO_INDEX_MODE', 'IMPLEMENT');
end;
/
Betty is a bit queasy about running a statement like this, one that implements automatic indexing in the database without her knowing it. “That’s understandable,” John agrees. John runs another SQL statement to prepare the database for the feature, but he specifies that SQL statements not use the new autocreated indexes.
begin
dbms_auto_index.configure ('AUTO_INDEX_MODE','REPORT ONLY');
end;
/
This sets in motion the process of identifying the possible indexes. Automatic Indexing, John explains, captures the SQL statements, identifies those from the list that may be helped by indexing, and then creates those indexes—automatically. These autocreated indexes are named with the prefix SYS_AI, to differentiate them from the manually created indexes. A new column, named AUTO in the DBA_INDEXES view, shows a YES value for these indexes.
“Wait a minute,” interrupts Betty. “It simply creates an index without checking in a representational test system whether the index will actually help or hurt something else? That sounds like an incredibly stupid idea, especially for a machine.”
“Not quite,” assures John. Creating an index on a whim is not smart. Therefore, the Automatic Indexing feature creates the index as invisible—it is not known to the database optimizer. He explains the concept of an invisible index to the audience with a simple demonstration.
He creates an index, but with the invisible keyword.
SQL> create index ix_region_id_01 on regions (region_name) invisible; Index created.
Then he checks the plan of a very simple SQL statement against that table.
SQL> explain plan for
2 select * from regions where region_name = 'Europe';
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 3077898360
----------------------------------------------------------------------------
|Id | Operation | Name | Rows| Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |. | 1 | 14 | 3 (0) | 00:00:01 |
|*1 | TABLE ACCESS FULL| REGIONS | 1 | 14 | 3 (0) | 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
1 - filter("REGION_NAME"='Europe')
13 rows selected.
John calls the attention of the audience to the output and the fact that the SQL statement did not use the index just created where it could have. The reason is simple: The index is marked as invisible to the optimizer.
He then alters the index to make it visible to the optimizer and checks the execution plan of the same SQL statement.
SQL> alter index ix_region_id_01 visible;
Index altered.
SQL> explain plan for select * from regions where region_name = 'Europe';
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 3897602228
-----------------------------------------------------------------------------------------------
|Id| Operation. | Name | Rows| Bytes| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| 14| 2 (0) | 00:00:01|
| 1| TABLE ACCESS BY INDEX ROWID BATCHED| REGIONS | 1| 14| 2 (0 )| 00:00:01|
|*2| INDEX RANGE SCAN | IX_REGION_ID_01| 1| | 1 (0) | 00:00:01|
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("REGION_NAME"='Europe')
Pointing to the output that shows that the IX_REGION_ID_01 index is used this time, John highlights that this happens because the optimizer now recognizes the presence of this index. Automatic Indexing autocreates indexes as invisible first and then tests the index impact against SQL statements. If the impact is positive—if the SQL statements perform better with an index—then the index is made visible, as John shows; otherwise, that index is marked unusable. To demonstrate that concept, John marks the index unusable and checks the execution plan once again:
SQL> alter index IX_REGION_ID_01 unusable; Index altered. SQL> explain plan for select * from regions where region_name = 'Europe'; Explained. SQL> select * from table(dbms_xplan.display()); Plan hash value: 3077898360 -------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 | |*1 | TABLE ACCESS FULL| REGIONS | 1 | 14 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
Pointing to the output, John shows that the index is not used now, even though it’s visible. He confirms that by using the following SQL:
SQL> select VISIBILITY, STATUS from user_indexes where index_name = 'IX_REGION_ID_01'; VISIBILITY STATUS ---------- -------- VISIBLE UNUSABLE
The Automatic Indexing feature, John explains, checks the SQL statements and evaluates if they can perform better via new indexes. It then creates the indexes as invisible. After that it checks the execution plans of all the SQL statements using that new index. If all the statements show improvement (or no impact), it will make the index visible. If all the SQL statements show degraded performance, the index will remain invisible.
Debbie seems unconvinced. A more likely scenario, she opines, is that some SQL statements will show improvements and some will show degradation due to this autocreated index. What will the fate of the index be then?
John agrees that scenario is most likely, and he assures her that the feature handles such a situation quite well. In this case, the index will be made visible, but Automatic Indexing will create a SQL plan baseline to prevent a SQL statement that regressed in performance from using the index. The other SQL statements—statements that did not regress in performance—will continue to use the index. Debbie nods in satisfaction.
“But what component of Oracle Database actually performs this autoindexing ,” Tanya asks. “Starting with Oracle Database 11g, the database automatically executes multiple tasks,” John answers. For example, Oracle’s advisors run as automatic tasks and the DBA_ADVISOR_TASKS view shows information about these tasks. John pulls up the view data:
select *
from dba_advisor_tasks
where owner='SYS'
order by task_id;
TASK_ID TASK_NAME ADVISOR_NAME
2 SYS_AUTO_SPM_EVOLVE_TASK SPM Evolve Advisor
3 SYS_AI_SPM_EVOLVE_TASK SPM Evolve Advisor
4 SYS_AI_VERIFY_TASK SQL Performance Analyzer
5 SYS_AUTO_INDEX_TASK SQL Access Advisor
6 AUTO_STATS_ADVISOR_TASK Statistics Advisor
7 INDIVIDUAL_STATS_ADVISOR_TASK Statistics Advisor
He points out the SYS_AUTO_INDEX_TASK task and two tasks with _AI_ in their name. These are the tasks behind the Automatic Indexing feature.
Betty’s interest is piqued now. “How do the DBAs administer this feature?” she asks. John reminds her that the DBAs actually do not need to implement anything; the system detects the need to create indexes automatically and creates them, and it even drops them when they are not needed. So the DBAs’ involvement is limited to
To set the default tablespace of the autocreated indexes to USER_AI, John uses the following:
dbms_auto_index.configure ('AUTO_INDEX_DEFAULT_TABLESPACE', 'USER_AI');
But autocreated indexes may overwhelm the space in that tablespace. To prevent that, John executes the following to ensure that only 50% of the tablespace is used:
dbms_auto_index.configure ('AUTO_INDEX_SPACE_BUDGET', '50');
Sometimes third-party application vendors don’t allow index creation or deletion outside of their control. “Will this feature break those applications?” asks one of the DBAs. “Not at all,” assures John. To exclude a specific schema, John uses
dbms_auto_index.configure ('AUTO_INDEX_EXCLUDE_SCHEMA', 'SCOTT');
“But I am not comfortable that all these important activities would be performed by the system under the covers without our knowing about it,” says Betty. Others chime in as well. The actions are hardly opaque, John assures them. All the current and historical actions are available in several data dictionary views. He points to the following types of information and views containing them to his audience:
| To get . . . | Use this view |
| The history of Automatic Indexing task executions | DBA_AUTO_INDEX_EXECUTIONS |
| Statistics related to automatic indexes | DBA_AUTO_INDEX_STATISTICS |
| Actions performed on automatic indexes | DBA_AUTO_INDEX_IND_ACTIONS |
| Actions performed on SQL to verify automatic indexes | DBA_AUTO_INDEX_SQL_ACTIONS |
| The history of configuration settings related to automatic indexes | DBA_AUTO_INDEX_CONFIG |
But the easiest way to monitor Automatic Indexing is to use the built-in report feature. The DBMS_AUTO_INDEX.REPORT_ACTIVITY function returns a CLOB containing all the relevant details. John pulls the data for activities between the 20th and 21st of April.
declare
report clob := null;
begin
report := DBMS_AUTO_INDEX.REPORT_ACTIVITY (
activity_start => TO_TIMESTAMP('2019-04-20', 'YYYY-MM-DD'),
activity_end => TO_TIMESTAMP('2019-04-21', 'YYYY-MM-DD'),
type => 'TEXT',
section => 'SUMMARY',
level => 'BASIC');
end;
This produces a report containing all the needed information. Here is part of the report:
-------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------
Activity start : 20-APR-2019 00:00:00
Activity end : 20-APR-2019 00:00:00
Executions completed. : 27
Executions interrupted : 2
Executions with fatal error : 0
--------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
--------------------------------------------------------------------------
Index candidates : 98
Indexes created (visible / invisible) : 21/0
Space used (visible / invisible). : 312.23 MB (312.23 MB / 0 MB)
Indexes dropped : 2
SQL statements verified : 312
SQL statements improved (improvement factor) : 115 (3x)
SQL statements disallowed from auto indexes : 34
Overall improvement factor : 3x
--------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
--------------------------------------------------------------------------
Unused indexes (visible / invisible) : 9 (6 / 3)
Space used (visible / invisible) : 281 MB (183 MB / 98 MB)
Unusable indexes : 0
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:*: invisible
-------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
----------------------------------------------------------------------------------------
| HR | DEPARTMENTS | SYS_AI_0urcv8chmxu20 | LOCATION_ID | B-TREE | NONE |
| HR | LOCATIONS | SYS_AI_1hgrs7xdghs31 | CITY | B-TREE | NONE |
... report truncated to save space ...
-------------------------------------------------------------------------------
VERIFICATION DETAILS
----------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
----------------------------------------------------------------
Schema Name : HR
SQL ID : 3dfa28psdfe73
SQL Text : select * from regions where region_name = 'Europe';
Improvement Factor : 2x
PLANS SECTION
---------------------------------------------------------------------------------
Original
-----------------------------
Plan hash value: 3077898360
---------------------------------------------------------------------------
| Id | Operation | Name | Rows| Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| 14 |. 3 (0)| 00:00:01|
| *1 | TABLE ACCESS FULL | REGIONS | 1| 14 | 3 (0)| 00:00:01|
---------------------------------------------------------------------------
With Auto Indexes
-----------------------------
Plan hash value: 3897602228
-------------------------------------------------------------------------------------------
|Id | Operation | Name | Rows| Bytes| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| 13| 2(0) | 00:00:01|
| 1 | TABLE ACCESS BY INDEX ...| REGIONS | 1| 13| 2(0) | 00:00:01|
|*2 | INDEX RANGE SCAN | SYS_AI_8hjkdfss93kdf| 1| | 1(0). | 00:00:01|
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("REGION_NAME"='Europe')
... report truncated to save space ...
The report, John explains, provides vital information on the activities of the Automatic Indexing feature, such as when it ran, how many SQL statements it considered, how many automatic indexes were created, how much space they consumed, and so on. In the subsequent sections, it also shows the indexes automatically created on columns. The last part of the report shows the SQL statements and the execution plans before and after the autocreated indexes, to confirm the improvements. This is the report both Debbie and Betty can examine to see the effectiveness of the Automatic Indexing feature at both a high and detail level.
Debbie is still somewhat skeptical. During the next evaluation of SQL statements by this feature, she explains, an existing SQL statement will likely inspire the automatic creation of an index that has already been created but not used. And Automatic Indexing will once again be forced to evaluate that index and again discard it as not useful. Isn’t this a vicious cycle of wasted resources, she asks, whereas a human would’ve avoided it?
Absolutely, agrees John; this would be a vicious cycle. To prevent it, he explains, the feature marks those SQL statements, and subsequent executions exclude these SQL statements from consideration and avoid getting into vicious cycles of repeated evaluation, creation, and invisibility of indexes.
Debbie is relieved but has another doubt. Sometimes the system simply won’t know the negative impact of an autocreated index as well as a human performance tuner. In this case, the SQL statement using the automatic index will simply be detrimental. Is there a way to suppress the use of automatic indexes for a query proactively, she wants to know.
“Of course there is,” replies John. If Debbie wants the optimizer not to use autocreated indexes for a specific SQL statement, she can simply include a hint:
select /*+ NO_USE_AUTO_INDEXES */ from regions where region_name = 'Europe';
Likewise, the USE_AUTO_INDEXES hint acts just the opposite way: It forces the SQL statement to use the autocreated indexes, if they are available.
“But that’s not all,” John continues. “Automatic Indexing includes several additional safety features to protect the database from damage.” He lists a handful of key ones:
DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_RETENTION_FOR_AUTO', '100')
However, the unused manually created indexes are never deleted by the automatic indexing process. They can be deleted automatically, if needed, but never by default. Betty can set another property, AUTO_INDEX_RETENTION_FOR_MANUAL, to specify after how many days the unused manual indexes can be dropped.This seems to allay everyone’s concern that something could go terribly out of control. In conclusion, John points out the big difference between the advisors available earlier and the Automatic Indexing feature in Oracle Database 19c. The advisors identify the need for and suggest possible indexes, but the onus of deciding whether those indexes will help or not lies with the DBAs. Automatic Indexing takes that responsibility away; it implements needed indexes automatically and constantly checks for their usefulness. DBA tasks are limited to setting the configuration parameters such as the default tablespace or the number of days to retain the unused indexes and getting reports on Automatic Indexing activities. Everyone is impressed with this new feature, thanks John, and leaves the room a little warmer and with their usual smile back on.
LEARN more about
Automatic Indexing.
Oracle Database 19c.
TRY Oracle Autonomous Database.
Illustration by Wes Rowell
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.