MaxDB: Server performance

[Former Member]

Hallo,

I have a powerfull Server with 16 CPUs and 72 GB Ram but i couldnt bring MaxDB Kernel to use it perfectly, i have somehere a bottleneck so that a 5 joins SQL Statment takes long time, the whole server is reserved for MAXDB!

here are some Config. Var. maybe you could help me, thanks in Advance.

  INSTANCE_TYPE OLTP Type of database instance

  MCOD NO Multiple Components One Database

  MAXLOGVOLUMES 2 Maximum number of log volumes, mirrored log volumes are not included

  MAXDATAVOLUMES 64 Maximum number of data volumes (including reserve)

  MAXBACKUPDEVS 2 Maximum number of backup devices used in parallel for backup/recovery

  LOG_MIRRORED NO  

  MAXCPU 16 Number of CPU's used for distributing the main load generated by the user tasks

  MAXUSERTASKS 1500 Maximum number of simultaneously active users (database sessions)

  MAXLOCKS 120040 Maximum number of current and requested row and table locks

  CACHE_SIZE 6400000 Size of the data cache and converter in pages

  RUNDIRECTORY /var/opt/sdb/data/wrk/PDB Path where context and diagnosis information is stored

  OPMSG1 /dev/console Name of the path to which priority 1 messages will be sent

  OPMSG2 /dev/null Name of the path to which priority 2 messages will be sent

  LOG_SEGMENT_SIZE 2133 Size of a log segment in pages

regards

[Former Member]

Hi,

to get an idea where the bottleneck in your database is, please start database Analyzer. If you are a SAP customer you can use SAP note 1423935 for detailes about Database Analyzer.

in an SAP environment DB Analyzer is started via transaction DBACockpit. The bottleneck Analysis gives an overview about the bottlenecks.

Please check that the parameters are set correctly related to your database release. You can use note 1111426. Download the attachment and let the parameter check run via DB-Analyzer as described in the note. Set the parameter as recommended.

Takle care that update statistics is running frequently.

If you have performance problems related to several SQL statements the following information is necessary:

Explain output of the SQL statement, the SQl statement, all table - and index definitions the optimizer statistics of the tables.

Regards, Christiane

[Former Member]

Hi Christiane,

thank alot for your reply.

unfortunately we are not a SAP customer.

Our x-CIO was an x-SAP Employee! he brought MaxDB to us and then let us alone with it.

i have started the Database Analyzer but coulndt find anything unusuall, there is 3 Warrings but not more.

thanks alot

regards

[Former Member]

Hi ,

so if the database Analyzer is showing only a few warnings the system configuration is ok. But you are talking abotu a join which does not perform best. Sio first - run update Statistics on the tables involved into the join. Update Statistics table and update statistic column on all indexed columns.

Then please insert the explain plan of the join -> only write the key word explain before the Join Statement. Send the SQL of the join as well. Add the table definition of the tables involved into the join and the index definition of tables.

If you want to learn more about the MaxDb Optimizer please check our Expert Session:

•   Session 16: SAP MaxDB SQL Query Optimization (Part 1)  -> Single Table Optimization
• Session 16: SAP MaxDB SQL Query Optimization (Part 2) -> Join Optimization

There are slides avaialble and recording of the sessions.

You'll find the sessions using the following link: http://maxdb.sap.com/training/

Regards, Christiane