Oracle Performance Prediction


Execution time can increase in a linear or exponential fashion depending on database size and complexity. Obviously it is better to gather statistics from small amounts of data. Then for instance, based on the number of rows selected and the time taken for completion of the query one can plot points on a graph to decide on whether performance will decrease linearly or exponentially based on the shape of the graph drawn between the poltted points.

For a Straight Line

Use a linear equation.

t1 (time taken) = g (gradient of line)0 + g1

where g0 = (y1 - y0) / (x1 - x0)
and   g1 = (y2 - y1) / (x2 - x1)

and   (x0,y0) is the point that the line crosses the y-axis
and   (x1,y1) and (x2,y2) are two successive points on the line.

For an Exponential Curve

Use a quadratic equation.

t2 = g0 + g1 x + g2 x2

Interpretation of Lines

The best form of plotted line is a straight, shallow line. A steeper incline or an exponential curve means that as more rows are retrieved that performance is suffering. A steeply inclined straight line could indicate lack of indexing, inappropriate indexing or overindexing depending on the types of SQL statements issued. Sudden changes to the gradient in a straight line indicate limitations reached or a bottleneck encountered.