High Page Latch Wait time on Temp tables

Gautam Sahu

High Page Latch Wait time on Temp tables
Hi Listers,

I am looking into a performance issue where some distributed threads from a
particular user are experiencing high response time. My findings show that
INSERT and SELECT queries on these CGTTs are showing high page latch wait
times. The volume of these transactions in the interval seems to be less
than what I saw in other intervals.

I would like your input on how to proceed from here to find out what caused
the high page latch wait time.

Thank you for your valuable time.

--
Thanks,
Gautam Sahu

Andy Smith

RE: High Page Latch Wait time on Temp tables
(in response to Gautam Sahu)

Gautam

We had a very similar problem a few years ago - we didn't have enough sort work file tablespaces defined and so we were seeing Page Latch suspend on these when we had "too many" concurrent threads in Db2.  We simply defined more tablespaces in the work file database.  If you go down this route, then have a read up on installation parameter WFDBSEP first - it might help you to decide which kind of tablespaces to define.

HTH

Andy

Gautam Sahu

High Page Latch Wait time on Temp tables
(in response to Andy Smith)
Thanks Andy and sorry for the late reply. We had also added more
tablespaces to the work file database but that did not help.
In our case we found that CPU constraints at particular times was causing
the high page latch wait times.

High page latch wait time usually indicates a systemic problem but due to
lack of tools like RMF I was not able to proceed. Got the system
performance guys involved and they helped with the analysis.

Gautam

On Wed, Jan 17, 2018 at 8:11 PM, Andy Smith <[login to unmask email]> wrote:

> Gautam
>
> We had a very similar problem a few years ago - we didn't have enough sort
> work file tablespaces defined and so we were seeing Page Latch suspend on
> these when we had "too many" concurrent threads in Db2. We simply defined
> more tablespaces in the work file database. If you go down this route,
> then have a read up on installation parameter WFDBSEP first - it might help
> you to decide which kind of tablespaces to define.
>
> HTH
>
> Andy
>
> -----End Original Message-----
>



--
Thanks,
Gautam

--
Thanks,
Gautam

Andy Smith

RE: High Page Latch Wait time on Temp tables
(in response to Gautam Sahu)

Gautam, we also had this problem when running at 100%, some rogue Db2 batch workload had dropped into a discretionary service class and was starved of CPU, unable to process latches in a timely manner. We worked with the WLM guys to ensure that all Db2 workloads had a guaranteed minimum CPU. We also tuned the WLM BLocked Workload Support parameters. John Campbell has numerous presentations in this area.