[intro music]
[theme music]
So let's start taking
the development of the demo a little bit further, and implementing
a connection pool. On implementing
connection pool, you'll notice that we've taken the performance up ten times
faster to over 3,000 transactions a second.
This order of magnitude,
very often people will take great credit, "oh, I made the application ten times
faster. I'm a hero."
But you'll notice that still we have actually
inside the database a lot of contention and serialization.
Although we've managed to increase the utilization the CPU and take the
transaction rate up,
there's a lot further to be done. Because you'll notice
if we take a look at this thing, this application is parsing
every sequel statement. Now the reason why
is parsing every single statement, is because the program has written
a very simple application that dynamically builds the sequel string
and then sends it to the database. This dynamically
built sequel string is a classic screen scraping or webform scraping application
where they take the contents out of a form
dynamically build a sequel statement, and send it down to the database.
This results in what we would call a hard parse and
the hard parse of the sequel statement means that
it's probably going to be a unique sequel statement
and it's not likely to find any match inside the Oracle shared pool
and so it won't be shared either, okay.
So we're gonna take the full penalty not of not only
syntactically checking the sequel statement, semantically checking the
sequel statement.
We're also gonna take performance penalty of generating an execution plan
for the sequel statement,
and all the memory management associated with it. Just think about that.
That's a lot of CPU that again, is not being spent
executing a sequel statement, it's just setting things up.
One of the ways to perhaps think about this is we are
effectively recompiling the application before we ran that.
Imagine recompiling a c: program
every time you wanted to run it. You just wouldn't do that. You just learn to
re-execute that.
Having eliminated the the logging on and off to the database being the
performance problem, you'll notice that
this application in itself is still not meeting the performance targets.
We're only doing about 4,000, three-and-a-half four thousand
transactions a second.
The response time has come down to about 18
milliseconds, but we have been successful in getting the
database CPU busy and we're running in user code, not system code.
So let's start investigating the database for why are
we not still not making the targets. Well, you'll notice that half
of the processes in the database that are running are on the CPU which is great,
but the other processes are all contending inside the database
for memory structures inside the shared pool.
You can see latch row cache objects and latch on shared pool. And you're seeing library
cache mutex.
And, and really, if we were to do the root cause of the problem
the answer is staring us in the face. We are hard parsing all the sequel
statements that we send down.
We're doing about 13,000 sequel statements, and we are hard parsing about
12,000 of them.
The reason is again, we remember this was an application that was
thrown together really quickly, and is the programmer
has not thought about cursors and has just done a classic screen scraping
application,
and is just copying the content out of the web form
and make building a sequel statement. So if we actually look at one of the sequel
statements that are actually being ran
inside Enterprise Manager here, you can see there
is an UPDATE statement and they're changing a field
but you'll notice that for everything in the WHERE clause
it's a hard-coded literal value that have been pulled out of the webform.
These using literal values will mean that
every sequel statement here is unique,
is being hard parse, which means we're doing a syntax check,
we're doing a semantic check to see that you're able
to validate correctly execute this sequel statement,
and then we have to generate an execution plan and memory,
and manage memory structures inside the shared pool. Which were the weight events
you were seeing because the shared pool
is a shared structure and so we have to manage concurrency to this.
The challenge here is that the developer haven't been using a concept called
bind variables, and had not been building them into the application,
and as a result this actual sequel statement is potentially
sequel injectable. If somehow in these escape sequences
someone had managed to inject into the field enough escape sequences quotes,
and things,
where they could actually start returning additional data or
updating additional data in the app,
in the base tables. So all they need to do is embed
'or 1 equals 1' into the sequel string
and of course all the rows are impacted. So this is known as sequel injection, and it is a
big security issues associated with
writing screen scraping applications. And again,
we're gonna talk about the use of bind variables this should be specified by
the system architect or the development management team.
If the programmer is left to figure it out themselves, they'll take the
easiest path to generate a functional app. It doesn't mean it will perform
or whether it's secure.