IDS Forum

Re: update statistics

Posted By: Art S. Kagel
Date: Friday, 17 February 2006, at 8:52 a.m.

In Response To: Re: update statistics (sergios.zagotsis@egnatiabank.gr)

> ----- Original Message -----
> From: Sergios.Zag.... <ids@iiug.org>
> At: 2/17 3:12
>
> Hi,

Hello.

> There are 4 tmpdb spaces defined
>
> onconfig statement
> DBSPACETEMP tmpdbs1:tmpdbs2:tmpdbs3:tmpdbs4

OK, so 4GB of tempdbspace...

> onstat -d output:
> ..................................
> 3ef8c978 7 0x2001 8 1 N T informix tmpdbs1
> 3ef8cac8 8 0x2001 9 1 N T informix tmpdbs2
> 3ef8cc18 9 0x2001 10 1 N T informix tmpdbs3
> 3ef8cd68 10 0x2001 11 1 N T informix tmpdbs4
>
> 3ef55a48 8 7 0 500000 499847 PO-
> E:\IFMXDATA\ol_prime\tmpdbs1_dat.000
> 3ef55bb0 9 8 0 500000 499847 PO-
> E:\IFMXDATA\ol_prime\tmpdbs2_dat.000
> 3ef55d18 10 9 0 500000 499847 PO-
> E:\IFMXDATA\ol_prime\tmpdbs3_dat.000
> 3ef55e80 11 10 0 500000 499847 PO-
> E:\IFMXDATA\ol_prime\tmpdbs4_dat.000
> ................................................................
>
> We have Informix 9.30 TC3 on a Xeon 2x3.8 GHz with 4GB Ram

9.30xC3 is the first optimized server version in the 9.xx codebase, so
the expanded use of DBUPSPACE and PDQPRIORITY applies. (See John Miller
III's article:
http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html
)

> There is no DBUPSPACE environment variable define

Unless you set PDQPRIORITY, DBUPSPACE is the only way to increase the
memory used for sorting from the default of 15MB for this version (4MB
for earlier versions) to the maximum without PDQ of 50MB (35MB in
earlier releases). I do not see from your notes below that you have
PDQPRIORITY set, so DS_TOTAL_MEMORY is ignored. Only if PDQPRIORITY >=
1 is DS memory used for sorting.

Do you have PSORT_DBTEMP set? If so the folders/directories/filesystems
listed there will take precedence over the temp dbspaces listed in
DBSPACETEMP for writing sort-work files and DBSPACETEMP will be ignored.

> C Drive has 50GB free disk and E drive has 800GB free disk

> onstat -g seq output
> Informix Dynamic Server Version 9.30.TC3 -- On-Line -- Up 23:28:59 --
> 1354176 Kbytes
>
> onconfig statements
> MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
> DS_MAX_QUERIES 32 # Maximum number of decision support queries
> DS_TOTAL_MEMORY 4096 # Decision support memory (Kbytes)
> DS_MAX_SCANS 1048576 # Maximum number of decision support scans
>
> I suppose that during update statistics the DS_TOTAL_MEMORY value is used

Only, as I stated above, PDQPRIORITY is set >0. Otherwise the session
will allocate a work area from the general virtual memory segment pool
(or cause another virtual segment to be allocated) sized according to
the contents of DBUPSPACE or the default of 15MB if that var is unset.

> and that low value causes the problem. Am I right?

No. The -567 error definitely refers to writing the sort-work area
(whereever it's allocated) to disk when the dataset to be sorted is
larger than the work area. Your problem is disk related somehow.

> I am not sure which value do I have to set for that variable. There is any
> way to calculate it?

I should have referred to this originally: I see you are trying to run
UPDATE STATISTICS HIGH on the entire table. This is unneccessary for
performance and takes a HUGE amount of resources and sort space. You
should read the Performance Guide and John Miller III's paper (URL
above) to see the recommended suite of commands to create useful levels
of stats and data distributions with minimal work and runtime.
Alternatively, you could get my dostats utility (new version coming soon
- one bug left to zap) which implements both sets of protocols
automatically. Dostats is contained in the package utils2_ak in the
IIUG Software Repository.

Art S. Kagel

> Thanks
>
> Sergios
> <Original Post SNIPPED>

Messages In This Thread

• update statistics
  sergios.zagotsis@egnatiabank.gr -- Thursday, 16 February 2006, at 9:38 a.m.
  • Re: update statistics
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Thursday, 16 February 2006, at 9:42 a.m.
    • Re: update statistics
      sergios.zagotsis@egnatiabank.gr -- Friday, 17 February 2006, at 3:11 a.m.
      • Re: update statistics
        Art S. Kagel -- Friday, 17 February 2006, at 8:52 a.m.
  • Re: update statistics
    Esteban Casuscelli -- Thursday, 16 February 2006, at 11:57 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.