Query Performance on HDR instance
Recently we have faced a scenario, a query was running quite fast on primary instance but performing poorly on HDR instance. The execution plan on both instances were different as well. After running "update stats" on tables involved in query, the execution plan on HDR become same as on primary instance. Still the query performance was too low as compared to primary instance. Eventually we restarted the HDR instance. To our surprise now that query is running quite good on HDR too. We need your expert opinion about this behavior. Can we resolve such issue without restarting an instance? Regards, Anees Ahmad
Unfortunately you dind't find out what is the cause of the issue. Only that you have an issue. I find it a bit surprising that the query plan changed after running the statistics on the primary. Don't mean to doubt you, but is there any change you may have confused? Or do you still have the plan files.... if so, could you re-check? The reason why I'm mentioning this is because there are a couple of bugs that can cause the secondary servers to don't refresh their in memory cache (table structure and/or distribution values) when you change things at the primary (like UPDATE STATISTICS). You don't mention the version, but at least in 11.50.xC7 one of those is still present. Assuming you made the correct verifications and that the query plan effectively changed after running the stats on the primary, then I have no idea why it would be slower on the secondary server (apart the obvious things like the different hardware and/or configurations, and the fact that the primary may have more cached info). So, in order to understand that you'd need the problem to happen again, and collect some info... like what is the session doing (I/O?), is there any procedure involved?) In principle there is no reason why a query on the secondary should run slower, if it's using the same query plan, except the differences in configuration and/or hardware... But if the hardware/configurations were the cause, stoping the instance would not solve the problem... The bugs mentioned above can really be a big PITA... I usually get around them by "flooding" the cache (distributions most of the time), but depending on the configuration and system it can be hard. Specially if you changed the default parameters (DD_*, DS_*, PC_*) Regards. On Wed, Jun 13, 2012 at 3:00 PM, ANEES AHMAD <aanees@i2cinc.com> wrote: > Recently we have faced a scenario, a query was running quite fast on > primary > instance but performing poorly on HDR instance. The execution plan on both > instances were different as well. After running "update stats" on tables > involved in query, the execution plan on HDR become same as on primary > instance. Still the query performance was too low as compared to primary > instance. > > Eventually we restarted the HDR instance. To our surprise now that query is > running quite good on HDR too. > > We need your expert opinion about this behavior. Can we resolve such issue > without restarting an instance? > > Regards, > Anees Ahmad > > > > ******************************************************************************* > 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... --00248c6a6726842def04c25b635c
Unfortunately you dind't find out what is the cause of the issue. Only that you On Wed, Jun 13, 2012 at 3:00 PM, ANEES AHMAD <aanees@i2cinc.com> wrote: > Recently we have faced a scenario, a query was running quite fast on > primary > instance but performing poorly on HDR instance. The execution plan on both > instances were different as well. After running "update stats" on tables > involved in query, the execution plan on HDR become same as on primary > instance. Still the query performance was too low as compared to primary > instance. > > Eventually we restarted the HDR instance. To our surprise now that query is > running quite good on HDR too. > > We need your expert opinion about this behavior. Can we resolve such issue > without restarting an instance? > > Regards, > Anees Ahmad > > > > ******************************************************************************* > 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... --20cf300fab41a9043904c25b6436
We are using 11.50.FC8W3 and software configuration are same on HDR whereas as hardware is better on secondary side. We have recheck the sql explain files and facts are same as described earlier. Also, we are sure that we didn't make any changes on both sides except update stats on primary and restarting the secondary instance. Can you refer us the bug id related to this scenario ? -- Regards, Anees Ahmad
http://www-304.ibm.com/support/docview.wss?uid=swg1IC73133 But since the query plan chenaged, I don't think this APAR explains your situation. The problem referred by this one means you will not be able to get the correct query plan on the secondary. So, in your case I think you have to gather more information if the situation repeats itself. And if the time permits it, let the query ran on both engines anc capture the runtime statistics (activated by the SET EXPLAIN). Regards. On Wed, Jun 13, 2012 at 4:14 PM, ANEES AHMAD <aanees@i2cinc.com> wrote: > We are using 11.50.FC8W3 and software configuration are same on HDR > whereas as > hardware is better on secondary side. We have recheck the sql explain files > and facts are same as described earlier. Also, we are sure that we didn't > make > any changes on both sides except update stats on primary and restarting the > secondary instance. > > Can you refer us the bug id related to this scenario ? > > -- > Regards, > Anees Ahmad > > > > ******************************************************************************* > 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... --00248c7118059ee59004c26dfa37