Real-World Performance – 3 – Connection Pools and Hard Parse

Real-World Performance – 3 – Connection Pools and Hard Parse

[intro music] [theme 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.

2 comments on “Real-World Performance – 3 – Connection Pools and Hard Parse

  1. Orlando Martins Post author

    I liked the video, it illustrates a problem I have with a reporting tool in a DSS-Like enviroment.


Leave a Reply

Your email address will not be published. Required fields are marked *