Update Statistics High

Hi, Our billing application is working on IDS 7.31.UD6W5. Most of the tables in the database range in size from a few thousand to a couple of hundred thousand records. There are a couple of tables with around 7 million records each. I would like to know the recommended approach to updating statistics of my database. Can I run "Update Statistics High" for the entire database (our support people are recommending this approach) ? Is there any downside of updating statistics? Regards Uday

UDAY PRABHU said: > > Our billing application is working on IDS 7.31.UD6W5. Most of the tables That's quite an old release. > in > the database range in size from a few thousand to a couple of hundred > thousand > records. There are a couple of tables with around 7 million records each. > I would like to know the recommended approach to updating statistics of my > database. > Can I run "Update Statistics High" for the entire database (our support > people You can, but I wouldn't recommend it. It would take "forever". I would rather get Art Kagel's dostats program from www.iiug.org and run that. It has things like a parallel driver to make sure you make the most use of your server. I would also advocate upgrading to 7.31.UD8 or whatever is current, as I think that has the changed DBUPSPACE functionality whereby DBUPSPACE doesn't just limit the amount of memory space given to UPDATE STATISTICS, but you can actually increase it. This can have a significant effect on UPDATE STATISTICS HIGH. > are recommending this approach) ? > Is there any downside of updating statistics? I assume you mean UPDATE STATISTICS HIGH: * Takes a long time. * Loads the system up. If you're not currently updating statistics at all, I strongly recommend you resign, as you're not doing your job properly. -- Bye now, Obnoxio "Jesus you fucking people are hopeless." -- Double Anal

It's fine to run update stats high on all tables, but is there enough processing time reserved to do that ? Look at the program "dostats" downloadable from the forum. Even if you would'nt use it, it's helpfull to learn about update stats sql and what to do. We use "dostats" (with thanks to the author A.Kagel) in a script. The rest of allocated time for update stats, we run stats high on tables less than 500 000 rows. When the time for updating stats is to expensive for a run during 1 night : - "Drive_dostats" allow to do multiple "update stats" simultaneous or - you can split the job in different scripts and run each once a week at different schedule time. Helpfull to split the job are the options "-e" and "-f" of dostats. Option "-e" output handle time per table and option "-f" output the SQL to a file. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Sent: 26 February 2006 00:40 To: ids@iiug.org Subject: Update Statistics High [6445] Hi, Our billing application is working on IDS 7.31.UD6W5. Most of the tables in the database range in size from a few thousand to a couple of hundred thousand records. There are a couple of tables with around 7 million records each. I would like to know the recommended approach to updating statistics of my database. Can I run "Update Statistics High" for the entire database (our support people are recommending this approach) ? Is there any downside of updating statistics? Regards Uday **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. *****DISCLAIMER***** Dit bericht en alle bijhorende zijn uitsluitend bestemd voor de geadresseerde en vertrouwelijk. Indien dit bericht niet voor U bestemd is, gelieve dit dan te vernietigen en de verzender te verwittigen. Openbaring, vermenigvuldiging, verspreiding en verstrekking aan derden is niet toegestaan, tenzij anders vermeld. Aangezien internet de integriteit van dit bericht niet kan verzekeren, kan de Dienst Vreemdelingenzaken niet verantwoordelijk gesteld worden indien dit bericht gewijzigd is. Bezoek onze website: http://www.dofi.fgov.be ---------------------------------------- *****DISCLAIMER***** Ce message et toutes les pieces jointes sont etablis a l'intention exclusive de ses destinataires et sont confidentiels. Si vous recevez ce message par erreur, merci de le detruire et d'en avertir l'expediteur. Toute utilisation de ce message non conforme a sa destination, toute diffusion ou toute publication, totale ou partielle, est interdite, sauf autorisation expresse. L'internet ne permettant pas d'assurer l'integrite de ce message, l'Office des Etrangers decline toute responsabilite au titre de ce message, dans l'hypothese ou il aurait ete modifie. Visitez notre site web: http://www.dofi.fgov.be

Uday: Your server version, though a bit old, has the latest internal update statistics and sorting optimizations. You should be doing the following to produce useful levels of stats while minimizing stats collection time: 1. Single HIGH with DISTRIBUTIONS ONLY on all columns that lead any index plus the first columns that are different when several indexes start with the same column(s). If this statement is longer than 64K break it up into two or more statements. 2. MEDIUM on all columns not listed in the HIGH run. If this statement is longer than 64K break it up into two or more statements. 3. LOW on the entire key of every index. OR - you can just get my dostats utility which implements these protocols automatically. Dostats also provides many options and features that make managing your server stats much easier. Dostats is included in the package utils2_ak available from the IIUG Software Repository. This works best if you set DBUPSPACE or PDQPRIORITY, DS_TOTAL_MEMORY, and DS_MAX_QUERIES (see the paper noted below for details) to increase in-memory sort space above the defaults. The information above is found in the Informix Performance Guide and in a paper by John Miller III at: www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/020 3miller.html Art S. Kagel ----- Original Message ----- From: Uday Prabhu <ids@iiug.org> At: 2/26 22:31 Hi, Our billing application is working on IDS 7.31.UD6W5. Most of the tables in the database range in size from a few thousand to a couple of hundred thousand records. There are a couple of tables with around 7 million records each. I would like to know the recommended approach to updating statistics of my database. Can I run "Update Statistics High" for the entire database (our support people are recommending this approach) ? Is there any downside of updating statistics? Regards Uday ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.