05-05-2010 4:53 PM
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.
05-05-2010 5:20 PM
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
05-05-2010 7:07 PM
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.
05-05-2010 7:31 PM
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
05-05-2010 7:36 PM
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
05-06-2010 12:38 AM
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.
05-06-2010 10:17 AM
Please post the statistics for table LTAK and for the index LTAK~Q so that the forum can help you more.
Regards,
Rui Dantas
05-06-2010 1:34 PM
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
05-06-2010 2:31 PM
Hi,
we need the distinct keys for the columns in index LTAK~Q as well.
Kind regards,
Hermann
05-06-2010 2:48 PM
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.
05-06-2010 3:14 PM
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
or
- give it a hint what should be done...
Kind regards,
Hermann
05-06-2010 3:22 PM
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!
05-06-2010 7:26 PM
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.
05-06-2010 7:27 PM
05-06-2010 8:08 PM
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