When Will Database Resources Reach Their Ceiling?
Note: Available for Oracle 10.2 and later.
The database resources that most often limit database performance are CPU utilization, memory utilization, and disk I/O. Use this page to predict when specified database resources (CPU, Memory, and Disk I/O) are expected to reach their full capacity.
A good time to run this analysis is:
- At any time when using the supplied sample data.
- After an initial data collection period (about 10 days) when using real data.
- After an additional data collection period when trend statistics have changed as a result of:
- An analysis of degrading SQL statements (SQL Performance) and impending bottlenecks on real data (Impending Bottlenecks - Timed Statistics).
- A change in the environment of the database (Trend Changes).
To open this page
- Select the Spotlight on Oracle connection in the Spotlight Browser.
-
Click Predictive Diagnostics | When will database resources reach their ceiling?.
Set the Criteria
Set the parameters used to predict the use of resources.
|
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 |
|---|---|
|
Chart the results as... |
Adjust to an acceptable level of probability.
|
Resource Utilization by Time chart
This chart predicts when the charted resources (CPU, Memory and Disk I/O) will reach their full capacity. Beyond those points database performance will suffer.
|
Legend |
Description |
|---|---|
|
Resource Ceiling |
A broken red line always at Y=100. This represents the ceiling for every resource plotted in the chart. Note: The default resource ceilings are: CPU=100%, Memory=100%, and Disk Latency=10 ms. Disk Throughput has no default value. On some systems, the actual ceiling for a specified database resource may be well below 100%. To redefine resource ceilings see Database Resource Ceilings. |
|
Resource Name |
There will be at most three of these lines, each representing the likely utilization of CPU, memory, or Disk I/O over time. Note: A missing line indicates either the resource use is decreasing over time or that Spotlight does not have enough information to make a prediction. |
|
Resource Name (Pessimistic) |
A broken line of the same color as the Resource Name and joined to the resource name. It represents a more pessimistic estimate of resource use. Note: Not available when Most Likely is selected on the chart the results as criteria slider. |
Notes:
- Refer to the Details pages for the raw data and chart analysis.
- Use the tool bar to the top right of the chart to change how the chart presents information. Chart configuration
- Every chart has a legend (list of symbols) to its right that describes the various series (line graphs) on the chart.
- Click an item in the legend to highlight its series (line) in the chart. Click a second time to return the series to its normal appearance.
- Move the mouse pointer over an item in the legend to view the current value for that series within the chart.
Details Pages
Where Resource Utilization by Time shows when database resources reach their ceiling, the tabbed Details pages show the raw data used to generate the Resource Utilization by Time chart.
|
Page |
Description |
|---|---|
|
Summary |
This page corresponds to the Resource Utilization by Time chart.
Note: This page shows the statistics for ALL resources, including those whose usage is decreasing over the analyzed period. |
|
CPU |
CPU Utilization by Time shows the resource ceiling, and plots how CPU utilization changes with time. It shows the raw data used in the analysis, new data that has not yet been analyzed, the predicted resource use and its Prediction Intervall, and any Trend Changes that you have used to ignore old data. |
|
Memory |
Memory Utilization by Time shows the resource ceiling for memory, and plots how memory use changes with time. |
|
Disk I/O |
The Disk Latency by Time or Disk Throughput by Time chart, depending on the metric selected in the Analyze Disk I/O for setting. |
Results
The results predict how well the current database resources will handle the load expected of them in the future. The prediction may indicate that:
- The current resources will NOT reach their limits by the specified end-date.
- The resources ARE increasing at a specified rate and will reach their limits by the end-date.
- The resources have already reached their ceiling.
In the last two cases, the options are:
- Tune the database to improve performance. The results of the analysis indicate areas where performance can be improved by optimizing the use of existing resources. Use Spotlight to identify where to make improvements. (This may occur most often with CPU and disk use.)
- Add to the available database resources. Where current performance cannot be improved with existing resources, add more resources to improve performance. Use the Predictive Diagnostics Why Will Database Resources Reach Their Ceiling? page to identify the critical resources.