Ansicht
Dokumentation
09106 - Free useful DB monitoring tool
BAL_S_LOG - Application Log: Log header data Vendor Master (General Section)This documentation is copyright by SAP AG.
Free useful DB monitoring tool
We are on 46B support pack 34. I have been trying to get morecurrent statistics for index advised and slowest 50 in ST04 but our
last updates were the end of November. We have not IPL'd since early
November. It is reasonable that our worst SQL was November since we
were doing large data loads into our existing system (1 million
customers), but I wouldn't want to use the recommendations for that
period as being prudent for everyday operation.
After reading this, my question is do you need to IPL before it will
begin analyzing only current data? I tried the reset but then all I
get is no data available even a week later.
I haven't found any notes describing a problem with this area.
Thanks,
Craig
--- In DoNotReply@consolut.eu, "Gueldenpfennig, Volker"
<volker.gueldenpfennigZs...> wrote:
> 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:smarianakisZm...]
> 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: smarianakisZm...
> *************************************
>
>
>
>
>
> 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
ROGBILLS - Synchronize billing plans PERFORM Short Reference
This documentation is copyright by SAP AG.
Length: 7610 Date: 20240420 Time: 081725 sap01-206 ( 4 ms )
