Fun with Bind variables

Recently one of my fellow-colleagues was there at a client site where I had visited more than a year ago.

during my period of stay, I had pointed the developers about their lack of bind variables usage and why the plans suddenly change over night for no reason and it stays there in shared pool ..I remember having the same discussion with DBAs too and the DBA folks believed "alter system flush shared pool" is probably statement discovered to fix any SQL running longtime

Interestingly when I was casually talking with my colleague he mentioned that he noted that bind variable peeking had been turned off (note: underscore parameter, thanks to whichever DBA there). I was like "Hmm..someone wanted to have a short-cut way to solve everything without fixing the code"..

and interestingly over the next coupla days we discovered more issues..all sqls are going unoptimized because of bind variable being turned off..Initially when I was told it didnt strike me to relate..but then a quick test and peek into 10053 reveals it so clear..

without bind variable peeking, CBO had no clue about incoming values and so it didnt consider histograms - which is to an extent okay..but the bigger issue was it wont even look into partition statistics (think about it in a 24*7 system where u have so much data flowing in every single day and you have all partitions nicely build to partition the data at various physical segments)..CBO looks at table statistics and decides on plan (no partition pruning or elimination) - the plan is guaranteed to be wrong ..& to get it right you would have ensure global statistics should be perfect reflection of actual data.

you fix a small problem with a short-cut way..opened up another can of worms.. after all, most performance problems are man-made (well developers & dba made)

I mean ..I have to be thankful to all these folks who work so hard on keeping consultants like me in business..otherwise my living would be in a soup

so thanks folks..mess up more all you can.. sometimes it gets really interesting to see how far can you go that lane.:)