RE: update statistics for SPECIFIC procedure problem
Art checked this out on release 10.00.UC1 and the problem seems to be
even worse there. Based on my test with 9.40.FC6X2 I had 150x as much
log activity with SPECIFIC as without. Art tried it on release 10.00
and got 325x as much logging with SPECIFIC as without.
Heres's the test case I sent IBM for anyone interested.
1) Create a new test database.
2) Run onstat -l and record the size and % used.
700000010313c20 38 U---C-L 11226 3:53 25600
7849 30.66
3) Run:
echo 'unload to /tmp/in delimiter " " select procname from
sysprocedures' | dbaccess test
4) Run:
for x in `cat /tmp/in`; do
echo $x
echo "update statistics for procedure $x" | dbaccess test
done
5) run onstat -l and determine how much log space was used. In this case
.7% of 100MB = 70KB
700000010313c20 38 U---C-L 11226 3:53 25600
7868 30.73
6) Run:
for x in `cat /tmp/in`; do
echo $x
echo "update statistics for SPECIFIC procedure $x" | dbaccess test
done
7) run onstat -l and determine how much log space was used. In this case
10.7% of 100MB = 10.7MB
700000010313c20 38 U---C-L 11226 3:53 25600
10605 41.43
Regards,
Bill
> -----Original Message-----
> From: owner-informix-list@iiug.org [SMTP:owner-informix-list@iiug.org]
> On Behalf Of mosserp@wellsfargo.com
> Sent: Thursday, August 04, 2005 7:27 PM
> To: informix-list@iiug.org
> Cc: Bill Dare; KAGEL@bloomberg.net
> Subject: 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
sending to informix-list