Ansicht
Dokumentation

09017 - Free useful DB monitoring tool

09017 - Free useful DB monitoring tool

RFUMSV00 - Advance Return for Tax on Sales/Purchases   ROGBILLS - Synchronize billing plans  
This documentation is copyright by SAP AG.
SAP E-Book

Free useful DB monitoring tool

Hi,

you have only to change the profile parameter on each appl-server, if you are
not running 4.6.
On 4.6 this would be default.

Please do NOT start STRDBMON, because this would slow down your system.

Regards

Volker

-----Original Message-----
From: ÓìáñéáííÜêçò ÃéÜííçò [mailto:smarianakisZminoan.gr]
Sent: Mittwoch, 22. Januar 2003 13:09
To: Gueldenpfennig, Volker; DoNotReply@consolut.eu
Subject: Re: Free useful DB monitoring tool


Hi Volker,

Do we need to start the DBMON from AS/400 command line (strdbmon) or just
change the parameter as4/dbmon/enable ?

Regards,



*************************************
Yannis Smarianakis
Systems & Network Dept.
Minoan Lines
tel : +302810399892
e-Mail: smarianakisZminoan.gr
*************************************





Gueldenpfennig, Volker [22/1/2003 7:14 ðì]:
Hi all,

Running the DB-Monitor tool can be helpful in every SAP system in order to find
the most expensive SQL-statements in an SAP environment. Experience with the
new DB-Monitor shows that it does not noticably slow performance of the SAP
system. Customers that have activated the new DB-Monitor have not
seen the difference in the SAP response times. Therefore, we recommend that all
customers activate the DB-Monitor.

When R/3 was originally delivered on iSeries, the DB-Monitor (transaction ST04)
collected data via the STRDBMON command. This command collected information
about every query that was run, which generated a very large output file, so it
could not be turned on for very long in a productive system. It
also slowed down performance. In recent releases, a new collection agent was
developed by IBM which uses a small amount of memory instead of an output file.
The new agent keeps statistics about the most expensive statements, and
discards the statistics that are of little
interest. Since it is memory resident, it is able to do this quickly.

The new DB-monitor ABAP that works with this IBM agent is available as of 4.5B
from SAP. See note 321729 for details about enhancements delivered in
support-packs. All customers below 4.5 can apply the new DB-monitor as
described in note 135369. Please also get the latest PTFs from the info apar
to make sure you have the latest IBM agent.
Attention: Customers on 3.1I and 4.0B: Please do NOT activate the old file
based DB-Monitor for general use!!! Please install the new one instead!

The activation happens with the following profile parameter:
as4/dbmon/enable = 1
(default in 4.6x)

As soon as this is active you can have a look to the transaction ST04. Then you
push the button "Detail analysis menu" and then the button "50 slowest queries".
Then you see the in total most expensive SQL-statements. You should ignore the
line with QXDAEDRS, because this is the commit/rollback time. Then you can
ignore all SQLs where the average time is below 10ms, because it is unlikely,
that you can tune them. But, if you find some in the top rows, that
take long (e.g. >500ms), you should have a look to the SQL.

The information can help you determine which ones consistently take the longest
times. It also tells you how many times each query is performed, giving you an
idea of which queries are the most important to try to improve. A very common
cause of a slow query is that the DB optimizer chooses to scan
the entire table rather than using an index. The optimizer usually chooses
table scan when either - no index exists over the file, or no index contains
enough relevant fields for the query. SAP delivers some indexes, that are
necessary for nearly all customer environments. But, as every customer
has a different set of modules in use and many user developed ABAPs or
modifications, the index needs of a customer may be different than those that
come with the SAP default delivery.

Adding an index should be done in a selective manner, since each new index on a
table increases the update/insert runtime on this table. If it does not seem to
get used for the query you were trying to help, you are best served to remove
it.

DB-Monitor data is deleted when you IPL the DB-Server. Keeping the server up as
long as possible will help you find the worst queries encountered by your
system.

I know, that this all is not perfect, but at least it might point you into the
right direction in order to reduce the load on your DB-Server quite a lot!
If you are not able to analyze this data yourself, you can order a service for
this from SAP.

Keep always in mind, that you get power here out of your iSeries, that is "just
for free", because it may help you postpone your next hw upgrade and will be in
most cases a big benefit for all of your users.

Regards

Volker

Have a look to our homepage at: http://www.consolut.net
DoNotReply@consolut.eu

Your use of consolut is subject to http://www.consolut.net



Durban Tours - Südafrika Safari

ABAP Short Reference   General Data in Customer Master  
This documentation is copyright by SAP AG.

Length: 6180 Date: 20240329 Time: 122407     sap01-206 ( 3 ms )