RE: Shared pool vs Private Pool for SQL -- MIDRANGE-L

I've lost track of whether temporary indexes the system has created survive
a Save 21 at whatever ptf and TR level you are at with 7.1
That could have a dramatic impact as DB2 would need to build new temp
indexes.
Do note that if you build the suggested indexes, the first running of this
process would/should reanalyze and perhaps recommend more.
The analysis never ends..
Btw - no one has implemented any tool for database read auditing,
encrypt/decrypt or anything like that? Any the Win query and connection is
read only with no major contention for the files? Obvious questions but got
to ask
Jim Franz

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
DrFranken
Sent: Thursday, December 08, 2016 10:35 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Shared pool vs Private Pool for SQL

We do know it's using SQE it reports that.

The queries do come from outside the i as you mention yours do. A single
query is 20K in a text document. We cannot with absolute certainty say they
haven't changed the queries but the analysts swear they didn't.

There were no PTFs loaded since last month. We did go restricted and take a
full SAVE 21 and then bring it back up. Not sure if anything would change
based on ending and restarting subsystems like that. I certainly would not
expect any.

One other thing is that last month-end was the first on Power8. It was MUCH
faster than on the Power7 before it. The Power7 system had internal disk but
most were SSD and had 3 cores vs 6 and about 1/4TB memory vs 3/8TB. This is
the second month end on Power8 and the again the primary change was about
50% additional data added due to a new customer.

We are in the process of building the requested indices as well as a few
others recommended.

- Larry "DrFranken" Bolhuis

www.Frankeni.com
www.iDevCloud.com - Personal Development IBM i timeshare service.
www.iInTheCloud.com - Commercial IBM i Cloud Hosting.

On 12/8/2016 8:50 PM, midrange wrote:

Indexes can make a big difference, but the 20 minutes to 24 hrs
indicates it is likely something else...
Our database is a fair amount larger on a Power 7+ single core and
they pull almost the entire database into a warehouse In about an hour
every night..
In our shop the win team is sometimes changing the queries so we have
to watch the indexes.
Certain changes could cause it to run the old "classic" engine which
is most likely much slower (but not always).
The analyzer can tell you if old versus newer SQE.

DB2 changes via ptf have bitten us couple times where it would spin
for hours, on a very complex query - we sent it to the lab And they
said it broke theirs as well (it was one query pages long ...) The win
guys rewrote it after I pointed out how bad some of it was.
I assume the network connections and packet sizes and all that did not
change.
A big possibility is that the win team changed or upgraded the data
connection product or parms - I have seen dramatic changes.
We do build all indexes as logical files so the Power I team has view
of the source code (still running Hawkeye to track all the database and

programs.

Jim Franz

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
DrFranken
Sent: Thursday, December 08, 2016 8:14 PM
To: Midrange Systems Technical Discussion
Subject: Re: Shared pool vs Private Pool for SQL

Faults? None. Paging? None. Those were all those .0's from WrkSysSts!

That's the frustrating part. The thing is doing almost no I/O at all
but burning four Power8 processors at 100% each. Basically for 24 Hours.

We are building the indices and hope they make the difference we need!!

- Larry "DrFranken" Bolhuis

www.Frankeni.com
www.iDevCloud.com - Personal Development IBM i timeshare service.
www.iInTheCloud.com - Commercial IBM i Cloud Hosting.

On 12/8/2016 2:44 PM, Charles Wilt wrote:

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

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