|
View previous topic :: View next topic
|
| Author |
Message |
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
Thanks for the suggestion.
I would consult with the DBA and post the findings |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Good luck - it will be good to know what caused this 
Knowing what has happened will help others who have the same thing happen.
d |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| And only the DBA has the permissions to view the EXPLAIN results |
and to think that we have made disparaging remarks about shops that outlaw ICETOOL!!!!  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
| r arunmoezhi wrote: |
Thanks for the suggestion.
I would consult with the DBA and post the findings |
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| 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? |
 |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| Thanks for the correction, GuyC. |
|
| Back to top |
|
|
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
Finally we were able to catch up with the DBA. He said that a patch was run a month ago and he was of the opinion that it would have caused the performance issue.
He did not provide the details of what the patch was. If i get the information I will update it here |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Thanks for the followup
d |
|
| Back to top |
|
|
|
|
