Re: Update Statistics
Forwarded from gbryan@ssf-sys.DHL.COM: -- Walt Hultgren Internet: walt@rmy.emory.edu Emory University, Atlanta, GA, USA BITNET: walt@EMORY Voice: +1 404 727 0648 UUCP: {...,rutgers,gatech}!emory!rmy!walt --- Header ------------------------------------------------------------------- > From gbryan@ssf-sys.DHL.COM Fri May 3 16:47:03 1991 > Received: from uunet.UU.NET by > rmy.rmy.emory.edu (5.59/2.14.EUCC-MathCS) via SMTP > id AA17882 ; Fri, 3 May 91 16:46:59 EST > Received: from Gateway.DHL.COM by uunet.UU.NET with SMTP > (5.61/UUNET-uucp-primary) id AA05588; Fri, 3 May 91 15:41:27 -0400 > Received: from dhlmis.ssf-sys.dhl.com by gateway.DHL.COM > id aa28342; Fri, 3 May 91 12:36:48 PDT > Received: by dhlmis.ssf-sys.DHL.COM > (5.61/1.8) id AA13232; Fri, 3 May 91 12:35:48 -0700 > From: Greg Bryan <gbryan@ssf-sys.DHL.COM> > Subject: Re: Update Statistics > To: informix-list-owner@rmy.emory.edu > Date: Fri, 3 May 91 12:35:47 PDT > X-Mailer: ELM [version 2.3 PL11] > Message-Id: <9105031237.aa28342@gateway.DHL.COM> --- Message ------------------------------------------------------------------ > Message-Id: <9105031553.AA15507@gatech.edu> > To: rmy!informix-list > Subject: Re: Update Statistics > In-Reply-To: Your message of Thu, 02 May 00 19:91:32 GMT > Date: Fri, 03 May 91 11:09:12 -0500 > From: Harry Bochner <emory!gatech!harvard!das!bochner@gateway.DHL.COM> > X-Informix-List-Id: <list.109> > > > Does anyone have a recommendation on how often the update statistics > > command should be run? > > Should it be run as an adminstrative routine, or started up at the > > beginning of each .4gi(.4go for some) > > Good question. I don't run it all, and haven't noticed any ill effects. > > My suspicion is that the command is obsolete, but my only reason for > suspecting it is this: in the earliest versions we had (< 2.0 ?), the > status command (under Info) didn't return an up-to-date count of number > of rows unless you did an 'update statistics' first. Starting somewhere > in version 2, however, I noticed that this count always seemed to be > up-to-date even if I didn't bother with 'update statistics'. So it's not > obvious to me that it is still needed. > > A quick look through the system tables in appendix B doesn't reveal anything > else it might be updating, but I haven't really tried to experiment with > it, and I don't have any inside information. Does anyone (who is willing > to tell us) know for sure? > > -- Harry Bochner > -- bochner@das.harvard.edu > The update statistics command is much more important with the Informix 4.x engines than it was with previous engines. The reason for this is the cost based optimizer, which requires current statistics about the data in order to make good decisions. The old engines utilized a rule based (read "dumb") optimizer that did not care about these statistics. The cost based optimizer weighs the cost of various query plans, and selects the plan with the lowest cost. To support the 4.x, cost based optimizer, more statistics have been added (such as number of disk blocks occupied by a table, height of a b-tree index, number of disk blocks occupied by the leaves of a b-tree index, clusteredness of an index, etc.). As a result, the cost to run UPDATE STATISICS has increased. The frequency with which this is run must be tuned for the particular trade-offs inherent in a given installation (types of queries, size of tables, static vs. dynamic nature of various tables, timing of user load on the system, etc.) - the basic trade-off is this: the COST of running UPDATE STATISTICS vs. the BENEFIT of BETTER OPTIMIZER PERFORMANCE. Greg Bryan Data Administrator DHL Systems Inc. 1700 S. Amphlett Blvd. San Mateo, CA 94402 415-574-4197 gbryan@ssf-sys.dhl.com uunet!ssf-sys.dlh.com!gbryan --- End of Message -----------------------------------------------------------