Performance issue in program

Rakesh Adhikarapu

Performance issue in program

Hello Team,

We have a table A with 500 Mn data and 5 indexes (Now we moved complete table data into another table B) and emptied out table A. No structural changes have been made. After a week we have data in table A about 32 Mn and ran Runstats and bound the packages. Performance on one program ABC (other 2 programs are good) degraded since emptying data (LPFETCH is showing 0) instead of 30-40 seconds it is taking more than 25 minutes.. I Need your help on this.

BEFORE : ELAPSED TIME: 00:00:32.59     TOTAL CPU TIME: 00:00:31.59

AFTER:  ELAPSED TIME: 00:21:46.69     TOTAL CPU TIME: 00:21:04.13

From Detector:

BEFORE:

Buffer Manager Activity                                            

 GETPAGE          -> 2385776             GETPFAIL         -> 0     

 SYNCREAD         -> 87                  SPFETCH          -> 0     

 LPFETCH          -> 63424               DYNPFETCH        -> 45078 

 PFPAGES          -> 59                  PAGEUPDT         -> 319213

 IMWRITE          -> 0                   REOPT            -> 0     

 

AFTER:

Buffer Manager Activity                                             

 GETPAGE          -> 155928682           GETPFAIL         -> 0      

 SYNCREAD         -> 3                   SPFETCH          -> 0      

 LPFETCH          -> 0                   DYNPFETCH        -> 7053823

 PFPAGES          -> 105                 PAGEUPDT         -> 319883 

 IMWRITE          -> 0                   REOPT            -> 0       

 

Thanks,

Rakeysh

Joe Geller

RE: Performance issue in program
(in response to Rakesh Adhikarapu)

With that increase in GETPAGES, it is likely that you have a different access path than you had before.  But it is hard to help unless we know what those access paths are.  Have you done an Explain?  Please share with us the data from the PLAN_TABLE.  We will also need the Index definitions, the statistics on the table and the indexes, and the query (if you need to change the column names in order to share with us, that is ok).

Joe

In Reply to Rakesh Adhikarapu:

Hello Team,

We have a table A with 500 Mn data and 5 indexes (Now we moved complete table data into another table B) and emptied out table A. No structural changes have been made. After a week we have data in table A about 32 Mn and ran Runstats and bound the packages. Performance on one program ABC (other 2 programs are good) degraded since emptying data (LPFETCH is showing 0) instead of 30-40 seconds it is taking more than 25 minutes.. I Need your help on this.

BEFORE : ELAPSED TIME: 00:00:32.59     TOTAL CPU TIME: 00:00:31.59

AFTER:  ELAPSED TIME: 00:21:46.69     TOTAL CPU TIME: 00:21:04.13

From Detector:

BEFORE:

Buffer Manager Activity                                            

 GETPAGE          -> 2385776             GETPFAIL         -> 0     

 SYNCREAD         -> 87                  SPFETCH          -> 0     

 LPFETCH          -> 63424               DYNPFETCH        -> 45078 

 PFPAGES          -> 59                  PAGEUPDT         -> 319213

 IMWRITE          -> 0                   REOPT            -> 0     

 

AFTER:

Buffer Manager Activity                                             

 GETPAGE          -> 155928682           GETPFAIL         -> 0      

 SYNCREAD         -> 3                   SPFETCH          -> 0      

 LPFETCH          -> 0                   DYNPFETCH        -> 7053823

 PFPAGES          -> 105                 PAGEUPDT         -> 319883 

 IMWRITE          -> 0                   REOPT            -> 0       

 

Thanks,

Rakeysh

Isaac Yassin

Performance issue in program
(in response to Joe Geller)
Hi,
Was it compressed before? Is it compressed now (REORG with building a
dictionary)?



*Isaac Yassin IBM Gold Consultant*
*IBM Champion for Analytics*
IBM Certified Solution Expert
IBM Certified Database Administrator - DB2 for z/OS 9,10,11
IBM Certified System Administrator - DB2 10, 11 for z/OS
IBM Certified Database Administrator - DB2 LUW 10.1
IBM Certified Specialist - PureData System for Analytics v7.1
IDUG Israel RUG co-Chair
IDUG GMC



On Wed, Dec 6, 2017 at 5:38 AM, Joe Geller <[login to unmask email]> wrote:

