BW slow sometimes, what to check at database level?

[Former Member]

Our BW reports are slow sometimes, and may be running fast some other time (with same parameters). For example, same report ran fast all day yesterday (20-40 seconds). This morning at 9 it took 5 minutes. But at 4 PM it ran in 30 seconds.

Nothing glaring is poping up at the system level for blaming. what should we check at database level? Does someone have any other suggestion what might be the problem?

[Former Member]

Compression and partitioning did the trick.

[markus_doehr2]

I'd check what else is running on the system, use SM50, OS06, SM66 etc.

Additionally I would check on operating system level, what is going on, depending on what you use

(Unix commands)

vmstat

prstat/mpstat

iostat

top/topas

sar

--

Markus

[Former Member]

Shankar,

One thing you could do is to schedule the "update database statistics" jobs atleast once a week. This might help speed up things.

"By running update statistics regularly, you make sure that the database statistics are up-to-date, so improving database performance. The Oracle cost-based optimizer (CBO) uses the statistics to optimize access paths when retrieving data for queries. If the statistics are out-of-date, the CBO might generate inappropriate access paths (such as using the wrong index), resulting in poor performance.

The CBO is a standard part of the SAP system. If statistics are available for a table, the database system uses the cost-based optimizer. Otherwise, it uses the rule-based optimizer." - source help.sap.com

Hope this helps.

Thanks

Shabnam

[lbreddemann]

hmm... "Keep your statistics up-to-date!"

That one gets passed far too often when it comes to query performance. And yes, this is also the wording from the SAP documentation. Well, that does not change anything, it's still wrong!

The truth is: in over 4 years of doing SAP support in the DB-component the vast majority of query performance problems were not able to be solved by updating the statistics.

First of all: the stats don't need to be up-to-date at all. The optimizer doesn't care how old the statistical information is. What's relevant is: does the statistical information give a good representation about the "reality" of data in the database?

If so: keep the stats as they are.

Most of you might have noticed that the DB13 schedules to update the cbo stats decides which tables should get new stats by checking how much data has been changed since the last statistics run. That's for a good reason. Creating your stats too often is a bad thing - it takes Cache, I/O performance and CPU away from processes that might do something meaningfull.

In BW systems the stats are quite current most of the time anyhow. Therefore there's only a small chance to solve your performance issue by simply updating the stats.

And please: forget about the rule-based-optimizer. It's gone. Passed away. Only there for some internal (that is: oracle-internal) backward compatibility issues.

In BW-Systems it's just not useable. With RBO there is no partitioning, no hash-joins no star-transformation.

So just put it into your mental recyclebin.

Sorry Shabnam - this was not against you, but these "myths" of performance tuning really stand in the way to find a correct solution.

KR Lars

[mubarakshabna_asmi]

Thanks Lars. Then tuning parameters seems like the only quick fix. Recently we started looking at the EW reports on our Solman now I question those suggestions too. Because one of them was to update statistics.

Thanks

Shabnam

Message was edited by:

Shabnam

[lbreddemann]

Dear Shankar,

when it's about performance issues on database level the very first (and easy) check should be:

are the db parameters set as recommended by SAP?

If not: correct them and check if the problem persists.

I cannot remember how many performance problems I've seen vanish just by tuning the parameters step by step after the SAP recommendation.

Next step would be: check if the database is on the newest patchset + merge fix.

If not, it may be a good idea to install them.

When these two easy steps did not solve the issue a deeper analysis has to be done. A support message would be appropriate then.

KR Lars

[Former Member]

There can be a lot of things you can check.

But maybe, it is taking longer because a lot of other things are running in the system at 4pm compared to 9am. General system performance can be checked from ST03.

You can also check ST04 oracle sessions and DB01 to see the explain plan and if something is blocking the report at database level.

Or check ST06 to see if memory is runnign low due to high load.

There can be infinite possibilities and you have to check at the time it is running slow.