Why is Oracle not using available indices?

In some cases, the performance of the xMatters application may degrade because some application queries may no longer perform as expected over the lifetime of the xMatters application. This can occur because the Oracle cost-based optimizer may choose an inaccurate execution plan that causes unacceptable application response times. This FAQ provides background on this issue, and describes how to populate or update existing Oracle statistics.

Background

Oracle provides a performance tuning document for each supported release. This document covers best practices for obtaining optimal database performance, including the following:

  • For application usage in which data is incrementally modified, you may only need to gather new statistics every week or every month.
  • For tables that are being substantially modified in batch operations (e.g., bulk loads), statistics should be gathered on those tables. The DBMS_STATS procedure should be called as soon as the load operation completes.
  • To allow Oracle to collect column statistics equivalent information for the expression, gather new column statistics on a table after creating a function-based index. This is done by calling the statistics-gathering procedure with the METHOD_OPT argument set to FOR ALL HIDDEN COLUMNS.

Resolution

Typically, Oracle does not use the indices available to it because related object statistics are unavailable, or inaccurate. This issue can be resolved by populating or updating existing statistics.

To populate or update existing statistics:

  1. Log in to the Oracle database using a system account.
  2. Initiate the statistics gathering process for the xMatters schema by running the following statement (replace xMATTERSDB with the actual name of the schema for the xMatters application):
BEGIN
 DBMS_STATS.GATHER_SCHEMA_STATS('xMATTERSDB');
END;
/
    1. Flush the shared pool by running the following statement:
ALTER SYSTEM FLUSH SHARED_POOL;
 
      1. Flush the buffered cache by running the following statement:
ALTER SYSTEM FLUSH BUFFER_CACHE; 

xMatters Reference

DTN-2520, SUP-4556, JDN-1830

created by Don Clark on Mar 15, 2011 3:16 PM

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk