by >I'd be very interested in seeing your model. Have you built a
>software model?
>Mike Meyer
I have produced a software model, but it is too large to post here, so
I'll give you a brief description of the process.
We currently have a program which plans optimization and modification
of tables on a round robin basis (Not Ideal). Another program then
executes the plan during a maintenance window overnight and stores the
execution time for each task within a database table. The problem is,
that as the executing program has no idea how long a task will take,
it will check to see if it has any time left to run a job, and then
executes it. If this is a large table then it can exceed the time
allotted for maintenance.
I have written a DB procedure to capture these execution times and
store them in an historical log. Another procedure uses linear
regression to calculate various coefficients, so that when a table is
planned to be modified or optimized, then an estimate can be made on
the execution time, using yet another Database procedure. The
intention is to feed these times to the execution program to try to
stop maintenance overruns.
Finally to close the loop, there is another new procedure which
captures the growth of tables, in order to recommend modifies and
optimizes.
The use of DB procedures was for prototyping only, to develop the
idea; run times can be large depending on the number of tables to be
addressed.
None of these procedures have been implemented within our production
environment, however tests within a development environment have shown
encouraging results.
+-----------------------------+-------------------------+
| Paul Stevens | Database Manager |
+-----------------------------+ Perot Systems Europe |
| Tel +44 (0)115 966 2028 | 398 Coppice Road |
| Fax +44 (0)115 966 2091 | Arnold |
+-----------------------------+-------------------------+