Home

About Me Contact Me Oracle12cSIG(IOUG)
 
Home  :: Oracle12c

Automatic Indexing

0 Comments

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:

  • Index usage is continuously monitored

  • Automatically created indexes that have not been used in a long time will be dropped

     

     

     

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

 

 

Posted on : 5/31/2020 by Anuj
 
Comments
No comment found.
 


Add Your Comments
 
Name :
Email :
Comments :

 


 
 
  Archives
 
  Recent Posts

Automatic Indexing
Posted on :   5/31/2020

Database 19c New Features
Posted on :   5/28/2020

Request for free Oracle Cloud certification exams
Posted on :   5/7/2020

  Recent Comments
 

Home  |  About Me  |  Contact  |  Oracle12cSIG(IOUG)

All Rights Reserved © 2019 Oracle-12c