RE: SQL PLan Cache

The optimizer will see that the environment changed from last time, and reoptimize the first run of each query. No way to stop that.


--
Jim Oberholtzer
Agile Technology Architects

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of DrFranken
Sent: Tuesday, December 18, 2018 11:28 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: SQL PLan Cache

Just the iASP would be flashed. Vary off, End flash copy, Restart flash copy, Vary on.

Because the iASP is a separate database it makes me think the system may clear all the entries for that iASP in this sequence.

You are correct that an IPL would clear it but we would not be doing an IPL. However if I understand everything correctly we are 'sorta' doing an IPL of the database on the iASP.

- 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/18/2018 12:23 PM, Roberto José Etcheverry Romero wrote:

Dr, when you say "re flash" do you mean an iASP or an entire "I flash
the LPAR complete, load source and all" Flashcopy?. If iASP I have NO
idea where the plan cache lives and worse, what would happen if the
underlying data changes and the plan cache isn't aware of it (maybe
invalidate the cache when doing a varyoff?), I assume that an entire
Flashcopy would bring the plan cache from the LPAR but, doesn't the plan cache expire on IPL?

Roberto


On Tue, Dec 18, 2018 at 2:15 PM DrFranken <midrange@xxxxxxxxxxxx> wrote:

Suppose I have a Storwize full of SSDs. I have all my data in iASP
because I use PowerHA. I already do flash copies for other reasons
such as Backup and training. However there are a group of folks that
do ad-hoc queries all day from windows servers and they often submit
a half a kabilion of these things all at once. THEN they have the
nerve to complain that they get different totals when they compare.
Well querying the data at different points in the day will do that on live data, DUH!

Flash copy to the rescue I believe. Now they can query last night's
data. Also it nicely boxes them into x CPU and y Memory. And while
the I/O itself is the same physical disks that's not been a problem
for performance.

QUESTION though is what happens to the plan cache for all their
queries when I re-flash the disk. Does it get flushed in a flash?
That would sorta suck.

--

- 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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://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: https://amazon.midrange.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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at https://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: https://amazon.midrange.com