Update statistics low

Hi, I have a database on which we run update statistics daily thru a cron. The size of the database is 64GB and it contains around 342 tables.Update statistics HIGH takes only 5 mins while update statistics LOW takes almost 7 hours to complete. I have other databases in the same server but they are completed in few minutes. What could be the reason behind LOW taking so long and how can I optimize it? Thanks in advance.

Despite the "LOW" keyword, that mode may be (and in fact is in many cases) the slowest mode. The reason for this is that it gathers information about the indexes. So you will noticed the slowness in tables with lots of indexes. Many times it may help if you recreate the indexes (if that is feasible in your environment). This is a known issue and R&D is looking into it AFAIK. But unfortunately it's not an easy problem. update stats low will read all the indexes in an ordered way. At first we may think that reading something that is already ordered in an ordered way should be fast. But the problem is that what is ordered is the keys in the index structure. But the pages of the index are not physically ordered on the disk. So the engine has to do many disk acesses. Recreating the index can help because the pages tend to become physically ordered on the disk. So consecutive reads of pages tend to take advantage of the hardware caches. I have the feeling that this is particular easy to spot in indexes with datetime fields but I have no real tests that show this. Note that the above does cause serious impact on normal index usage. Only while gathering the low statistics information (from the indexes). The information I'm talking about is stored in the sysindexes table. In particular, the "clustered" value is the reason for all this behavior. AFAIK it's very important for some of the optimizer decisions.... Another approach is to run update stats without collecting all this info. LOW mode collects the index information and also very important information regarding the table itself. Specifically it updates the number of rows and pages in systables. If you can live with just these last ones, you can run UPDATE STATISTICS LOW FOR TABLE (non_index_column) This will run immediately and will update the number of rows and the number of pages used in the table (in other words, the size of the table). Naturally this can have impact on the optimizer since you're not collecting all the info it likes... Regards. On Tue, Mar 22, 2011 at 5:54 AM, SUSHANT KODE <ksushants@gmail.com> wrote: > Hi, > > I have a database on which we run update statistics daily thru a cron. The > size of the database is 64GB and it contains around 342 tables.Update > statistics HIGH takes only 5 mins while update statistics LOW takes almost > 7 > hours to complete. I have other databases in the same server but they are > completed in few minutes. > > What could be the reason behind LOW taking so long and how can I optimize > it? > > Thanks in advance. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174feb240e91dd049f0eeb5c

Version and platform information please! Yes it makes a difference. Also, what are the EXACT commands you are running for the LOW and HIGH (if at the table level, examples for a few tables will suffice)? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 22, 2011 at 1:54 AM, SUSHANT KODE <ksushants@gmail.com> wrote: > Hi, > > I have a database on which we run update statistics daily thru a cron. The > size of the database is 64GB and it contains around 342 tables.Update > statistics HIGH takes only 5 mins while update statistics LOW takes almost > 7 > hours to complete. I have other databases in the same server but they are > completed in few minutes. > > What could be the reason behind LOW taking so long and how can I optimize > it? > > Thanks in advance. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec51dd7eda5181b049f0fed0e

Hi, The commands are as follows: 1) for Update statistics LOW echo "update statistics low;" | dbaccess $db 2) for Update statistics HIGH dbaccess $db << _____UPD_HG &>/dev/null OUTPUT TO "$tmp" WITHOUT HEADINGS SELECT unique "update statistics high for table "||trim(tabname)||"(" ||trim(colname)||");" FROM systables t, sysindexes i, syscolumns c WHERE t.tabid = i.tabid AND t.tabid = c.tabid AND i.tabid = c.tabid AND i.part1 = c.colno AND t.tabtype = "T" AND t.tabid > 99 _____UPD_HG Informix version: 11.10.FC2 OS: SUSE Linux Enterprise Server 10 SP1 (x86_64)