rebuild index extreme slow with VER115UC3

Hi Gurus, I am still playing with our new IDS V115UC3 on AIX box. Our server is IBM,7026-6H1, it has 4 cpu, 4G memory. Normally it will only take 10 hours to rebuild all indexes with IDS Ver7.31.. After migrate to IDS V115UC3, we try to rebuild the index and it takes almost 24 hours and still not finish. Anybody has idea why and how to speed up the rebuilding. 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 électronique pourrait contenir des informations privilégiées et confidentielles. Si vous n'en êtes pas le récipiendaire prévu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le présent message. Si vous l'avez reçu par erreur, veuillez prévenir l'expéditeur par courriel, puis effacer ce message et en détruire toute copie. Le courrier électronique n'est pas garanti sécuritaire ni exempt d'erreurs. Les messages pourraient être interceptés, corrompus, égarés, retardés ou contaminés par des virus. L'expéditeur n'est pas responsable de ces risques.

turn off logging if you can, and you should. turn on pdq build your index. Hope this helps. ________________________________ From: "Guo, Denny" <DGuo@livingstonintl.com> To: ids@iiug.org Sent: Wednesday, February 4, 2009 11:22:32 AM Subject: rebuild index extreme slow with VER115UC3 [14733] Hi Gurus, I am still playing with our new IDS V115UC3 on AIX box. Our server is IBM,7026-6H1, it has 4 cpu, 4G memory. Normally it will only take 10 hours to rebuild all indexes with IDS Ver7.31.. After migrate to IDS V115UC3, we try to rebuild the index and it takes almost 24 hours and still not finish. Anybody has idea why and how to speed up the rebuilding. 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 électronique pourrait contenir des informations privilégiées et confidentielles. Si vous n'en êtes pas le récipiendaire prévu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le présent message. Si vous l'avez reçu par erreur, veuillez prévenir l'expéditeur par courriel, puis effacer ce message et en détruire toute copie. Le courrier électronique n'est pas garanti sécuritaire ni exempt d'erreurs. Les messages pourraient être interceptés, corrompus, égarés, retardés ou contaminés par des virus. L'expéditeur n'est pas responsable de ces risques. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

It could be the new functionality that automatically collects distributions for the first column of the index when you create an index that is slowing you down. Watch the session that is building the index, if you see a lone sqlexec thread running that appears to be reading the newly created index pages (via onstat -D) after what appears to be the intensive part of the index build has completed (lots of data page reads, psort and xchg threads running, temp dbspace writes, temp dbspace reads and index page writes) then you're updating distributions for the first column of your new index. We experienced this problem during our migration from 10.0 to 11.5. The "index build" part of the index build was much faster in 11.5 but this new functionality in 11.5 actually made the index build take longer. To my knowledge there is no way to disable this functionality. Which makes me sad because I'd rather have control over the update statistics so I can set PDQ, enable dirty reads, etc. Andrew

