We recently had an issue with the execution of a simple stored procedure. When run from sqlplus on one machine it ran in 1 second when run from another machine (using the exact same parameters) again in sqlplus it took 5 minutes......
Initially we looked at sqlplus versions and looked for anything obviously wrong at the client end but nothing was apparent.
We then took a closer look at the explain plan of what it was actually doing - surely the same stored procedure run with the same sqlplus client version with the same single parameter passed in will run the same no matter which client it is run from?
We found that the explain plan was different and it was executing a different way - but why?
After much head scratching we came to the conclusing that something about the client must be different - and it turned out it was.
The one client was installed in English and the other in German.
This meant NLS_LANG was different at the client end.
This somehow then influenced the optimizer to get a totally different plan - i guess something to do with the data is sorted being different between different languages somehow affected it?
Setting NLS_LANG to the English settings and the performance went down to 1 second response
hope this helps someone else as it had us scratching our heads for a while..
Database version 11.2.0.2 on SLES11.
I had same problem. There must be global solution for this.
ReplyDelete