Welcome to Oracle Universities Training on OCI, autonomous database auto indexing feature. I'm Angela Wall and I'm going to show you how life with indexes just got easier. Creating indexes manually requires deep knowledge of the Data Model, the application, and how the data is distributed. Often, as DBAs make choices about which indexes to create and then never revise our choices. As a result, opportunities for improvement are lost and unnecessary indexes can become a performance liability. With automatic indexing, the database monitors the application workload, creating and maintaining indexes automatically. The indexing feature is implemented as an automatic task that runs at a fixed interval. In the past, we would print out maps or use maps to determine the best route to get to a destination, but we had no knowledge of what the current traffic was like when we set off on that journey. Nowadays, however, with GPS and navigation systems, we're able to adapt our journey if we wish, based on traffic changes. As you can see in this example, from Oracle Headquarters to the Oakland offices, the shortest distance would be to go via South San Francisco, through San Francisco, and then on to Oakland. The longer distance would be to go through the East Bay taking eight AD but it actually turns out to be a faster journey because there's a lot less traffic. Here, the optimizer and SQL plans are very much like that. Oracle execution plans are like driving directions; they will change as the data distribution changes. That's the data volumes in the statistics. Indexes can be thought of as roads and bridges. With automatic indexing, we're able to detect if there are any workload changes and look for a suitable candidate to introduce an index. As we can see here, with auto-indexing, we have proposed a new path, which in this case would actually be a bridge crossing the entire bay. This could potentially provide us with a much quicker way of solving our problem based on the workload changes. Changes in data volume and SQL workload are continuously captured. We have machine learning algorithms to detect changes and come up with optimal plans and indexes. Which means that at the end of the day, the database has completely and automatically tuned and it is able to adapt to workloads. Oracle execution plans are like driving directions; they will change as the data distribution changes. With auto indexing, new roads will be added as the workload changes. Let us look at the automatic indexing process. First step is the capture. It periodically capture SQL statements into a SQL repository. It will include plans, bind values, execution, statistics, etc. Step 2, identify candidate indexes. Those are the SQL statements that might benefit from those new indexes and they will create those candidate indexes as unusable and invisible indexes. They would just be metadata. Step 3, verify. Ask the optimizer if those candidates will actually use those indexes. If the index candidates are not used, the optimizer will automatically drop those. If they are used, it will complete the creation of those indexes and it will verify that the performance actually improved. Step 4, decide. If performance is better for all statements, the indexes are marked visible. If performance is worse for all statements, the indexes are dropped. If performance is worse for some, the indexes are marked visible except for statements that regressed in performance. Lastly, there's Step 5; monitor. Index usage is continuously monitored. Automatically created indexes that have not been used in a long time will be dropped and any decaying indexes will be rebuilt. Automatic indexing automates the index management task and the autonomous database. You can use the automatic indexing feature to configure and use auto indexes and an Oracle database to improve database performance. Auto indexing is disabled by default in the autonomous database, but you can configure automatic indexing in the database using the DBMS auto index configure procedure. You can use the auto-index mode configuration settings to enable or disable automatic indexing as well. The first statement enables automatic indexing in a database and creates any new auto indexes as visible indexes so that they can be used in SQL statements. This second statement disables automatic indexing in a database so that no new auto indexes are created. Existing auto indexes, however, will remain enabled. This third statement enables automatic indexing in a database, but creates any new auto indexes as invisible indexes so that they cannot be used in SQL statements. You can use the auto index retention for auto configuration setting to specify a period for retaining unused auto indexes in a database. The unused auto indexes are deleted after the specified retention period. This fourth statement sets the retention period for unused auto indexes to 373 days, which is actually the default. The auto index schema parameter can be used to specify schemas that can use auto indexes. Note however, that when automatic indexing is enabled for an entire database, all the schemas in the database can use auto indexing by default. The fifth and sixth statements add the SH and HR schemas to the exclusion list so that the SH and HR schemas cannot use auto indexes. The last statement removes the HR schema from the exclusion list so that the HR schema can use auto indexes. The DBMS auto index report activity function returns a report of the automatic indexing operations executed during a specific period in a database. Here is an example of the output that shows the last 24 hour activity report after the indexes have been created. Here we're showing the automatic indexing page. It is available only if you signed into a dedicated Oracle autonomous transaction processing release 19c database or later as a user with administrator rights. It consists of auto Index settings and activity report pages. You can use the auto Index settings page to configure settings for automatic indexing in a database and use the activity page to configure settings related to generating reports for automatic indexing operations in a database. You can find more information on auto indexing at docs.oracle.com. To wrap things up, Oracles automatic indexing functionality enables you to take all of the guesswork out of the index maintenance process, resulting in better storage and overall resource utilization. Thanks so much for watching.
