IDS Forum

Re: SQL running too slow in IDS 9.4

Posted By: Art Kagel
Date: Thursday, 19 August 2010, at 2:19 p.m.

In Response To: SQL running too slow in IDS 9.4 (JACK PAPA)

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

Messages In This Thread

• SQL running too slow in IDS 9.4
  JACK PAPA -- Thursday, 19 August 2010, at 10:06 a.m.
  • Re: SQL running too slow in IDS 9.4
    Art Kagel -- Thursday, 19 August 2010, at 2:19 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.