V.14 Sales Transaction Performance

[former_member139937]

Hi Experts,

We are having an performance issue with  V.14 Sales transaction performance, its taking 45 Minues to generate the report, Issue seems to be with the query preformed on view V_VMVAC trying to fetch, Its  taking 30 mins while fetching data from V_VMVAC.  Both the tables in the view has indexes.

SQL Trace details in the attached doc.

Could you please advise on how to proceed from here. Appreciate your assistance with this.

Rgards,

Rajesh

V.14 SQL Trace.txt.zip

[former_member139937]

Hi Experts,

Below is the query i am having issue with, could you please provide some hint on improving the performance.

Query

SELECT

  *

FROM

  "V_VMVAC"

WHERE

  "MANDT" = :A0 AND "VKORG" = :A1 AND "VTWEG" = :A2 AND "SPART" = :A3

ORDER BY

  "MANDT" , "VBELN"#

SQl Trace

*****************

10:45:01.147           1,112 SAPLV05L V_VMVAC    PREPARE 43                    0 R/3   SELECT WHERE "MANDT" = :A0 AND "VKORG" = :A1 AND "VTWEG" = :A2 AND "SPART" = :A3 ORDER BY "MANDT" , "VBELN"

10:45:01.148               5 SAPLV05L V_VMVAC    OPEN    43                    0 R/3   SELECT WHERE "MANDT" = '040' AND "VKORG" = 'OMSO' AND "VTWEG" = '01' AND "SPART" = '02' ORDER BY "MANDT" , "VBELN"

10:45:01.149      13,003,939 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

10:45:14.154     461,466,330 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

10:52:55.621     334,596,471 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

10:58:30.218     154,985,453 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:01:05.204      28,478,057 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:01:33.683      11,776,453 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:01:45.460      35,536,761 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:02:20.999      16,749,974 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:02:37.749       9,840,591 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:02:47.591         785,049 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:02:48.376       6,098,256 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:02:54.475       2,080,718 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:02:56.556       1,216,690 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:02:57.773         437,904 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:02:58.212       1,064,598 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:02:59.277         153,442 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:02:59.430         378,072 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:02:59.809       1,100,294 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:03:00.910         865,773 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:03:01.776         953,377 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:03:02.731         726,963 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:03:03.459         571,959 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:03:04.031         653,766 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:03:04.687         590,583 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:03:05.278         802,234 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

11:03:06.081         497,171 SAPLV05L V_VMVAC    FETCH   43     478   478      0 R/3

Regards,

Rajesh

[Former Member]

Hi Rajesh,

I suppose I should have asked whether this is a standard transaction or one of your own reports.  If it's oneof your own, then perhaps the ABAPers need to check the select statement as the Selection Conditions of the view are VBUK fields: VBOBJ and BESTK.  I note that only one of these fields are in the select statement.

Regards,

Graham

[Former Member]

Hi Rajesh,

When was the last time you updated the statistics on the indexes?  You may need to get it to perform the stats on the actual number of records not just an estimated number.

Regards,

Graham

[former_member139937]

Hi Graham,

Thanks for your reply, from DB13 i can see that cost based optimizer statistics updated on 10th April, if the index statistics update you sepcified is different to CBO stats update, could you please guide me on how to schedule this.

Regards,

Rajesh

[Former Member]

Hi Rajesh,

Try this:

DB02 > Performance > Indexes > Index Usage

Finf the indexes for your view and drill into them

Select the index and see when the last update was

If too old, click on it and select "Update Statistics" icon.

Regards,

Graham

[former_member139937]

Hi Graham,

We are in ECC6 version, IN DB02 i tried follow your navigation but under performance i couldn't find any thing related to indexes. I have got Wait event analysis, performance overview,  SQl statement analysis and oether options.  However i have updated the stats for VBAK and VBUK tables under view  V_VMVAC in DB20, but this didn't help.

Any other advise?

Regards,

Rajesh