Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

performance issue in a DB2 program
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Sat Nov 14, 2009 8:41 am    Post subject: performance issue in a DB2 program
Reply with quote

A code running in production was using a cursor which was accessing four tables. This used to consume on an average 2 seconds of CPU time.

A new select query was added to the code and this change was implemented using a ChangeMan package.

The code kept running for more than 1000 CPU time.

We thought that the new Select query was causing this performance issue and hence we rolled back the changes.

But when the code was again run in production it kept consuming more than 1000 CPU time (Even after the new select query was removed).

what can be a reason for this.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Nov 14, 2009 9:29 am    Post subject:
Reply with quote

Hello,

How long had the original code been running?

How often are RUNSTATS created?

Were there any changes to any of the table definitions - especially in the definition of keys?

Has anyone looked at the results of an EXPLAIN of the old process and the new one.

How many rows are in the tables and how many of these should be used by this process?

It sounds as though the optimizer is now choosing a worse access method. Or possibly there has been a major difference in the volume and type of data being processed.

These questions are for thought rather than actually needing the answers posted.
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Sun Nov 15, 2009 11:01 am    Post subject:
Reply with quote

i could answer few of your questions.
the original code has been running for more than a yr with an average CPU time of 4 CPU seconds.
Run stats was done on the table a week back. (before the new version of the code was implemented)
There was no change in the table definitions.
We did not have the time to look at the EXPLAIN results. When the new version started to consume lots of CPU time We backed out the new version with the old version.

Your suggestion is that the optimizer would have chosen a worse access method with the new version (THis is highly possible since the new version had some changes with the SQL)
But my doubt is that if the new version is removed and the old version of the code is backed out wont the old access path (Which was a faster one) get established when we BIND the old version.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Nov 15, 2009 11:26 am    Post subject:
Reply with quote

Hello,

Quote:
But my doubt is that if the new version is removed and the old version of the code is backed out wont the old access path (Which was a faster one) get established when we BIND the old version.
I suspect that when the "old" process was returned to service, it went thru a bind - which may have chosen the worse access path.

Suggest your group work with the dba and determine why both sets of queries now use an access method that has problems with and without the new select.
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Sun Nov 15, 2009 11:42 am    Post subject:
Reply with quote

Quote:

I suspect that when the "old" process was returned to service, it went thru a bind - which may have chosen the worse access path.

When the old process was returned to service it indeed went through the BIND process. That is what is making us wonder. Why dint the BIND try to choose a optimal access path?. Previously an access path had existed which made the code run for 4 CPU seconds. When the same code was replaced (after removing the new process) why dint the BIND process choose an access path which was similar to the one which already existed.

I'm not sure if my above understanding did make any sense with respect to BIND. please correct me if my understanding on BIND and access path determination are correct.

Thanks,
Arun
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Nov 15, 2009 11:58 am    Post subject:
Reply with quote

Hello,

Quote:
Why dint the BIND try to choose a optimal access path?.
Something has changed in the environment that has caused the sql to be "optimized" differently than before.

You need to work with your dba and take the time to do the explain on any of the poorly performing sql.
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Sun Nov 15, 2009 12:06 pm    Post subject:
Reply with quote

Thanks for the suggestion.
I would consult with the DBA and post the findings
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Nov 15, 2009 1:15 pm    Post subject: Reply to: performance issue in a DB2 program
Reply with quote

Good luck - it will be good to know what caused this icon_smile.gif

Knowing what has happened will help others who have the same thing happen.

d
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun Nov 15, 2009 9:20 pm    Post subject:
Reply with quote

Quote:
We did not have the time to look at the EXPLAIN results.


i did not have time to check the fuel level and I ran out of gas in the middle of the desert.

Why?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun Nov 15, 2009 9:37 pm    Post subject:
Reply with quote

Quote:
We did not have the time to look at the EXPLAIN results.


why not? it would have given you an answer.

actually, you should have looked before you moved the new model to production.

especially since you changed sql.

y'all need to professionalize you shop. is everyone there a rookie?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Nov 15, 2009 11:00 pm    Post subject:
Reply with quote

Hello,

Something else to consider - is the bind being done in the "real" environment or is the bind done in an environment that only has minimal/skewed data thereby providing false info to the optimizer?

Some places re-bind in the production environment, some do not.
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Tue Nov 17, 2009 2:10 am    Post subject:
Reply with quote

as far as i know the bind was done in the real production environment.
And only the DBA has the permissions to view the EXPLAIN results
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Nov 17, 2009 2:43 am    Post subject:
Reply with quote

Quote:
And only the DBA has the permissions to view the EXPLAIN results

12.gif


and to think that we have made disparaging remarks about shops that outlaw ICETOOL!!!! 36_11_6.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 17, 2009 2:44 am    Post subject:
Reply with quote

Hello,

Quote:
And only the DBA has the permissions to view the EXPLAIN results
Then it is time to work with the dba to determine why the process no longer runs acceptably.
Back to top
View user's profile Send private message
r arunmoezhi

New User


Joined: 26 Jun 2009
Posts: 32
Location: Chennai

PostPosted: Tue Nov 17, 2009 3:03 am    Post subject:
Reply with quote

r arunmoezhi wrote:
Thanks for the suggestion.
I would consult with the DBA and post the findings
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 17, 2009 3:14 am    Post subject:
Reply with quote

Hello,

We will be interested to learn what has happened to cause the degredation.

Personally, i wonder what was changed "to improve things". . .

Good luck with the dba icon_smile.gif
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Tue Nov 17, 2009 11:32 am    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
Quote:
We did not have the time to look at the EXPLAIN results.


i did not have time to check the fuel level and I ran out of gas in the middle of the desert.

Why?

icon_lol.gif
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Tue Nov 17, 2009 11:35 am    Post subject:
Reply with quote

My DB2 is a bit rusty but isn't the access path chosen during RUNSTATS, not the BIND? I didn't see where a RUNSTATS was run after the original SQL was reinstated.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Nov 17, 2009 2:24 pm    Post subject:
Reply with quote

speaking about static SQL, with normal binding parameters REOPT(NONE) :
The access path is chosen during (re)bind based on the statistics of that moment.

A runstats later wil not affect the access path.

Reinstating a old version probably involves a rebind, thus re-deciding access paths based on the current statistics.

In DB2 9 you could have a look at the new feature : Plan Stability.

Most access path decisions can be steered by rewriting the SQL. But of course you would like to know what the old one was (old explain info) and which statements have now another one (new explain info).
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Tue Nov 17, 2009 8:54 pm    Post subject:
Reply with quote

Thanks for the correction, GuyC.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts IMS BMP program causes 878 system abend Artemk IMS DB/DC 7 Tue Nov 22, 2016 8:26 pm
No new posts DFHRESPONSE returns issue divated CICS 3 Wed Nov 02, 2016 6:32 pm
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts A sample of exit program for exit XMN... lind sh CICS 2 Mon Oct 03, 2016 5:07 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us