Re: ** Pick Release has poor response time **... -- BPCS-L

Hi Clare, 

Overall it is always good to tune your system and search for new logical
files to build. However, in response to this particular issue raised -
if performance degrades over time and was once acceptable but now is not
then, the work file cleanup is usually a great help. There is just no
need for the 'junk' to stay in those files and it will make the queries
run faster, so best to get it off your disk with proper cleanup
routines. I have seen this repeatedly work like a charm in real customer
experiences with no further need for time consuming or expensive
performance analysis of the system.

Also I must say that in our testing we find that SQL indexes are not
always 'much better' than logicals for all queries and can even cause
some queries to run slower under certain conditions. 

IBM has claimed this to us as well but due to the way that BPCS is
constructed and the queries used in BPCS we don't fit their 'ideal' case
for use of SQL indexes. In fact a sparse index (which is a select/omit
logical file) that is updated immediately when records in the physical
file change (which the ones delivered in BPCS are set to do) beats the
pants off of SQL indexes every time for certain types of queries where
there is a set comparison value for a field on the WHERE clause (ie
WHERE RID = 'PZ'). 

The smaller size of that pre-selected index means that more records
already matching the query will fit into memory at once for the fetch
operations, resulting in less paging and less blocks to fetch. There are
a great deal of queries in BPCS with this type of logic and a great deal
of select/omit logical files built to match those queries. This
advantage of sparse indexes has been borne out in testing on our and
customer's boxes and also in a recent incident where IBM development
didn't think it 'would matter' to put out a PTF on V5R2 and V5R3
suddenly making Select/Omit logicals invalid to use to run SQL queries.
The number of customers who called IBM support about their performance
degradations afterwards and to say that even after building 250 new SQL
indexes the performance was not back to normal does bear out my
understanding of this. IBM quickly put out a corrective test PTF which
we hope/expect will be approved in the next 3 weeks. There is a bulletin
also posted to the SSA OnePoint website explaining that issue. 

Additionally we have seen some clients build SQL indexes and it caused
other unrelated queries to run slower, because the system so heavily
favored SQL indexes over logicals to run an SQL statement in the newer
releases (in the SQL optimizer logic) that we saw several cases where an
SQL index with fewer matching keys was selected over a DDS logical with
better keys to implement a query, resulting in a slow down of the query.


These types of issues are complex and not easy to work via Helplines at
IBM or SSA and it was often difficult to get corrective action from IBM
about the optimizer's error, so our recommendation to BPCS customers is
that if they experience this type of problem they should only build
logical files for tuning queries and this avoids the mixing of the 2
types and the optimizer will make better decisions overall and
performance will be better overall. In the past we didn't have that
recommendation because the weighting of logicals or indexes in the
optimizer was quite even and we didn't see this kind of problem. There
is a Product Bulletin posted to the OnePoint website explaining this
reasoning in detail.

In future if SSA redesigned the BPCS database it is possible that using
only SQL indexes would result in an overall performance improvement but
at this time that is not the case. 

One other further point - the new query optimizer (SQE) which makes the
best use of the enhancements done to the SQL indexes is not used to run
BPCS queries. This is because if there is even one Select/Omit logical
built against a physical it will toss the query back to the old query
optimizer (CQE) to run it. We tested BPCS at the Benchmark Center and
found that indeed 99.9% of BPCS queries ran via CQE. That I am told may
change in a future operating system release if they enhance SQE to work
when a physical is linked to logicals with select/omit, and at that time
our advice to clients about how to best tune their systems may also
change.

Thanks, 

Genyphyr Novak
Senior System Software Engineer
SSA Global R&D


message: 2
date: Fri, 15 Apr 2005 08:41:55 +0100
from: "Clare Holtham" <Clare.Holtham@xxxxxxxxxxxxxx>
subject: Re: [BPCS-L] ** Pick Release has poor response time **...

Hi Genyphyr, Sandy,

The workfile cleanup gives some benefit, but not nearly as much as the
database tuning using indexes. As Dave Trevino pointed out and IBM will
confirm, SQL indexes are much better than logicals....
And the purge programs are just that. Whereas the Archiving (Locksmith)
will
allow old records to be removed to another database in an ordered
manner,
avoiding 'orphaned' data, and allowing users to still look at the old
data
if they want to.
Sometimes it is not the obvious files that are causing slowdown
problems.
Files like the ZPD and CMF are accessed constantly, and don't forget
that
SQL will read all the 'deleted' records every time too.
It is worth using DSPFD *Mbr to list the files that have high increments
and
high numbers of 'deleted' records, and fixing them with CHGPF and
RGZPFM.
I would recommend a combination of this housekeeping plus the database
tuning and archiving, and of course the workfile cleanup is an essential
part of that, and should probably be incorporated in overnight
processing.
We and others (e.g. Unbeaten Path) offer this service, not including the
Archiving,  as a one or two day exercise which should be considered as
Preventive Maintenance on an annual basis. There's nothing like having
happy
users!

cheers,

Clare

Clare Holtham
Director, Small Blue Ltd - Archiving for BPCS
Web: www.smallblue.co.uk
IBM Certified iSeries Systems Professional
Email: Clare.Holtham@xxxxxxxxxxxxxxx

*************************************