on 2008 Jun 03 2:10 PM
Good afternoon
from a BW system , a user try to extract data in a R3 46C system .
this loading hangs during the access of table mseg for many hours.
the select in hang is the following:
SELECT
T_00 . "MBLNR" , T_00 . "MJAHR" , T_00 . "ZEILE"
FROM
"MSEG" T_00 , "MKPF" T_01
WHERE
( T_01 . "MANDT" = :A0 AND T_01 . "MBLNR" = T_00 . "MBLNR" AND T_01 . "MJAHR" = T_00 . "MJAHR" )
AND T_00 . "MANDT" = :A1 AND T_01 . "CPUDT" >= :A2 AND T_00 . "MATNR" BETWEEN :A3 AND :A4 AND
T_00 . "XAUTO" <> :A5#
The index used is the following:
NONUNIQUE Index MSEG~M
MANDT 1
MATNR 11.877
WERKS 142
LGORT 556
BWART 174
SOBKZ 4
Last statistics date 06.05.2008
Analyze Method Sample 612.175 Rows
Levels of B-Tree 3
Number of leaf blocks 674.400
Number of distinct keys 25.071
Average leaf blocks per key 26
Average data blocks per key 1.635
Clustering factor 40.992.900
I've created a new index to have a better performance:
NONUNIQUE Index MSEG~Z07
Column Name #Distinct
MANDT 1
MBLNR 7.521.784
MJAHR 8
MATNR 11.877
XAUTO 2
Last statistics date 03.06.2008
Analyze Method mple 63.678.689 Rows
Levels of B-Tree 3
Number of leaf blocks 449.133
Number of distinct keys 59.182.326
Average leaf blocks per key 1
Average data blocks per key 1
Clustering factor 17.885.740
Anyway the query doesn't take this index.
Any suggestion about this query? from sm51 i have the wp running with MSEG.
SAP 46C
Oracle 10.2.0.2
thanks
Nick
Hi,
I'd need more information (like the current access plan, all statistics, how many rows are returned in total ... ) to be sure.
But the reason the new index is not taken is because it is not selective.
Lets take a look at the query (a bit modified for clarity)
SELECT
T_00 . "MBLNR" , T_00 . "MJAHR" , T_00 . "ZEILE"
FROM
"MSEG" T_00 , "MKPF" T_01
WHERE (
T_01 . "MANDT" = :A0 AND
T_01 . "MBLNR" = T_00 . "MBLNR" AND
T_01 . "MJAHR" = T_00 . "MJAHR" ) AND
T_01 . "CPUDT" >= :A2 AND
T_00 . "MANDT" = :A1 AND
T_00 . "MATNR" BETWEEN :A3 AND :A4 AND
T_00 . "XAUTO" :A5
On the query there are 3 parts after the "WHERE":
1) Join condition on both queries
2) Filter condition for table "MKPF"
3) Filter condition for table "MSEG"
Now, lets take a look at the point 3 and your new index
MANDT -> It is specified in the Filter condition
MBLNR -> it is not specified in the Filter condition
MJAHR -> it is not specified in the Filter condition
MATNR -> It is specified in the Filter condition
XAUTO -> It is specified in the Filter condition
The index is not used because the second field is not in the conditions, it is only on the join condition, and not considered by the CBO in case of nested loop join.
A much better index would be
MANDT
XAUTO (I think it is specified with an "=", only have two values, is the one specified selective?)
MATNR (it is specified with BETWEEN, not too good)
MBLNR
MJAHR
ZEILE
Adding the last 3 field to the index you will avoid the access to the table as all information needed is on the index.
Perhaps something similar could be done for the MKPF table.
Edited by: Fidel Vales on Jun 3, 2008 9:09 PM
Not sure if the XAUTO is specified with an equal, it is missing on the original information
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hy Fidel
thanks for you explanation about my query....
this is my actual access plan:
Execution Plan
SELECT STATEMENT ( Estimated Costs = 53.336 , Estimated #Rows = 2.639 )
5 FILTER
5 NESTED LOOPS
5 TABLE ACCESS BY INDEX ROWID MSEG
INDEX RANGE SCAN MSEG~M
5 TABLE ACCESS BY INDEX ROWID MKPF
INDEX UNIQUE SCAN MKPF~0
I tried to following your analysys changing my index , anyway i've seen that another index (unique index) exists with that fields:
UNIQUE Index MSEG~0
Column Name #Distinct
MANDT 1
MBLNR 7.521.784
MJAHR 8
ZEILE 904
Last statistics date 03.06.2008
Analyze Method Sample 524.250 Rows
Levels of B-Tree 3
Number of leaf blocks 703.738
Number of distinct keys 65.228.897
Average leaf blocks per key 1
Average data blocks per key 1
Clustering factor 15.831.243
what do you suggest? could be possible add XAUTO and MATNR to do better performance?
thanks a lot
Nick
Hello kaushal,
> Please post your query in ABAP forum so you get better answers.
I'm the first one thinking that a lot of posts do not belong to this Forum, but in this case I do not (fully) agree. I have not seen a line of ABAP here, so I do not know how the ABAPers could help.
It is true that the first point to tune is the source code, but in this case a better index would help a lot (and then it could be investigate if the source code can/should be changed or it is the users input the one with "issues")
Going to the explain plan we see something "good"
SELECT STATEMENT ( Estimated Costs = 53.336 , Estimated #Rows = 2.639 )
FILTER
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID MSEG
INDEX RANGE SCAN MSEG~M
TABLE ACCESS BY INDEX ROWID MKPF
*INDEX UNIQUE SCAN MKPF~0*
The access to the second table (MKPF) is done via Index Unique Scan. Only 0 or one rows are retuned from this part. Then the first access is the one to be tuned.
The unique index should not be touched (if you change it, adding more fields, you can have duplicate keys for the current keys (MANDT, MBLNR, MJAHR, ZEILE ).
So no touch it.
My previous recommendation still is valid.
An index on MANDT, XAUTO, MATNR is better for the query. and adding at the end MBLNR, MJAHR, ZEILE avoid the access to the table. The access path should be then (more or less):
SELECT STATEMENT ( Estimated Costs = xxxxx , Estimated #Rows = xxxx )
NESTED LOOPS
INDEX RANGE SCAN MSEG~ZZZ
TABLE ACCESS BY INDEX ROWID MKPF
*INDEX UNIQUE SCAN MKPF~0*
Fidel
only to be sure....do you suggest to put the fileds in the order you describe?
MANDT, XAUTO, MATNR,MBLNR, MJAHR, ZEILE ?
i ask it because, looking the query, i thought that MATNR and XAUTO was at the end before zeile:
MANDT,MBLNR, MJAHR,MATNR,XAUTO,ZEILE...
what do you think? is it the same thing?
thanks
Nick
Hello Nick,
It is not the same. Too long to explain here, but in resume (and generalizing):
the order on the SQL is not important
the order on the index IS important.
As I mentioned before, if your second field in the index is MBLNR, then Oracle will not be able to use it as a filter condition as its value is unknown. In the query it is only specified as part of the join of both tables:
> T_01 . "MBLNR" = T_00 . "MBLNR" AND
As it cannot be used and the first field (MANDT) is unselective, then Oracle will dismiss the index
Hy Fidel
I tried to create the new index Z07 . Now the select take the correct index. Anyway the estimated cost is not so good as i expected. Anyway i'll see if the query will have a better performance.
the new cost of the query:
Execution Plan
SELECT STATEMENT ( Estimated Costs = 53.118 , Estimated #Rows = 2.639 )
5 FILTER
5 NESTED LOOPS
INDEX FAST FULL SCAN MSEG~Z07
5 TABLE ACCESS BY INDEX ROWID MKPF
INDEX UNIQUE SCAN MKPF~0
the index MSEG-Z07 :
MANDT
XAUTO
MATNR
MBLNR
MJAHR
ZEILE
Thanks a lot for your help ...i'll add all points possible:-)
Nick
Hi,
It looks better, nevertheless it is taking a "FAST FULL SCAN" instead of a "RANGE SCAN"
Probably because XAUTO only has two different values and MATNR is specified with a range.
You can do a test with the mentioned index and then modify the statistics, increasing the number of different values for the field XAUTO and see if the range scan is take. This is better only if the value you are choosing for XAUTO is selective
Great explanation Fidel.
I would like to add that CPUDT is not contain in any standard-index of MKPF so either by adding an index containg that field or using BUDAT (Posting Date) instead (which is contained in an index) can also improve performance.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.