AUSP table access in 4.7
Client has just upgraded to 4.7 and the processing of configurable materials during costing has just ground to a halt. The select in question is:
select i~objek i~cuobj i~obtab
from
( ( ( ( ausp as a1 join ausp as a2
on a1~objek = a2~objek and
a1~mafid = a2~mafid and
a1~klart = a2~klart )
join ausp as a3
on a1~objek = a3~objek and
a1~mafid = a3~mafid and
a1~klart = a3~klart )
join ausp as a4
on a1~objek = a4~objek and
a1~mafid = a4~mafid and
a1~klart = a4~klart )
join ausp as a5
on a1~objek = a5~objek and
a1~mafid = a5~mafid and
a1~klart = a5~klart )
join inob as i
on a1~objek = i~cuobj and
a1~klart = i~klart
up to p_max_hits rows
into (p_objects_tab-object, p_objects_tab-cuobj,
p_objects_tab-obtab)
where (p_where_tab)
and a1~klart = p_tcla_struc-klart
and a1~mafid = c_o
and i~obtab in p_r_objecttypes_tab
%_hints oracle p_hint
informix p_hint.
from include LCLSCF2G. This comprises a series of self joins. The hint supplied suggests using indexes N1 and N3 of AUSP for accessing the first table and then N3 for the subsequent tables. OSS suggests changing N1 for N3 as this is a more appropriate index:
Name Unique Short description Status
2 Index for Engineering Change Management Active
N1 Index for ATINN and ATWRT Active
N2 Index for ATINN and ATFLV Active
N3 Index with OBJEK and ATWRT Active
N4 Index for OBJEK and ATFLV Active
ZOM By Objek, mafid and klart Active
The nearest thing on OSS suggests changing the hint given to the database from using index N1 to index N3. This results in a doubling of the time taken for the select to return.
I've created an index based upon the joins and this sends the time taken through the roof (part of the output from a debugging report is shown below):
Call to Execute Select:
P_Where_Tab:
( ( A1~ATINN = '0000000096'
AND
A1~ATWRT EQ 'GBC'
)
)
AND ( ( A2~ATINN = '0000000097'
AND
A2~ATWRT EQ 'GR'
)
)
AND ( ( A3~ATINN = '0000000098'
AND
A3~ATWRT EQ 'GY'
)
)
AND ( ( A4~ATINN = '0000000099'
AND
A4~ATWRT EQ 'SC'
)
)
AND ( ( A5~ATINN = '0000000100'
AND
A5~ATWRT EQ '20'
)
)
p_Objects_Tab
p_r_Subclasses_tab
IEQ0000000002
p_max_hits 00000
p_hint
ordered use_nl ( &TABLE 1& &TABLE 2& &TABLE 3& &TABLE 4& &TABLE 5& &TABLE 6& ) index ( &TABLE 1& "AUSP~N1" ) index ( &TABLE 2& "AUSP~ZOM" ) i
p_count 5
p_tcla_struc 800300MARA XXXXX X XXX X X 00000000
Runtime: 54,956,586
I don't particularly want to start changing the select statement but any one out there have any further ideas ?
_________________
Regards
R
Abap KC
SFMDR
Hi R,
Paste here an execution plan for the statement (from ST05 -> Explain).
Also, are the CBO statistics up to date after the upgrade?
T.
T is thinking along the same lines as me. If that system is very new it may not have any statistics pointing the optimiser to the correct index paths.
A SQL trace will typically show some big red figures. Drilling down into these can identify if an index is being used. If you see 'full table scan' anywhere it's likely to be busted.
You can run the analyser from the trace screen and this will fix that particular table, but there will probably be others in the same state.
Had a similar problem last year one a newly loaded QA box. The so-called (offsite) support staff took two weeeks to figure it out. Funny thing was we told them exactly what was wrong a week before as a visiting Oracle expert clued us in
( Me no Basisaurus, so me no speakee the Old Tongue of the Oracle but I know a duff DB when I see one ).
Please let us know if it was something else though...
I've been using ST05 to find where the time was going. Here's the execution plan as requested. Just running the analysis now, but client says that this was all done at the weekend during the upgrade.
Will keep you informed.
ta!
Execution Plan
SELECT STATEMENT ( Estimated Costs = 20 , Estimated #Rows = 1 )
5 NESTED LOOPS
5 NESTED LOOPS
5 NESTED LOOPS
5 NESTED LOOPS
5 NESTED LOOPS
5 NESTED LOOPS
INDEX RANGE SCAN AUSP~N1
INDEX RANGE SCAN AUSP~N3
INDEX RANGE SCAN AUSP~N3
INDEX RANGE SCAN AUSP~N3
INDEX RANGE SCAN AUSP~N3
INDEX RANGE SCAN KSSK~0
5 TABLE ACCESS BY INDEX ROWID INOB
INDEX UNIQUE SCAN INOB~0
_________________
Regards
R
Abap KC
SFMDR
From my experience, having so many nested loops inside of nested loops is almost always a sign that the statistics aren't up to date.
For such a complex SQL statement, I would expect to see a bunch of hash joins.
T.
Weeelll, not much I can contribute but this is a discussion forum as well isn't it?
The EXPLAIN looks OK, it is using the correct indexes.
It is doing range scans however; are we able to determine how big those ranges are and what proportion of the actual data range those ranges would be? It could still be searching a lot of data, especially since it seems to be iterative on some of the indexes.
This is one downside of joins, there's probably one culprit table but how do you get individual table access stats for a join. Is there a tool in SAP that can do the job (I have no idea if the DB admin would have these tools available).
And oooohhh, I haven't seen the %hint clause before - something new! Probably never will again. Makes you wonder why they had to add it here, normally the optimiser works fine without it.
Maybe you'll have to throw it over to the Basis forum, it doesn't look like ABAP is at fault.
At least musing on this kept me busy 'til hometime.
Bye all, the Tar Pit calls.
Optimizing the table had no effect, nor did creating an index for the joins. However, it was noticed that the routine was called 4 or more ties consecutively using the same parameters.
Therefore the parameters and the result set were cached in two static variables. The parameters passed into the procedure were then checked against the previously cached parameters. If these were identical then the cached result set was moved to the output parameters (p_objects_tab).
If the parameters were different then the selects were run with the results being cached.
This resulted in runtimes being reduced from for example 14,540,000 ms ( 3 times ) to 289 ms (3 times).
_________________
Regards
R
Abap KC
SFMDR
So is it fixed or did you just manage to figure out the conditions that led to the performance drop? Sounds like the latter.
In the eyes of the user....... it's fixed
_________________
Regards
R
Abap KC
SFMDR