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

Copying files larger than 2 GB over a Remote Desktop Services

When you try to copy a file that is larger than 2 GB over a Remote Desktop Services or a Terminal Services session through Clipboard Redirection (copy and paste) by using the RDP client 6.0 or a later version, the file is not copied. And, you do not receive an error message.


There are two methods for this

Method 1:Use Drive Redirection through Remote Desktop Services or a Terminal Services session if you want to transfer files larger than 2 GB.

Method 2:
Use command-line alternatives such as XCopy to copy files larger than 2 GB over a Remote Desktop Services or Terminal Services session. For example, you can use the following command:

xcopy \\tsclient\c\myfiles\LargeFile d:\temp
Copy and paste files:
You can copy and paste the files between the remote session and the local computer or between the local computer and the remote session by using the Copy and Paste feature. However, this is limited to files that are smaller than 2 GB.

Transfer of files by using Drive Redirection:
The drives on the local computer can be redirected in the session so that files can easily be transferred between the local host and the remote computer. The drives that you can use in this manner include the following:

1. local hard disks
2. floppy disk drives
3. mapped network drives

On the Local Resources tab of Remote Desktop Connection, users can specify which kinds of devices and resources that they want to redirect to the remote computer.