Thanks Kern,=0D=0A=0D=0AI make big mistake and forget to turn off logging= =2E=0D=0A=0D=0ADenny=0D=0A=0D=0A-----Original Message-----=0D=0AFrom: ids-b= ounces@iiug=2Eorg [mailto:ids-bounces@iiug=2Eorg] On Behalf Of Kern Doe=0D= =0ASent: Wednesday, February 04, 2009 11:31 AM=0D=0ATo: ids@iiug=2Eorg=0D= =0ASubject: Re: rebuild index extreme slow with VER115UC3 [14734]=0D=0A=0D= =0A=0D=0Aturn off logging if you can, and you should=2E =0D=0Aturn on pdq = =0D=0Abuild your index=2E =0D=0A=0D=0AHope this helps=2E =0D=0A=0D=0A______= __________________________ =0D=0AFrom: "Guo, Denny" <DGuo@livingstonintl=2E= com> =0D=0ATo: ids@iiug=2Eorg =0D=0ASent: Wednesday, February 4, 2009 11:22= :32 AM =0D=0ASubject: rebuild index extreme slow with VER115UC3 [14733] =0D= =0A=0D=0AHi Gurus, =0D=0A=0D=0AI am still playing with our new IDS V115UC3 = on AIX box=2E =0D=0AOur server is IBM,7026-6H1, it has 4 cpu, 4G memory=2E = =0D=0ANormally it will only take 10 hours to rebuild all indexes with IDS V= er7=2E31=2E=2E=2E =0D=0AAfter migrate to IDS V115UC3, we try to rebuild the= index and it takes almost =0D=0A24 hours and still not finish=2E =0D=0AAny= body has idea why and how to speed up the rebuilding=2E =0D=0A=0D=0AThanks,= =0D=0ADenny =0D=0A=0D=0A******************************************* =0D=0A= =0D=0AThe information contained in this e-mail message may =0D=0Acontain pr= ivileged and confidential information=2E =0D=0AIf you are not the intended = recipient, you are =0D=0Ahereby notified that any review, dissemination, = =0D=0Adistribution or duplication of this communication =0D=0Ais strictly p= rohibited=2E If you have received this =0D=0Amessage in error, please notif= y the sender by return =0D=0Ae-mail, delete this message and destroy any co= pies=2E =0D=0AInternet e- mail is not guaranteed to be secure or =0D=0Aerro= r-free=2E Messages could be intercepted, corrupted, =0D=0Alost, arrive late= or contain viruses=2E =0D=0AThe sender will not be liable for =0D=0Athese = risks=2E =0D=0A=0D=0A******************************************* =0D=0ACe m= essage =E9lectronique pourrait contenir des informations =0D=0Aprivil=E9gi= =E9es et confidentielles=2E Si vous n'en =EAtes pas le =0D=0Ar=E9cipiendair= e pr=E9vu, nous vous signalons qu'il est strictement =0D=0Ainterdit d'exami= ner, de diffuser, de distribuer et de reproduire le =0D=0Apr=E9sent message= =2E Si vous l'avez re=E7u par erreur, veuillez pr=E9venir =0D=0Al'exp=E9dit= eur par courriel, puis effacer ce message et en d=E9truire =0D=0Atoute copi= e=2E Le courrier =E9lectronique n'est pas garanti s=E9curitaire =0D=0Ani ex= empt d'erreurs=2E Les messages pourraient =EAtre intercept=E9s, =0D=0Acorro= mpus, =E9gar=E9s, retard=E9s ou contamin=E9s par des virus=2E =0D=0AL'exp= =E9diteur n'est pas responsable de ces risques=2E =0D=0A=0D=0A=0D=0A*******= ************************************************************************ = =0D=0AForum Note: Use "Reply" to post a response in the discussion forum=2E= =0D=0A=0D=0A=0D=0A********************************************************= *********************** =0D=0A Forum Note: Use "Reply" to post a response = in the discussion forum=2E =0D=0A=0D=0A************************************= *******=0D=0A=0D=0AThe information contained in this e-mail message may =0D= =0Acontain privileged and confidential information=2E =0D=0AIf you are not = the intended recipient, you are =0D=0Ahereby notified that any review, diss= emination, =0D=0Adistribution or duplication of this communication =0D=0Ais= strictly prohibited=2E If you have received this =0D=0Amessage in error, p= lease notify the sender by return =0D=0Ae-mail, delete this message and des= troy any copies=2E =0D=0AInternet e- mail is not guaranteed to be secure or= =0D=0Aerror-free=2E Messages could be intercepted, corrupted, =0D=0Alost, = arrive late or contain viruses=2E =0D=0AThe sender will not be liable for = =0D=0Athese risks=2E =0D=0A=0D=0A******************************************= * =0D=0ACe message =E9lectronique pourrait contenir des informations=0Apriv= il=E9gi=E9es et confidentielles=2E Si vous n'en =EAtes pas le=0Ar=E9cipiend= aire pr=E9vu, nous vous signalons qu'il est strictement=0Ainterdit d'examin= er, de diffuser, de distribuer et de reproduire le=0Apr=E9sent message=2E S= i vous l'avez re=E7u par erreur, veuillez pr=E9venir=0Al'exp=E9diteur par c= ourriel, puis effacer ce message et en d=E9truire=0Atoute copie=2E Le courr= ier =E9lectronique n'est pas garanti s=E9curitaire=0Ani exempt d'erreurs=2E= Les messages pourraient =EAtre intercept=E9s,=0Acorrompus, =E9gar=E9s, ret= ard=E9s ou contamin=E9s par des virus=2E=0AL'exp=E9diteur n'est pas respons= able de ces risques=2E

