Re: Shared pool vs Private Pool for SQL

Doc...

My first thought is that the 50% more data has outgrown the memory pool....

But I'd expect to see more disk I/O...what do the fault rates look like?

My understanding, private pools are great for ensuring a given level of
performance, but the cost is extra admin to change them if the workload
changes. Adding 50% more data, I'd expect to have to add memory to keep
the same level of performance.

Charles

On Thu, Dec 8, 2016 at 12:19 PM, DrFranken <midrange@xxxxxxxxxxxx> wrote:

Have a customer running some pretty serious SQL queries from MS products
to i. Those have recently begun taking Waaaaaaaaaaaaaaaaaay longer than
they used to. Like from 20 minutes last month end to 24+ Hours this time.
Only difference is about 50% more data. Yeah that's a lot but it shouldn't
make this level of difference!!!

Power8. 6 Cores. 384GB Memory. SSD Storage on Storwize. i 7.1. Current
PTFs.

The queries in question (QZDASOINIT) run in a private memory pool to a
subsystem NOT in a shared pool. Thus the paging option (*CALC *FIXED) is
forced to *FIXED.

Anyone seen where this makes a significant difference moving to a shared
pool with *CALC? Given the fabulously low I/O I'm thinking not.

A bit more on the environment:

The pool has 15GB memory and just one query at a time runs there. For
EXTENDED periods of time the I/O numbers for the pool from WRKSYSSTS are
.0 .0 .0 .0 .0 .0 .0. I/Os in the job are from WRKACTJOB might be
4 or 5 per second. There are four active threads each effectively consuming
a full processor and all are running DbopThreadMain__FP14DbopThreadParm.
The jobs burn through 250,000 CPU seconds in 24 hours.

We did bring up visual explain and yeah there is a lot going on there. The
thing could make a cool "FatHead" decal and would need to be that big to be
readable on a wall. But the same queries ran last month so....

IBM's only advice is 'you need to build the indices the query optimizer is
recommending.' There are 9 it recommends with a total build time of about
45 minutes so not insignificant. Tables are in the 4M rows plus in size so
'big' but certainly not 'huge'. We are working on building these to shop
standard naming. Hoping they really do help!

--

- Larry "DrFranken" Bolhuis

www.Frankeni.com
www.iDevCloud.com - Personal Development IBM i timeshare service.
www.iInTheCloud.com - Commercial IBM i Cloud Hosting.
--
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.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD