topic Re: Performance problem for table LTAK in Application Development and Automation Discussions

Performance problem for table LTAK

Former Member — Wed, 05 May 2010 15:53:12 GMT

Hi All,

I am fetching data from LTAK table my where clause have LGNUM, KQUIT and there exist an Index LTAK~Q for LTAK table, but my query is not using any index not even primary index as LGNUM is primary key. Can any one please put some light on this problem.

Thanks in advance,

Anmol Bhat.

Re: Performance problem for table LTAK

ThomasZloch — Wed, 05 May 2010 16:20:35 GMT

Hmm, did you really count on me not seeing your next attempt after locking the last one?

I'll leave it open for now, but explain in detail what you have done so far to solve this yourself, as I said there were several discussions of this topic before. Did you check the sticky threads of this forum?

Also at least quote your actual WHERE-condition and how you know for sure that no index is used, which I feel is very unlikely.

Did you run an SQL trace?

Thomas

Re: Performance problem for table LTAK

Former Member — Wed, 05 May 2010 18:07:49 GMT

Hi Thomas,

Ya Actually I search the forums but did not get anything wht I really want.

I ran the SQL trace there I noted that the query is not using any index .

Please check my Where condn...

SELECT

"LGNUM" , "TANUM" , "VBELN" , "QUEUE"

FROM

"LTAK"

WHERE

"MANDT" = :A0 AND "LGNUM" = :A1 AND "KQUIT" = :A2

This query is not using any Index , check the trace details

