Performance Questions 7.x -Reply
Carol
Can you please isolate a query for a small number of
tables that are causing you a problem, and post the
following.
table schemas
row counts
onstat -d
The sample query.
sqlexplain output.
fragmentation schemes for tables and indices.
While you are at it. You might want to check the
following;
Have you run update statistics with high
distributions at any point? This can cause the
optimiser to spend a long time setting up the path to
the data because of the volume of distributions that
it has to read. If you have then try running update
statistics with drop distributions to clean them out.
Is your machine a multi-processor, and does pdq
work in 7.10.ud1 (it didn't in uc1.) If you have it
turned on, try turning it off, and vice versa. If you
haven't got a version where pdq works then consider
an upgrade (a bit late in the piece i know) so that you
can use it.
Have you run onperf to see how it is breaking up the
query?
Are your disk spindles committed to Informix?
What other activity if any on your machine could be
slowing it down. Is your disk raided? I am running
raid 0 with informix mirroring. Performance is not
an issue. (Comparatively speaking)
Personally I have avoided fragmenting tables
because of all the issues with fragmenting indexes
etc; but then I have never suffered from your
performance woes.
Lastly; If you upgraded your database in situ then I
would recommend, if you are able to, a
dbexport/dbimport of the database. I just have a
hunch that this sets things to rights in some
instances, especially where indexing is concerned.
regards
--
--
Peter Tashkoff <tashkop@kiwi.co.nz>
Project Consultant/DBA
NZ Kiwifruit Marketing Board Usual
Disclaimers Apply
--------------------------------------------------------------------------
ALL RIGHTS RESERVED
This posting is copyright and may be used only for
the purposes
of professional study or the professional exchange
of information.
Use of this posting by any commercial organisation
for the
purposes of vilifying another is explicitly prohibited
---------------------------------------------------------------------------