update statistics for SPECIFIC procedure problem

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. Regards, Bill sending to informix-list

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. If it's the log space, what can I do about it? 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? 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