Chat now with support
Chat with Support

Please note, you may experience access issues between 6am - 7am Eastern time on Saturday, May 28 2022 due to planned maintenance

Spotlight on Oracle 10.4 - 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 Transaction Details

To open the SQL Transaction Details page

  1. Open the SQL Performance page. Select a SQL statement from the SQL Performance Predictions grid.
  2. Click SQL Transaction Details.

Cost Summary

The Cost Summary for the SQL statement contains this information:

  • The trend prediction for the SQL statement (for example, Increasing at an increasing rate).
  • The current cost of the SQL statement as a percentage of the total current cost.
  • The predicted cost of the SQL statement as a percentage of the total predicted cost at the end-date.
  • The increase in the cost of the SQL statement at the end-date as a percentage of the current cost.
  • The increase in the number of cost events (for example, buffer gets) for the SQL statement between now and the end-date.

Cost per Second versus Time.

This shows how the cost rate of the SQL statement will change between now and the end-date. The Cost per Second value is calculated on the Cost Metric (for example, buffer gets) used for the projection.

Chart data points

Data Points Description
The raw data (blue data points) The actual data used to generate the chart.
New data (in a contrasting color) New data (when available) is data collected AFTER the raw data was analyzed.
The shaded region The Prediction Interval that is based on the raw data.
A vertical trend change line

If displayed, it shows the early data follows a different trend from the more recent data. Trend Changes The early data is not used in making predictions.

Note: You can set a Trend Change line via the chart toolbar.

Chart toolbar

Toolbar Description

Add Trend Change

Mark trend changes on the Predictive Diagnostics chart.

In the Add Trend Change window:

  1. Use the arrow buttons to position the trend change line on the chart. (The trend change line appears on the chart as you use the controls.)
  2. Add some text in the Description box to identify the trend change.
  3. Select Also Create... to add the same trend change to related Predictive Diagnostics charts.

Delete Trend Change

Remove trend change lines from the chart.

In the Delete Trend Change window, select the Delete? option that corresponds to the line you want to remove.

Accept suggested trend change

If Predictive Diagnostics notices a possible trend change it will mark it on the chart and prompt you to accept or reject it.

Reject suggested trend change

 

Reanalyze

Generate a new Prediction Interval on the chart from the raw data.

You can mark a trend change on the chart wherever you like, but the trend change is valid only in circumstances where it represents a REAL change in Oracle's handling of SQL statement executions, system bottlenecks, or database resources.

  • You can expect (and should mark) a trend change when you change the environment where the SQL statements are executed — for example, when you install a new version of Oracle, or when you upgrade the platform that hosts the database.
  • Other trend changes may be clear from a visual inspection of the raw data displayed in the chart. If you believe you can see such a change, you should mark it. (If you add a trend change line where NO real trend change exists, you will NOT change the nature of Spotlight's prediction, but you WILL make it less accurate, as Spotlight is using fewer data points to generate the prediction.)

Note: Trend changes in SQL Performance Predictions charts apply only to the specified chart for the selected SQL statement. Predictions on the execution of SQL statements are specific to the SQL statements themselves. If one SQL statement will perform poorly on the Oracle database as data volumes and SQL execution rates increase, the same does not necessarily apply to other SQL statements. Equally, a SQL statement whose performance scales adequately with respect to one cost metric (CPU time, for example) may not scale adequately with respect to another (Disk Reads, for example).

SQL Statement

Predictive Diagnostics forecasts how the execution of SQL statements will perform in the future as data volumes and execution rates increase.

  • You may also want to view further information on the execution plan that Oracle applies to a particular SQL statement. You can do this via the Spotlight Explain Plan.

  • You may also want to view further information on how to optimize a particular SQL statement.You can do this via SQL Optimizer (if installed). SQL Optimizer is an application that provides context-sensitive tuning advice for SQL statements based on the Oracle execution plan and the database structure.

The full text of the SQL statement used to generate the contents of the summary and chart on this page.

Action

Description

Click Explain SQL

Explain the SQL via the Explain Plan. Tools | Explain Plan

Click SQL Optimizer

Display context-sensitive tuning advice for SQL statements via SQL Optimizer (or SQL Tuning). Tools | SQL Optimizer

Note: For more information on SQL Optimizer, see the SQL Optimizer documentation.

Right-click the SQL Text window and select Format SQL. Display the SQL statement in a more readable form.

Note: If the SQL cannot be explained, the Explain Plan and SQL Optimizer (SQL Tuning) options may be unavailable.

 

Related Topics

SQL Performance

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating