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):
#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
#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.
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!
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 SQLplus 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