IDS Forum

Re: light scans with update statistics

Posted By: Art Kagel
Date: Sunday, 31 March 2013, at 12:24 p.m.

In Response To: light scans with update statistics (ROGER VILCA)

Roger:

Several points:

1. The algorithm for LOW stats has been seriously improved in the latest

release (12.10), so an upgrade might be a good idea.

2. You should not need to perform a LOW on the whole table if you are

also performing MEDIUM and HIGH on selected columns. The HIGHs in index

leading columns and MEDIUM on all other columns will take care of the LOW

stats for those columns - which together covers all columns - unless you

specify DISTRIBUTIONS ONLY.

3. The recommended suite of update statistics commands in the

Performance Guide does include separate LOWs for the entire key of each

index (you only have to do that for indexes with more than one key column

since the HIGH on the leading and only column for singleton indexes will

take care of that already).

4. You should use my dostats utility for update statistics - in the

package utils2_ak downloadable from the IIUG Software Repository.

5. You can speed all of your update statistics in the following ways:

1. Set PDQPRIORITY as high as possible without adversely affecting

other processes that are running.

2. Set PSORT_NPROCS to 2X the number of CPU VPs

3. Set PSORT_DBTEMP to at least three but up to six filesystems with

enough space each to hold a sorted copy of the largest/widest index. This

will be faster than temp dbspaces in many cases.

4. If you do not set PSORT_DBTEMP make sure that you have at least

three and as many as six temp dbspaces available and listed in
DBSPACETEMP.

5. Set DBUPSPACE to increase the amount of memory available for

in-memory sorting

6. Set the ONCONFIG variable DS_NONPDQ_QUERY_MEM to at least 15MB

(the default is 128K) and up to 25% of DS_TOTAL_MEMORY (you may have to

increase SHMVIRTSIZE and/or DS_TOTAL_MEMORY).

Here's a link to a paper on optimizing UPDATE STATS that John Miller posted
several years ago:

http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller/0203miller.html

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 Sun, Mar 31, 2013 at 10:09 AM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote:

> Informix environment: 11.50 FC8 with HP-UX
>
> Hi,
> I want to improve the performance of "update statistics low" for large
> tables.
> I have the following facts:
> a) When I run "update statistics low for table <bigtable>", the sentence
> runs
> for several hours.
> b) When I review the details of the session with onstat-g ses appear
> status =
> IO Wait- most of the time.
> c) I have reviewed in the operating system level and the amount of I/O
> operations barely growing. In terms of CPU and memory, the operating system
> seems not to be much variation.
> d) When I check onstat-g lsc nothing appears. I tried with set "isolation
> to
> dirty read" and LIGHT_SCANS = FORCE but with no success.
>
> My questions are:
> 1. Why "update statistics low" not use light scans?
> 2. What other considerations should be taken to improve the performance of
> update statistics low?
>
> Thanks in advance,
> Roger
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--089e01161a9478998a04d93af120

Messages In This Thread

• light scans with update statistics
  ROGER VILCA -- Sunday, 31 March 2013, at 10:09 a.m.
  • Re: light scans with update statistics
    Fernando Nunes -- Sunday, 31 March 2013, at 10:23 a.m.
    • Re: light scans with update statistics
      ROGER VILCA -- Sunday, 31 March 2013, at 10:51 a.m.
  • Re: light scans with update statistics
    Art Kagel -- Sunday, 31 March 2013, at 12:24 p.m.
    • Re: light scans with update statistics
      ROGER VILCA -- Sunday, 31 March 2013, at 12:48 p.m.
      • Re: light scans with update statistics
        Art Kagel -- Sunday, 31 March 2013, at 1:16 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.