Board index Database

Predicting Optimizedb Execution Times

Predicting Optimizedb Execution Times

Post by Paul.Steven » Tue, 17 Sep 1996 04:00:00



------ =_0_MIME_Boundary_25930.323d374c.imfl9l60.eurh021.eur.ps.net

---------------------------- Forwarded with Changes
---------------------------

Date: 8/13/96 5:35PM
To: Paul Stevens at Not-Cop4
*To: Alan Flower at Not-Cop2
*To: Colin MacKellar at Not-Cop3
*To: Eddie O'Neill at Not-Cop2
*To: Mark*son at Not-Cop2
*To: Martin Smith at Not-Cop2
*To: Mike McCullagh at Not-Cop2

Subject: Re: Predicting Optimizedb Execution Times
-----------------------------------------------------------------------------
--

------ =_0_MIME_Boundary_25930.323d374c.imfl9l60.eurh021.eur.ps.net


>>     Interestingly, the coefficients that I have derived where the number
>>     of columns is ignored, shows a much stronger relationship than
when
>>     the time is factored by the number of columns in the table.
>This would make sense.  Why scan each time for each column when you
>have all column information required already.  i.e. take the row, work out
>which columns need stats and collate.
>>     Maybe this is a misunderstanding on my part on the operation of
>>     optimizedb, as I thought that a table scan occurred for each
>column
>     addressed ?
>Jon Machtynger

I have noticed that when using the -zv and -zh flags on optimizedb, it would

appear that a scan occurs for each column, if you look at the output.

+-----------------------------+-------------------------+
| Paul Stevens                |  Database Manager       |
+-----------------------------+  Perot Systems Europe   |
| Tel  +44 (0)115 966 2028    |  398 Coppice Road       |
| Fax  +44 (0)115 966 2091    |  Arnold                 |

+-----------------------------+-------------------------+

------ =_0_MIME_Boundary_25930.323d374c.imfl9l60.eurh021.eur.ps.net--

 
 
 
Top

Predicting Optimizedb Execution Times

Post by Karl Schend » Wed, 18 Sep 1996 04:00:00



Quote:> Subject: Predicting Optimizedb Execution Times
> ...
>      On a recent test however, the time taken to optimize a table was 2.5
>      times longer than expected; on this occasion I made use of the -zv and
>      -zh flags, directing output to a log file.  The log seemed to intimate
>      that a scan occurred for each column, though I have raised the
>      question before on this group, as to how optimizedb operates, with a
>      response that the utility makes a single pass.

>      Has anybody got any thoughts ?

printqry seems to show pretty conclusively that optimizedb makes one pass
per column requested, at least in 6.4.
It does an ordered cursor select for each requested column.

--
Karl Schendel            Phone: (412) 963-8844
Telesis Computer Corp      Fax: (412) 963-1373


 
 
 
Top

Predicting Optimizedb Execution Times

Post by Paul.Steven » Wed, 18 Sep 1996 04:00:00



>>     Interestingly, the coefficients that I have derived where the number
>>     of columns is ignored, shows a much stronger relationship than
when
>>     the time is factored by the number of columns in the table.
>This would make sense.  Why scan each time for each column when you
>have all column information required already.  i.e. take the row, work out
>which columns need stats and collate.
>>     Maybe this is a misunderstanding on my part on the operation of
>>     optimizedb, as I thought that a table scan occurred for each
>column
>     addressed ?
>Jon Machtynger

I have noticed that when using the -zv and -zh flags on optimizedb, it would

appear that a scan occurs for each column, if you look at the output.

+-----------------------------+-------------------------+
| Paul Stevens                |  Database Manager       |
+-----------------------------+  Perot Systems Europe   |
| Tel  +44 (0)115 966 2028    |  398 Coppice Road       |
| Fax  +44 (0)115 966 2091    |  Arnold                 |

+-----------------------------+-------------------------+

 
 
 
Top

Predicting Optimizedb Execution Times

Post by Paul.Steven » Wed, 18 Sep 1996 04:00:00


     I have been testing a prototype for predicting the length of time
     taken to run optimizedb against individual tables.  So far the trials
     have shown encouraging results.

     On a recent test however, the time taken to optimize a table was 2.5
     times longer than expected; on this occasion I made use of the -zv and
     -zh flags, directing output to a log file.  The log seemed to intimate
     that a scan occurred for each column, though I have raised the
     question before on this group, as to how optimizedb operates, with a
     response that the utility makes a single pass.

     Has anybody got any thoughts ?

     TIA

     +-----------------------------+-------------------------+
     | Paul Stevens                |  Database Manager       |
     +-----------------------------+  Perot Systems Europe   |
     | Tel  +44 (0)115 966 2028    |  398 Coppice Road       |
     | Fax  +44 (0)115 966 2091    |  Arnold                 |

     +-----------------------------+-------------------------+

 
 
 
Top

1. Predicting Optimizedb Execution Times

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

     +-----------------------------+-------------------------+

2. ADO Recordset open speed

3. Problems with compeling the AFCL in NewEra 3.0

4. How2 predict Query Execution Time ?

5. Starting Web-based Database in Java

6. current time of execution during this execution

7. PA-Erie-267549--C-C++-Java-ORACLE-SQL-SOFTWARE PROGRAMMER

8. Maximum time execution time error

9. How to measure Store Procedure execution time?

10. Difference in execution Time

11. limiting execution time

12. Unable to open table at query execution time


All times are UTC
Board index