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
|
|
For performance modeling |
Disk storage requirements depend on how frequently data is collected, and the length of time that performance data is retained. Example
|
Parameters set in Predictive Diagnostics - View | Options and the SQL Statement Trend Analysis Wizard also affect the storage requirements for Predictive Diagnostics data.
Oracle User Wizard | DBA Connection Credentials | User Account | Ready To Configure Oracle User | Configuring... | End
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center
