Chat now with support
Chat with Support

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

Database Throughput And Concurrency

Predictive Diagnostics makes predictions for the consumption of database resources (CPU, memory, and disk I/O) by generating a model that uses database throughput and database concurrency as inputs. Use the Database Throughput and Concurrency page to specify which database throughput and concurrency metrics are used when creating this model.

Note: Use this page to select database throughput and concurrency metrics for real data only.

Specifically, use the page to:

  • Choose as default metrics those that are the best for representing throughput and concurrency for the Oracle database.
  • Define a customized concurrency metric if the standard concurrency metrics (connected sessions and active sessions) are not appropriate.

To open the page

  1. Select the Spotlight on Oracle connection in the Spotlight Browser.
  2. Click Predictive Diagnostics | Select database throughput and concurrency metrics.

How to use the Database Throughput and Concurrency page

Set the default throughput and concurrency metrics.

Throughput metric

A measure of how fast data moves through the database.

The first action taken when you open the Database Throughput and Concurrency page is an automatic analysis to recommend which throughput metric is the best (broadest) predictor for the utilization for the database resources.

The analysis takes a short time, during which Spotlight displays a Calculating recommendation... message.

You can choose NOT to accept the recommendation. (For example, you may want to select a different metric if the original analysis was performed on too little real data.)

Choose from one of these Throughput Metrics:

Option Description

Logical reads/s

The rate of requests for database blocks (buffer gets).

Rows processed/s

The rate at which rows are processed by SQL statements.

SQL executions/s

The rate at which SQL statements are executed.


The rate of transactions on the database. A transaction contains one or more SQL statements. It begins with the execution of the first SQL statement, and ends when the transaction is either committed or rolled back.

Concurrency metric

A measure of the number of users connected to the database at the same time.

The choice of concurrency metric is usually straightforward. Connected Sessions is a realistic measure of the number of sessions.

If you prefer concurrency to be based on specific TYPES of sessions, select Customized SQL and write your own SQL statement to count the sessions of the desired type.

Choose from one of these Concurrency Metrics:

Option Description

Connected sessions

The average number of user sessions connected to the database at the time.

Active sessions

The average number of user sessions that are performing some database activity at the time.

Note: High values for Active sessions can be generated by a single session performing SQL queries in parallel OR by many sessions performing many SQL queries of short duration.

Because of this, Active sessions also may be considered a measure of the total amount of work performed (throughput).

Customized SQL

Use a customized concurrency metric defined in the Database Throughput And Concurrency page for database throughput and concurrency.


Specifying a customized concurrency metric

When you choose Customized SQL as the concurrency metric, you can choose an existing metric or set a new metric. Use the custom controls.

Custom controls




Type a description used to identify the customized metric.

{SQL statement}

In the text pane, type the SQL statement that defines the customized metric.

Test button

Click to execute the SQL statement to verify that it runs without error and returns the expected value. (If the statement fails to execute, Spotlight displays the error encountered.)

Save button

Click to save the text of the edited SQL statement.

Revert Changes button

Revert to the saved version of the metric.

Set the customized metric


...Do this

Use SQL to define a new concurrency metric

  1. Select Customized SQL as the concurrency metric.
  2. Type a new entry in the Description of customized concurrency metric list.
  3. In the SQL text box below, edit the text of the statement that defines the new metric. We provide an (optional) sample template for each new entry.

    To format the SQL text, right-click and choose Format SQL.

  4. Click Test to test the SQL statement.

  5. Click Save to apply the change.

Edit an existing custom metric

  1. Select Customized SQL as the concurrency metric.
  2. Edit the text of the SQL statement that defines the metric.
  3. Click Save.

Revert to the saved version of the metric

Click Revert Changes.

Notes on customized metrics: A customized metric is a SQL statement that can be executed on the database to collect data for Predictive Diagnostics. The data that is collected is linked to the NAME of the metric.

Take care when renaming metrics or re-using the names of old metrics:

  • When you change the name of a customized metric in the Description... box, you create a new SQL statement; data that was collected under the old name still exists, but is no longer used.
  • When you edit the text of the statement WITHOUT changing its name, OR when you re-use an OLD name, any new data collected (however different) is simply appended to the old data.


Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating