Automatic Indexing
As an Oracle DBA we have been continually working on making
things fast in Oracle database. One of the key tasks in performance tuning is
introducing new indexes.
As a DBA/Developer you propose indexes to your production
system when you are assured that this this the best option as introducing index
is two-way sward. On positive side it enhances data access but on negative side
it can slow your inserts, add up time to stats gathering job, and adds up to
database size.
Oracle database 19c introduced Automatic Indexing using
database package DBMS_AUTO_INDEX. Currently this feature is only available in
Exadata’s and Oracle cloud. In this article , I will explain you how it works.
I will use a little hack in my test database running on OEL 7 to act as
Exadata. Please note this is for testing purpose only. Please Do not do it in your live environment
Automatic
Indexing – How It Works (Concepts)
Capture:
Periodically capture the application SQL history
into a SQL repository
Includes SQL, plans, bind values, execution
statistics, etc.
Identify candidate:
Identify candidate indexes that may benefit the
newly captured SQL statements
Creates index candidates as unusable, invisible
indexes (metadata only)
Drop indexes obsoleted by newly created indexes
(logical merge)
Verify:
Ask the optimizer if index candidates will be
used for captured SQL statements
Materialize indexes and run SQL to validate that
the indexes improve their performance
All verification is done outside application
workflow
Decide:
If performance is better for all statements, the
indexes are marked visible
If performance is worse for all statements, the
indexes remain invisible
If performance is worse for some, the indexes
are marked visible except for the SQL statements that regressed
Online Validation:
The validation of the new indexes continues for
other statements, online
Only one of the sessions executing a SQL
statement is allowed to use the new indexes
Monitor:
Now let’s
See it in action
Coming soon…
Ref: https://static.rainfocus.com/oracle/oow18/sess/1523250557343001OBEw/PF/TRN3980_Test_Drive_Auto_Index_Creation_in_ADB_1541192406753001UYd0.pdf