Hello everyone. Today we are going to talk about a new feature of Oracle 19c that we believe can be very useful. The functionality is called Automatic Indexing and is one of the most important advances that have been made in recent years.
What is Automatic Indexing?
Basically, the database analyzes the current database load and is able to identify possible indexes that can autonomously improve it. It creates them in a way that is not visible to users (invisible) and evaluates if their performance is as expected, if so, it makes them visible to the applications and leaves them as permanent.
The next step in the evaluation process is as follows:
It is a process that runs every 15 minutes by default in the background and takes care of the maintenance of the indexes. The functionality is managed with the DBMS_AUTO_INDEX package and has the following peculiarities:
- Candidate automatic indexes are created as inviable indexes by default.
- If the performance of the SQL statements does not improve with the new index, the index is marked UNUSABLE and the SQL statements are marked in a blacklist.
- Auto indexes cannot be used for a SQL statement that is executed for the first time in the database.
- Auto indexes are created as simple (one column), concatenated indexes or indexes with functions and all use advanced low compression.
- Unused auto indexes are deleted after 373 days by default.
- Indexes that are not automatic but are not used (those created manually) are never deleted by the automatic indexing process (but this can also be configured).
The automatic indexing process can be deactivated at any time or left alone in report mode, being created only in invisible mode and not being used by the manager. This is done with the commands:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
View process activity
To see the activity performed by the process, the report_activity of the package itself is used:
SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
As we have seen, the functionality is very interesting and in the sites that we have tested it is usually work quite well.
We hope you like the entry and see you next time. If you don’t want to miss any of them, sign up for our monthly newsletter. With just one email per month you will be informed of all our publications.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn
More information: https://blogs.oracle.com/oraclemagazine/autonomous-indexing