Re: Performance ans Access Plans

In a message dated 7/21/99 6:06:00 AM Central Daylight Time, 
FRANCISCO.ZABALETA@roche.com writes:

> Hello,
>  
>  does anyone experimented already the lock problem while updating the acces 
> plan
>  (SQL Package)?
>  We are AS/400 6.0.04 MM, and we want to split the object libraries for each
>  production enviroment (8 in Europe).
>  This was an SSA recommendation, but after some tests we've seen that the 
> access
>  plan can not be always updated because the optimizer needs an exclusive 
> acces to
>  the object to rewrite the access plan.
Every time I have seen this, the cause is that more than 1 user is in the 
same program at the same time. The best and really the only solution is to 
run the program once when you can be sure no one else is using it, so this 
can be updated. Once it is updated, the message will disappear -- unless 
large changes in data/new access paths make the optimizer reconsider this 
again at a future point in time. 

But despite the ominous sound of the message, even if the plan is not being 
updated per the info message, the optimizer is still choosing and using the 
best access path to the data. It just can't get a lock to record that fact 
inside the Access Plan to use for the next program run. So maybe you are 
slowing down the decision tree for the optimizer a few hundred milliseconds 
or so. Generally this is not giving a major performance impact, unless you 
see this over and over in the same joblog. 

Likely culprits are users who do things like keep up a display program screen 
for entering invoices, orders, reciepts or what have you, all day long and 
don't ever shut it down until the end of the day. You'll have to arrange to 
either have them sign off during a specified period of time (like lunch) and 
have 1 person run the program, or otherwise arrange it so you are sure no one 
else is in the program. If you know which program it is, do a WRKOBJLCK to 
check who has locks on it. 

 
+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---