Oracle elapsed time tips
elapsed time tips
Oracle Tips by Burleson
The SQL execute elapsed
Oracle metric is the
amount of elapsed time SQL statements are executing. Note that
for SQL select statements this also includes the amount of time spent
performing fetches of query results.
Also see these important notes on measuring
Remember, Oracle cannot know the actual end-to-end response
time for a SQL statement because Oracle cannot measure network
latency outside the instance.& Hence, the &SQL ordered by
elapsed time& section of a AWR report should not take the
execution times literally.& The formula for the Oracle
elapsed time is as follows:
elapsed time = && cpu time +&&&
user i/o wait time +&&& application_wait_time + &&
concurrency_wait_time + && cluster_wait_time + &&
plsql_exec_time + && java_exec_time
A SQL trace (10046 trace) will also show the elapsed
time, as will the v$sql view.
This query will show the SQL execution elapsed
time duration (in hours) for long-running SQL statements:
col program format a30
select query_runs.*,
&&&&&&&&&&&&&&&
round ( (end_time - start_time) * 24, 2) as duration_hrs
&&&&&&&&&&
from (& select u.username,
&&&&&&&&&&&&&&&&&&&&&&&&&
ash.program,&&&&&&&&&&&&&&&&&&&&&&&&&
ash.sql_id,&&&&&&&&&&&&&&&&&&&&&&&&&
ash.sql_plan_hash_value as plan_hash_value,
&&&&&&&&&&&&&&&&&&&&&&&&&
ash.session_id as sess#,
&&&&&&&&&&&&&&&&&&&&&&&&&
ash.session_serial# as sess_ser,
&&&&&&&&&&&&&&&&&&&&&&&&&
cast (min (ash.sample_time) as date) as start_time,
&&&&&&&&&&&&&&&&&&&&&&&&&
cast (max (ash.sample_time) as date) as end_time
&&&&&&&&&&&&&&&&&&&&
from dba_hist_active_sess_history ash, dba_users u
&&&&&&&&&&&&&&&&&&&
where u.user_id = ash.user_id and ash.sql_id = lower(trim('&sql_id'))
&&&&&&&&&&&&&&&&
group by u.username,&&&&&&&&&&&&&&&&&&&&&&&&&
ash.program,&&&&&&&&&&&&&&&&&&&&&&&&&
ash.sql_id,&&&&&&&&&&&&&&&&&&&&&&&&&
ash.sql_plan_hash_value,
&&&&&&&&&&&&&&&&&&&&&&&&&
ash.session_id,&&&&&&&&&&&&&&&&&&&&&&&&&
ash.session_serial#) query_runs
order by sql_id, start_
While STATSPACK and AWR reports can easily show the top SQL that ran
with the longest execution time, you can run a dictionary query to see the
SQL with the longest run times:
&& sql_id,
child_number,
&& sql_text,
&& elapsed_time
&& (select
sql_id_child_number,
&&&&& sql_text,
elaped_time,
&&&&& cpu_time,
disk_reads,&& rank ()
&&&&& (order by elapsed_time desc)
&&&&& sql_rank
&&&&& v$sql)
&& sql_rank & 10;
In sum, it is important to
note that the SQL elapsed time metric is not the same as the actual response
time for a SQL statement.&
The sys_time_model_int_
query below can be used to retrieve
information from
dba_hist_sys_time_model&view for a
particular AWR snapshot interval.
sys_time_model.sql
column &Statistic Name& format A40
column &Time (s)& format 999,999
column &Percent of Total DB Time&
format 999,999
select e.stat_name &Statistic Name&
&&&& , (e.value - b.value)/1000000&&&&&&&
&Time (s)&
&&&& , decode( e.stat_name,'DB time'
&&&&&&&&&&&& , to_number(null)
&&&&&&&&&&&& , 100*(e.value -
&&&&&&&&&&&& )/
&&&& ( select nvl((e1.value -
b1.value),-1)
&&&& from dba_hist_sys_time_model&
&&&&&&& , dba_hist_sys_time_model&
&&&& where b1.snap_id&&&&&&&&&&&&& =
&&&& and e1.snap_id&&&&&&&&&&&&&&& =
&&&& and b1.dbid&&&&&&&&&&&&&&&&&& =
&&&& and e1.dbid&&&&&&&&&&&&&&&&&& =
&&&& and b1.instance_number&&&&&&& =
b.instance_number
&&&& and e1.instance_number&&&&&&& =
e.instance_number
&&&& and b1.stat_name&&&&&&&&&&&& =
&&&& and b1.stat_id&&&&&&&&&&&&&&& =
e1.stat_id
&&&& &Percent of Total DB Time&
& from dba_hist_sys_time_model e
&&&& , dba_hist_sys_time_model b
&where b.snap_id&&&&&&&&& &&&&&&= &pBgnSnap
&& and e.snap_id&&&&&&&&&&&&&&& =
SQL& @Sys_time_model_int_10g.sql
Statistic Name&&&&&&&&&&&&&&&&&&&& Time
(s) Percent of Total DB Time
----------------------------------
-------- ------------------------
DB time&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
sql execute elapsed time
&&&&&&&&&&&&&&&156&&&&&&&&&&&&&&&&&&&&&&
DB CPU&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
153&&&&&&&&&&&&&&&&&&&&&& 90
PL/SQL execution elapsed time&&&&&&&&&&&
77&&&&&&&&&&&&&&&&&&&&&& 46
background cpu time&&&&&&&&&&&&&&&&&&&&&
53&&&&&&&&&&&&&&&&&&&&&& 31
parse time
elapsed&&&&&&&&&&&&&&&&&&&&&&&
6&&&&&&&&&&&&&&&&&&&&&&& 4
hard parse elapsed
time&&&&&&&&&&&&&&&&&&
4&&&&&&&&&&&&&&&&&&&&&& &3
connection management call elapsed
time&& 0&&&&&&&&&&&&&&&&&&&&&&& 0
Java execution elapsed
time&&&&&&&&&&&&&&
0&&&&&&&&&&&&&&&&&&&&&&& 0
PL/SQL compilation elapsed
time&&&&&&&&&& 0&&&&&&&&&&&&&&&&&&&&&&&
sequence load elapsed
time&&&&&&&&&&&&&&& 0&&
&&&&&&&&&&&&&&&&&&&&&0
hard parse (sharing criteria) elapsed ti&
0&&&&&&&&&&&&&&&&&&&&&&& 0
hard parse (bind mismatch) elapsed
time&& 0&&&&&&&&&&&&&&&&&&&&&&& 0
See also on SQL response time:
This, and many other Oracle performance metrics are discussed in
my book && by Rampant TechPress.& You can buy it directly from
the publisher and save 30% at this link:
Oracle Training
from Don Burleson&
The best on site
&& are just a phone call away! You can
get personalized Oracle training by Donald Burleson, right at
your shop!
��
Burleson is the American Team
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.&
Feel free to ask questions on our
experience!
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
&Oracle technology is changing and we
strive to update our BC Oracle support information.& If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback.& Just&
&and include the URL for the page.
&&&&&&&&&&&&&&&&&&&&
Burleson Consulting
The Oracle of
Database Support
Copyright & 1996 -& 2016
All rights reserved by
is the registered trademark of Oracle Corporation.date - Calculate elapsed time in Java / Groovy - Stack Overflow
to customize your list.
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.
J it only takes a minute:
Join the Stack Overflow community to:
Ask programming questions
Answer and help your peers
Get recognized for your expertise
Date start = new Date()
Date stop = new Date()
I'd like to get the years, months, days, hours, minutes and seconds ellapsed between these two dates.
I'll refine the question.
I just want to get the elapsed time, as an absolute measure, that is without taking into account leap years, the days of each month, etc.
Thus I think it's impossible to get the years and months elapsed, all I can get is days, hours, minutes and seconds.
More specifically I want to tell that a certain task lasted for e.g.
13 min, 4 sec
2 h, 10 min, 2 sec
4 d, 4 h, 2 min, 2 sec
So please forgive my lack of precision.
5,124113144
19.5k43164256
I've just discovered this quick Groovy-sourced solution:
import groovy.time.TimeCategory
import groovy.time.TimeDuration
TimeDuration td = TimeCategory.minus( stop, start )
println td
7,306105094
You can do all of this with division and mod.
long l1 = start.getTime();
long l2 = stop.getTime();
long diff = l2 - l1;
long secondInMillis = 1000;
long minuteInMillis = secondInMillis * 60;
long hourInMillis = minuteInMillis * 60;
long dayInMillis = hourInMillis * 24;
long elapsedDays = diff / dayInM
diff = diff % dayInM
long elapsedHours = diff / hourInM
diff = diff % hourInM
long elapsedMinutes = diff / minuteInM
diff = diff % minuteInM
long elapsedSeconds = diff / secondInM
That should give you all of the information you requested.
EDIT: Since people seem to be confused, no, this does not take things like leap years or daylight savings time switches into account. It is pure elapsed time, which is what opensas asked for.
9,63962743
Not so easy with the standard Date API.
You might want to look at , or
I'm not an expert in Joda, but I think the code would be:
Interval interval = new Interval(d1.getTime(), d2.getTime());
Period period = interval.toPeriod();
System.out.printf(
"%d years, %d months, %d days, %d hours, %d minutes, %d seconds%n",
period.getYears(), period.getMonths(), period.getDays(),
period.getHours(), period.getMinutes(), period.getSeconds());
33.9k1176120
Regarding JodaTime I thanks to the responder who suggested it. Here's a more condensed version of the Joda code suggested:
Period period = new Period(d1.getTime(), d2.getTime());
System.out.printf(
"%d years, %d months, %d days, %d hours, %d minutes, %d seconds%n",
period.getYears(), period.getMonths(), period.getDays(),
period.getHours(), period.getMinutes(), period.getSeconds());
(not sure if this is helping the original question but certainly searchers).
Well since Java 1.5 you should use
Here is a simple & plain example for this. I think in groovy it might get shorter(as always).
* Formats a given {@link Date} to display a since-then timespan.
* @param created
* @return String with a format like "3 minutes ago"
public static String getElapsedTime(Date created) {
long duration = System.currentTimeMillis() - created.getTime();
long seconds = TimeUnit.MILLISECONDS.toSeconds(duration);
long days = TimeUnit.MILLISECONDS.toDays(duration);
long hours = TimeUnit.MILLISECONDS.toHours(duration);
long minutes = TimeUnit.MILLISECONDS.toMinutes(duration);
if (days & 0) {
return days + " days";
if (hours & 0) {
return hours + " hrs";
if (minutes & 0) {
return minutes + " minutes";
return seconds + " seconds";
Oh and avoid mult)
Actually, regarding the above answers about .
There's an easier way to do this with Joda-Time’s
Period period = new Period(startDate, endDate);
System.out.println(PeriodFormat.getDefault().print(period));
To customize the output, look into the , , and
33.8k8109162
4,84063344
Apart from the aforementioned great
API which I do recommend, the best standard Java alternative you can have is the . It is cumbersome to work with it (this is an understatement .. look at the single-line JodaTime examples here), but you can calculate the elapsed time with it as well. Important key is that you should use the Calendar#add() in a loop to get the elapsed value for years, months and days to take leap days, years and centuries into account. You should not calculate them back from the (milli)seconds.
Here's a basic example:
import java.util.C
public class Test {
public static void main(String[] args) throws Exception {
Calendar start = Calendar.getInstance();
start.set(, 12, 35, 0); // Just an example.
Calendar end = Calendar.getInstance();
Integer[] elapsed = new Integer[6];
Calendar clone = (Calendar) start.clone(); // Otherwise changes are been reflected.
elapsed[0] = elapsed(clone, end, Calendar.YEAR);
clone.add(Calendar.YEAR, elapsed[0]);
elapsed[1] = elapsed(clone, end, Calendar.MONTH);
clone.add(Calendar.MONTH, elapsed[1]);
elapsed[2] = elapsed(clone, end, Calendar.DATE);
clone.add(Calendar.DATE, elapsed[2]);
elapsed[3] = (int) (end.getTimeInMillis() - clone.getTimeInMillis()) / 3600000;
clone.add(Calendar.HOUR, elapsed[3]);
elapsed[4] = (int) (end.getTimeInMillis() - clone.getTimeInMillis()) / 60000;
clone.add(Calendar.MINUTE, elapsed[4]);
elapsed[5] = (int) (end.getTimeInMillis() - clone.getTimeInMillis()) / 1000;
System.out.format("%d years, %d months, %d days, %d hours, %d minutes, %d seconds", elapsed);
private static int elapsed(Calendar before, Calendar after, int field) {
Calendar clone = (Calendar) before.clone(); // Otherwise changes are been reflected.
int elapsed = -1;
while (!clone.after(after)) {
clone.add(field, 1);
elapsed++;
It should print my age as of now =)
Oh, I should add, you can "convert" Date to Calendar using Calendar#setTime().
664k19823662614
Hmm, if I get what you're asking, you want to know that if:
start = Jan 1, :00.000 am and
stop = Jan 6, :00.000 pm
you want an answer of 2 years, 0 months, 5 days, 2 hours, 1 minute
Unfortunately, this is a specious answer.
What if the dates were Jan 2, and Jan 31?
Would that be 29 days?
Ok, but Feb 2 to Mar 2 is 28 (29) days, but would be listed as 1 month?
The length of time in anything other than seconds or possibly days is variable without knowing the context since months and years are of different lengths.
The difference between 2 dates should be in static units, which are easily computable from stop.getTime() - start.getTime() (which is the difference in millisecs)
10.4k33859
It' You should set the right timezone
import java.util.TimeZ
import java.util.logging.L
import org.joda.time.DateTimeZ
import org.joda.time.format.DateTimeF
import org.joda.time.format.DateTimeF
public class ImportData {
private final static Logger log = Logger.getLogger(ImportData.class.getName());
private final static DateTimeFormatter dtf = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss.SSS");
private final static DateTimeFormatter dtfh = DateTimeFormat.forPattern("HH:mm:ss.SSS");
* @param args the command line arguments
public static void main(String[] args) throws Exception {
TimeZone.setDefault(TimeZone.getTimeZone("Europe/Berlin"));
DateTimeZone.setDefault(DateTimeZone.forID("Europe/Berlin"));
// Quotes connection=Quotes.getInstance();
final long start
= System.currentTimeMillis();
// do something here ...
// connection.importTickdata();
Thread.currentThread().sleep(2000);
final long end
= System.currentTimeMillis();
" + dtf.print(start));
" + dtf.print(end));
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
DateTimeZone.setDefault(DateTimeZone.forID("UTC"));
("[duration] " + dtfh.print(end - start));
// connection.logOff();
// System.exit(0);
10.11.:12 ImportData main
INFO: [start]
00:08:10.306
10.11.:12 ImportData main
INFO: [end]
00:08:12.318
10.11.:12 ImportData main
INFO: [duration] 00:00:02.012
There is no point creating a Date object as all this does is wrap System.currentTimeMillis(). The getTime() function just unwraps the Date object.
I suggest you just use this function to obtain a long.
If you only need second accuracy, this is fine.
However if you want sub-millisecond accuracy use System.nanoTime() to get the elapse time.
336k36374682
This is a complete function I implemented based on Sebastian Celis answer. And again, from his post - this does not take things like leap years or daylight savings time switches into account. It is pure elapsed time.
The output is tailored towards my need. This only outputs three significant sections. Instead of returning
4 months, 2 weeks, 3 days, 7 hours, 28 minutes, 43 seconds
It just returns first 3 block (see more sample run at the end of post):
4 months, 2 weeks, 3 days
Here is the complete method source code:
* Format milliseconds to elapsed time format
* @param time difference in milliseconds
* @return Human readable string representation - eg. 2 days, 14 hours, 5 minutes
public static String formatTimeElapsedSinceMillisecond(long milisDiff) {
if(milisDiff&1000){ return "0 second";}
String formattedTime = "";
long secondInMillis = 1000;
long minuteInMillis = secondInMillis * 60;
long hourInMillis = minuteInMillis * 60;
long dayInMillis = hourInMillis * 24;
long weekInMillis = dayInMillis * 7;
long monthInMillis = dayInMillis * 30;
int timeElapsed[] = new int[6];
// Define time units - plural cases are handled inside loop
String timeElapsedText[] = {"second", "minute", "hour", "day", "week", "month"};
timeElapsed[5] = (int) (milisDiff / monthInMillis); // months
milisDiff = milisDiff % monthInM
timeElapsed[4] = (int) (milisDiff / weekInMillis); // weeks
milisDiff = milisDiff % weekInM
timeElapsed[3] = (int) (milisDiff / dayInMillis); // days
milisDiff = milisDiff % dayInM
timeElapsed[2] = (int) (milisDiff / hourInMillis); // hours
milisDiff = milisDiff % hourInM
timeElapsed[1] = (int) (milisDiff / minuteInMillis); // minutes
milisDiff = milisDiff % minuteInM
timeElapsed[0] = (int) (milisDiff / secondInMillis); // seconds
// Only adds 3 significant high valued units
for(int i=(timeElapsed.length-1), j=0; i&=0 && j&3; i--){
// loop from high to low time unit
if(timeElapsed[i] & 0){
formattedTime += ((j&0)? ", " :"")
+ timeElapsed[i]
+ " " + timeElapsedText[i]
+ ( (timeElapsed[i]&1)? "s" : "" );
} // end for - build string
return formattedT
} // end of formatTimeElapsedSinceMillisecond utility method
Here are some sample test statement:
System.out.println(formatTimeElapsedSinceMillisecond(L));
// Output: 8 months, 1 week, 1 day
System.out.println(formatTimeElapsedSinceMillisecond(L));
// Output: 1 day, 22 minutes, 4 seconds
System.out.println(formatTimeElapsedSinceMillisecond(123434L));
// Output: 2 minutes, 3 seconds
1,97232041
This is another similar function, It won't show up days, hours, minutes, etc. if its not needed change its literals if needed.
public class ElapsedTime {
public static void main(String args[]) {
long start = System.currentTimeMillis();
start -= (24 * 60 * 60 * 1000 * 2);
start -= (60 * 60 * 1000 * 2);
start -= (60 * 1000 * 3);
start -= (1000 * 55);
start -= 666;
long end = System.currentTimeMillis();
System.out.println(elapsedTime(start, end));
public static String elapsedTime(long start, long end) {
String auxRet = "";
long aux = end -
long days = 0, hours = 0, minutes = 0, seconds = 0, milliseconds = 0;
if (aux & 24 * 60 * 60 * 1000) {
days = aux / (24 * 60 * 60 * 1000);
aux = aux % (24 * 60 * 60 * 1000);
if (aux & 60 * 60 * 1000) {
hours = aux / (60 * 60 * 1000);
aux = aux % (60 * 60 * 1000);
// minutes
if (aux & 60 * 1000) {
minutes = aux / (60 * 1000);
aux = aux % (60 * 1000);
// seconds
if (aux & 1000) {
seconds = aux / (1000);
milliseconds = aux % 1000;
if (days & 0) {
auxRet = days + " days ";
if (days != 0 || hours & 0) {
auxRet += hours + " hours ";
if (days != 0 || hours != 0 || minutes & 0) {
auxRet += minutes + " minutes ";
if (days != 0 || hours != 0 || minutes != 0 || seconds & 0) {
auxRet += seconds + " seconds ";
auxRet += milliseconds + " milliseconds ";
return auxR
15.2k53674
This is a problem and an algorithm needs to be made to account for leap years and exact amount of months and days beside years. Interesting how it is simple if only one unit of measure is to be used. For example, total number of days between two days is correct as apposed to reminder number of days after number of months and years is calculate within let's say two decades.
I am currently working on this to provide it from my PML implementation, for example, in the form of:
unemployed &- date.difference[
Till = now,
YEARS, MONTHS, DAYS
]: yyyy-MM-dd
$$-& *unemployed -& date.translate[ YEARS, MONTHS, DAYS ] -& print["Unemployed for:", .]
Of course, this would also be useful and required for exact interest rate calculations.
15.9k84466
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
Stack Overflow works best with JavaScript enabledTime - Wikipedia, the free encyclopedia随笔 - 24&
&&&&&&&&&&&
　 &在我们使用set statistics time on 来查看SQL语句执行的时间时，SQL Sever会给我们返回来两个时间一个是CPU Time 另一个是Elapsed Time 那么这两个时间有什么不同呢？
&&&&&&& CPU Time 指的是CPU在忙于执行当前任务的时间，其并没有考虑等待时间，如IO等待，网络等待等，而Elapsed Time 则是执行当前任务所花费的总时间，也就是说这两者之
问的关系统可以表示为：Elapsed Time = Cpu Time + Wait Time 但是在多核处理器的情况下，由于多个CPU同时处理任务所以可能会出现Cpu Time 大于Elapsed Time 的情况
　　首先需要说明的是这篇文章的内容并不是如何调节SQL Server查询性能的（有关这方面的内容能写一本书），而是如何在SQL Server查询性能的调节中利用SET STATISTICS IO和SET STATISTICS TIME这二条被经常忽略的Transact-SQL命令的。
　　从表面上看，查询性能的调节是一件十分简单的事。从本质上讲，我们希望查询的运行速度能够尽可能地快，无论是将查询运行的时间从10分钟缩减为1分钟，还是将运行的时间从2秒钟缩短为1秒种，我们最终的目标都是减少运行的时间。
　　尽管查询性能调节困难的原因有许多，但这篇文章将只涉及其中的一个方面，其中最重要的原因是，每当使用环境发生变化时，就需要对性能进行调节，因此很难搞清楚到底需要如何调节查询的性能。
　　如果象大多数用户那样在一台测试用的服务器上进行性能调查，其效果往往并不是十分地令人满意，因为测试服务器的环境与实际应用的服务器环境并不完全相同。随着对资源要求的不断变化，SQL Server会自动地进行自我调节。
　　如果对这一点有疑问，可以在一台负载很大的服务器上反复地运行同一个查询，在大多数情况下，执行查询所使用的时间并不相同。当然，差距并不大，但其变化足以使性能的调节比它应有的程度要困难一些。
　　这到底是怎么回事儿？是你的想法错了还是在运行查询时，服务器的负载过重？这是引起运行时间增加的原因吗？尽管可以多次反复地运行查询得到一个平均时间，但这样作的工作量很大。我们需要用一种很科学的标准对每次测试时的性能进行比较。
　　测量服务器资源是解决查询性能调节问题的关健
　　在服务器上执行查询时，会用到许多种服务器资源。其中的一种资源是CPU的占用时间，假设数据库没有发生任何改变，反复地运行同一个查询其CPU的占用时间将是十分接近的。在这里，我指的不是一个查询从运行开始到结束的时间，而是指运行这一查询所需要的CPU资源数量，运行一个查询所需要的时间与服务器的忙碌程度有关。
　　SQL Server需要的另一种资源是IO。无论何时运行查询，SQL Server都必须从数据缓冲区中读取数据（逻辑读），如果所需要的数据没有在缓冲区中，则需要到磁盘上读取（物理读）。
　　从讨论中可以知道，一个查询需要的CPU、IO资源越多，查询运行的速度就越慢，因此，描述查询性能调节任务的另一种方式是，应该以一种使用更少的CPU、IO资源的方式重写查询命令，如果能够以这样一种方式完成查询，查询的性能就会有所提高。
　　如果调节查询性能的目的是让它使用尽可能少的服务器资源，而不是查询运行的时间最短，那么就更容易测试你采取的措施是提高了查询的性能还是降低了查询的性能。尤其是在资源利用不断变化的服务器上更是如此。首先，需要搞清楚在对查询进行调节时，如何测试我们的服务器的资源使用情况。
　　又想起了SET STATISTICS IO和SET STATISTICS TIME
　　SQL Server很早以前就支持SET STATISTICS IO和SET STATISTICS TIME这二条Transact-SQL命令了，但由于其他一些原因，在调节查询的性能时，许多DBA（数据为系统管理员）都忽略了它们，也许是它们不大吸引人吧。但不管是什么原因，我们下面就会发现，它们在调节查询性能方面还是很有用的。
　　有三种方式可以使用这二条命令：使用Transact-SQL命令行方式、使用Query Analyzer、在Query Analyzer中设置当前连接适当的连接属性。在这篇文章中，我们将使用Transact-SQL命令行的方式演示它们的用法。
　　SET STATISTICS IO和SET STATISTICS TIME的作用象开关那样，可以打开或关闭我们的查询使用资源的各种报告信息。缺省状态下，这些设置是关闭的。我们首先来看一个这些命令如何打开的例子，并看看它们会报告一些什么样的信息。
　　在开始我们的例子前，启动Query Analyzer，并连接到一个SQL Server上。在本例中，我们将使用Northwind数据库，并将它作为这个连接的缺省数据库。
　　然后，运行下面的查询：
　　　SELECT * FROM [order details]
　　如果你没有改动过order details这个表，这个查询会返回2155个记录。这是一个典型的结果，相信你已经在Query Analyzer中看到过好多次了。
　　现在我们来运行同一个查询，不过这次在运行查询之前，我们将首先运行SET STATISTICS IO和SET STATISTICS TIME命令。需要记住的是，这二个命令的打开只对当前的连接有效，当打开其中的一个或二个命令后，再关闭当前连接并打开一个新的连接后，就需要再次执行相应的命令。如果想关闭当前连接中的这二个命令，只要将原来命令中的ON换成OFF，再执行一次就可以了。
　　在开始我们的例子前，先运行下面的这二条命令（不要在正在使用的服务器上执行），这二条命令将清除SQL Server的数据和过程缓冲区，这样能够使我们在每次执行查询时在同一个起点上，否则，每次执行查询得到的结果就不具有可比性了：&&&&&&&&&&DBCC DROPCLEANBUFFERS　　　DBCC FREEPROCCACHE　　输入并运行下面的Transact-SQL命令：　　　SET STATISTICS IO ON　　　SET STATISTICS TIME ON　　一旦上面的准备工作完成后，运行下面的查询：　　　SELECT * FROM [order details]　　如果同时运行上面所有的命令，你得到的输出就会与我的不同，也就很难搞清楚到底发生了什么事情。在运行上述的命令后，就会在结果窗口中看到以前没有看到过的新资料，在窗口的最顶端，会有下面的信息：
SQL Server parse and compile time: （SQL Server解析和编译时间：）CPU time = 10 ms, elapsed time = 61 ms. SQL Server parse and compile time: （SQL Server解析和编译时间：）CPU time = 0 ms, elapsed time = 0 ms.
　　在显示上面的数据后，查询得到的记录就会显示出来。在显示完2155条记录后，会显示出下面的信息：
Table 'Order Details'. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.（表：Order Details，扫描次数 1，逻辑读 10，物理读 1，提前读取 9）SQL Server Execution Times:（SQL Server执行时间：）CPU time = 30 ms, elapsed time = 387 ms.
　　（每次得到的结果可能各不相同，在下面我们讨论显示的信息时会提到这一点。）　　那么，这些信息的具体含意是什么呢？下面我们就来详细地进行分析。　　　　SET STATISTICS TIME的结果　　　　SET STATISTICS TIME命令用于测试各种操作的运行时间，其中一些可能对于查询性能的调节没有什么用处。运行这一命令可以在屏幕上得到如下的显示信息：　　输出的最开始处：
SQL Server parse and compile time: CPU time = 10 ms, elapsed time = 61 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
　　输出的结束处：　　　SQL Server Execution Times:　　　CPU time = 30 ms, elapsed time = 387 ms.　　在输出的最开始处我们可以看到二次测试时间，但第一行执行某一操作所需的CPU的时间和总共时间，但第二行似乎就不是了。　　“SQL Server parse and compile time”表示SQL Server解析“ELECT * FROM [order details]”命令并将解析的结果放到SQL Server的过程缓冲区中供SQL Server使用所需要的CPU运行时间和总的时间。　　在本例中，CPU的运行时间为10毫秒，总时间为61毫秒。由于服务器的配置和负载不同，你得到的CPU运行时间、总时间这二个值可能会与本例中的测试结果有所不同。　　第二行的“SQL Server parse and compile time”表示SQL Server从过程缓冲区中取出解析结果供执行的时间，大多数情况下这二个值都会是0，因为这个过程执行得相当地快。　　如果不清除缓冲区而再次运行SELECT * FROM [order details]命令，CPU运行时间和编译时间会都是0，因为SQL Server会重复使用缓冲区中的解析结果，因此就不需要再次编译的时间了。这些信息在查询性能的调节中对你的帮助真的很大吗？也许并非如此，但我将解释一下这些信息的真正含意，你将会很惊奇，大多数的DBA居然都不真正明白这些信息的含意：　　我们最感兴趣的是显示在输出最后的时间信息：　　　SQL Server Execution Times:　　　CPU time = 30 ms, elapsed time = 387 ms.　　上面显示的信息表明，执行这次查询使用了多少CPU运行时间和运行查询使用了多少时间。CPU运行时间是对运行查询所需要的CPU资源的一种相对稳定的测量方法，与CPU的忙闲程度没有关系。但是，每次运行查询时这一数字也会有所不同，只是变化的范围没有总时间变化大。总时间是对查询执行所需要的时间（不计算阻塞或读数据的时间），由于服务器上的负载是在不断变化的，因此这一数据的变化范围有时会相当地大。　　由于CPU占用时间是相对稳定的，因此可以使用这一数据作为衡量你的调节措施是提高了查询性能还是降低了查询的性能的一种方法。
　　　SET STATISTICS IO的效果　　　SET STATISTICS IO的输出信息显示在输出的结束处，下面是它显示的一个例子： 　　　Table 'Order Details'. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.　　这些信息中的一部分是十分有用的，另一部分则不然，我们来看看每个部分并了解其含意：　　Scan Count：在查询中涉及到的表被访问的次数。在我们的例子中，其中的表只被访问了1次，由于查询中不包括连接命令，这一信息并不是十分有用，但如果查询中包含有一个或多个连接，则这一信息是十分有用的。　　一个循环外部的表的Scan Count值为1，但对于一个循环内的表而言，其值为循环的次数。可以想象得到，对于一个循环内的表而言，其Scan Count值越小，它所使用的资源越少，查询的性能也就越高。因此在调节一个带连接的查询的性能时，需要关注Scan Count的值，在进行调节时，注意观察它是增加还是减少了。　　Logical Reads: 这是SET STATISTICS IO或SET STATISTICS TIME命令提供的最有用的数据。我们知道，SQL Server在可以对任何数据进行操作前，必须首先把数据读取到其数据缓冲区中。此外，我们也知道SQL Server何时会从数据缓冲区中读取数据，并把数据读取到大小为8K字节的页中。 　　那么Logical Reads的意义是什么呢？Logical Reads是指SQL Server为得到查询中的结果而必须从数据缓冲区读取的页数。在执行查询时，SQL Server不会读取比实际需求多或少的数据，因此，当在相同的数据集上执行同一个查询，得到的Logical Reads的数字总是相同的。　　为什么说在调节查询性能中知道SQL Server执行查询时的Logical Reads值是很重要的呢？因为在每次执行同一查询时，这个数值是不会变化的。因此，在进行查询性能的调节时，这是一个可以用来衡量你的调节措施是否成功的一个很好的标准。　　在对查询的性能进行调节时，如果Logical Reads值下降，就表明查询使用的服务器资源减少，查询的性能有所提高。如果Logical Reads值增加，则表示调节措施降低了查询的性能。在其他条件不变的情况下，一个查询使用的逻辑读越少，其效率就越高，查询的速度就越快。　　Physical Reads：在这里我要说的的东西可能初听起来有点自相矛盾，但只要反复思考，就会明白其中的真正含意。　　物理读指的是，在执行真正的查询操作前，SQL Server必须从磁盘上向数据缓冲区中读取它所需要的数据。在SQL Server开始执行查询前，它要作的第一件事就是检查它所需要的数据是否在数据缓冲区中，如果在，就从中读取，如果不在，SQL Server必须首先将它需要的数据从磁盘上读到数据缓冲区中。　　我们可以想象得到，SQL Server在执行物理读时比执行逻辑读需要更多的服务器资源。因此，在理想情况下，我们应当尽量避免物理读操作。　　下面的这一部分听起来让人容易感到糊涂了。在对查询的性能进行调节时，可以忽略物理读而只专注于逻辑读。你一定会纳闷儿，刚才不是还说物理读比逻辑读需要更多的服务器资源吗？　　情况确实是这样，SQL Server在执行查询时所需要的物理读次数不可能通过性能调节而减少的。减少物理读的次数是DBA的一项重要工作，但它涉及到整个服务器性能的调节，而不仅仅是查询性能的调节。在进行查询性能调节时，我们不能控制数据缓冲区的大小或服务器的忙碌程度以及完成查询所需要的数据是在数据缓冲区中还是在磁盘上，唯一我们能够控制的数据是得到查询结果所需要执行的逻辑读的次数。　　因此，在查询性能的调节中，我们可以心安理得地不理会SET STATISTICS IO命令提供的Physical Read的值。（减少物理读次数、加快SQL Server运行速度的一种方式是确保服务器的物理内存足够多。）　　Read-Ahead Reads：与Physical Reads一样，这个值在查询性能调节中也没有什么用户。Read-Ahead Reads表示SQL Server在执行预读机制时读取的物理页。为了优化其性能，SQL Server在认为它需要数据之前预先读取一部分数据，根据SQL Server对数据需求预测的准确程度，预读的数据页可能有用，也可能没用。　　在本例中，Read-Ahead Reads的值为9，Physical Read的值为1，而Logical Reads的值为10，它们之间存在着简单的相加关系。那么我在服务器上执行查询时的过程是怎么样的呢？首先，SQL Server会开始检查完成查询所需要的数据是否在数据缓冲区中，它会很快地发现这些数据不在数据缓冲区中，并启动预读机制将它所需要的10个数据页中的前9个读取到数据缓冲区。当SQL Server检查是否所需要的全部数据都已经在数据缓冲区时，会发现已经有9个数据页在数据缓冲区中，还有一个不在，它就会立即再次读取磁盘，将所需要的页读到数据缓冲区。一旦所有的数据都在数据缓冲区后，SQL Server就可以处理查询了。　　我们应该怎么办？　　我在本篇文章的开始曾提到，在对查询的性能进行调节时用一些科学的标准来测量你的调节措施是否有效是十分重要的。问题是，SQL Servers的负载是动态变化的，使用查询总的运行时间来衡量你正在调节性能的查询的性能是提高了还是没有，并不是一个合理的方法。　　更好的方法是比较多个数据，例如逻辑读的次数或者查询所使用的CPU时间。因此在对查询的性能进行调节时，需要首先使用SET STATISTICS IO和SET STATISTICS TIME命令向你提供一些必要的数据，以便确定你对查询性能进行调节的措施是否真正地得到了目的
阅读(...) 评论()}