[intro music]
[theme music]
So within Oracle, it's possible to re-execute the same sequel statement,
and share the sequel statement by the use of what we would call
bind variables. Now, if we take things to use bind variables
you'll notice that we jump the performances application
another order magnitude to over 30,000 transactions a second.
Now, bearing in mind we came from three hundred transactions a second
this is a big gain. We have increased performance.
We haven't changed the hardware. We haven't changed the licensing model.
The only thing we've changed
is how the application logic is sending down the code.
Ok? So by use of bind variables in being able to reuse each other's cursors
and execution plans, we have jumped another order of magnitude.
Other things to note, when doing these sort of things,
is that we've actually changed
the application to put little markers which is what we would call by
bind variables in the sequel statement. As a result we can reuse the sequel
statement
or share the sequel statement, and in many cases
we deal with people saying "we'd actually think it's that important to use bind
variables" or
"it's slowing down my programmer productivity."
Well you may be slowing down your programmer productivity
and that's because you're having to get a retrofit an application that
wasn't structured
correctly from the beginning. And the programmer wasn't told to use bind
variables,
or was never even educated in the use of them, and now
you are trying to defend the un-defendable, and using
silly arguments. One of the other issues is if you use a
screen scraping application and don't use bind variables, you leave yourself
way open for security implications.
What happens if somebody typed into those fields
inside the screen scraping application the right
series of escape sequences, commas,
escapes, such as they could imbed the sequel
clause or one equals one? If they could do that,
that means potentially this web form could return
all rows in the table that you are wanting to look at. You now let yourself open to
what we would call 'sequel injection
attacks'. And so one of the safest ways to write on, when you're doing a web
facing, or a customer-facing,
or an online application to protect yourself
from security implications is to write it using bind variables from the
beginning.
Again this is something that the architects
or the development managers need to specify
before the programmers start writing code. This cannot be emphasized enough
because to retrofit it is always difficult, it's embarrassing,
and is best avoided altogether. Just do it right from the beginning.
So let's go back and start changing the application
to start using bind variables.
And I have a tab here, and we'll just basically apply that and you will notice
a dramatic change in the performance profile of
the application now. You'll notice as we transition the cursor, there is a dip in
performance and that's normal,
so nothing to worry about that, but you can now see
now that we're using shared sequel
and bind variables in every metric,
that we have everything is improving.
You can see we're now meeting our performance target
as we're now up over 30,000 sequel statements per second.
The response time is now
so rapid it's barely forming a graph. In fact
you can see it's two milliseconds and remember we were at about
230 milliseconds when we started. When logging on and off.
If you actually look at the database profile
we're running with no contention, next to no contention it all, down here.
It's all running on the CPU,
and because we're actually not going through the process of re-parsing the
sequel statements in creating cursors,
we saved some code paths so we are actually doing less code path
and still the code path--the majority of it--is in user space.
But the big gain is to be seen in the real world metric,
which is the response time and throughput.