SELECT STATEMENT ( Estimated Costs = 93,679 , Estimated #Rows = 1,388,745 )

1 TABLE ACCESS FULL LTAK

( Estim. Costs = 93,679 , Estim. #Rows = 1,388,745 )

Even there is a Index(LTAK~Q) with fields MANDT, LGNUM, KQUIT.

Please Suggest..

Thanks ,

Anmol Bhat.

Re: Performance problem for table LTAK

Former Member — Wed, 05 May 2010 18:31:00 GMT

You can look forever in the forums and not find a solution that exactly solves your particular question, but there are lots of discussions about this sort of problem.

Search the forum for "selectivity".

Rob

Re: Performance problem for table LTAK

Former Member — Wed, 05 May 2010 18:36:10 GMT

Hi Rob,

Thanks for your replay. Can you suggest me any such forum. I tried the same but did not find anything which is similar to this and Answered. What you think may be the problem in my case. My indexes are activ and exist in database also. I am using 4.7 Version

Please suggest.

Anmol Bhat.

Edited by: anmol112 on May 5, 2010 8:36 PM

Re: Performance problem for table LTAK

Former Member — Wed, 05 May 2010 23:38:15 GMT

I agree with Thomas, this has all been discussed before. If you check out enough postings in the forum, you will find plenty of references. However, I'll still answer, because I got so upset that I failed get any hit in the SAP documentation ([help.sap.com|http://help.sap.com]) for database hints using "%_HINTS"...

First of all consider that not using an index might actually be a feature and not a bug. E.g. if you look at your SQL execution plan you see that the CBO estimates 1,388,745 rows for the result set (how many entries in total does your table have?). Let me try a (poor) analogy: Assume you have one telephone book for a couple of cities sorted by last name, first name, city, telephone number and you want to retrieve all phone numbers of residents in one city. Luckily your telephone book also has an index on city, last name referring to the actual page in the phone book.

Thus you have two choices:

<ol>

<li>Start in the index on city/last name and get all references for the city you're looking for and then find the actual phone numbers for each referenced entry (by looking up the entry on the corresponding page)</li>

<li>Browse through the whole telephone book and collect all phone numbers whenever you see an entry with matching city.</li>

</ol>

Fairly soon you'll discover, that there's scenarios where the first approach is simply painful and inefficient. Basically the additional level of indirection (look-up index first, but then you have to go to the actual entry) results in inefficient access if you have a phone book with few cities and lots of matches for the one city you're looking for. Clear?

Back to your problem: In general the CBO assumes equal distribution of data values for estimating the result set, which is inappropriate if you have highly skewed data. E.g. let's say that all your transfer orders have to be confirmed and are usually confirmed within a short timeframe. Thus in LTAK you'd expect a very small number of entries with KQUIT = 'X' and very few with KQUIT = ' '. You know that, but the CBO doesn't; the CBO just assumes it's 50/50, so a selection on KQUIT with ' ' might be highly selective (few rows). If that's what happens in your case, you should try to push the optimizer in the right direction using database hints. Just search the forum with "%_HINTS" and you'll find plenty of hits...

Cheers, harald

Disclaimer: This was a naive and simplified explanation.

Re: Performance problem for table LTAK

Rui_Dantas — Thu, 06 May 2010 09:17:56 GMT

Please post the statistics for table LTAK and for the index LTAK~Q so that the forum can help you more.

Regards,

Rui Dantas

Re: Performance problem for table LTAK

Former Member — Thu, 06 May 2010 12:34:49 GMT

Hi Rui Dantas,

Thanks for your initiative, Following are the Statistics for the Table LTAK.


Last statistics date                  05/06/2010 
Analyze Method              mple 16,664,938 Rows 
Number of rows                        16,664,938 
Number of blocks allocated               617,216 
Number of empty blocks                     1,025 
Average space                                937 
Chain count                                    0 
Average row length                           263 
Partitioned                                   NO

The Statistics for LTAK~Q are


Last statistics date                  05/06/2010 
Analyze Method              mple 16,664,938 Rows 
Levels of B-Tree                               2 
Number of leaf blocks                     58,588 
Number of distinct keys                       44 
Average leaf blocks per key                1,331 
Average data blocks per key              100,319 
Clustering factor                      4,414,061

The Select Query


SELECT                                                
  "LGNUM" , "TANUM" , "VBELN" , "QUEUE"               
FROM                                                  
  "LTAK"                                              
WHERE                                                 
  "MANDT" = :A0 AND "LGNUM" = :A1 AND "KQUIT" = :A2

Will fetch some where 1200 records and it is taking more than 15 Mins.

Moreover this performance problem started just 1 month back, earlier it is doing good.

Please Help .

Anmol Bhat.

Edited by: anmol112 on May 6, 2010 3:00 PM

Re: Performance problem for table LTAK

HermannGahm — Thu, 06 May 2010 13:31:11 GMT

Hi,

we need the distinct keys for the columns in index LTAK~Q as well.

Kind regards,

Hermann

Re: Performance problem for table LTAK

Former Member — Thu, 06 May 2010 13:48:41 GMT

Hi Hermann,

The Distinct key for the sec Index LTAK~Q is


NONUNIQUE  Index   LTAK~Q                        
                                                 
Column Name                     #Distinct        
                                                 
MANDT                                          1 
LGNUM                                          6 
KQUIT                                          2 
QUEUE                                         22

One more fact that the same program is performing good in Dev but fails in QA and PROD. And In Dev the query is using secondary index LTAK~Q but not in QA and PROD.

Thanks,

Anmol Bhat.

Re: Performance problem for table LTAK

HermannGahm — Thu, 06 May 2010 14:14:09 GMT

Hi,

based on your stats the optimizer

estimates a rather big result set :1,388,745 rows:

SELECT STATEMENT ( Estimated Costs = 93,679 , Estimated #Rows = 1,388,745 )

which you don't have:

Will fetch some where 1200 records and it is taking more than 15 Mins.

Your data is not distributed evenly as Rui and Harald already said.

You have to help the optmizer either with

- more detailed statistics (histograms) and concrete values for the bind variables

- give it a hint what should be done...

Kind regards,

Hermann

Re: Performance problem for table LTAK

Former Member — Thu, 06 May 2010 14:22:43 GMT

Sorry for derailing yet another thread, but 16 Mio entries ... archiving RL_TA is sssoooo easy! And fast! The hours you have spent here trying to get advise on performance improvements could have rid yourself of at least half of your records (o.k. that's a wild guess, but stil).

RL_TA does neither face legal implications, nor financial auditings - you are free!

Re: Performance problem for table LTAK

Former Member — Thu, 06 May 2010 18:26:32 GMT

Hi all,

Thanks for the contributions from all of you, my basis adm tried to rebuild the indexes and now indexes are working properly.

I am now clossing the Thread.

Thanks again,

Anmol Bhat.

Re: Performance problem for table LTAK

Former Member — Thu, 06 May 2010 18:27:22 GMT

Thread clossed as myself got the answer.

Re: Performance problem for table LTAK

Former Member — Thu, 06 May 2010 19:08:57 GMT

OK - I'm a bit ticked.

Are and the same person or do they just happen to have exactly the same problem at exactly the same time?

Rob