Possible optimizer problem?

Hi We are currently busy with a 4-5 TB "like for like" Informix IDS10.00 FC8 migration from Tru64 to AIX5.3 We are constrained to producing a migrated system that is as close as possible to their old system, warts and all. No code is expected to be changed unless as an absolute last resort. Thus changing anything in terms of configuration (onconfig etc.) is not desirable, except if forced by the new platform. For example IDS 10 on AIX does not support 2K pagesizes, so are pretty much forced into this config change (4K pagesize). Currently, we've noticed that a certain query produced an particular explain plan on Tru64 and a different explain plan on AIX (Tru64 produces HASH and AIX produces NL - for the exact same onconfig and update stats distributions) The structure and data of the tables on both systems are exactly the same. We have used the same stat collection methods on both sides, even the configuration of the onconfig is identical (Other than fields such as DBSERVERNAME and ROOTPATH). The problem is that this SQL (inefficient as we understand it to be) runs for 6 hours (yeah!) on Tru64 and 3 days on AIX. This performance difference is unacceptable to the customer, who is not interested in changing it. We found by adding a directive to the SQL running on AIX, the runtime was just over an hour. Clearly the problem is the plan coming out NL on AIX that is producing a poor runtime. Could someone enlighten me with other possible influences that could cause the optimizer to make a different plan? We have logged this problem with IBM support, but I'm also looking for other input as well. Thanks Regards Tien Cheng

Tien Cheng WROTE: ----------------------------------------------------------------------------- Hi We are currently busy with a 4-5 TB "like for like" Informix IDS10.00 FC8 migration from Tru64 to AIX5.3 We are constrained to producing a migrated system that is as close as possible to their old system, warts and all. No code is expected to be changed unless as an absolute last resort. Thus changing anything in terms of configuration (onconfig etc.) is not desirable, except if forced by the new platform. For example IDS 10 on AIX does not support 2K pagesizes, so are pretty much forced into this config change (4K pagesize). Currently, we've noticed that a certain query produced an particular explain plan on Tru64 and a different explain plan on AIX (Tru64 produces HASH and AIX produces NL - for the exact same onconfig and update stats distributions) The structure and data of the tables on both systems are exactly the same. We have used the same stat collection methods on both sides, even the configuration of the onconfig is identical (Other than fields such as DBSERVERNAME and ROOTPATH). The problem is that this SQL (inefficient as we understand it to be) runs for 6 hours (yeah!) on Tru64 and 3 days on AIX. This performance difference is unacceptable to the customer, who is not interested in changing it. We found by adding a directive to the SQL running on AIX, the runtime was just over an hour. Clearly the problem is the plan coming out NL on AIX that is producing a poor runtime. Could someone enlighten me with other possible influences that could cause the optimizer to make a different plan? We have logged this problem with IBM support, but I'm also looking for other input as well. Thanks Regards Tien Cheng ----------------------------------------------------------------------------- Tien, Check the OPTCOMPIND value if it is set to 0 try switching it to 2. I've ran into the same problem you describe going between versions. The optimizer on one version appeared to view the parameter as a weighted factor but would sometimes still choose a hash join over a nested loop. After we upgraded, the optimizer appeared to view the parameter as law and forced nested loop joins where hash joins had been previously produced. It is at least something to review. Good Luck, Dave Griffen

Is OPTCOMPIND set the same on both platforms? That controls whether the optimizer prefers NL or Hash joins. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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, 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, Jun 9, 2010 at 9:08 AM, TIEN-MING CHENG <tien.cheng@reagola.com>wrote: > Hi > > We are currently busy with a 4-5 TB "like for like" Informix IDS10.00 FC8 > migration from Tru64 to AIX5.3 We are constrained to producing a migrated > system that is as close as possible to their old system, warts and all. No > code is expected to be changed unless as an absolute last resort. Thus > changing anything in terms of configuration (onconfig etc.) is not > desirable, > except if forced by the new platform. For example IDS 10 on AIX does not > support 2K pagesizes, so are pretty much forced into this config change (4K > pagesize). > > Currently, we've noticed that a certain query produced an particular > explain > plan on Tru64 and a different explain plan on AIX (Tru64 produces HASH and > AIX > produces NL - for the exact same onconfig and update stats distributions) > > The structure and data of the tables on both systems are exactly the same. > We > have used the same stat collection methods on both sides, even the > configuration of the onconfig is identical (Other than fields such as > DBSERVERNAME and ROOTPATH).> > The problem is that this SQL (inefficient as we understand it to be) runs > for > 6 hours (yeah!) on Tru64 and 3 days on AIX. This performance difference is > unacceptable to the customer, who is not interested in changing it. We > found > by adding a directive to the SQL running on AIX, the runtime was just over > an > hour. Clearly the problem is the plan coming out NL on AIX that is > producing a > poor runtime. > > Could someone enlighten me with other possible influences that could cause > the > optimizer to make a different plan? We have logged this problem with IBM > support, but I'm also looking for other input as well. Thanks > > Regards > Tien Cheng > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd3439c2a8bd404889a6283