That is exactly right ... I also saw a significant increase in the time necessary to rebuild an index when first moving from v10 to v11 .... Maybe they will make that configurable down the road ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "ANDREW FORD" <aford@networkip.net> To: ids@iiug.org Date: 02/04/2009 11:46 AM Subject: Re: rebuild index extreme slow with VER115UC3 [14735] Sent by: ids-bounces@iiug.org It could be the new functionality that automatically collects distributions for the first column of the index when you create an index that is slowing you down. Watch the session that is building the index, if you see a lone sqlexec thread running that appears to be reading the newly created index pages (via onstat-D) after what appears to be the intensive part of the index build has completed (lots of data page reads, psort and xchg threads running, temp dbspace writes, temp dbspace reads and index page writes) then you're updating distributions for the first column of your new index. We experienced this problem during our migration from 10.0 to 11.5. The "index build" part of the index build was much faster in 11.5 but this new functionality in 11.5 actually made the index build take longer. To my knowledge there is no way to disable this functionality. Which makes me sad because I'd rather have control over the update statistics so I can set PDQ, enable dirty reads, etc. Andrew ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

There are two things. First version 11 automatically integrates an update stats high on the lead column of the index and then does an update stats low on the index. This means you save allot of time in the overall processing. The second issues is defect idsdb00095831 which is with the generating low statistics portion of the process. Getting the fix to this defect can speed up the create index by 20-60%. The calculation of the update stats low inadvertently does a second scan. The fix will completely remove the second scan and keep the entire process to one scan of the data.

Hi John,=0D=0A=0D=0AWhere we can get this fix? Or how can we verify we have= this fix?=0D=0A=0D=0AThanks,=0D=0ADenny=0D=0A=0D=0A-----Original Message--= ---=0D=0AFrom: ids-bounces@iiug=2Eorg [mailto:ids-bounces@iiug=2Eorg] On Be= half Of JOHN MILLER=0D=0ASent: Wednesday, February 04, 2009 12:28 PM=0D=0AT= o: ids@iiug=2Eorg=0D=0ASubject: Re: rebuild index extreme slow with VER115U= C3 [14739]=0D=0A=0D=0A=0D=0AThere are two things=2E =0D=0A=0D=0AFirst versi= on 11 automatically integrates an update stats high on the lead =0D=0Acolum= n of the index and then does an update stats low on the index=2E This means= =0D=0Ayou save allot of time in the overall processing=2E =0D=0A=0D=0AThe = second issues is defect idsdb00095831 which is with the generating low =0D= =0Astatistics portion of the process=2E Getting the fix to this defect can = speed up =0D=0Athe create index by 20-60%=2E The calculation of the update = stats low =0D=0Ainadvertently does a second scan=2E The fix will completely= remove the second =0D=0Ascan and keep the entire process to one scan of th= e data=2E =0D=0A=0D=0A=0D=0A***********************************************= ******************************** =0D=0A Forum Note: Use "Reply" to post a = response in the discussion forum=2E =0D=0A=0D=0A***************************= ****************=0D=0A=0D=0AThe information contained in this e-mail messag= e may =0D=0Acontain privileged and confidential information=2E =0D=0AIf you= are not the intended recipient, you are =0D=0Ahereby notified that any rev= iew, dissemination, =0D=0Adistribution or duplication of this communication= =0D=0Ais strictly prohibited=2E If you have received this =0D=0Amessage in= error, please notify the sender by return =0D=0Ae-mail, delete this messag= e and destroy any copies=2E =0D=0AInternet e- mail is not guaranteed to be = secure or =0D=0Aerror-free=2E Messages could be intercepted, corrupted, =0D= =0Alost, arrive late or contain viruses=2E =0D=0AThe sender will not be lia= ble for =0D=0Athese risks=2E =0D=0A=0D=0A**********************************= ********* =0D=0ACe message =E9lectronique pourrait contenir des information= s=0Aprivil=E9gi=E9es et confidentielles=2E Si vous n'en =EAtes pas le=0Ar= =E9cipiendaire pr=E9vu, nous vous signalons qu'il est strictement=0Ainterdi= t d'examiner, de diffuser, de distribuer et de reproduire le=0Apr=E9sent me= ssage=2E Si vous l'avez re=E7u par erreur, veuillez pr=E9venir=0Al'exp=E9di= teur par courriel, puis effacer ce message et en d=E9truire=0Atoute copie= =2E Le courrier =E9lectronique n'est pas garanti s=E9curitaire=0Ani exempt = d'erreurs=2E Les messages pourraient =EAtre intercept=E9s,=0Acorrompus, =E9= gar=E9s, retard=E9s ou contamin=E9s par des virus=2E=0AL'exp=E9diteur n'est= pas responsable de ces risques=2E

