IDS Forum

Re: Update Statistics

Posted By: Art Kagel
Date: Wednesday, 3 March 2010, at 2:24 p.m.

In Response To: RE: Update Statistics (Wyza, Jonathon)

OK, older versions of Informix (7.xx, 9.xx) depended on the user's
environment variable DBUPSPACE which controlled the amount of memory that
IDS would allow a single session to use for in-memory sorting if parallel
query was not enabled (setting PDQPRIORITY > 0 or running SET PDQPRIORITY
within the session). DBUPSPACE defaulted to 15MB with a maximum of 50MB.
If you were running under PDQPRIORITY, then the amount of memory available
was governed by the DSS memory configuration parameters in the ONCONFIG
file. These include DS_TOTAL_MEMORY (total memory for all DSS or parallel
queries), DS_MAX_QUERIES (the number of active DSS or parallel queries that
can run, and PDQPRIORITY (the percentage of server resources that the
session wants access to). The resulting amount of memory available to the
parallel session for in-memory sorting and other purposes (known in the
documentation as a 'quanta') is essentially limited only by the system
memory and the DBA's configuration.

In 11.10 IBM introduced the new ONCONFIG parameter DS_NONPDQ_QUERY_MEM which
now overrides DBUPSPACE for nonparallel queries (ie PDQPRIORITY=0). That
can be set to anything from zero to the size of the quanta determined by the
interaction of DS_TOTAL_MEMORY and DS_MAX_QUERIES. Unfortunately it
defaults to 128KB not 15MB. One has to make certain to configure
DS_NONPDQ_QUERY_MEM up to more reasonable levels for real-world
applications.

In truth, neither DBUPSPACE nor DS_NONPDQ_QUERY_MEM are a consideration for
real index builds or for UPDATE STATISTICS processing which should always be
carried out using PDQPRIORITY set as high as one can without impacting other
running tasks or production queries. Also note the importance, mentioned in
passing, in John's article, of the parallel sort environment variables
PSORT_NPROCS and PSORT_DBTEMP.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

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 Wed, Mar 3, 2010 at 10:14 AM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote:

> Interesting. The older article talks about memory size for update
> statistics
> to use. It also talks about having memory sizes of 50 mb. This seems oddly
> low, do you normally use higher memory amounts in modern systems? It also
> talks about not running update statistics "high" on large tables. What is
> classified as a large table? (ex I think our largest table has something on
> the order of 6 million rows). Do you based the frequency of when you run
> update stats on the time it's going to take or the amount of changes made
> to
> tables?
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> ==========================
> SLES 10 SP2 & IDS 10.0 HC9
>
> " I would love to change the world, but they won't give me the source
> code."
> -- Unknown
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Fernando
> Nunes
> Sent: Wednesday, March 03, 2010 9:51 AM
> To: ids@iiug.org
> Subject: Re: Update Statistics [19171]
>
> UPDATE STATISTICS is a fundamental step for good database performance.
> Informix query optimizer is not the most feature reach one, but it does a
> great job. What I mean is that there are some ways to do queries that
> Informix does not (yet) use. But the optimizer tends to choose the best
> available plan. BUT, for doing that it's essential that it knows about the
> data. And that is done through "good" update statistics.
>
> The most recent article that I know of, about this subject is
>
>
>
> http://www.ibm.com/developerworks/data/library/techarticle/dm-0803changappa/index.html
>
> You should also take a look at the previous "bible":
>
>
>
> http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller/0203miller.html
>
> but this one is obviously a bit outdated (all new stuff in Informix 11.x is
> not covered).
> And as usual you MUST take a look at the manual, specifically the Syntax
> Guide and the Performance Guide.
>
> You can use some tools to help you. Automatic update statistics was
> introduced in Informix 11 and you can take advantage of that using Open
> Admin Tool (OAT). Besides that Art Kagel has nice tools (dostats). You'll
> need to compile them.
> If you prefer the simplicity of shell script I can provide some (I should
> put them in the IIUG software repository, but time is short - and bad
> excuses are common - :( )
>
> Regards.
>
> On Wed, Mar 3, 2010 at 1:45 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu
> >wrote:
>
> > I've seen the recommendation to do update statistics on ids on this list
> > over
> > the last couple months in certain situations. Sadly I know little about
> > administering IDS but I've muddle through so far. I was wondering if
> > someone
> > could do a quick tutorial about update statistics covering things like
> when
> > to
> > use it and why to use it and how often to use it.
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu>
> > ==========================
> > SLES 10 SP2 & IDS 10.0 HC9
> >
> > " I would love to change the world, but they won't give me the source
> > code."
> > -- Unknown
> >
> >
> >
> >
>
>
> *******************************************************************************
> > 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...
>
> --0016e6d99efabae2850480e69b26
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001517402aaefb3c950480ea6bfe

Messages In This Thread

• Update Statistics
  Wyza, Jonathon -- Wednesday, 3 March 2010, at 8:45 a.m.
  • Re: Update Statistics
    Fernando Nunes -- Wednesday, 3 March 2010, at 9:50 a.m.
    • RE: Update Statistics
      Wyza, Jonathon -- Wednesday, 3 March 2010, at 10:14 a.m.
      • Re: Update Statistics
        Art Kagel -- Wednesday, 3 March 2010, at 2:24 p.m.
      • RE: Update Statistics
        John Miller iii -- Wednesday, 3 March 2010, at 6:49 p.m.
  • Re: Update Statistics
    Art Kagel -- Wednesday, 3 March 2010, at 12:12 p.m.
  • Re: Update Statistics
    VIKAS HIVARKAR -- Friday, 5 March 2010, at 10:47 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.