Re: SQL Access Paths -- BPCS-L

Hi Phyllis,

What was the original problem you were trying to resolve? Did the either a
cume from IBM or SSA cause a sudden slow-down in performance? Did you also
apply the latest BMRs for that SSA program that was slowest?

What SQL statement was the chosen path being used for? That is a key
question, as all the other analysis will rest upon the SQL statement itself.

It is true that the optimizer will look at the most recently created paths
first, and all things being equal it will use the best match it finds first.
But you are saying all things were not equal, and that when it used the 2nd
path, performance was bad. I have also been told that SQL indexes can be
paged into memory in larger 'chunks' so are sometimes preferred over logical
files, depending on various factors.

Could be:
1. Optimizer is making a mistake (was IBM brought in to review?) since
performance is worse for one result and better for another
2. The SQL statement being executed is not specific enough about what it
wants (ordering or some other specification) to make a difference to the
optimizer for choosing between the 2 paths and the SQL statement could be
improved.

One would result in an IBM PTF, and the other would result in an SSA BMR.

The only way to really find the answer is to find the SQL statement in
question, and have either IBM or SSA support analyze why this has happened.
All else is conjecture.

Thanks,

Genyphyr Novak
SSA GT
----- Original Message -----
From: "Phyllis Field" <pfield@xxxxxxxxxxxx>
To: <BPCS-L@xxxxxxxxxxxx>
Sent: Wednesday, May 21, 2003 5:17 PM
Subject: SQL Access Paths


We recently upgraded from V6.1.01 Mixed Mode to V6.1.02 Mixed Mode  We have
no custom modifications.  Our operating system is V4R5.  We are not
partitioned.

I applied the latest Cumm PTFs and the APAR PTFs that dealt with SQL
performance.  I loaded the upgrade into the Test Environment and we ran
testing on it.  Everything seemed to be fine in the Test Environment so I
promoted to Production.   Started receiving complaints about the performance
in Production so I worked with SSA and got some additional BMRs that I again
loaded to Test.  After testing them, performance in test seemed great so I
then loaded these to Production.  Still received complaints on the
performance in Production but was able to isolate the problem to Order
Inquiry (ORD300) when filtering by Customer PO/Customer/Order.   When I
started looking at open files I discovered that the Test Environment was
using the access path for the logical ECHL07 which is keyed by HCPO, HCUST,
HORD thus the great performance in Test.  However, the Production
Environment was using the access path for the logical ECHL09 which is keyed
by HORD,  HCUST, HSAL, HCPO.  Both logicals exist in the data library for
production and the data library for test.  We (SSA and I ) did performance
monitoring, checked and rechecked and ultimately found no reason for this -
files and programs are at the same level, no mods.   At SSA's suggestion, I
created an index on the ECH file (ECHL99) and sequenced it by HCPO, HCUST,
HORD which is the same as ECHL07.  Production is now using the ECHL99 path
and Test is still using the ECHL07 path and performance is great in both
environments.  After all this information, my question is WHY?  Why was the
Production Environment using the ECHL09 access path instead of the ECHL07?


Phyllis Field, CPIM
IS Manager
Specialty Products Co.
pfield@xxxxxxxxxxxx
www.spcperformance.com
303-772-2103

_______________________________________________
This is the SSA's BPCS ERP System (BPCS-L) mailing list
To post a message email: BPCS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/bpcs-l
or email: BPCS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/bpcs-l.