Re: Clarification on SQL performance tuning....

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 http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list