Re: SQL Access Paths

Thanks for the responses!

Genyphyr, the original problem was poor performance after we upgraded from
V6.1.01 to V6.1.02.  I did not notice a particular cume or BMR that caused
this  - the performance was great in V6.1.01 and poor in V6.1.02.  Yes, I
did apply the latest BMRs from SSA for the program that seemed to be the
slowest.  SSA Helpline was very helpful to me in this matter but ultimately
they could not say why this was happening.  I have given the SQL statement
(prtsqlinf) to the person at SSA that I was working with but I don't think
it sheds much light on the issue.

Myron, after your response I checked the create times.  In production, the
ECHL09 file was created after the ECHL07 file but the same held true in the
test environment where the access path for the ECHL07 file was selected.

I also checked the file sizes as Chick suggested.  Both of the production
files in question are larger (data size and # of records) than the test
files but in both environments the ECHL07 file is larger (data size) than
the ECHL09.

As Wally and Genyphyr suggest, this could very well be an IBM issue.   I'll
contact their support line.  Also, I am upgrading to V5R2 this summer which
may take care of everything.

For now the 'dummy' index has solved the problem with this program.  I just
like to know 'why' in case it happens again.

Thanks again!
Phyllis





----- Original Message -----
From: "Genyphyr Novak" <novakg@xxxxxxxx>
To: "SSA's BPCS ERP System" <bpcs-l@xxxxxxxxxxxx>
Sent: Thursday, May 22, 2003 7:42 AM
Subject: Re: SQL Access Paths


> 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.
>
>
>
> _______________________________________________
> 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.
>
>