Re: Performance adjustment and query optimization

[Jack Kingsley <iseriesflorida@xxxxxxxxx>]

Rob, as you research this, I wonder how much impact the settings of your
QAQQINI might have in regards to this.

On Thu, Dec 1, 2016 at 7:38 AM, Rob Berendt <rob@xxxxxxxxx> wrote:

> I believe I've read somewhere that having automatic performance adjustment
> on can have an adverse effect on query optimization.  Because when the
> query optimizer runs it ignores the previous optimization analysis and
> reanalyzes if the memory changes because the memory available is taken
> into the calculation.  So, if you have like a million sql statements in a
> loop (canned vendor package) each time it loops it may have to reanalyze
> because the memory changes, well maybe not every loop because the adjuster
> is not quite that fast to recalculate.
>
> I have a PMR open with IBM and they are looking at Job Watcher data and
> now have moved on into SQL plan cache but while waiting on that I wanted
> to explore other things.
>
> Also, on a closely related note, does having your paging set *CALC vs
> *FIXED affect query optimization?
> See WRKSYSSTS and WRKSHRPOOL
>
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>           Garrett, IN 46738
> Ship to:  Dock 108
>           6928N 400E
>           Kendallville, IN 46755
> http://www.dekko.com
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.