Chat now with support
Chat with Support

Spotlight on Oracle 10.10 - Release Notes

Data Storage Requirements for Predictive Diagnostics

Data Storage Requirements for Predictive Diagnostics

Predictive Diagnostics goals are:

  • 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.
  • 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.
  • 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.

 

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 Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating