SQL Performance
Show projected performance predictions for SQL statements executed on the Oracle instance.
To open the SQL Performance page
| For a filtered set of SQL statements in the Oracle database | For a selected SQL Statement in the Oracle database |
|---|---|
Note: The first time you open this page the SQL Statement Trend Analysis Wizard runs automatically. |
|
Set the Criteria
The criteria for predicting future performance of SQL statements is set in three places. The default criteria is set in Predictive Diagnostics - View | Options. Criteria for this session of Predictive Diagnostics for this instance is set in the SQL Statement Trend Analysis Wizard. You can further refine the criteria using the page controls:
| Control | Description | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| Project performance out to |
Reset the end-date for the projected analysis. The default end-date is six months ahead. Note: The relative order of SQL statements in the SQL Performance Prediction grid may change when you change the end-date. By default the SQL statements are ordered by percentage increase in {cost} per second between now and the end-date. If the prediction for one SQL statement follows a linear curve, and the prediction for a second statement follows an exponential curve, the cost increase in the near future MAY be larger for the first statement, but it will be outstripped later by the cost increase for the second statement. | ||||||||||
| Predict for |
Reset the {cost metric} used to calculate the projection.
| ||||||||||
| Set SQL Criteria | Re-run the SQL Statement Trend Analysis Wizard. |
SQL performance predictions Grid
|
Column |
Description | ||||||
|---|---|---|---|---|---|---|---|
|
Cost Type Image |
A representation of the cost/second trend for the SQL statement.
| ||||||
|
Trend Change Image |
Suggested Trend Changes exist for the SQL statement. Click to view the suggested trend change. (The tab on the sub-page also displays this image.) | ||||||
|
% of Total Now |
The cost NOW for the SQL statement, as a percentage of the total cost for all SQL statements executed. The predicted values for cost and total cost are taken from the prediction line. That is the line of best fit to the raw data in the Cost per second versus Time chart. ({Predict for:} / Sec Versus Time) | ||||||
|
% of Total Predicted |
The cost at the end-date for the SQL statement, as a percentage of the total cost for all SQL statements executed at the end-date. The predicted values for cost and total cost are taken from the prediction line. That is the line of best fit to the raw data in the Cost per second versus Time chart. | ||||||
|
% Increase |
The increase in cost for the SQL statement between now and the end-date, as a percentage of the cost of the SQL statement now. This cost value is independent of the cost values for other SQL statements. | ||||||
|
Total Cost Increase |
The total increase in the cost of executing the statement between now and the end-date. | ||||||
|
SQL Statement |
The SQL statement text. | ||||||
|
SQL ID |
The SQL identifier of the SQL statement. | ||||||
|
Current Value |
The current value of the cost metric. | ||||||
|
Model Quality |
A measure of how well the prediction fits the raw data for the cost metric. | ||||||
|
Predicted Increase |
The predicted increase in the value of the cost metric between now and the end-date, based on the line of best fit to the raw data. | ||||||
|
Predicted Value (Max) |
The maximum predicted value of the cost metric at the end-date, based on the upper confidence level for the prediction interval. | ||||||
|
Predicted Value (Mid) |
The value of the cost metric at the end-date, based on the line of best fit to the raw data. | ||||||
|
Predicted Value (Min) |
The minimum predicted value of the cost metric at the end-date, based on the lower confidence level for the prediction interval. |
Notes:
- Not all columns are visible by default. Use the Column Organizer to view hidden columns. Show, Hide & Order Columns
- When the Spotlight application window is too small to show the tabs for all the sub-pages at the same time, Spotlight will indicate the hidden tabs with scroll arrows. Click a scroll arrow to display the hidden tabs.
- Spotlight takes some time (typically about 10 days) to collect enough data to make valid predictions on the future performance of your Oracle instance. In the meantime use the prepared sample data to learn more about Predictive Diagnostics and how it works.
Analysis on a SQL statement on the SQL performance predictions Grid
| Action | Description |
|---|---|
| Show predictive diagnostics for the SQL statement |
(Select, Click, Highlight) the SQL statement. |
| Show current statistics for the SQL statement |
Right click the SQL statement and select SQL Details. Spotlight opens the SQL & Application Workload | Top SQL page with the details of the transaction. If there are multiple occurrences of the transaction, it shows multiple rows. Note: This requires that the SQL statement is being processed now. The message SQL Details are not available for this SQL statement is displayed if the SQL statement is NOT being processed now. |