Ansicht
Dokumentation
13355 - DB/SQL Problem
RFUMSV00 - Advance Return for Tax on Sales/Purchases rdisp/max_wprun_time - Maximum work process run timeThis documentation is copyright by SAP AG.
DB/SQL Problem
Hi Volker,Thanks for clearing up the issue with raja. I was confused and thought
maybe I did something wrong, thinking my email went beyond our group
somehow, although I don't know how that could have happened.
I appreciate and agree with the great advice for the many things I asked,
but I do have one comment on hyper PTF's. We subscribe to the weekly
"Alert" notice from IBM which is a service where IBM informs customers of
hypers that should go on, or in some cases be removed, from your system.
IBM defines these PTF's as fixes to known critical issues that could
severely impact the performance of your system.
I know most iSeries shops do not put these on weekly, because the risk is
low, but we do for 3 reasons :
(1) When we report a problem to IBM it is easier and quicker for them to
re-create the problem and provide a fix if we are current.
(2) SAP puts a strain on the iSeries I have not seen with other ERP
packages - we are trying to be pro-active.
(3) the quality of the PTF's has improved, including how IBM handles co
and pre-reqs.
I know I create other problems with this strategy as you already pointed
out. I need to look at it from both sides and decide what's best.
Thanks again to you and all others for your kind and patient advice. I am
a long time AS/400 guy but new to SAP and find it both fascinating and
frustrating.
Tom
"Volker Gueldenpfennig" <volker.gueldenpfennigZe...>
03/13/2004 02:01 AM
Please respond to SAP on System i
To: DoNotReply@consolut.eu
cc:
Subject: Re: DB/SQL Problem
Hi Tom,
it's really not necessary to write to the private adress just
because of this crazy raja person!
Either he wants to use this forum, then every mail is "just normal"
or he can just leave this group via an unsubscribe. So, it should
not be our problem right now. As soon as we receive a similar mail
like the one you are referring to, I will remove him manually ...
You are right with the SQL-Pack deletion - especially after creation
of indexes!
I did this at a client for several indexes without restarting SAP
and therefore without deleting the SQL-Packs. The result was the
absolute horror in performance, temp-storage use and so on. As soon
as we deleted the SQL packs, all went back to normal.
The reason was simple - at least in the review afterwards:
The optimizer detected that new indexes were available that made
sense. So, he tried to update the SQL-Packs which was not possible
as they were locked because of other WPs. Then the optimizer decided
to create the SQL packs in QTEMP and used them there. This happened
in nearly every WP!
What do other clients with SQL-Pack deletion ?
I think, installing hypers every week in an SAP system is pretty
unusual. As soon as the system is running stable, I would create a
strategy on implementing PTFs in a regular timeframe - e.g. all 6-12
weeks depending on your needs downtimes etc.
Then I would order ALL PTFs of the current infoapar and apply them
to test and 2-3 weeks later to PRD. You have the following
advantages:
- you might see problems in test already
- you might hear through this group on problems and can stop the
process as you install only 2-3 weeks old PTFs
Your strategy to install hypers every week is not that good in my
eyes. Even when all PTFs should be "independent" except the pre- and
co-reqs this is not the total truth. PTFs in an SAP environment
have "to fit together". This happens with installing all of the
current - or any older - infoapar. One example of a customer a 2-3
years ago:
They were running fine with very old PTFs. Then they installed the
CUM - but just the CUM. This resulted in many SQL errors
(SQL0901, ...) because the old DB fixpack and the very new CUM
didn't fit - even when this should not be the case - nobody can
garanty for the correctness of such a configuration.
What doing with deletion of thew SQL-packs:
You are mostly right, that the first time the report is called the
SQL pack is prepared. Some few SQLs are prepared before already as
they are "common" (in lib r3sidx0000). It is correct, that this is a
bit bad for the responsetime in the first hours, but your restart
every night is very bad as well and all users that come to the
company early should see a worse performance than the ones starting
later - I would guess 1-2 hours should be "suffering".
If you now change your backup strategy to restart SAP max once a
week or even all 2-5 weeks, you should gain performance here. If you
then install all 6 weeks PTFs and delete the SQL packs then, the
users shouldn't complain too much on that monday.
Buffer filling at SAP is something the user initiates - except the
ABAP buffer, that is prefetched. So, your normal workload will
result in useful filled buffers for you. You should have an eye on
swaps in ST02. On iSeries, they should be normally at 0, because you
can increase all buffers very hihg - at least as of 4.6x.
Backup with SAVR3SYS:
the savr3sys has the advantage that it covers everything that might
be of interest on one page. But, you can use SAVLIB with save-while-
active (e.g. 1800s) as well, because SAVR3SYS calls SAVLIB and SAV
internally. If you decide for SAVR3SYS ensure, that you are using
the option *RUN an NOT *DSCDB, because this is not working properly
and normally not necessary either.
Hope this helps,
Volker
==============================
Hi Volker,
In case the email from raja "don't send mail to me" was meant for
me, I'm
sending this directly to you instead of the group. Please reply to
"tom.gainesZu..." if you don't mind.
I found the guidelines for deleting SQL packages in the Implementing
SAP
R/3 Redbook - it fully supported what you said earlier. I think
that's
certainly a part of our problem - we need to delete the packages
more
often. We put hypers on weekly, perform occassional client copies,
create
new indexes when needed, etc. The interesting question is why have
the
errors started only recently? - I'm sure IBM will help us with some
PTF's.
We have been reluctant to delete packages too often because of the
performance hit to our users. It's my understanding that the paths
do not
rebuild until the user initiates the job that needs them and it can
be
very slow with the first initiation only.
So that's my question - how do most companies handle the performance
issue
- simply tell their users it's due to required maintenance?
Thanks also for your comments on the IPL and online backup - it
makes
sense - does the buffer filling and ODP opening happen right after
the IPL
or when the users first sign on?
For the online backup were you referring to SAVR3SYS?
I am very grateful for all your help! - sorry about the length of my
messages.
Tom
--- In DoNotReply@consolut.eu, "Volker Gueldenpfennig"
<volker.gueldenpfennigZe...> wrote:
> Hi Tom,
>
> that sounds really interesting!
> This should be not the case in my eyes if the workload over the
week
> should stay similar - which could be checked in ST03N as well.
>
> I wouldn't argue with the joblogs, because these messages could be
> OK and it would be normal, that as older as the SQL-Packs are and
> the more tables are in ODPs open, the more of these messages might
> occur.
>
> Regards
>
> Volker
>
> --- In DoNotReply@consolut.eu, tom.gainesZu... wrote:
> > Hi Volker,
> >
> > Thanks for the info. I'm checking with our Basis Administrator
on
> the
> > ST03N statistics but from our OS/400 monitoring there was a
> definite
> > gradual increase in CPU utilization with the passing of time
after
> SQL
> > packages are deleted. For example, if packages were deleted on
a
> weekend,
> > Monday and Tuesday would show very few SQL 7917 errors and
typical
> CPU
> > use. By Friday, there would be many errors in our job logs and
an
> > increase of 20 - 30% in CPU utilization.
> >
> > We have not had an increase in number of users.
> >
> > Tom
> >
> >
> >
> >
> >
> > "Volker Gueldenpfennig" <volker.gueldenpfennigZe...>
> > 03/12/2004 12:56 AM
> > Please respond to SAP on System i
> >
> >
> > To: DoNotReply@consolut.eu
> > cc:
> > Subject: Re: DB/SQL Problem
> >
> >
> > Hi Tom,
> >
> > I forgot to ask:
> > Did your responsetime change in ST03N ?
> > Are you having more users on it ?
> > Do you think, that the performance degrades after several days
or
> > weeks and gets recovered with deleting the SQL-Packs ? (because
> this
> > shouldn't be the case)
> >
> > Regards
> >
> > Volker
> >
> > e-mail: volker.gueldenpfennig "at" easymarketplace.de
> > web: www.easymarketplace.de
> >
> > --- In DoNotReply@consolut.eu, tom.gainesZu... wrote:
> > > Hi all,
> > >
> > > From an appreciative long-time viewer, here's my first
posting -
> > >
> > > For about the last 2 months we have been getting SQL
> 7917 "Access
> > Plan Not
> > > Updated" errors on our production system (4.6c). CPU
> utilization
> > has
> > > increased but not enough to hurt us much, although there have
> been
> > > complaints of slow response time on certain jobs. We are
> running
> > an 830
> > > with 3 processors and 10gb memory allocated to PRD. We have
> > deleted the
> > > SQL packages several times which temporarily cleans up the
> problem
> > but the
> > > frequency and impact of the error then builds up again until
we
> > delete the
> > > packages again.
> > >
> > > We opened a PMR with IBM and applied the PTF's they created
for
> > us. We
> > > applied the latest cum at their request. We are also current
on
> > the fixes
> > > recommended on the SAP infoapar. We now have a new error MCH
> 6802
> > > "Literal Values Cannot be Changed" and the SQL 7917 error has
> > resurfaced
> > > again. We also had a MCH 3601 error "Pointer Not Set for
> Location
> > > Referenced" which caused one of our SAP WP's to overload,
> requiring
> > an IPL
> > > to recover. I don't know if the pointer error is related to
the
> > others or
> > > not.
> > >
> > > IBM is doing what they can to get to provide fixes but it
takes
> > time. My
> > > question to the group is has anyone experienced these errors,
> how
> > much
> > > should I worry about them, and what did you do to remedy them.
> > Our
> > > kernel level is old (1313) because we are in a FDA regulated
> > environment
> > > and updates require a special process. Is the old kernel a
> factor
> > and if
> > > so, why? Until now, we have not had performance issues.
> > >
> > > Thanks much for any feedback.
> > >
> > > ____________________________________________________
> > >
> > > Tom Gaines
> > > Data Center Supervisor
> > > Upsher-Smith Laboratories, Inc.
> > >
> > > [Non-text portions of this message have been removed]
> >
> >
> >
> > Have a look to our homepage at: http://www.EasyMarketplace.de
> > DoNotReply@consolut.eu
> >
> >
> > consolut Sponsor
> >
> >
> >
> >
> >
> >
> > consolut Links
> > To visit your group on the web, go to:
> > http://groups.consolut.net/group/SAP on System i/
> >
> > To unsubscribe from this group, send an email to:
> > DoNotReply@consolut.eu
> >
> > Your use of consolut is subject to the consolut Terms of
> Service.
> >
> >
> >
> > [Non-text portions of this message have been removed]
Have a look to our homepage at: http://www.EasyMarketplace.de
DoNotReply@consolut.eu
consolut Sponsor
consolut Links
To visit your group on the web, go to:
http://groups.consolut.net/group/SAP on System i/
To unsubscribe from this group, send an email to:
DoNotReply@consolut.eu
[Non-text portions of this message have been removed]
Durban Tours - Südafrika Safari
CPI1466 during Backup rdisp/max_wprun_time - Maximum work process run time
This documentation is copyright by SAP AG.
Length: 15724 Date: 20240418 Time: 204522 sap01-206 ( 3 ms )
