Parameters. parameters, parameters … the mantra of db performance troubleshooting

Database performance problems are one of the more complex tasks that brighten the day of a database software supporter.

Each and every single day since I’m doing support for Oracle and MaxDB there had been messages, in which customers complained about bad database performance.

Statements ‘suddenly’ took ages where they needed only seconds before – although the DBA “did not change anything”.

Parameters need to be maintained

In fact, not changing anything might have been the cause of the problem. With new versions of the database software – whether it’s MaxDB or Oracle – new recommendations for database parameters come up and these should be implemented.

Unfortunately, there are not only three or four parameters but somewhat hundreds of them. Which parameter should you set? Which one is the important one, that determines if the database is slow or fast?

Fortunately, it’s not necessary that you actually know all the parameters and their meanings.
All you’ve to do is to follow the parameter recommendations that are available these notes (take the one for your database software version):

Oracle

#124361 – Oracle parameterization (R/3 >= 4.x, Oracle 8.x/9.x)
#830576 – Parameter recommendations for Oracle 10g
#632556 – Oracle 9.2.0.* database parameterization for BW

MaxDB

#814704 – MaxDB Version 7.6 parameter settings for OLTP/BW
#767635 – MaxDB Version 7.5 parameter settings for OLTP/BW
#1004886 – MaxDB Version 7.7 parameter recommendations
#901377 – MaxDB 7.5/7.6 Parameter recommendations for BW systems

Now how to check the parameters?

Ok, looking into each one of these notes might be a bit scary as each of them includes tens of parameters, most together with some reference to another note and explanations about what the parameter is for or against.
This is “too much information” for a quick check.

So what are you’re going to do? Compare parameters one by one without understanding them? That’ll be a kind of monkey-job.

Luckily there are options to automate this.
Usually, such checks would have been build into the Netweaver Basis (for BW actually had this…) but as the parameter recommendations are likely to be changed rather often, these built-in checks would be nearly always wrong itself.

Thus there are semi-automatic check options for MaxDB and Oracle that are maintained whenever the parameter recommendations are modified.

Oracle

For Oracle, there is a SQL-Script available, which you find as an attachment to note
#0830576 – Parameter recommendations for Oracle 10g
Although the note is for Oracle 10g systems, the attached scripts do work as well on 9i systems.
You can use report “RSORADJV” to execute it or the SQL-Query-functionality in the DBACOCKPIT.
Of course, you can also use it within SQL*plus – but then you’ll need to set formatting parameters (see below).

The output delivers to you how the parameters are currently set, how they should be set and references the SAP notes for this. Quite handy!

MaxDB

For MaxDB, there is a different option available, described in note
#1111426 – Parameter check for liveCache/MaxDB instances
Basically, it’s a special parameter file for the DBAnalyzer.
Just start the dbanalyzer with this parameter file and you get all wrong set parameters and some additional ‘health checks’.

It’s really easy once you know…

As you see, it’s not black magic to make sure that the database parameters are set OK. You won’t believe how many performance-related support messages are really solved by this – it’s a lot!

)
When you like to run the parameter check script in SQL
plus you need to setup some formatting options.
The following worked for me (just copy&paste the line before running the check script):

set linesize 300
set pagesize 1000
COLUMN name    FORMAT   a40
COLUMN set     FORMAT   a8
COLUMN remark    FORMAT   a60
COLUMN recommendation  FORMAT   a70
COLUMN is_value   FORMAT   a50
COLUMN should_be_value  FORMAT   a50

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.