> With that increase in GETPAGES, it is likely that you have a different
> access path than you had before. But it is hard to help unless we know
> what those access paths are. Have you done an Explain? Please share with
> us the data from the PLAN_TABLE. We will also need the Index definitions,
> the statistics on the table and the indexes, and the query (if you need to
> change the column names in order to share with us, that is ok).
>
> Joe
>
> In Reply to Rakesh Adhikarapu:
>
> Hello Team,
>
> We have a table A with 500 Mn data and 5 indexes (Now we moved complete
> table data into another table B) and emptied out table A. No structural
> changes have been made. After a week we have data in table A about 32 Mn
> and ran Runstats and bound the packages. Performance on one program ABC
> (other 2 programs are good) degraded since emptying data (LPFETCH is
> showing 0) instead of 30-40 seconds it is taking more than 25 minutes.. I
> Need your help on this.
>
> BEFORE : ELAPSED TIME: 00:00:32.59 TOTAL CPU TIME: 00:00:31.59
>
> AFTER: ELAPSED TIME: 00:21:46.69 TOTAL CPU TIME: 00:21:04.13
>
> From Detector:
>
> BEFORE:
>
> Buffer Manager Activity
>
> GETPAGE -> 2385776 GETPFAIL -> 0
>
> SYNCREAD -> 87 SPFETCH -> 0
>
> LPFETCH -> 63424 DYNPFETCH -> 45078
>
> PFPAGES -> 59 PAGEUPDT -> 319213
>
> IMWRITE -> 0 REOPT -> 0
>
>
>
> AFTER:
>
> Buffer Manager Activity
>
> GETPAGE -> 155928682 GETPFAIL -> 0
>
> SYNCREAD -> 3 SPFETCH -> 0
>
> LPFETCH -> 0 DYNPFETCH -> 7053823
>
> PFPAGES -> 105 PAGEUPDT -> 319883
>
> IMWRITE -> 0 REOPT -> 0
>
>
>
> Thanks,
>
> Rakeysh
>
>
> -----End Original Message-----
>

Rakesh Adhikarapu

RE: Performance issue in program
(in response to Isaac Yassin)

Hello Issac,

The table B is having COMPRESS YES. But Table A which I'm having performance issue now was defined with COMPRESS NO, this has been altered on 25th Nov and not sure if it was earlier defined with YES before.

Rakeysh

Isaac Yassin

Performance issue in program
(in response to Rakesh Adhikarapu)
Changing to compress yes needs to be followed by a reorg to materialize the
change and build the compression dictionary



*Isaac Yassin IBM Gold Consultant*
*IBM Champion for Analytics*
IBM Certified Solution Expert
IBM Certified Database Administrator - DB2 for z/OS 9,10,11
IBM Certified System Administrator - DB2 10, 11 for z/OS
IBM Certified Database Administrator - DB2 LUW 10.1
IBM Certified Specialist - PureData System for Analytics v7.1
IDUG Israel RUG co-Chair
IDUG GMC



On Wed, Dec 6, 2017 at 9:37 AM, Rakesh Adhikarapu <[login to unmask email]>
wrote:

> Hello Issac,
>
> The table B is having COMPRESS YES. But Table A which I'm having
> performance issue now was defined with COMPRESS NO, this has been altered
> on 25th Nov and not sure if it was earlier defined with YES before.
>
> Rakeysh
>
> -----End Original Message-----
>

Rakesh Adhikarapu

RE: Performance issue in program
(in response to Isaac Yassin)

Hello Issac,

I have found that the table A (performance issue) was defined with COMPRESS NO, both earlier and now. Previous Alter was done inorder to increase MAXPARTITIONS to 256.

Rakeysh

Edited By:
Rakesh Adhikarapu[Organization Members] @ Dec 06, 2017 - 03:00 PM (Asia/Calcutta)

Isaac Yassin

Performance issue in program
(in response to Rakesh Adhikarapu)
Did you try reorg + stats or only stats?
What's the statistical info? before and after? (both table & indexes).
Access paths changes?



*Isaac Yassin IBM Gold Consultant*
*IBM Champion for Analytics*
IBM Certified Solution Expert
IBM Certified Database Administrator - DB2 for z/OS 9,10,11
IBM Certified System Administrator - DB2 10, 11 for z/OS
IBM Certified Database Administrator - DB2 LUW 10.1
IBM Certified Specialist - PureData System for Analytics v7.1
IDUG Israel RUG co-Chair
IDUG GMC



On Wed, Dec 6, 2017 at 11:29 AM, Rakesh Adhikarapu <[login to unmask email]>
wrote:

> Hello Issac,
>
> I have found that the table A (performance issue) was defined with
> COMPRESS NO, both earlier and now.
>
> Rakeysh
>
> -----End Original Message-----
>

Rakesh Adhikarapu

RE: Performance issue in program
(in response to Isaac Yassin)

I have bound the package with my name as collection and ran explain, it is pointing to two multi indexes (And I dont see plan entry for Prod package).

I have done Drop impact analysis on these indexes, it is pointing to my package and not production package.

I believed the Prod package is not picking index(It could not have correct stats before)

I have rebound the Prod package now and it ran in less than 40 seconds.

 

INDB2_TIME       INDB2_CPU  

00:36.393392    00:31.796665

 

Thanks Issac and Joe,

Rakeysh