Another unexpected sequel (this time its NLS)

A long time ago in a galaxy far... i posted (an admittedly short) blog entry on the weirdest performance problem I'd ever seen. At the time i was very pleased we'd managed to sort the issue as it was so obscure.

However this week we've seen the same issue again (but with an even further twist).

For those of you that didn't read the original post (that's most of you i'm guessing unless you clicked the link above) so a quick recap.

You have a stored proc in a database (11.2.0.2), you have 2 clients, from one client you call the stored proc passing in argument 'XXX', from the other client you do the exact same thing with the same argument.

Now all common sense would tell you these should execute the same way - it's within the database after all - but again we saw massively different performance profiles.

client 1 ran in 1 second, client 2 ran in 90 minutes!

Thankfully because we'd the similar issue before i knew the track to go along to investigate this. Indeed the stored proc (well the one statement within it) had a completely different execution plan - causing the big difference in the timings. Now unfortunately the plan for this statement is about 300 lines long and finding the exact point where they diverge was not easy (in fact i gave up) - but essentially it was the same issue i discovered before - an NLS setting of German caused Oracle to come up with a different plan - i can only think it's some part of the step where it involves sorting (where the different language comes into play - maybe a sort/group by step) that the difference occurs.

So how to fix it.

Well i just assumed we'd fix it the same way as before - just stick NLS_LANG in the registry and job done.....

However - for the normal 11g client it was already set, the application in use though was using odp.net (which still completely confuses me how the thing is setup and how it relates to a normal client, what is managed/unmanaged - do you need 32/64 bit etc etc)

So anyway after much messing about with many registry keys and config file settings we got nowhere - it just refused to use English. The only thing we didn't change was the locale etc of the actual windows server - which we didn't want to do as it could have affected other applications (this was currently set to German and clearly where odp seemed to be picking it up from).

So how do we resolve this?

Use plsql of course!

So right inside the start of the stored proc - directly after the BEGIN - we add this little nugget:

dbms_session.set_nls('NLS_LANGUAGE','ENGLISH');

And note it's NLS_LANGUAGE not NLS_LANG - thanks to the developers for that one which had me stumped for a while ( I assume the NLS_LANG environment variable was shortened many years ago to meet 8 character limits that used to exist in the past?)

So what this does is override any client setting of NLS_LANG and force it to use English - and it's magically fixed - the execution plan is now the fast one.

Now this could have been fixed with logon triggers,execute immediate or making some .NET change or indeed by changing the app server locale - this line of plsql seemed the least impact approach though.

Not something I've ever used before - but nice to know it's there and works - and in the case proved very useful!