update statistics performance issue

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 .

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\\ " 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. =

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\\ " 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 .