Skip to Content

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 inlcudes tens of paramters, 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 build-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 does 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 paramters are currently set, how they should be set and references the SAP notes for this. Quite handy!

MaxDB

For MaxDB theres a different option available, decsribed 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 paramter 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
To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Jim Spath
    I run the SQL script from SAP note 830576 with these SQL*Plus commands prepended:

    set  markup html on
    spool ../public_html/SCT_O10_parameters.html

    … where the spool file goes to a directory serviced by an httpd daemon on UNIX.  This output file is nicely formatted for others to browse on the web.  I wrote code to compare parameters between databases; perhaps I can be persuaded to post this to the code snippet gallery wiki.  //jim

    (0) 

Leave a Reply