[intro music]
[theme music]
So the first thing we're going to talk about
is the use of cursors and sessions.
Now this demo that we're going to look at
we're gonna start the demonstration running along, and will see it running at
its most suboptimal.
The reasons for this will become
very obvious because you will notice that the number log ons
taking place on this demo per second
is almost identical to the transaction rate. In this case
the developer had made the mistake to log on
for every transaction taking place in the database.
Now logging on to the database is extremely expensive operation.
Likewise logging off from the database is a
extremely expensive operation. So
as people evolve their applications,
they need to learn how to use a connection pool so that they share the
connections to the database
and you don't incur the cost of logging on off
to the database. This is a big deal,
okay. Back in 1999, when the dot-com craziness was taking place
people forgot this and they wrote per se "stateless" middleware
and stateless middleware meant that they just basically
disconnected from the database until they actually needed to make a sequel
request
and then they log back onto the database
made a request and then logged back off. These companies lasted between three and
six months
before they either learned to use a connection pool
or they literally ran of business. And this is
you know a harsh fact. Very often it's very quick to prototype an application
that looks great on a laptop with one user,
by doing the sort of techniques, but when they scale up to enterprise class
and web-scale
type things, they just will not scale. And so,
here we are demonstrating an application running
that may be built by say an intern student or something like that,
and you can see the performance
in terms of response time is nothing
clever. It should be one or two milliseconds. You'll notice it is much greater than that
and we should be hitting thousands of transactions per second but we're barely
achieving 300.
If we actually look at the operating system
we'll notice that the operating system is getting up there to be fifty percent
busy,
and if you actually subdivide where we're spending the busy time in the
operating system, you'll notice that half the time itself
is actually being spent inside the Linux kernel.
That means that we're not even executing database code. We are
executing code in the operating system. Code inside the operating system
doesn't get you any work done, it just utilizes the CPU
managing the system. And in this case what we're seeing is that when we log
on and off to the database
it costs us a lot in terms of kernel time, literally mapping the SGA
in and out of process address space as we create processes
and tear them down. This is incredibly resource-intensive.
You'll also notice that we can't
actually get the CPU to
use fully utilized because
eventually the Linux kernel will serialize
just managing all those shared memory pages.
So we're talking about here is an application that has
been written fairly sub-optimally, and in fact
completely sub-optimally. As a result
it had a target response time of over 20,000 transactions a second,
but if we actually have a look at it, and we interrogate the screen
in the top lines, we can see that we're only achieving about
and 200 to 300 transactions a second
and the response time for the transactions at the screen level
is about quarter of a second
to 130 milliseconds. So you can see not
a great transaction rate, not a great response time.
If we look at the database level, we can see that
we put relatively few sessions active inside the database and they're all on
the CPU, and not seeing any
contention of any note and
to be concerned about. So the database looks fine,
and everything looks great. So the clue to the bad performance
actually comes from the operating system
in that if we will interrogate the operating system you can see that we're
spending
as much time inside the operating system kernel
as we do in times of user space. And
really, you know, this is CPU being utilized to
running the operating system that not executing database code at all.
And so you can see there's something not right with this system.
And by looking at the database statistics, you can see that the actual
number of connections or the log ons per second to the database
is almost identical to the transaction right,
and that in itself gives you the clue as to actually what is wrong with this
application.
That the application is logging on to the database,
executing a transaction, and logging off.
And as a result we're getting degraded performance because the operating system
is working too hard,
it's really difficult to get all the CPUs busy,
and the response time as you can see is choppy and erratic
and in summary,
it's a suboptimal application. Its functionally correct
but it's not a performance application.