[Info-Ingres] Invalidate cache

[John Dennis]

On May 21, 2:31 pm, Cecil Westerhof <d... at dummy.nl> wrote:
> Roy Hann wrote:
> > "Cecil Westerhof" <d... at dummy.nl> wrote in message
> >news:465018e1$0$337$e4fe514c at news.xs4all.nl...
> >>I have to do a performance test on severall different sets of queries.
> >>(The
> >> old queries of the client and my replacement queries.) But to get a
> >> reliable result the cache does have to be cleared before every query
> >> offcourse. I understood that I could do this with the following
> >> statement:
> >>        SET TRACE POINT DM421
>
> >> But when I try to do this, I get 'You do not have permission to set trace
> >> flags'. How do I set this permission? I am not a regular Ingres user. I
> >> was
> >> just asked to solve his performance problem.
> >> The client is working with Ingres Linux Version II 3.0.2.
>
> > If you have permission to run the accessdb utility, go into the "Users"
> > submenu, choose your user ID to edit it, and set the "Set trace flags"
> > switch to "y".  Don't forget to save the change.  Log back in to pick up
> > the new privilege.
>
> This works. Thanks.
>
> But it gives a new question.
> I run my queries on a machine that is doing nothing except executing my
> queries. Before every query I execute
>         SET TRACE POINT DM421
> But still there is a big difference between the queries. For example when
> executing one of the old queries five times it takes: 355,97, 690,16,
> 379,51, 100,27 and 60,96. When I execute the new version it takes: 2,60,
> 10,57, 0,99, 0,74, and 1,01 seconds. So it is save to say the new query is
> faster, but it is not clear by which factor. In the old query the
> difference between the fastest and slowest is 11,3 and in the new query it
> is 14,3. Is this normal?
> One point, the testing is on a VMware virtual machine. Can the difference be
> that big? Or is the performance of a virtual machine not constant?

I find it useful when the load on the machine is unknown to gather
information which should, theoretically, be consistent. Use dbmsinfo
to gather _dio_cnt, _cpu_ms and perhaps dbms_cpu before and after the
query you are tying to measure.  The will then tell you how many disk
accesses were required to run the query, and how much cpu was used by
the processor.

John