IDS Forum

Re:Update Statistics

Posted By: ART KAGEL, BLOOMBERG/ 731 LEXIN
Date: Tuesday, 4 September 2007, at 10:23 a.m.

In Response To: Update Statistics (Juan Pablo)

I believe that you are asking: "What is the impact on my database server of
executing UPDATE STATISTICS commands?" If that is it, here is my answer:

Overall there is little impact of running UPDATE STATISTICS. Follows details
on what effects/impacts I have observed:

- Since update statistics has to read all or at least a significant percentage
of the rows in a table, and sort the data, there will be contention for IO
bandwidth resources and memory and temporary disk space for intermediate sort
results.

- The table being updated is not locked, however, there will be brief row level
locks taken on rows in the systables, syscolumns, sysdistrib, and sysindexes or
sysindices tables at the time that the calculated results are saved.

- If you have stored procedures that reference the updated table, they will
have to be recompiled by executing UPDATE STATISTICS FOR PROCEDURE/
FUNCTION ...; for each. If this is not done the procedure/function will be
recompiled the next time it is called which will at least slow it down and may
return an error.

- If you have any running processes with prepared statements that reference the
updated tables, their query plans may become invalid in which case the tasks
will receive a -710 error the next time the prepared statement is executed
either by an execute statement or an OPEN cursor statement. The task either
has to reprepare the statements in response to the -710 or be killed and
restarted.

-- Note: This last problem has finally been resolved in IDS 11.10 which
automatically reprepares such statements when they are executed without any
error.

FYI: Consider using my dostats utility for running update statistics on your
tables. It automatically implements the recommended suite of commands to
update your statistics to optimum levels with minimum resource use. It also
contains options that help you manage when to update stats on tables and to
automate the process using cron. Dostats is downloadable from the IIUG
Software Repository in the package utils2_ak.

Art S. Kagel

----- Original Message -----
From: Juan Pablo <ids@iiug.org>
To: ids@iiug.org
At: 9/03 19:36:10

Hello, during the execution of UPDATE STATISTICS to the tables of my BD, as
it is the impact that produces the execution.

Thanks

_________________________________________________________________
MSN Amor: busca tu ½ naranja http://latam.msn.com/amor/

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Update Statistics
  Juan Pablo -- Monday, 3 September 2007, at 7:35 p.m.
  • Re:Update Statistics
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Tuesday, 4 September 2007, at 10:23 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.