Chat now with support
Chat with Support

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

Why Will Database Resources Reach Their Ceiling?

Note: Available for Oracle 10.2 and later.

When you have used the When Will Database Resources Reach Their Ceiling? page to predict when database resources will run out, use this page to help determine database resources will run out and what can be done to alleviate the problem.

To open this page

  1. Select the Spotlight on Oracle connection in the Spotlight Browser.
  2. Click Predictive Diagnostics | Why will database resources reach their ceiling?.

Analysis

The database concepts on which Predictive Diagnostics bases its investigation are throughput and concurrency. Throughput is a measure of how fast data moves through the database. Concurrency is a measure of the number of users connected to the database at the same time, though some users may impose a higher workload on the database than others.

Set the Criteria

Set the initial conditions for predicting why resources will reach their ceiling.

Control

Description

Perform an analysis of...

Select Real Data (your database) or Sample Data (to experiment with).

Note: 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.

Within the processing period...

Select an activity period to use for your prediction. The more selective this is the better the prediction.

Tip: To define new processing periods or redefine an existing processing period see Peak Processing Periods.

For all collected data

or

For data collected since...

Click all or the date to open Performance Modeling Oracle Job.

If you select a start date in Data Analysis Options then this text reads For data collected since....

Note: The start date should be a date for which recorded data exists. For example, you cannot select a date later than today's date. Use this setting to ignore data that occurred before a specific time.

Analyze Disk I/O for.

The Predictive Diagnostics metric for disk utilization can be either disk latency or disk throughput (Disk Latency And Disk Throughput). Select the one that best suits your environment.

Predict forward until...

Select an end date for the prediction.

 

Control

Description

Using...for database throughput

Select a throughput metric to represent the total amount of work performed (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.

Transactions/s

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.

Tip: Choose combinations of the throughput and concurrency metrics to predict which are likely to use up database resources, and which resources will be consumed.

Using...for database concurrency

Select a concurrency metric to represent the number of users (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.

Tip: Choose combinations of the throughput and concurrency metrics to predict which are likely to use up database resources, and which resources will be consumed.

Details Pages - Analysis

For each combination of metrics, use the charts in the CPU, Memory, Disk I/O and Throughput and Concurrency pages to view how database resources are affected by the load placed on the database.

Details Page Description
CPU and Memory

The charts in the CPU and Memory sub-pages display the following relationships, where [Resource] is either CPU or Memory.

DISK I/O

The charts in the Disk I/O sub-page operate in the same way as those in the Memory and CPU sub-pages, but you also need to choose whether to use Disk Latency or Disk Throughput to represent disk utilization. Disk Latency And Disk Throughput

Throughput and Concurrency

Use the charts in the Throughput and Concurrency sub-page to view how the combination of ALL database resources affects the performance of the database over time, as measured by database throughput and database concurrency. Database Throughput And Concurrency By Time

You also can view this sub-page from the What Database Resources Are Needed? page.

Charts

Analysis

Description

[Resource] by Time

This chart is the same as the [Resource] Utilization by Time chart in the Details section of the When Will Database Resources Reach Their Ceiling? page.

[Resource] by Database Throughput

This chart shows how the resource varies with the metric (logical reads/s, rows processed/s, SQL executions/s, or transactions/s) chosen to represent database throughput.

[Resource] by Database Concurrency

This chart shows how the resource varies with the metric (connected sessions, active sessions, or a customized metric) chosen to represent database concurrency.

Notes:

  • If the points plotted on a chart displays no relationship between database load and resources, there is no prediction interval.
  • A chart that shows NO relationship between database load and resource use under one peak-period setting may still reveal a relationship when viewed under an alternative setting.
  • Where a Throughput or Concurrency chart displays No Direct Relationship, any apparent relationship shown is an artifact of a relationship with the other metric.

    This example shows two charts, BOTH of which appear to show an increase in CPU resource use with time.

    However, Spotlight's analysis determines that the apparent relationship in the Concurrency chart between CPU and connected sessions only reflects the real relationship in the Throughput chart between CPU and logical reads per second.

Summary

Often you cannot identify the resources that affect database performance simply by looking at the charts on the Why Will Database Resources Reach Their Ceiling? page. The limitation of a single resource MAY be responsible for all performance problems, but there also may be other factors at work.

The pane at the right of each Why Will Database Resources Reach Their Ceiling? sub-page provides a summary of the analysis used to draw the charts on the left, and indicates:

  • The database resources whose availability may lead to performance problems.
  • Whether an apparent relationship between a resource and performance is real, or whether it reflects another relationship.
  • The extent to which a resource is affected by the load on the database.
  • Whether separate trends are affecting different aspects of performance.
  • When resource ceilings will be reached.
  • How to reduce the impact of a resource limit by augmenting resources.

Notes:

  • Click a Show Details item to see more details on the summary.
  • Click a link to go to a related Predictive Diagnostics page, or to view additional information.

 

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating