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

Predictive Diagnostics Settings

When you choose these settings, Spotlight will schedule an Oracle job that collects SQL performance data and timed statistics, periodically updates the current schema, and purges obsolete data.

To choose the settings required to run Predictive Diagnostics select or enter values in these fields:

Setting Description

SQL Performance Collection Interval

How often to collect data for Predictive Diagnostics. We recommend the default collection interval of 12 hours.

The goal is to detect SQL statements that will not scale adequately as data volumes grow and execution rates increase. To achieve this goal, Spotlight needs to measure the performance of those SQL statements over time, as data volumes grow and execution rates increase. The period of time required will depend on the growth rates experienced by the database.

The frequency of the collection interval used by Predictive Diagnostics will NOT influence the growth rates experienced by the database, so a collection that occurs TOO frequently generates excessive amounts of data from which no benefit can be obtained.

Bottleneck Collection Interval

The goal is to identify the potential causes of bottlenecks in the system as they affect database throughput and response time. To achieve this goal, Spotlight needs to sample the relevant timed statistics as database throughput increases.

Unlike the SQL performance data, the collected bottleneck data is a set of instantaneous "snapshots" of the relevant metrics.

More frequent data collection can improve the accuracy of the prediction. We recommend the default collection interval of 30 minutes.

Note: The values returned in a bottleneck snapshot may also depend on WHEN it is taken — bottleneck statistics may peak in response to events scheduled to occur on a daily or weekly basis.

You can allow for these scheduled events in the Predictive Diagnostics Peak Processing Periods page.

Performance Modeling Collection Interval

The goal is to predict Oracle's future use of database resources — CPU, memory, and disk I/O. To achieve this goal, Spotlight needs to collect database performance statistics over time as database throughput and concurrency increase.

More frequent data collection can improve the precision of the performance model. We recommend the default collection interval of 10 minutes.

Note: As with bottleneck snapshots, performance modeling statistics may peak in response to scheduled events. Allow for these in the Peak Processing Periods page.

Purge collected measurements that are older than...

The period of time that collected data is kept by Predictive Diagnostics. Valid values for this setting range from 30 days to 731 days (two years).

Note: If you enter the value 0 (zero), Spotlight will never purge the collected data. This is NOT recommended, however, as it will adversely affect the performance and space usage of Predictive Diagnostics.

Storage requirements for Predictive Diagnostics

The disk storage requirements for Predictive Diagnostics schemas comprise:

For sample data

The Predictive Diagnostics sample data requires approximately 70 MB of storage space.

For SQL statements

A fixed overhead of approximately 10 MB per schema.

An additional requirement of approximately 1 KB per snapshot for each SQL statement.

The number of individual snapshots collected depend on the application workload and size of the shared pool. The storage requirements also depend on the collection interval for predictive snapshots, and the length of time that each snapshot is retained.

Example

If there is a fixed overhead of 10 MB, and there are 500 eligible SQL statements per snapshot, and snapshots are collected twice a day, and each snapshot is kept for one year, the disk storage requirement for SQL statements is:

10MB + (500 x .001MB x 2 x 365) = 375 MB

For impending bottlenecks

Disk storage requirements depend on how frequently data is collected, and the length of time that snapshots are retained.

Examples

  • If data is collected every 10 minutes and retained for six months, the disk storage requirement for impending bottlenecks is approximately 200 MB.
  • If data is collected every 30 minutes and retained for six months, the disk storage requirement for impending bottlenecks is approximately 65 MB.
  • If data is collected every 60 minutes and retained for six months, the disk storage requirement for impending bottlenecks is approximately 35 MB.

For performance modeling

Disk storage requirements depend on how frequently data is collected, and the length of time that performance data is retained.

Example

  • If data is collected every 10 minutes and retained for one year, the disk storage requirement for performance modeling is approximately 130 MB.

Parameters set in Predictive Diagnostics - View | Options and the SQL Statement Trend Analysis Wizard also affect the storage requirements for Predictive Diagnostics data.

 

Related Topics

Oracle User Wizard | DBA Connection Credentials | User Account | Ready To Configure Oracle User | Configuring...End

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating