Chat now with support
Chat with Support

Spotlight on Oracle 10.5 - Getting Started Guide

Welcome to Spotlight Install Spotlight Start Spotlight Spotlight on Oracle Spotlight on Oracle Data Guard Spotlight on Oracle RAC Spotlight on Unix Spotlight on Windows Spotlight on MySQL Troubleshooting: Connection Problems

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
  1. Select the Spotlight on Oracle connection in the Spotlight Browser.

  2. Click Predictive Diagnostics | Identify Degrading SQL.

Note: The first time you open this page the SQL Statement Trend Analysis Wizard runs automatically.
  1. Select the Spotlight on Oracle connection in the Spotlight Browser.
  2. Open the Top SQL grid. For example, click SQL & Application Workload | Top SQL.
  3. Select the SQL Statement | SQL Details | Predictive Diagnostics.

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.

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.

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.

Increasing at an increasing rate.

Decreasing.

Increasing at a constant rate.

No prediction possible.

Increasing at a decreasing rate.

Analysis pending.

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:

  • 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.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating