Clarification on SQL performance tuning....

Rajasekaran, Rajesh · Rajasekaran, Rajesh

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
[email]rrajasekaran@forestpharm.com[/email]

------_=_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 =
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

 

 

</BODY>
</HTML>
------_=_NextPart_001_01C37BAA.3B7FBDB0--

------_=_NextPart_000_01C37BAA.3B7FBDB0
Content-Type: application/octet-stream;
name="sqexplain.out"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="sqexplain.out"

------------------------------------------------------------------------=
--------
Table Information
------------------------------------------------------------------------=
--------
table name: mcha

dbspace: psapstab rows: 64018
fragmentation: none columns: 34
row size: 272 bytes
locking mode: row
first extent: 22288 KB views: 27
next extent: 128 KB indexes: 2
size: 23056 KB
extents: 7 (218 still available) fill level: 100 %
------------------------------------------------------------------------=
--------
Table Information
------------------------------------------------------------------------=
--------
table name: mseg

dbspace: psapbtab rows: 6299684
fragmentation: none columns: 170
row size: 1089 =
bytes
locking mode: row
first extent: 11649528 KB views: 6
next extent: 400000 KB indexes: 4
size: 13294360 KB
extents: 18 (198 still available) fill level: 97 %
------------------------------------------------------------------------=
--------
Table Information
------------------------------------------------------------------------=
--------
table name: mkpf

dbspace: psapbtab rows: 1918113
fragmentation: none columns: 24
row size: 196 bytes
locking mode: row
first extent: 16 KB views: 8
next extent: 20480 KB indexes: 2
size: 519184 KB
extents: 13 (212 still available) fill level: 99 %

QUERY:
------
SELECT T_01.matnr i_matnr,T_02.matnr p_matnr,T_02.wemng =
p_wemn,T_03.maktx,
T_04.umrez,T_04.umren,T_05.licha
FROM mkpf T_00, mseg T_01 , afpo T_02 , makt T_03 , marm T_04 , mcha =
T_05
WHERE T_00.mandt =3D '040' AND T_00.budat BETWEEN '20030101' and =
'20030131'
AND (T_01.mandt =3D '040' AND T_01.mblnr =3D T_00.mblnr
AND T_01 .mjahr =3D T_00.mjahr )
AND T_01.matnr IN ( '000000000000003608','000000000000003659')
AND T_01.werks IN ( 'LAC1','INW1','ERC1','COM1','CIN1')
AND T_01.bwart BETWEEN '261' AND '262'
AND (T_02 .mandt =3D '040' AND T_02.aufnr =3D T_01.aufnr)
AND (T_03.mandt =3D '040' AND T_03.matnr =3D T_01.matnr
AND T_03.spras =3D 'E') AND ( T_04.mandt =3D '040'
AND T_04.matnr =3D T_01.matnr AND T_04.meinh =3D 'KG')
AND (T_05.mandt =3D '040' and T_05.matnr =3D T_01 . matnr
AND T_05.werks =3D T_01.werks AND T_05 . charg =3D T_01 . charg )

Estimated Cost: 439
Estimated # of Rows Returned: 1

1) informix.t_04: INDEX PATH

(1) Index Keys: mandt matnr meinh=20
Lower Index Filter: (informix.t_04.mandt =3D '040' AND =
(informix.t_04.meinh =3D 'KG' AND informix.t_04.matnr =3D =
'000000000000003608' ) )=20

(2) Index Keys: mandt matnr meinh=20
Lower Index Filter: (informix.t_04.mandt =3D '040' AND =
(informix.t_04.meinh =3D 'KG' AND informix.t_04.matnr =3D =
'000000000000003659' ) )=20

2) informix.t_05: INDEX PATH

(1) Index Keys: mandt matnr werks charg (Key-First)
Lower Index Filter: (informix.t_05.mandt =3D '040' AND =
informix.t_05.matnr =3D informix.t_04.matnr )=20
Key-First Filters: (informix.t_05.werks IN ('LAC1' , 'INW1' , =
'ERC1' , 'COM1' , 'CIN1' ))
NESTED LOOP JOIN

3) informix.t_01: INDEX PATH

Filters: informix.t_01.charg =3D informix.t_05.charg=20

(1) Index Keys: mandt matnr werks lgort bwart sobkz (Key-First)
Lower Index Filter: (informix.t_01.mandt =3D '040' AND =
(informix.t_01.werks =3D informix.t_05.werks AND informix.t_01.matnr =
=3D informix.t_04.matnr ) )=20
Key-First Filters: (informix.t_01.bwart <=3D '262' ) AND
(informix.t_01.bwart >=3D '261' )
NESTED LOOP JOIN

4) informix.t_03: INDEX PATH

(1) Index Keys: mandt matnr spras=20
Lower Index Filter: (informix.t_03.mandt =3D '040' AND =
(informix.t_03.spras =3D 'E' AND informix.t_03.matnr =3D =
informix.t_04.matnr ) )=20
NESTED LOOP JOIN

5) informix.t_02: INDEX PATH

(1) Index Keys: mandt aufnr posnr=20
Lower Index Filter: (informix.t_02.mandt =3D '040' AND =
informix.t_02.aufnr =3D informix.t_01.aufnr )=20
NESTED LOOP JOIN

6) informix.t_00: INDEX PATH

Filters: (informix.t_00.budat >=3D '20030101' AND =
informix.t_00.budat <=3D '20030131' )=20

(1) Index Keys: mandt mblnr mjahr=20
Lower Index Filter: (informix.t_00.mandt =3D '040' AND =
(informix.t_00.mblnr =3D informix.t_01.mblnr AND informix.t_00.mjahr =
=3D informix.t_01.mjahr ) )=20
NESTED LOOP JOIN
------_=_NextPart_000_01C37BAA.3B7FBDB0--
sending to informix-list

Rajib Sarkar · Rajib Sarkar

See the suggestions below ...

Thanx much,

Rajib Sarkar
Advisory Software Engineer (RAS)
IBM Data Management Group
Ph : (602)-217-2100
Fax: (602)-217-2100
T/L : 667-2100

As long as you derive inner help and comfort from anything, keep it --
Mahatma Gandhi

"Rajasekaran,
Rajesh" To: "'informix-list@iiug.org'" <informix-list@iiug.org>
<RRajasekaran@fores cc: "'sapmix@iiug.org'" <sapmix@iiug.org>
tpharm.com> Subject: Clarification on SQL performance tuning....
Sent by:
owner-informix-list
@iiug.org

09/15/2003 09:55 AM

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.31
UD2XG 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".
-- YES

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.
-- If that's the case, you can re-order the order of the tables in the FROM
clause and force the optimizer to use the order using the ORDERED hint
Check the cost, after you run it with the ORDERED hint ... u'll
definitely see the cost to be higher than what the optimizer is showing
without the ORDERED hint. But, then the execution time of the query and the
COST are not always directly related .... (COST is a relative cost and is
an approximation)

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.
-- U never know... there are OPTIMIZER bugs .. it would require more
research to conclude that its a bug.

4)* Do you think this SQL can be re-framed in any order to get better
results ?
-- Possibly yes... Here is how I will proceed if I were you ...
-- Do a select count(*) with the join conditions for T_04 and T_05 to see
what's the number of rows selected, and proceed to find out which would
eliminate the most # of rows from the resultant set (by trying different
joins).
-- Essentially the goal is to make the resultant set to be small so that
the next join works faster instead of joining un-necessary rows and then
discarding them later on. The optimizer will try and decide that for you
(there was a paper on 'Peephole optimization' some time back) but it can do
so much.

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.
-- U will have to try Optimizer hints for that ...

Any suggestions/comments* are greatly appreciated.

Thanks
Rajesh Rajasekaran
Informix Database Administrator
Forest Pharmaceuticals Inc.
(314) 493-7073
[email]rrajasekaran@forestpharm.com[/email]

#### sqexplain.out has been removed from this note on September 15, 2003 by
Rajib Sarkar

sending to informix-list

Mark D. Stock · Mark D. Stock

Rajasekaran, Rajesh wrote:

> 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.

You will get more help if you post in text only.

> 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 ?

Yes.

> -- 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".

Yes.

> 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.

I would think it is more likely to join on the smaller tables first with
index joins. Inaccurate estimates can be indicative of inaccurate or
missing statistics. If for example you have a very large table with a
skewed data distribution, then LOW or MEDIUM statistics may not help much.

> 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 ?

I can't tell. I don't know what statistics you updated. I don't know what
the relationships are between the tables. I don't know all the data volumes
involved, etc.

> 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 ?

I think you may be missing joins. For example:

mkpf.mandt = mseg.mandt
mkpf.mandt = afpo.mandt
mkpf.mandt = makt.mandt
mkpf.mandt = marm.mandt
mkpf.mandt = mcha.mandt

If those are the correct relationships, and get rid of:

mseg.mandt = '040'
afpo.mandt = '040'
makt.mandt = '040'
marm.mandt = '040'
mcha.mandt = '040'

But without seeing the relationships between the tables, in terms of the
primary and foreign keys, it is difficult to tell.

> 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.

The optimiser will use indexes that appear to eliminate the most records.

> 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.

That's the way you change the optimiser's mind, but be very careful that
you don't make things worse. :-)

I have seen query performance improved with optimiser hints, only to get
worse again after the next UPDATE STATISTICS is run.

> Any suggestions/comments are greatly appreciated.

Check that your UPDATE STATISTICS commands are up to date and specific
enough for your data distribution.

Check the joins between each table pair. Even extract these joins and run
them separately to see that you are getting the desired records.

Check the indexes on the join columns.

Check the physical location of these tables / fragments. I'd hate to think
you are joining tables on the same disk. ;-)

Check that your expectations are realistic. Perhaps 30 mins is a good time,
given a six-way join and the data you have.

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd [url]http://MydasSolutions.com[/url] |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+

sending to informix-list

Gorazd Hribar Rajteriè · Gorazd Hribar Rajteriè

You can try using optimizer hints. Detailed description is available at
[url]http://www.klimaexpert.com/gorazd/informix/index.html[/url] 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
> [email]rrajasekaran@forestpharm.com[/email]
>
>
>
>
> ------_=_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 =
> 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
> 
> 
>
>  
>
> </BODY>
> </HTML>
> ------_=_NextPart_001_01C37BAA.3B7FBDB0--
>
> ------_=_NextPart_000_01C37BAA.3B7FBDB0
> Content-Type: application/octet-stream;
> name="sqexplain.out"
> Content-Transfer-Encoding: quoted-printable
> Content-Disposition: attachment;
> filename="sqexplain.out"
>
> ------------------------------------------------------------------------=
> --------
> Table Information
> ------------------------------------------------------------------------=
> --------
> table name: mcha
>
> dbspace: psapstab rows: 64018
> fragmentation: none columns: 34
> row size: 272 bytes
> locking mode: row
> first extent: 22288 KB views: 27
> next extent: 128 KB indexes: 2
> size: 23056 KB
> extents: 7 (218 still available) fill level: 100 %
> ------------------------------------------------------------------------=
> --------
> Table Information
> ------------------------------------------------------------------------=
> --------
> table name: mseg
>
> dbspace: psapbtab rows: 6299684
> fragmentation: none columns: 170
> row size: 1089 =
> bytes
> locking mode: row
> first extent: 11649528 KB views: 6
> next extent: 400000 KB indexes: 4
> size: 13294360 KB
> extents: 18 (198 still available) fill level: 97 %
> ------------------------------------------------------------------------=
> --------
> Table Information
> ------------------------------------------------------------------------=
> --------
> table name: mkpf
>
> dbspace: psapbtab rows: 1918113
> fragmentation: none columns: 24
> row size: 196 bytes
> locking mode: row
> first extent: 16 KB views: 8
> next extent: 20480 KB indexes: 2
> size: 519184 KB
> extents: 13 (212 still available) fill level: 99 %
>
>
> QUERY:
> ------
> SELECT T_01.matnr i_matnr,T_02.matnr p_matnr,T_02.wemng =
> p_wemn,T_03.maktx,
> T_04.umrez,T_04.umren,T_05.licha
> FROM mkpf T_00, mseg T_01 , afpo T_02 , makt T_03 , marm T_04 , mcha =
> T_05
> WHERE T_00.mandt =3D '040' AND T_00.budat BETWEEN '20030101' and =
> '20030131'
> AND (T_01.mandt =3D '040' AND T_01.mblnr =3D T_00.mblnr
> AND T_01 .mjahr =3D T_00.mjahr )
> AND T_01.matnr IN ( '000000000000003608','000000000000003659')
> AND T_01.werks IN ( 'LAC1','INW1','ERC1','COM1','CIN1')
> AND T_01.bwart BETWEEN '261' AND '262'
> AND (T_02 .mandt =3D '040' AND T_02.aufnr =3D T_01.aufnr)
> AND (T_03.mandt =3D '040' AND T_03.matnr =3D T_01.matnr
> AND T_03.spras =3D 'E') AND ( T_04.mandt =3D '040'
> AND T_04.matnr =3D T_01.matnr AND T_04.meinh =3D 'KG')
> AND (T_05.mandt =3D '040' and T_05.matnr =3D T_01 . matnr
> AND T_05.werks =3D T_01.werks AND T_05 . charg =3D T_01 . charg )
>
> Estimated Cost: 439
> Estimated # of Rows Returned: 1
>
> 1) informix.t_04: INDEX PATH
>
> (1) Index Keys: mandt matnr meinh=20
> Lower Index Filter: (informix.t_04.mandt =3D '040' AND =
> (informix.t_04.meinh =3D 'KG' AND informix.t_04.matnr =3D =
> '000000000000003608' ) )=20
>
> (2) Index Keys: mandt matnr meinh=20
> Lower Index Filter: (informix.t_04.mandt =3D '040' AND =
> (informix.t_04.meinh =3D 'KG' AND informix.t_04.matnr =3D =
> '000000000000003659' ) )=20
>
> 2) informix.t_05: INDEX PATH
>
> (1) Index Keys: mandt matnr werks charg (Key-First)
> Lower Index Filter: (informix.t_05.mandt =3D '040' AND =
> informix.t_05.matnr =3D informix.t_04.matnr )=20
> Key-First Filters: (informix.t_05.werks IN ('LAC1' , 'INW1' , =
> 'ERC1' , 'COM1' , 'CIN1' ))
> NESTED LOOP JOIN
>
> 3) informix.t_01: INDEX PATH
>
> Filters: informix.t_01.charg =3D informix.t_05.charg=20
>
> (1) Index Keys: mandt matnr werks lgort bwart sobkz (Key-First)
> Lower Index Filter: (informix.t_01.mandt =3D '040' AND =
> (informix.t_01.werks =3D informix.t_05.werks AND informix.t_01.matnr =
> =3D informix.t_04.matnr ) )=20
> Key-First Filters: (informix.t_01.bwart <=3D '262' ) AND
> (informix.t_01.bwart >=3D '261' )
> NESTED LOOP JOIN
>
> 4) informix.t_03: INDEX PATH
>
> (1) Index Keys: mandt matnr spras=20
> Lower Index Filter: (informix.t_03.mandt =3D '040' AND =
> (informix.t_03.spras =3D 'E' AND informix.t_03.matnr =3D =
> informix.t_04.matnr ) )=20
> NESTED LOOP JOIN
>
> 5) informix.t_02: INDEX PATH
>
> (1) Index Keys: mandt aufnr posnr=20
> Lower Index Filter: (informix.t_02.mandt =3D '040' AND =
> informix.t_02.aufnr =3D informix.t_01.aufnr )=20
> NESTED LOOP JOIN
>
> 6) informix.t_00: INDEX PATH
>
> Filters: (informix.t_00.budat >=3D '20030101' AND =
> informix.t_00.budat <=3D '20030131' )=20
>
> (1) Index Keys: mandt mblnr mjahr=20
> Lower Index Filter: (informix.t_00.mandt =3D '040' AND =
> (informix.t_00.mblnr =3D informix.t_01.mblnr AND informix.t_00.mjahr =
> =3D informix.t_01.mjahr ) )=20
> NESTED LOOP JOIN
> ------_=_NextPart_000_01C37BAA.3B7FBDB0--
> sending to informix-list

Clarification on SQL performance tuning....

Thread Tools

Display

Clarification on SQL performance tuning....

Similar Questions and Discussions

Performance Tuning: stand-alone vs. enterprise

Performance tuning on RedHat Enterprise Linux 3

Performance tuning in web service

Clarification on ONBAR Backup performance issue..

performance tuning at process level

Re: Clarification on SQL performance tuning....

Re: Clarification on SQL performance tuning....

Re: Clarification on SQL performance tuning....

Posting Permissions