Re: SQL performance problem on one job only

["Clare Holtham" <clare.holtham@xxxxxxxxxxxxxx>]

Hi Martin,

Yes, Pat is right - the SQL Optimizer looks at how much memory is available 
in the pool where it is running, and divides it by the activity level. 
What's your activity level in that pool? Which pool are you running it in? 
Batch or Interactive? Depending on the result, the optimizer will decide to 
process the data a completely different way, and that could be very 
inefficiently. We don't have much control over what it decides, apart from 
giving it lots of memory and the minimum activity level....

Hope that helps,.

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

----- Original Message ----- 
From: "PatM" <pat@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Sent: Friday, April 28, 2006 4:10 AM
Subject: RE: SQL performance problem on one job only


> Do you have enough memory in the pool that this is running in?  I've seen
> SQL statement run fine when there is plenty of memory.  As soon as memory
> becomes constrained, the query optimizer takes a different execution
> strategy and does table scans.  When this happened, I've increased the
> memory, or decreased the number of active jobs in the pool and things work
> well again.
>
> A base rule that I've found is that memory in the pool divided by the 
> number
> of active jobs gives a close approximation to the amount of memory 
> available
> for the job.  This is very rough, but it works.
>
> PatM
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Bath, Martin
> Sent: Thursday, April 27, 2006 2:02 AM
> To: 'midrange-l@xxxxxxxxxxxx'
> Subject: SQL performance problem on one job only
>
> Hi guys
>
> Anyone help me with this before I scream, please?
> I have one 3rd Party job that contains 30 embedded SQL commands that runs 
> ok
> for a while (runtime between 10 secs and 3 minutes in batch) and then,
> inexplicably, runs for over an hour without ever finishing.
>
> So far, I've
> 1) Checked DBMON for missing logicals. Created some but this seem to make 
> it
> worse.
> 2) Checked for damaged logicals. Replaced them all anyway
> 3) Recompiled program.
> 4) Converted program from OPM to ILE and recompiled it.
> 5) Created copy of program for just that (BPCS) environment.
> 6) Triple checked all PTFs / Cumpacks / Feng Shui
> 7) Checked with supplier / internet.  "The job is functioning as designed"
> 8) Archived data  (it's a General ledger job)
> 9) Amended QAQINI (or whatever it's called) settings.
> 10) Removed some logicals that weren't in other environments - no effect.
> 11) Checked PRTSQLINF - learnt nothing from this.
> 12) Swore a lot
>
> I have found references to problems with progs over 16mb in size and this
> prog is over 16mb.
> Currently the only workaround that works is to have it a scheduled job to
> recompile it every two hours. My gut feeling is that the fact that this
> workaround seems to work proves that the 16mb issue is the crux of the
> problem.  It's not the only prog above 16mb but it does appear to be the
> only one with the problem.
>
> BTW This is on a 720 running V5R2.
>
> I've run out of solutions other that rewrite the prog.
>
> Regards
>
> Martin Bath
> Global IT Group
> Invensys Controls
> Tel:      +44 (0) 1752 724388
> Mobile: +44 (0) 7736 017318
> Fax :    +44 (0) 1752 732455
> **** PLEASE NOTE NEW EMAIL ADDRESS  ****
> Email : martin.bath@xxxxxxxxxxxxxxxxxxxx
> ***********************************************************
>
> CONFIDENTIALITY NOTICE
> This e-mail and any attachments are confidential and also may be 
> privileged.
> If you are not the named recipient, or have otherwise received this
> communication in error, please delete it from your inbox, notify the 
> sender
> immediately, and do not disclose its contents to any other person, use 
> them
> for any purpose, or store or copy them in any medium. Thank you for your
> cooperation.
>
>
>
>
>
> --
> 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.
>
> --
> 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.