If I understand correctly you're moving a version 10 instance from Tru64 to AIX. You probably know this, but IDS 10 will be out of support in September this year. Going through a migration process that will lead to an unsupported environment in 3 months doesn't seem to be a good idea. But it's up to your customer.... Regarding the situation... The server environment is the same. And the client environment? OPTCOMPIND was referenced by other posters, but it can also be defined on the client side... The optimizer is a very complex piece of software... I suppose even the page size could interfere with it, although I don't remember having seen that happen. Please check the histograms with dbschema -hd. Also check if the indexes are equal (attached/detached) in both servers. As a last resort try to investigate the EXTERNAL DIRECTIVES functionality.It could work for you. It's a way to supply directives without changing the code. If this is your only problem/query maybe this is a reaonable workaround. Regards. On Wed, Jun 9, 2010 at 2:08 PM, TIEN-MING CHENG <tien.cheng@reagola.com>wrote: > Hi > > We are currently busy with a 4-5 TB "like for like" Informix IDS10.00 FC8 > migration from Tru64 to AIX5.3 We are constrained to producing a migrated > system that is as close as possible to their old system, warts and all. No > code is expected to be changed unless as an absolute last resort. Thus > changing anything in terms of configuration (onconfig etc.) is not > desirable, > except if forced by the new platform. For example IDS 10 on AIX does not > support 2K pagesizes, so are pretty much forced into this config change (4K > pagesize). > > Currently, we've noticed that a certain query produced an particular > explain > plan on Tru64 and a different explain plan on AIX (Tru64 produces HASH and > AIX > produces NL - for the exact same onconfig and update stats distributions) > > The structure and data of the tables on both systems are exactly the same. > We > have used the same stat collection methods on both sides, even the > configuration of the onconfig is identical (Other than fields such as > DBSERVERNAME and ROOTPATH).> > The problem is that this SQL (inefficient as we understand it to be) runs > for > 6 hours (yeah!) on Tru64 and 3 days on AIX. This performance difference is > unacceptable to the customer, who is not interested in changing it. We > found > by adding a directive to the SQL running on AIX, the runtime was just over > an > hour. Clearly the problem is the plan coming out NL on AIX that is > producing a > poor runtime. > > Could someone enlighten me with other possible influences that could cause > the > optimizer to make a different plan? We have logged this problem with IBM > support, but I'm also looking for other input as well. Thanks > > Regards > Tien Cheng > > > > ******************************************************************************* > 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... --0015174487c0e9913704889b8a74

The change in pagesize will have a large impact on the optimizer. Just to highlight a few items: 1. Sequential scans is I/O (or page) based. A scan of the same size data will take half the I/O operations. And the optimizer is well aware of this fact. 2. cluster, a value the optimizer uses heavily. More rows can fit on a single page so this can be increased on the AIX box. 3. Index page size, more items can fit on a single page John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 06/09/2010 09:39:04 AM: > [image removed] > > Re: Possible optimizer problem? [20349] > > Fernando Nunes > > to: > > ids > > 06/09/2010 09:40 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > If I understand correctly you're moving a version 10 instance from Tru64 to > AIX. > You probably know this, but IDS 10 will be out of support in September this > year. Going through a migration process that will lead to an unsupported > environment in 3 months doesn't seem to be a good idea. But it's up to your > customer.... > > Regarding the situation... The server environment is the same. And the > client environment? > OPTCOMPIND was referenced by other posters, but it can also be defined on > the client side... > > The optimizer is a very complex piece of software... I suppose even the page > size could interfere with it, although I don't remember having seen that > happen. > Please check the histograms with dbschema -hd. Also check if the indexes are > equal (attached/detached) in both servers. > > As a last resort try to investigate the EXTERNAL DIRECTIVES functionality.It > could work for you. It's a way to supply directives without changing the > code. If this is your only problem/query maybe this is a reaonable > workaround. > > Regards. > > On Wed, Jun 9, 2010 at 2:08 PM, TIEN-MING CHENG > <tien.cheng@reagola.com>wrote: > > > Hi > > > > We are currently busy with a 4-5 TB "like for like" Informix IDS10.00 FC8 > > migration from Tru64 to AIX5.3 We are constrained to producing a migrated > > system that is as close as possible to their old system, warts and all. No > > code is expected to be changed unless as an absolute last resort. Thus > > changing anything in terms of configuration (onconfig etc.) is not > > desirable, > > except if forced by the new platform. For example IDS 10 on AIX does not > > support 2K pagesizes, so are pretty much forced into this config change (4K > > pagesize). > > > > Currently, we've noticed that a certain query produced an particular > > explain > > plan on Tru64 and a different explain plan on AIX (Tru64 produces HASH and > > AIX > > produces NL - for the exact same onconfig and update stats distributions) > > > > The structure and data of the tables on both systems are exactly the same. > > We > > have used the same stat collection methods on both sides, even the > > configuration of the onconfig is identical (Other than fields such as > > DBSERVERNAME and ROOTPATH).> > > > The problem is that this SQL (inefficient as we understand it to be) runs > > for > > 6 hours (yeah!) on Tru64 and 3 days on AIX. This performance difference is > > unacceptable to the customer, who is not interested in changing it. We > > found > > by adding a directive to the SQL running on AIX, the runtime was just over > > an > > hour. Clearly the problem is the plan coming out NL on AIX that is > > producing a > > poor runtime. > > > > Could someone enlighten me with other possible influences that could cause > > the > > optimizer to make a different plan? We have logged this problem with IBM > > support, but I'm also looking for other input as well. Thanks > > > > Regards > > Tien Cheng > > > > > > > > > ******************************************************************************* > > 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... > > --0015174487c0e9913704889b8a74 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi all, Many thanks for the inputs above, apologies for the late reply as I'm in South Africa (GMT +2:00) currently. Art and David: The OPTCOMPIND is set to 0 on both servers, and the client parameters are the same on both sides. However, the explain plan on Tru64 with a 2k page size still produces a HASH join. We have logged a PMR at IBM, from the responses we are getting from support, it seems like they are not too sure why this is happening either... Fernando: I do understand that IDS 10 will be out of support in September and have highlighted this to the client. Unfortunately it was out of the original project scope, a further project will be launched to bring them to 11.5 after this migration. The table and indexes are built in an identical fashion to the current system, the only difference is the data layout on the SAN. I'm busy investigating the histograms now, expecting very minor differences. I have also investigated external directives after you mentioned them, but unfortunately they are unsuited for this situation due to the design of this system. John: Very valid points regarding how the pagesize, clustering and index pagesize can effect the decisions taken by the optimizer. Strangly, this has only happened to one of the environments that I am migrating. But I will investigate into this further. Guys, thanks again for all the help and suggestions Regards Tien Cheng

