Database Management
DPA index advisor shows UNKNOWN as the estimated savings
This article explains why index advisors in DPA 2023.2 and later might show UNKNOWN as the estimated savings you would get if you added a recommended index.
First published date
Last published date
Overview
When you view the list of index advisors for a database instance, DPA displays the recommended indexes, along with the estimated time that adding each index would save.
In DPA 2023.2 and later, some entries in this column might display UNKNOWN instead of a duration and percentage. In earlier DPA versions, DPA would have displayed a value greater than 100% for those entries.
Product section
Cause
DPA calculates the estimated time savings using information from the related query execution plans provided by the database vendor. Occasionally, these execution plans include a cost estimate for a single step in the plan that is greater than the estimated time for the entire plan. This type of information from the database vendor causes DPA's estimated savings value to be greater than 100%, which is not possible.
DPA calculates the estimated time savings using information from the related query execution plans provided by the database vendor. Occasionally, these execution plans include a cost estimate for a single step that is greater than the total cost of all steps in the entire query. Using these incorrect values provided by the database vendor could cause DPA to estimate the benefit of creating the index to be greater than 100%, which is not possible.
Resolution
In DPA 2023.2 and later, DPA detects this type of discrepancy in the database’s execution plans for queries that are likely to be affected by a recommended index. To avoid confusion, DPA now displays UNKNOWN when it detects an inconsistent value.