Clarification on SQL performance tuning....

Posted in 2003

Topics: Performance & Tuning, SQL Development & Query Writing, Server Administration, Platform-Specific Issues, Versions, Editions & End-of-Life

Rajasekaran, Rajesh — Mon, 15 Sep 2003 11:55:56 -0500 — source: Usenet: comp.databases.informix

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_000_01C37BAA.3B7FBDB0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C37BAA.3B7FBDB0" ------_=_NextPart_001_01C37BAA.3B7FBDB0 Content-Type: text/plain; charset="iso-8859-1" Hello all, I would like to discuss a SQL performance issue here and i am hoping to get some suggestions/tips here. We have SAP R/3 46c running on IDS 7.31UD2XG on Solaris 9 . Here is the issue. The query should extract some records based on the date filter from "mkpf" table and joins those records to "mseg" table where the material records to be picked.Also, it has to join with "mcha" to pick some more columns for the resultant set. There were some couple of other tables(small in size) which i didnt include here was a part of the original SQL to pick some more information. When i break it up the SQL by introducing tables and join conditions for those tables one by one, i found out that the delay happened only when i introduce the "MCHA" table and its related joins. Hence my SQL & its query plans pasted here involved only those tables and joins. The whole query takes 30-40 mins to get the results. Please find the attached table info for 3 specific tables and the SQL query optimizer plan. Questions -------------- 1) Does the query path chosen here get executed in the same sequence as it shows in the SQL plan ? -- I mean does the optimiser sequence in terms of applying join and filters as it shows on the query plan like first on "marm",then on "mcha", then on "mseg", then on "makt",then on "afpo",then on "mkpf". 2) Ideally i feel based on the table data & considering the type of application data stored, size etc. , the query can be better off by choosing the route "mkpf", "mseg","mcha" order to get the desired records. This can happen if the optimizer chooses hash join i guess since the MSEG and MKPF are bigger tables of size. I have seen before sometimes the "estimation cost shows high figure" and the query results come in quite a good time but not for this case though. 3) I have the update statistics executed upto date for these tables. at 0% from sapdba tool with default suggested method. Do you think the optimiser behaving wrongly here ? Our OPTCOMPIND is supposed to be 0 for our SAP R/3 environment. Hence the optimizer prefers nested loop join by default. 4) Do you think this SQL can be re-framed in any order to get better results ? 5) Also on MKPF table, there is another index with "mandt,budat,mblnr". Ideally the date search should have used this index. But i think bcos of the join condition involved between MKPF & MSEG on the sql, the optimiser always choose the unique index (mandt,mblnr,mjahr) and apply the date filters on that index. Is there any way to change that behaviour ? Iam right now testing the SQL with optimiser hints like forcing a specific index,hash join etc. Any suggestions/comments are greatly appreciated. Thanks Rajesh Rajasekaran Informix Database Administrator Forest Pharmaceuticals Inc. (314) 493-7073 rrajasekaran@forestpharm.com ------_=_NextPart_001_01C37BAA.3B7FBDB0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2653.12"> <TITLE>Clarification on SQL performance tuning....</TITLE> </HEAD> <BODY> Hello all,   I would like to discuss a SQL performance = issue here and i am hoping to get some suggestions/tips here. We have = SAP R/3 46c running on  IDS 7.31UD2XG on Solaris 9 . Here is the = issue.  The query should extract some records based on = the date filter from "mkpf" table and joins those records to = "mseg" table where the material records to be picked.Also, it = has to join with "mcha" to pick some more columns for the = resultant set. There were some couple of other tables(small in = size)  which i didnt include here was a part of the original SQL = to pick some more information. When i break it up the SQL by = introducing tables and join conditions for those tables one by one, i = found out that the delay happened only when i introduce the = "MCHA" table and its related joins. Hence my SQL & its = query plans pasted here involved only those tables and = joins. The whole query takes 30-40 mins to get the results. = Please find the attached table info for 3 specific tables and the SQL = query optimizer plan. Questions -------------- 1) Does the query path chosen here get executed in = the same sequence as it shows in the SQL plan ?     -- I mean does the optimiser = sequence in terms of  applying join and filters as it shows on the = query plan like first on "marm",then on "mcha", = then on "mseg", then on "makt",then on = "afpo",then on "mkpf".  2) Ideally i feel based on the table data & = considering the type of application data stored, size etc. , the query = can be better off by choosing the route  "mkpf", = "mseg","mcha" order to get the desired records. = This can happen if the optimizer chooses hash join i guess since the = MSEG and MKPF are bigger tables of size. I have seen before sometimes = the "estimation cost shows high figure" and the query results = come in quite a good time but not for this case though. 3) I have the update statistics executed upto date = for these tables. at 0% from sapdba tool with default suggested method. = Do  you think the optimiser behaving wrongly here ?     Our OPTCOMPIND is supposed to be 0 = for our SAP R/3 environment. Hence the optimizer prefers nested loop = join by default. 4)  Do you think this SQL can be re-framed in = any order to get better results ?    5)  Also on MKPF table, there is another index = with "mandt,budat,mblnr". Ideally the date search should have = used this index. But i think bcos of the join condition involved = between MKPF & MSEG on the sql, the optimiser always choose the = unique index (mandt,mblnr,mjahr) and apply the date filters on that = index. Is there any way to change that behaviour ? Iam right now =@

↪ replying to Rajasekaran, Rajesh

Gorazd Hribar Rajteri�� — Tue, 16 Sep 2003 09:13:54 +0200 — source: Usenet: comp.databases.informix

