Executing stored procedures - performance problem
Posted in 1996
Topics: Performance & Tuning, Stored Procedures & SPL, Server Administration
Hello,
recently we found a kind of interesting behaviour of our Informix system.
Stored procedures which normally work within 2 - 3 minutes started
to take 8 - 9 hours to complete (180 times performance drop - ha-ha).
Turning explain on for this procedures (we now have problems with 2 of them)
creates sqlexplain.out file 1 byte long. Tracing (SET DEBUG FILE TO...
TRACE ON...) placed at the very beginning of the stored procedure didn't
give any output within first hour. I have played with SET OPTIMIZATION
LOW/HIGH - no effect.
After problem erased I have tried to execute procedures from dbaccess with
the same result, so it is unlikely to be a problem of the client system.
Server that we are using is Informix Online 7.10 under SCO Unix. The database
is not so big - about 2.5 Gb but one table contains about 3 mln rows.
Such problem appeared on both - production and development - cites.
There is nothing special inside the procedures - just usual combination of
select(s)/insert(s)/update(s).
The only way we have found to solve this situation is to DROP DISTRIBUTION,
UPDATE STATISTICS MEDIUM, DROP PROCEDURE, CREATE PROCEDURE, UPDATE STATISTICSFOR PROCEDURE... Sometimes it works, sometimes - not, but in any case
the effect is seems to be unstable. (Plus it takes forever
to update statistics).
Does anybody have the same problem? I'll be appreciate for any information.
Best regards,
Oleg Besedin