Do You Know If Your Database Is Slow?

The time to respond:

There was a question at Pythian a while ago on how to monitor Oracle database instance performanceand alert if there is significant degradation. That got me thinking, while there are different approaches that different DBAs would take to interactively measure current instance performance, here we would need something simple. It would need to give a decisive answer and be able to say that “current performance is not acceptable” or “current performance is within normal (expected) limits”.

Going to the basics of how database performance can be described, we can simply say that database performance is either the response time of the operations the end-user do and/or the amount of work the database instance does in a certain time period – throughput.

We can easily find these metrics in from the v$sysmetric dynamic view:

SQL> select to_char(begin_time,'hh24:mi') time, round( value * 10, 2) "Response Time (ms)"
from v$sysmetric
where metric_name='SQL Service Response Time'

TIME Response Time (ms)
--------------- ------------------
07:20 .32


So this is the last-minute response time for user calls (here in ms). We can check the throughput by checking the amount of logical blocks (it includes the physical blocks) being read, plus we can add direct reads (last minute and last several seconds output here for a database with 8 KB block):

SQL> select a.begin_time, a.end_time, round(((a.value + b.value)/131072),2) "GB per sec"

from v$sysmetric a, v$sysmetric b

where a.metric_name = 'Logical Reads Per Sec'

and b.metric_name = 'Physical Reads Direct Per Sec'

and a.begin_time = b.begin_time

BEGIN_TIME END_TIME GB per sec

-------------------- -------------------- ----------

16-jun-2013 08:51:36 16-jun-2013 08:52:37 .01

16-jun-2013 08:52:22 16-jun-2013 08:52:37 .01

We can check more historical values through v$sysmetric_summary, v$sysmetric_history and dba_hist_ssysmetric_summary.

So did these queries answer the basic question “Do we have bad performance?”? 100 MB/sec throughput and 0.32 ms for a user call? We have seen better performance, but is it bad enough that we should alert the on-call DBA to investigate in more detail and look for the reason why we are seeing this kind of values? We cannot say. We need something to compare these values to so that we can determine if they are too low or too high. It is somewhat like being in a train that passes next to another moving train, going in same direction but at a different speed. We don’t know the speed of our train, and we don’t know the speed of the other train, so we cannot answer the question “Are we going very fast?”. If we turn to the other side and see a tree passing on the other side of the train, we will be able to estimate the speed of the train (also taking into account our experience of what is very fast for a train…). So we need something that has an absolute value. In the case of the tree, we know that the tree has speed of 0

The Difference Between HTTP and HTTPS

Most web addresses begin with "HTTP," which is an acronym for "Hyper Text Transfer Protocol." It's the protocol used to allow you to communicate with web sites.


"HTTPS" stands for "Hyper Text Transfer Protocol Secure." It means that information exchanged between you and a web site is encrypted and cannot be hijacked by someone who might want to electronically eavesdrop when you type a credit card number, a password, a social security number, or any other person information.

The purpose of the email is to encourage you to check for the "HTTPS" before you give financial information. Most web sites are not HTTPS, but when you click a link to make a purchase, many of them will direct you to an HTTPS site.


According to Verisign.com, a provider of Internet infrastructure services, Secure Socket Layer Encryption is a technology that protects Web sites and makes it easy to develop trust by means of an "SSL Certificate that enables encryption of sensitive information during online transactions. Each SSL Certificate contains unique, authenticated information about the certificate owner and a Certificate Authority verifies the identity of the certificate owner when it is issued. "

Just because a website uses such SSL encryption does not safeguard internet users from phishing and other schemes. When visiting websites that accept financial information online it is always a wise practice to make sure the online company is legitimate, has a good reputation in customer service and uses SSL encryption in their transactions.

ANTS Memory Profiler Walkthrough - Part 2

Figure 11. The Instance Categorizer shows chains of instances sorted by their shortest path to GC Root.

We see that over 21MB of the String class are held in memory by the same shortest path back to GC Root, via our QueryForm and ConnectForm. We select Show the Instances on this Path to view a list of every instance in the previous category.

Figure 12. The instance list view shows us a set of strings which we recognize as coming from our SQL Database.

The Instance List is showing us data which QueryBee had retrieved from the SQL Database, but that data should have been destroyed when QueryForm was closed. We select one of the instances and click the icon to generate an Instance Retention Graph.

Figure 13. This instance retention graph.

Using the instance retention graph, we should be able to find out what is still referencing our String instances. Then, we'll be able to go back into our code to break the chain of references that is keeping them in memory.

We start at the bottom and work our way up the graph until we find a reference that needs to be broken. We'll just need to break the chain at one point to allow the garbage collector to clean up everything below that.

By navigating up, we can see the string is being held onto by QueryForm, even though that should have been released from memory. Looking a little further up, the graph is telling us that a System.EventHandler is referencing QueryForm and, if we step up one more level, it's telling us that the event handler is referenced by our ConnectForm instance – this is the form that asked us for the database connection details. In other words, the ConnectForm is holding onto the QueryForm via an Event Handler.

If we look at this node more closely, we see that it's actually being referenced by the ConnectForm's Foregrounded field. 

Let's find this Foregrounded event in our code. We right-click on the QueryBee.ConnectForm node and open the ConnectForm source code in Visual Studio^™.

Figure 14. Foregrounded event in the ConnectForm source code.

The profiler automatically jumps to the Foregrounded event. We check where it is being used by right-clicking on Find All References.

Figure 15. The Foregrounded event is used in three places.

We've got three usages and we find that the last usage is where QueryForm registers for the Foregrounded event, but it doesn't look like it unregisters. If we fix that, then the memory leak should go away.

Once we're done, we need to rebuild, but first we need to stop profiling QueryBee so that the executable isn't locked. We go back to Profiler and click on the Stop Profiling button.
Then, we rebuild.

Figure 16.  We rebuild our QueryBee application.

Back in the profiler, we start up a new profiling session. We want to find out whether the reference to the QueryForm has disappeared.Note that it remembered our settings from last time, so all we need to do is click Start Profiling.

Figure 17.  The settings dialog remembers settings from last time.

We take a first snapshot to use as a baseline.
We perform the same actions as last time: take a baseline snapshot while QueryBee is idle, then a snapshot once we’ve connected and run a query.
We'll also take an extra snapshot, because we want to be able to verify that the QueryForm has disappeared.

Finally, we close the query window with the results grid and we take a third snapshot.
We switch to a comparison between snapshots 1 and 3, using the snapshot selection field just under the timeline.

Figure 18.  Summary screen comparing snapshots 1 and 3.

We can see there is now only a small memory increase between the snapshots, which is promising. Let's see if there's a QueryForm still in the class list.
We switch to the class list view and search only for classes in the QueryBee namespace.

Figure 19.  Class list for the QueryBee namespace.

No, it's gone. We're no longer leaking the form.
As you saw, it was fairly easy to track down a form which was being leaked.