You can try using optimizer hints. Detailed description is available at http://www.klimaexpert.com/gorazd/informix/index.html check chapter 3. Gorazd "Rajasekaran, Rajesh" <RRajasekaran@forestpharm.com> wrote in message news:bk4rua$rnb$1@terabinaries.xmission.com... > > Hello all, > > I would like to discuss a SQL performance issue here and i am hoping to > get some suggestions/tips here. We have SAP R/3 46c running on IDS > 7.31UD2XG on Solaris 9 . Here is the issue. > > The query should extract some records based on the date filter from "mkpf" > table and joins those records to "mseg" table where the material records to > be picked.Also, it has to join with "mcha" to pick some more columns for the > resultant set. There were some couple of other tables(small in size) which > i didnt include here was a part of the original SQL to pick some more > information. When i break it up the SQL by introducing tables and join > conditions for those tables one by one, i found out that the delay happened > only when i introduce the "MCHA" table and its related joins. Hence my SQL & > its query plans pasted here involved only those tables and joins. > > The whole query takes 30-40 mins to get the results. Please find the > attached table info for 3 specific tables and the SQL query optimizer plan. > > Questions > -------------- > > 1) Does the query path chosen here get executed in the same sequence as it > shows in the SQL plan ? > -- I mean does the optimiser sequence in terms of applying join and > filters as it shows on the query plan like first on "marm",then on "mcha", > then on "mseg", then on "makt",then on "afpo",then on "mkpf". > > 2) Ideally i feel based on the table data & considering the type of > application data stored, size etc. , the query can be better off by choosing > the route "mkpf", "mseg","mcha" order to get the desired records. This can > happen if the optimizer chooses hash join i guess since the MSEG and MKPF > are bigger tables of size. I have seen before sometimes the "estimation cost > shows high figure" and the query results come in quite a good time but not > for this case though. > > 3) I have the update statistics executed upto date for these tables. at 0% > from sapdba tool with default suggested method. Do you think the optimiser > behaving wrongly here ? > Our OPTCOMPIND is supposed to be 0 for our SAP R/3 environment. Hence > the optimizer prefers nested loop join by default. > > 4) Do you think this SQL can be re-framed in any order to get better > results ? > > 5) Also on MKPF table, there is another index with "mandt,budat,mblnr". > Ideally the date search should have used this index. But i think bcos of the > join condition involved between MKPF & MSEG on the sql, the optimiser always > choose the unique index (mandt,mblnr,mjahr) and apply the date filters on > that index. Is there any way to change that behaviour ? Iam right now > testing the SQL with optimiser hints like forcing a specific index,hash join > etc. > > Any suggestions/comments are greatly appreciated. > > Thanks > Rajesh Rajasekaran > Informix Database Administrator > Forest Pharmaceuticals Inc. > (314) 493-7073 > rrajasekaran@forestpharm.com > > > > > ------_=_NextPart_001_01C37BAA.3B7FBDB0 > Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> > <HTML> > <HEAD> > <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = > charset=3Diso-8859-1"> > <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = > 5.5.2653.12"> > <TITLE>Clarification on SQL performance tuning....</TITLE> > </HEAD> > <BODY> > > Hello all, > > >   I would like to discuss a SQL performance = > issue here and i am hoping to get some suggestions/tips here. We have = > SAP R/3 46c running on  IDS 7.31UD2XG on Solaris 9 . Here is the = > issue. > >  The query should extract some records based on = > the date filter from "mkpf" table and joins those records to = > "mseg" table where the material records to be picked.Also, it = > has to join with "mcha" to pick some more columns for the = > resultant set. There were some couple of other tables(small in = > size)  which i didnt include here was a part of the original SQL = > to pick some more information. When i break it up the SQL by = > introducing tables and join conditions for those tables one by one, i = > found out that the delay happened only when i introduce the = > "MCHA" table and its related joins. Hence my SQL & its = > query plans pasted here involved only those tables and = > joins. > > The whole query takes 30-40 mins to get the results. = > Please find the attached table info for 3 specific tables and the SQL = > query optimizer plan. > > Questions > -------------- > > > 1) Does the query path chosen here get executed in = > the same sequence as it shows in the SQL plan ? >     -- I mean does the optimiser = > sequence in terms of  applying join and filters as it shows on the = > query plan like first on "marm",then on "mcha", = > then on "mseg", then on "makt",then on = > "afpo",then on "mkpf".  > > 2) Ideally i feel based on the table data & = > considering the type of application data stored, size etc. , the query = > can be better off by choosing the route  "mkpf", = > "mseg","mcha" order to get the desired records. = > This can happen if the optimizer chooses hash join i guess since the = > MSEG and MKPF are bigger tables of size. I have seen before sometimes = > the "estimation cost shows high figure" and the query results = > come in quite a good time but not for this case though. > > 3) I have the update statistics executed upto date = > for these tables. at 0% from sapdba tool with default suggested method. = > Do  you think the optimiser behaving wrongly here ? > >     Our OPTCOMPIND is supposed to be 0 = > for our SAP R/3 environment. Hence the optimizer prefers nested loop = > join by default. > > 4)  Do you think this SQL can be re-framed in = > any order to get better results ? >    > 5)  Also on MKPF table, there is another index = > with "mandt,budat,mblnr". Ideally the date search should have = > used this index. But i think bcos of the join condition

Clarification on SQL performance tuning....

Related threads

Glossary

Your privacy choices