IDS Forum

RE: update statistics performance issue

Posted By: Guo, Denny
Date: Tuesday, 6 February 2007, at 1:46 p.m.

In Response To: Re: update statistics performance issue (John Miller iii)

Thanks John,

I was actually following your documents to do the test.
The b3recapiid is the lead key of an index.
I try to set PSORT_NPROCS=8, seems there is little performance gain.
I will try DBUPSPACE parameter.
Your input is really helpful.

Thanks a lot.
Denny

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
John Miller iii
Sent: Tuesday, February 06, 2007 1:32 PM
To: ids@iiug.org
Subject: Re: update statistics performance issue [8365]

If the column b3recapiid is the lead key of an index there is a good
chance it will use and index scan and avoid the sequential scan. This
can be very slow do to the random I/O generated.

You can try setting the environment variable DBUPSPACE which
has the following format to the value below

disk:memory:options

Options Values

1. Do not use any index paths and Print information in sqexplain.=
out

2. Do not use any index paths and do not print information in

sqexplin.out

DBUPSPACE=3D0:50:1

By setting this environment variable you will see the line in the set
explain
file below.

"Index scans disabled\n"

If you are already doing the sorting and adding more memory does not he=

lp
then it is one of two items:
1. Improve the parallelism by looking at tuning the parallel sorts

2. In version 7.3 there is a performance bug that was fixed in 9.4

that deals with how slow the sorts free's memory, when you start u=
sing
a large

amount of memory the sorting becomes slower because of the

number of blocks it frees. This is legacy defect 149270.

Hope this helps.
John

=

"Guo, Denny" =

<DGuo@livingstoni =

ntl.com> =
To

Sent by: ids@iiug.org =

ids-bounces@iiug. =
cc

org =

Subj=
ect

update statistics performance is=
sue

02/06/2007 09:51 [8364] =

AM =

=

=

Please respond to =

ids@iiug.org =

=

=

Hi Gurus,

I have a quick question on the update statistics.
I am trying to improve the update performance. So I turn on the SQL
explain.
I am able to use MGM to give IDS more memory (can use up to 1G) to
execute the update. However seems it even slow than using the default
15M memory.
Anybody has idea?

Also from the explain out file:

Table: informix.b3_recap_details
Mode: HIGH
Number of Bins: 267 Bin size 195948
Sort data 300.4 MB PDQ memory granted 315.4 MB
Estimated number of table scans 1
PASS #1 b3recapiid
Light scans enabled
Completed pass 1 in 1 minutes 48 seconds

Is the 1m48s the exactly time to execute the update on this column?
Because I am using tail -f sqexplain.out to trace the log and find out
it takes more than that time to execute the update.

Please give me some hints.

Thanks,
Denny

*******************************************
The information contained in this e-mail message may
contain privileged and confidential information.
If you are not the intended recipient, you are
hereby notified that any review, dissemination,
distribution or duplication of this communication
is strictly prohibited. If you have received this
message in error, please notify the sender by return
e-mail, delete this message and destroy any copies.
Internet e- mail is not guaranteed to be secure or
error-free. Messages could be intercepted, corrupted,
lost, arrive late or contain viruses.
The sender will not be liable for
these risks.

*******************************************
Ce message electronique pourrait contenir des
informations privilegiees et confidentielles. Si vous
n'en etes pas le recipiendaire prevu, nous vous
signalons qu'il est strictement interdit d'examiner,
de diffuser, de distribuer et de reproduire le
present message. Si vous l'avez recu par erreur,
veuillez prevenir l'expediteur par courriel, puis
effacer ce message et en detruire toute copie.
Le courrier electronique n'est pas garanti
securitaire ni exempt d'erreurs. Les messages
pourraient etre interceptes, corrompus, egares,
retardes ou contamines par des virus.
L'exp'editeur n'est pas
responsable de ces risques .

***********************************************************************=

********

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

=

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

*******************************************
The information contained in this e-mail message may
contain privileged and confidential information.
If you are not the intended recipient, you are
hereby notified that any review, dissemination,
distribution or duplication of this communication
is strictly prohibited. If you have received this
message in error, please notify the sender by return
e-mail, delete this message and destroy any copies.
Internet e- mail is not guaranteed to be secure or
error-free. Messages could be intercepted, corrupted,
lost, arrive late or contain viruses.
The sender will not be liable for
these risks.

*******************************************
Ce message electronique pourrait contenir des
informations privilegiees et confidentielles. Si vous
n'en etes pas le recipiendaire prevu, nous vous
signalons qu'il est strictement interdit d'examiner,
de diffuser, de distribuer et de reproduire le
present message. Si vous l'avez recu par erreur,
veuillez prevenir l'expediteur par courriel, puis
effacer ce message et en detruire toute copie.
Le courrier electronique n'est pas garanti
securitaire ni exempt d'erreurs. Les messages
pourraient etre interceptes, corrompus, egares,
retardes ou contamines par des virus.
L'exp'editeur n'est pas
responsable de ces risques .

Messages In This Thread

• update statistics performance issue
  Guo, Denny -- Tuesday, 6 February 2007, at 12:51 p.m.
  • Re: update statistics performance issue
    John Miller iii -- Tuesday, 6 February 2007, at 1:32 p.m.
    • RE: update statistics performance issue
      Guo, Denny -- Tuesday, 6 February 2007, at 1:46 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.