SQL running too slow in IDS 9.4

Hi All, This has been asked again and again.. I'm curious how can i tell that i need to do the ff: 1. Update statistics -- this just been executed during the weekend, 5 days now. 2. How can i identify what causes excessive memory use? i did onstat -g mem, onstat -g iov for virtual processes.3. Reindex - how to know if i need to do re-indexing of a table?

See my responses below: Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Thu, Aug 19, 2010 at 10:06 AM, JACK PAPA <informix2009@gmail.com> wrote: > Hi All, > > This has been asked again and again.. I'm curious how can i tell that i > need > to do the ff: > > 1. Update statistics -- this just been executed during the weekend, 5 days > now. > That's going to depend on the activity on the table, the percentage of rows inserted, deleted, updated (on indexed columns) and the nature of the changes. If the net results of the operations over the 5 days is to maintain the relative balance between sets of values in indexed columns, then the existing data distributions are probably still usable by the optimizer to make reasonable decisions. If the net result is a change in the relative counts of certain values, then they stats are stale. You know your data and operations better than anyone, so that's the best answer. Short of that, you can try to guess. My dostats utility's browsing options (-b & -B) determine whether the number of rows of data has changed by a given percentage since the stats were last produced and will redo that stats for tables where the number of rows increases or decreases by more than that value. The aging options (-a & -A) cause dostats to update tables whose stats are older than a specified number of days. Used together aging and browsing minimize the number of tables that dostats will update. I tend to have dostats run daily with -b -B 10 & -a -A 7 and weekly with neither option. That way all tables get fresh stats each weekend and highly active tables get redone during the week once or twice. > 2. How can i identify what causes excessive memory use? i did onstat -g > mem, > onstat -g iov for virtual processes.> Tough one. Very hard to glean. > 3. Reindex - how to know if i need to do re-indexing of a table? > If the index is constantly high on the BTree Scanners cleaning list, if the number of index levels is higher than 4, if the number of pages in the index seems too high (partial pages not being compressed together - not done well until 11.50.xC5) you may get some performance improvement from rebuilding that index. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5965c5cfa0f048e313728