Do you know if the defect is fixed in FC3? Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "JOHN MILLER" <miller3@us.ibm.com> To: ids@iiug.org Date: 02/04/2009 12:28 PM Subject: Re: rebuild index extreme slow with VER115UC3 [14739] Sent by: ids-bounces@iiug.org There are two things. First version 11 automatically integrates an update stats high on the lead column of the index and then does an update stats low on the index. This means you save allot of time in the overall processing. The second issues is defect idsdb00095831 which is with the generating low statistics portion of the process. Getting the fix to this defect can speed up the create index by 20-60%. The calculation of the update stats low inadvertently does a second scan. The fix will completely remove the second scan and keep the entire process to one scan of the data. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

How was the 11.50 instance created (upgrade in place or unloaded and reloaded to a fresh instance)? Post both the 7.31 and 11.50 ONCONFIG files and the environment (output from 'export' please not 'env' - the sorting makes it easier). How do you rebuild the indexes (one at a time or in batches of <N> indexes)? Art On Wed, Feb 4, 2009 at 11:22 AM, Guo, Denny <DGuo@livingstonintl.com> wrote: > Hi Gurus, > > I am still playing with our new IDS V115UC3 on AIX box. > Our server is IBM,7026-6H1, it has 4 cpu, 4G memory. > Normally it will only take 10 hours to rebuild all indexes with IDS > Ver7.31.. > After migrate to IDS V115UC3, we try to rebuild the index and it takes > almost > 24 hours and still not finish. > Anybody has idea why and how to speed up the rebuilding. > > 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 électronique pourrait contenir des informations > privilégiées et confidentielles. Si vous n'en êtes pas le > récipiendaire prévu, nous vous signalons qu'il est strictement > interdit d'examiner, de diffuser, de distribuer et de reproduire le > présent message. Si vous l'avez reçu par erreur, veuillez prévenir > l'expéditeur par courriel, puis effacer ce message et en détruire > toute copie. Le courrier électronique n'est pas garanti sécuritaire > ni exempt d'erreurs. Les messages pourraient être interceptés, > corrompus, égarés, retardés ou contaminés par des virus. > L'expéditeur n'est pas responsable de ces risques. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. --0016360e3b3ce5d24904621c06bd

What release has that fix, John? Art On Wed, Feb 4, 2009 at 12:28 PM, JOHN MILLER <miller3@us.ibm.com> wrote: > There are two things. > > First version 11 automatically integrates an update stats high on the lead > column of the index and then does an update stats low on the index. This > means > you save allot of time in the overall processing. > > The second issues is defect idsdb00095831 which is with the generating low > statistics portion of the process. Getting the fix to this defect can speed > up > the create index by 20-60%. The calculation of the update stats low > inadvertently does a second scan. The fix will completely remove the second > scan and keep the entire process to one scan of the data. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. --001636832598f7f7f904621c14d7

The current plans for this fix are 11.50.xC3W3 and 11.50.xC4. My personal testing with this fix shows that create index still takes longer than in version 10, but the difference now is very very small (1-3%).

John, Is the xc3 patch available yet? The way you worded that I wasn't sure if it is currently available ... or upcoming ... Thanks ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "JOHN MILLER" <miller3@us.ibm.com> To: ids@iiug.org Date: 02/04/2009 03:24 PM Subject: Re: rebuild index extreme slow with VER115UC3 [14749] Sent by: ids-bounces@iiug.org The current plans for this fix are 11.50.xC3W3 and 11.50.xC4. My personal testing with this fix shows that create index still takes longer than in version 10, but the difference now is very very small (1-3%). ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Fix Central shows 11.50.xC3W1 as the latest available version. Pretty sure this means it will be a few months before 11.50.xC3W3 is released. Andrew