Skip to Content
author's profile photo
Former Member

DB statistics for BW DSO table

Hello,

We have performance problems with one of our DSO tables which contains 16m rec of CATS data.

The ABAP program reads from the DSO table by sec.index (trace shows that the index is used) but still we get like 500k cpu and 280 iocost.

I was looking at the statistics for this table and 2 things that got my attention:

1. statistics are about 1 year old (last date 17.6.2009)

2. Deviation is 24% over the last year (3,2m change/1,6m add)

Are the statistics causing the problems with a select on index and why does the DBCONNECT don't update the statistics automatically (it does run every day and does change quiet a lot of other tables)?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Best Answer
    Posted on Jun 08, 2010 at 10:01 AM

    > We have performance problems with one of our DSO tables which contains 16m rec of CATS data.

    > The ABAP program reads from the DSO table by sec.index (trace shows that the index is used) but still we get like 500k cpu and 280 iocost.

    As far as I understand your post, you are happy about the index access - so what's the issue?

    As long as the correct execution plan is chosen the absolute costs are not that important.

    Much more important is: how much actual work is necessary for the query of your ABAP program?

    Maybe the index is not optimal for the selection!?

    Maybe too much data is seleted?

    > I was looking at the statistics for this table and 2 things that got my attention:

    > 1. statistics are about 1 year old (last date 17.6.2009)

    So what? Statistics don't get old, they stay "fresh". There is nothing like a "best-before" for statistics.

    The only thing that could happen is: your data changes over time and the statistics don't fit to it properly anymore.

    > 2. Deviation is 24% over the last year (3,2m change/1,6m add)

    One quarter is usually not so much - but since certain attributes of the statistics can change heavily even when only a smaller part of the data is changed, this is just a rule of thumb.

    We would have to know more about the statistics as they are now and about the data to tell you more.

    regards,

    Lars

    Add comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 08, 2010 at 09:48 AM

    It could be the fact that your thresh hold value for Statistics update has been set over 24% ( like 25 % or 50%).

    So until and unless the variation is more than that thresh hold, it will not update automatically. You can update those from DB20.

    Steps:

    1. Enter the table name

    2. Press refresh

    3. If the diff is huge, press create statistics. ( as this operation may take time run it in quiet period).

    Before doing this you can Activate and Adjust Table indexes from SE14.

    Regards

    Anindya

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on May 04, 2011 at 05:21 PM

    I have a question about running DB statistics for the DSO table from the process chain.There is a way to run DB statistics from the process chain for InfoCubes, DTPs and InfoPackages, but I would like to run it for some DSO tables. Maybe an ABAP program?

    Thanks!

    Add comment
    10|10000 characters needed characters exceeded

    • Hello Lyudmilla,

      up to my knowledge there isn't a report or any other option to schedule the collection of DB optimizer statistics for DSO objects.

      That's likely because once the DSO tables have left their initial empty status and some data is loaded in, then the statistics will

      a) be covered by the standard statistics job whenever necessary

      and

      b) don't make up for a difference concerning execution plans.

      As DSO active data tables usually only have one key index and maybe an custom build index for reporting, the available access paths for the data will very likely dominate the optimizer calculation.

      In plain english: it's not there, because you very likely wouldn't need it.

      If you in fact do feel that you need it, this should be closely analyzed.

      It really shouldn't be necessary to calculcate new statistics everytime you load/delete data; the statistics should provide a modell of the data distribution that is good enough for the optimizer to come up with a good execution plan.

      best regards,

      Lars

  • author's profile photo
    Former Member
    Posted on May 05, 2011 at 02:13 PM

    Thanks a lot.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      RSANAORA might be able to do what you like, depending on release and SP level.

      But as Lars pointed out, think if it really makes sense, esp. if the table is big.

      When this is an initial load it might be a very good option to have stats runs

      at the very early phase of the load.

      Reason: Stats are telling the optimizer the table is empty (i.e. 8 Blocks), so in case of

      a select, you might get full scans of tables or small indexes, and in the early phase these

      are indeed may be "the best" access plans. But after a few 30 secs. the table has the

      first MBs and now things start to go wrong.

      "new" or "fresh" stats make a huge difference, when an object changes from small to midsize.

      If a table is midsize or big, stats can be years old and are still "good" enough to provide the required information.

      Hope thi shelps

      Volker