Chat now with support
Chat with Support

Spotlight on Oracle 10.4 - Release Notes

Degrading SQL Configuration

Use the Degrading SQL Configuration options to:

  • Specify the cost metric that will be used to identify the SQL statements whose performance may not scale adequately as data volumes and SQL execution rates increase.
  • Specify how far to project those predictions into the future.
  • Specify the maximum size for the predicted metric displayed.

To set the Degrading SQL Configuration options

  1. Click View | Options
  2. Click Predictive Diagnostics | Degrading SQL Configuration
  3. Configure the following options:

    Option Description
    Degrading SQL Cost Metric

    Select the SQL Cost Metric you want to use when making your prediction.

    Cost metric


    CPU Time

    The amount of CPU time spent processing the SQL statement.

    Disk Reads

    The number of physical disk reads recorded against the SQL statement.

    Elapsed Time

    The amount of elapsed time (including wait time) taken to execute the SQL statement.

    Logical Reads

    The number of logical reads (buffer gets) retrieved by the SQL statement.

    Prediction Time-Spans

    Specify how far you want to look into the future.

    Note: Because each prediction is based only on the existing raw data, the forecast becomes less accurate the further you look into the future.

    Prediction Limits

    When the Cost Metric increases at an increasing rate (follows an exponential trend), the cost may ultimately grow so large that it over-emphasizes the importance of the analyzed metric relative to others.

    Use the Prediction Limit Multiplier option to define a realistic upper limit on the cost of the SQL statement. Beyond this point, the predicted growth will be ignored. By default, the upper limit is set at 100 times the largest cost value observed so far.

    Trend-Change Suggestions

    You can set Spotlight to automatically suggest any Trend Changes in the data it collects. Use the available options to control which changes are suggested.

    Option Description
    Only suggest trend changes with multiple explain plans

    Choose this to reduce the performance overhead when suggesting trend changes.

    The presence of multiple explain plans for a SQL statement is a good indicator that a suggested trend change may be real.

    Suggest trend-changes for {chartname}

    Clear one or more of these to prevent Spotlight from suggesting trend changes in the relevant chart in the SQL Performance drilldown.

    Note: The presence of a trend change in one chart may not be reflected in other charts in the drilldown.

    Minimum required impact on predictions Set the size of the trend change that triggers a suggestion; this restriction eliminates changes that are not significant.


Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating