Oracle performance tuning -SQL and for that matter MS SQL tuning or any SQL server performance tuning can be analyzed by capturing TCP database transactions at the packet level. The Oracle Performance Tuning video is an example of the value of network analysis to optimize existing systems rather than await the forklift upgrade and its expense to improve performance. Andy hey – that forklift upgrade might not contain the silver bullet you are looking for to improve performance. There are many factors that can impact database and application performance – it might be a network or authentication issue slowing down your database.
…better to start database tuning with “brain cells” than capital budget.
Transcript for this Video
Today, we’re going to talk about Oracle Performance Tuning opportunities for application
optimization. So you basically take and measure your performance. So let’s
just say you click and it comes back and it takes ten seconds. You then
take a look at that transaction with an analyzer, etc., and you then
calculate what it’s doing and figure out, theoretically, how long that
should take or experientially how long that should take.
Let’s just say you come up with ten seconds as a measured performance, and
you found that you could theoretically get this done in under a second. So
there’s nine seconds of what we call optimization opportunity.
Well, that’s the first thing that you need to do is to determine if your
optimization is viable, if you’re getting a material return on your
investments. So you take a look at measured performance, calculate
theoretical performance, and arrive at your optimization opportunity.
After that, you want to take a look at some real information. Here is an
actual Wireshark trace file, a SYN, SYNACK, ACK. So how long does it take to connect
up across the network? Well, it takes four milliseconds to connect up to
the application and back at the TCP layer. Then, it goes and it says,
“Okay. Connect user Oracle,” and he comes over here and he gets a, “Hey,
resend this.”
That takes 73 milliseconds. Then he resends it and it comes back in a half
a millisecond. So you can see right here that that’s quite a bit of time.
73 milliseconds is a very long time. If you only do it once per
transaction, you’re only burning under 100 milliseconds.
However, this particular transaction performs about 86 queries, and each
one of those queries starts out with a 73 millisecond connect request to
the Oracle database. Well, that’s going to take 86 times 73 milliseconds.
So it’s going to be pretty substantial in how much that costs you in the
form of the transaction. That’s an extra six seconds added to your
transaction already.
So, if you could basically reduce that down, that would be optimal.
However, let’s just think about this. What if you only logged in one time
and then performed 86 queries across a persistent connection? That would be
a better way of architecting this application, and indeed, that is the
suggested optimization.
So, in the next session, we’re going to talk about to your CIO what this
means in dollars and cents and how to justify your return on investment for
something that you’re looking at trying to optimize.
Now, Oracle performance tuning SQL optimization is free, right? It’s just something that you have to do.
Sadly, today most of your optimizations are in the form of large scale,
forklift upgrades, and sadly, a lot of times we don’t even look at
opportunities to optimize the existing systems. Well, those opportunities
do, in fact, exist, and in the time of tough economy and tight budgets,
we’re starting to go back and take a look at how can we optimize what we
already own and improve our lifecycle cost and improve our applications. So
that’s what we’re talking about today.