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