RE: Update Statistics for Stored Procedures

Posted in 2003

Colin Bull — Tue, 9 Dec 2003 08:21:37 -0000 — source: Usenet: comp.databases.informix

KalpanaPai wrote > Sent: 09 December 2003 03:21 > > I have around 80 User defined Stored procedures in our informix > database (9.21 on Solaris). > > All SPL's are very simple ones having 1 or 2 sqls, few of them have > some calculations, no recursive calls , not having nested procs. > > At the moment no Update stats running for the SPLs . > Do i need to run Update stats even for SPLs also? If so when i write > new SPLs when i should run update stats ? Is there any performance > issues with Simple SPLs if i don't run Update stats? > Do i have to run Individually or is there any command which will run > for all SPLs. > > Please advise me the Guidelines for the above as i am not a Informix > Person. > The execution plan for the SQL contained in an SPL is done when it is compiled, and when the update stats for procedure is done. If any tables change enought to warrant ordinary update stats to be done, they will probably need doing for procedures. Perversly, the command to do all procedures in one go is - uopdate statistics for procedure ; Update stats for procedures should also be done if any tables are renamed. Colin Bull c.bull@videonetworks.com sending to informix-list