RE: update statistics for SPECIFIC procedure problem
> -----Original Message-----
> From: owner-informix-list@iiug.org
> [mailto:owner-informix-list@iiug.org] On Behalf Of Bill Dare
> Sent: Thursday, August 04, 2005 10:17 AM
> To: Art S. Kagel; informix-list@iiug.org
> Subject: RE: update statistics for SPECIFIC procedure problem
>
>
>
> > -----Original Message-----
> > From: Art S. Kagel [SMTP:KAGEL@bloomberg.net]
> > Sent: Thursday, August 04, 2005 12:40 PM
> > To: Bill Dare
> > Subject: Re: update statistics for SPECIFIC procedure problem
> >
> > Bill Dare wrote:
> > > 9.40.FC6X2
> > > AIX 5.2
> > > FYI
> > > Ran accross a problem with update statistics for stored procedures
> > when
> > > using the SPECIFIC keyword. I am posting this because I
> know a lot
> > of
> > > you out there use Art's dostats utility and it uses the SPECIFIC
> > > keyword. If you have a lot of stored procedures in your database
> > you
> > > will see considerable thrashing in the transaction logs
> if you do a
> > > "dostats -h $DBSERVER -d $DATABASE -p -P 0". In my case I have
> > about
> > > 150 stored procedures and I would get 550MB of transactions when I
> > ran
> > > that dostats. All of the transactions were inserts and deletes on
> > > sysprocplan. Even with an empty database containing only the IDS
> > > defined stored procedures, update stats with the SPECIFIC keyword
> > and
> > > you will get 10MB of transactions. Without SPECIFIC and you get
> > <1MB.
> > > Each additional user defined procedure will add about 3.7MB of
> > > transactions if SPECIFIC is used.
> >
> > Bill, is the problem the fact that dostats is updating the stats on
> > your
> > stores procs and functions or that it takes so much log space to
> > record
> > changes to the compiled byte code? If the former, you can just add
> > '-t "*"'
> > to the dostats commandline and you will effectively disable the
> > default -p
> > option that updates the procs when you do an entire database.
> [Bill Dare]
> No, I was expicitly running dostats to update stats on the
> stored procedures.
>
> > If it's the
> > log space, what can I do about it?
> [Bill Dare]
> There's nothing you can do about it. I posted this message
> because I wanted people to be aware of the problem. There's a lot of
> DBAs out there that use your utilities.
>
> > Perhaps the sysprocplan table should be
> > a RAW table? I dunno. Or, hmm, are you saying that if dostats
> > updated the
> > procedures without the SPECIFIC keyword all would be well?
> [Bill Dare]
> Yes, exactly. The SPECIFIC keyword causes the excessive logging
> activity. Without specific everything is fine. It is, in my opinion,
> caused by a defect in IDS. I have a case open with IBM but I don't
> believe it has been determined yet that it is a defect. I
> simply worked
> around the problem by using a shell script instead of dostats.
>
> Regards,
> Bill
>
> > SPECIFIC is
> > required to be able to update stats for multiple
> procs/funcs with the
> > same
> > name but different signatures. Without SPECIFIC I believe you will
> > get an
> > error if you run UPDATE STATISTICS FOR PROCEDURE myproc;
> and there are
> >
> > several versions of myproc with different signatures. That is why
> > dostats
> > adds the SPECIFIC clause when it detects a 9.xx+ server.
> >
> > Please, Bill, expand this post and let's start a dialogue (or
> > multilogue if
> > someone else wants to join).
> >
> > Art S. Kagel
> >
>
> sending to informix-list
>
I use dostats on a weekly basis, and the database has a LOT of SP's.
Had a problem for awhile in that I would get SQL errors during the
update stats of the procedures (don't remember the exact error, but it
seems like it was a locking contention error). I resolved it by
workaround: specify options in dostats such that it will NOT do the
update stats on the SP's, and then immediately follow that up with a
simple "set lock mode to wait; update statistics for procedure;"
submitted via dbaccess. This seems to run much faster than letting
dostats march thru the individual SP's one at a time, including all of
the "system" proc's.
HTH,
Paul Mosser
sending to informix-list