Database Statistics in Infocube

[former_member541575]

hi

how the DB statistics of Info Cube are helpful to improve the query performance.

Regards

Atul

[former_member188080 · Accepted solution]

Hi,

SAP has provided with statistic standard technical content. install the technical content and the reports you can monitor the system activities and performance.

http://help.sap.com/saphelp_nw70/helpdata/EN/b8/609797e55a4c4998cc91fae068e7e2/frameset.htm

The database statistics are used by the system to optimize the query performance.It is recommended that you always update the statistics if you have loaded more than a million new records into the InfoCube since the last updatehttp://help.sap.com/saphelp_nw70/helpdata/en/66/019441b8972e7be10000000a1550b0/frameset.htm

There are a number of ways in which the database system can read data for a query from the underlying database table; some of these are very costly (eg., reading a big database table sequentially and getting only a few records as output), and others are less so (eg. using indexes to read the required data very quickly). Because there may be a number of indexes available on the same table, database statistics can help the database optimizer get the optimal execution path for a query. On the other hand, if the statistics aren't up to date, the database optimiser may decide on the wrong path, costing time and resource. As such, one of the important tasks of a SAP NetWeaver BW administrator is to keep the database statistics up to date.

Thanks and regards

[lbreddemann · Accepted solution]

Hi there!

The important part with handling database optimizer statistics is not to have them up-to-date.

Statistics don't age, they don't loose accuracy over time - even if they are years old.

The key point is to have the statistics match the actual data (and its distribution) properly.

As the optimizer uses these statistic information to make up a model of the tables involved in a query, it's important that this model is rather a good fit to the real data.

For this, loading a certain amount of data is only one indicator of when new statistics should be gathered.

Another one is when important data changes happened.

Did you just updated/activated new master data? Update statistics should be done.

Did you just load the first rows of data from the new quarter? Update statistics can be a very good idea to make sure that the new quarter is no 'outlier' value in the statistics.

Did you just condense several large requests of an infocube? yep - update statistics would be a good idea.

However, as updating statistics can pull a fair amount of system resources, you have to decide on whether you wait for the daily scheduled standard update statistics run or if you want to trigger the statistics collection in your process chain just after you're done with all the data changing activities.

regards,

Lars

[former_member182470]

Hi,

In the Performance Tab of your Cube, we should always maintain Indexes, Aggregate Indexex and DB Statistics regularly. Every day, we have to drop the indexes before the data gets uploaded to Cube and after that we have to build Indexes.

DB Statistics will dramatically improve the Query performance. It will enable us to take proper decisions on the Performance tuning techniques like Aggregates, BI accelerator etc...

Just for your understanding:

Just drop the indexes, Aggregate Indexes of your Cube and try running the report, it will take its own time to display the data. Make a note of this response time.

After this, build indexes and run the report. You will feel the change.......

Regards,

Suman

[Former Member]

Best usage is you can create a query on the Stastics cube with the help of that you can find out the usage of different cubes i.e how may times the cube has been hit for the data etc.

based on those findings you can decide where aggregates is required i.e you can do performance tuning in a better way.

cheers

[Former Member]

Hi,

Based on the current statistics you can take the decision about implementing strategies for query performance e.g. BIA, Aggregates etc.

Regards,

Durgesh