Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

First Steps - SAP MaxDB Problem Analysis

This page provides information about the first steps in a problem analysis related to SAP MaxDB.
If the SAP MaxDB is suspected to be the cause of a problem with the SAP system, the first place to look for more information is the Database Assistant/DBA Cockpit (DB50/DBACOCKPIT transaction).
As this page just lists some first-steps in error analysis make sure to check the SAP MaxDB Support Guide for a more detailed troubleshooting guideline.  

Table of contents


What needs to be checked always?

Although database problems can have very different causes there are some information that are needed in any case.
The following questions need to be answered:

As the startup part of the knldiag contains the parameter setup of the database it's a good idea to save the whole file to a local text file and attach it to the support message.
Many problems can be analyzed just by checking the knldiag file!

More information about this and the other logfiles can be found here SAP MaxDB log files.

  • Check if the problem fits any of the well-known Connect Problems.
  • If you're a SAP customer perform a SAP notes search. You can do this either via the Search in Knowledge Base, the relevant component for SAP MaxDB is BC-DB-SDB*.
  • If this does not cover the issue open a support message or use SAP Community (Tag: SAP MaxDB) including the information collected during the previous steps.

What exactly is the problem?

How can the issue be observed? Where? All servers/users/transactions?
Try to describe the problem as specific as possible.
Write down the facts you know.
If necessary draw a picture of the problem setup.

What database version is used? On what hardware? What is the parameter setup?

Where to check

Check KnlMsg/KNLDIAG file!

In the startup part of the KnlMsg file all these information can be found.
Look for information that look similar to this example:

Parameterinformation (example)

[...]
RTE      Dump of all kernel parameters start
RTE      Using mode NORMAL for data volume 1
RTE      Using mode NORMAL for data volume 2
RTE      Using mode NORMAL for data volume 3
RTE      Using mode NORMAL for data volume 4
RTE       ADMIN=1
RTE       _AK_DUMP_ALLOWED=YES
RTE       ALLOW_MULTIPLE_SERVERTASK_UKTS=YES
RTE       AUTHENTICATION_ALLOW=
RTE       AUTHENTICATION_DENY=
RTE       AUTOSAVE=1
RTE       AUTO_RECREATE_BAD_INDEXES=NO
RTE       BACKUPRESULT=1
RTE       BACKUP_BLOCK_CNT=64
RTE       _BACKUP_HISTFILE=dbm.knl
RTE       _BACKUP_MED_DEF=dbm.mdf
RTE       CACHE_SIZE=800
RTE       CALLSTACKLEVEL=0
RTE       _CAT_CACHE_MINSIZE=262144
RTE       CAT_CACHE_SUPPLY=1344
RTE       CHECKDATA=1
...
RTE       OPTIMIZE_AGGREGATION=YES
RTE       OPTIMIZE_FETCH_REVERSE=YES
RTE       OPTIMIZE_FIRST_ROWS=YES
RTE       OPTIMIZE_JOIN_HASHTABLE=YES
RTE       OPTIMIZE_JOIN_HASH_MINIMAL_RATIO=1
RTE       OPTIMIZE_JOIN_ONEPHASE=YES
RTE       OPTIMIZE_JOIN_OPERATOR_SORT=YES
RTE       OPTIMIZE_JOIN_OUTER=YES
RTE       OPTIMIZE_JOIN_PARALLEL_MINSIZE=1000000
RTE       OPTIMIZE_JOIN_PARALLEL_SERVERS=0
RTE       OPTIMIZE_MIN_MAX=YES
RTE       OPTIMIZE_OPERATOR_JOIN_COSTFUNC=YES
RTE       OPTIMIZE_OPERATOR_JOIN=YES
RTE       OPTIMIZE_QUAL_ON_INDEX=YES
RTE       OPTIMIZE_QUERYREWRITE=OPERATOR
RTE       OPTIMIZE_STAR_JOIN=YES
RTE       OPTIM_CACHE=NO
RTE       OPTIM_INV_ONLY=YES
RTE       OPTIM_JOIN_FETCH=0
RTE       OPTIM_MAX_MERGE=500
...
RTE       VOLUMENO_BIT_COUNT=8
RTE       _WORKDATA_SIZE=4096
RTE       _WORKSTACK_SIZE=8192
RTE       XP_CONVERTER_REGIONS=0
RTE       XP_DATA_CACHE_RGNS=0
RTE       XP_MAXPAGER=0
RTE      Dump of all kernel parameters done

SAP MaxDB software/platform information (example)

[...]
VERSION  'Kernel    7.6.05   Build 009-123-191-997'
VERSION  'W32/INTEL 7.6.05   Build 009-123-191-997'
INFO     Starting SERVERDB:      'DB760'
INFO              SERVERNODE:    'VIEN60145654A.dhcp.vie.sap.corp'
INFO              Process ID:    2028
INFO              Date:          2008-09-30
INFO              Owner:         'SYSTEM'
INFO              Machine:       'Intel IA32 level 6 revision d06'
INFO              Processors:       1
INFO              Fiber:         'YES'
INFO              Max virtual memory:        2047 MB
INFO              Total physical memory:     1022 MB
INFO              Available physical memory: 337 MB
RTE      Memory allocation not limited
RTE      System page cache usage enabled
TCLUSTER tw;al;ut;100*bup;10*ev,10*gc;ti,100*dw;30000*us,2000*sv;compress
TCLUSTER number of 'BUP':   0
TCLUSTER number of ' DW':  13
TCLUSTER number of ' US':  20
TCLUSTER number of ' SV':  74
TCLUSTER number of ' EV':   2
TCLUSTER number of ' GC':   1
TCLUSTER Total Number of UKT 6
[...]

Are there any error messages?

Where to check

Check KnlMsg/KnlMsgArchive.err files.
Check if there are saved KnlMsg files in DIAGHISTORY folder.

Also check the SAP NetWeaver log files via SAP transactions like SM21, ST22, ST11, etc.

Is the software correctly installed?

Some errors result from incorrectly installed software, so make sure that the database software has been correctly installed and that it wasn't changed afterwards.

Where to check

run sdbverify as root/administrator!

SDBVERIFY can also be used to check client software installations on application servers!
There should be no missing or modified files and no files with changed permissions.

SDBVERIFY output (example)

DB Analyzer 7.7.04.21 32 bit in c:/sapdb/programs
-------------------------------------------------
check files... ok
check dependencies... ok
package data is consistent

[...]
VERIFICATION SUMMARY:
*********************
NUMBER OF INVALID PACKAGES:                   0

NUMBER OF VALID PACKAGES:                     28
NUMBER OF INCONSISTENT PACKAGES:              0

TOTAL NUMBER OF FILES:                        2868
NUMBER OF MISSED FILES:                       0
NUMBER OF MODIFIED FILES:                     0
NUMBER OF FILES WITH MODIFIED PERMISSIONS:    0

back to top


After the general information have been gathered, many critical issues fall in one of the following problem categories:

  • Performance problems
  • Database 'halts'
  • Connection problems

The following sections provide some hints what to check for each kind of problems.

Performance problems

To address performance problems make sure to check the following points: Tuning SAP MaxDB, SAP MaxDB FAQ Performance, HowTo - SAP MaxDB Performance

Database Parameters

Are the parameters setup correctly as recommended by SAP?
You always use the Database Analyzer Check to check your current SAP MaxDB Kernel parameter setting.

Detailed Information can be found in SAP MaxDB Database Parameter Recommendations.

To review and change the current parameter settings you can use the Database Studio, Database Manager CLI (DBMCLI: DBM command param_directgetall) or the Database Assistant/DBA Cockpit (Transactions in CCMS, DBACOCKPIT: Administration – Parameters.  Choose Expert View in the Goto menu to get a list of all parameters at once).

Statistics

Statistics that fit well to the current data set are vital for the SAP MaxDB optimizer to find the optimal data access strategies.

Therefore make sure that the statistics are up to date and that the sample size is correctly set. This can easily be done with the report ZZ_SET_SAMPLE_SIZES from SAP note 808060  Changing UPDATE STATISTICS sample values



Further Information: Troubleshooting, SAP MaxDB HowTo, SAP MaxDB Support Guide