I was using my “recent sql” check on client site some time ago, and found that one of their programmers had clearly been advised to make use of bind variables when writing SQL for an OLTP system, because I found code like the following in the library cache:
select customer_name from customers where cust_id = :v_104356;
Seems okay so far – but the second statement dumped by my query looked like this:
select customer_name from customers where cust_id = :v_27054;
And so it went on – I found about 30 statements like this that had appeared in the previous 60 seconds. The developer was using bind variables but managing to do it in a way that caused a “hard parse” on every statement.
I couldn’t help smiling when I saw this, then I paused to ask myself if this was a deliberate design decision and if so what possible justification there could be for doing something like this. And then I thought of a rational answer that might explain the findings.
This week’s challenge: can you think of a reasonable explanation for finding 30 statements like this as recent arrivals in the library cache ?
Update – for clarification:
While looking at an OLTP system you find 30 statements like the above (yes, absolutely that simple) in v$sql, and say to the client: “This is a little unusual, why are you doing it?” The client supplies an explanation.
Although you were expecting to respond to any explanation with a comment like “You shouldn’t do it like that” (or possibly “We’ll have to find out why that’s happening and stop it” ) you feel the urge to say “That was a good idea”.
So can you think why something that initially looks like an odd design flaw could actually be a clever idea.