I'm sorry to insist. But can you explain why the external directives won't allow you to workaround that specific query? They're configured on the server side... And you already mentioned that it can be solved by using a (internal) directive. Don't get me wrong... I hate using directives to solve problems (I only recall doing it as a temporary workaround), but in some cases... Thanks! On Thu, Jun 10, 2010 at 9:51 AM, TIEN-MING CHENG <tien.cheng@reagola.com>wrote: > Hi all, > > Many thanks for the inputs above, apologies for the late reply as I'm in > South > Africa (GMT +2:00) currently. > > Art and David: > The OPTCOMPIND is set to 0 on both servers, and the client parameters are > the > same on both sides. However, the explain plan on Tru64 with a 2k page size > still produces a HASH join. We have logged a PMR at IBM, from the responses > we > are getting from support, it seems like they are not too sure why this is > happening either... > > Fernando: > I do understand that IDS 10 will be out of support in September and have > highlighted this to the client. Unfortunately it was out of the original > project scope, a further project will be launched to bring them to 11.5 > after > this migration. > > The table and indexes are built in an identical fashion to the current > system, > the only difference is the data layout on the SAN. I'm busy investigating > the > histograms now, expecting very minor differences. > > I have also investigated external directives after you mentioned them, but > unfortunately they are unsuited for this situation due to the design of > this > system. > > John: > Very valid points regarding how the pagesize, clustering and index pagesize > can effect the decisions taken by the optimizer. Strangly, this has only > happened to one of the environments that I am migrating. But I will > investigate into this further. > > Guys, thanks again for all the help and suggestions > > Regards > Tien Cheng > > > > ******************************************************************************* > 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... --0016e65c7dfe5325870488b3f1b4

Hi Fernando Thanks for the reply, I will try explain this problem as best I can... The problem is that this query is not just run on one specific table. Currently this system works in billing cycles for their different clients. Certain clients are billed on certain days of the month. For each billing cycle within a month, they build multiple tables with the same schema to bill different clients within that cycle. The only differences are the name of the table. This causes hundreds of tables with the same schema to be created per month. They are all individually populated afterwards throughout the month. This query is run for each of the tables for cleanup before the end of a billing cycle. It is run against all tables of that particular cycle and cleans up some billing data. They change the table names in the query manually, and run them all manually.... As I understand it (Please correct me if I am wrong) each external directive only apply to one query string. So in this situation there would be hundreds of external directives created for each table that is created with that schema. As I understand that all queries have to be checked for external directives, this could cause a degradation in terms of performance. On top of this, it has been explained that we are not to change the system in anyway (even for performance benefits). I have offered to rewrite the query for them, however that was turned down as well. I'm not sure if I have explained the problem very clearly... But I'm abit hesitant to use external directives for this particular problem. Any ideas on the optimiser problem though? :D Regards Tien Cheng