It’s always an educational experience to listen to Oracle ACE, Janis Griffin talk about database performance tuning and the features that help make life easier. In a Database Training Days webcast session, Janis brought a whole new perspective to the popular feature, Automatic Indexing in Oracle Database 19c.
How Automatic Indexing in Oracle 19c works
Creating indexes manually requires deep knowledge of the data model, application and distribution. Often database professionals decide which indexes to create, but then do not revise their choices which can result in lost opportunities for improvement and unnecessary indexes that can impact database performance.
However, with Automatic Indexing in Oracle Database 19c, 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. The feature is almost like having a performance tuning expert in-a-box since it doesn’t require a database professional to run it. The Automatic Indexing capability runs in the background and is constantly evaluating indexes and deciding whether new ones will make queries run faster.
Janis explained the five steps that Automatic Indexing uses to tune query performance:
- Capture – First, it captures SQL history and stores it in a repository
- Identify and create – The feature identifies indexes that it thinks will speed things up, and then creates them but makes them invisible
- Verify –The automatic indexing feature tests whether query performance improves when using the invisible indexes
- Decide – If performance is improved, the new indexes are made visible; if not, they are removed
- Monitor – The new indexes are then made available to the remainder of the workload and their usage is monitored
The whole process then begins again.
The finer details of Automatic Indexing
Janis was careful to emphasize that the Automatic Indexing feature is not supported on a production system but could be deployed in a test or development environment. Once the results are in, then they could carefully be applied to production. The feature can be used during all pre-production stages of the application lifecycle and is particularly good for online transactional processing (OLTP) and mixed workloads. But it is only available in Oracle Enterprise Edition and only for Exadata.
Automatic Indexing must be turned on, and this is done via a hidden parameter that Janis demonstrated. She also showed how to alter settings that control things like index and report retention times, compression, etc. Janis reviewed several functions, including activity reports that display which new indexes were created, whether they were made visible and which statements were improved. Report results can be exported as text, HTML or XML.
In the final part of the session, Janis examined three case studies where she compared how automatic indexing performed versus manual tuning. How did it stack up? You’ll have to watch the session recording to find out – the results may surprise you.
Want to learn more database performance tuning tips? Check out all our free Database Training Days sessions.
