Spotlight on Oracle 10.7 - 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 Description 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

Predictive Diagnostics - View | Options

Predictive Diagnostics

SQL Performance