query execution plan
Hi I have captured explain plan for an SQL query in 2 servers having IDS 7.31. Problem is Im getting different plans for the same query in different servers havin same table structure and indexes. Only difference is number of records. I hope this shouldnt be the cause for the different query execution plans. I have also dropped statistics and run them again in both servers but still im facing the same issue. what could be the problem? Pls Suggest
There may not be a problem at all. The optimizer uses the number of records involved to develop the query plan. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of S SURESH Sent: Saturday, May 05, 2007 8:12 AM To: ids@iiug.org Subject: query execution plan [9074] Hi I have captured explain plan for an SQL query in 2 servers having IDS 7.31. Problem is Im getting different plans for the same query in different servers havin same table structure and indexes. Only difference is number of records. I hope this shouldnt be the cause for the different query execution plans. I have also dropped statistics and run them again in both servers but still im facing the same issue. what could be the problem? Pls Suggest **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Which fixpack of 7.31? Several of the older (much older) fixpacks had bugs that could cause the optimizer to choose the incorrect instance. When that occurred, rebuilding the indexes sometimes got the optimizer to select the 'right' plan. Are you seeing a problem with performance with one plan versus the plan on the other server? Christine On May 5, 2007, at 7:49 AM, Jack Parker wrote: > There may not be a problem at all. The optimizer uses the number of > records > involved to develop the query plan. > > j. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of S > SURESH > Sent: Saturday, May 05, 2007 8:12 AM > To: ids@iiug.org > Subject: query execution plan [9074] > > Hi > > I have captured explain plan for an SQL query in 2 servers having > IDS 7.31. > Problem is Im getting different plans for the same query in different > servers > havin same table structure and indexes. > Only difference is number of records. > > I hope this shouldnt be the cause for the different query execution > plans. > > I have also dropped statistics and run them again in both servers > but still > im > facing the same issue. > > what could be the problem? Pls Suggest > > ********************************************************************** > ****** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ********************************************************************** > ********* > Forum Note: Use "Reply" to post a response in the discussion forum. >
As Jack Parker mentioned earlier, there may not be any problem, optimizer just choosing query path based on number of rows. If you are seeing performance problem with one plan versus other, may consider using optimizer directive to enforce the correct path. -Sanjit Chakraborty
Yes, this is possible because the query plan also depends on the data stored in the tables. If the distribution of this data is very different you can have different query plans. Even if you have the same indexes. Regards, Victor ----- Mensaje original ---- De: S SURESH <suresh.sambana@ril.com> Para: ids@iiug.org Enviado: sábado 5 de mayo de 2007, 9:11:49 Asunto: query execution plan [9074] Hi I have captured explain plan for an SQL query in 2 servers having IDS 7.31. Problem is Im getting different plans for the same query in different servers havin same table structure and indexes. Only difference is number of records. I hope this shouldnt be the cause for the different query execution plans. I have also dropped statistics and run them again in both servers but still im facing the same issue. what could be the problem? Pls Suggest ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. __________________________________________________ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas