Display query execution time in sql monitor

[xiongwei]

How to display query execution time in sql monitor as vdba?

[rhann]

AFAIK there is no direct way to display the elapsed time of a query in VDBA, but you can work it out to the resolution of one second by noting the time before and after running the query as follows:

select date('now');
select blah blah blah;
select date('now');

If you are timing a very fast query you'll either have to write an application to measure the response time, or time running the query a thousand times and divide the total time by a thousand.

[daryl.monge]

Also look at the backslash operators in the terminal monitor; in particular \t

[xiongwei]

thanks.
I used select dbmsinfo('dbms_cpu'), but I am not sure what the value of the dbmsinfo function means.

[denjo02]

"select local_time" seems to return what you want. When run against a Solaris server it returns a figure like this:

14:14:32.958787850

which seems to have enough decimal places for anybody.....

Incidentally, I always seem to have trouble finding these constants. They appear in the SQL Reference Guide in Chapter 3, a section titled (curiously enough) SQL Constants.

[kschendel]

Originally Posted by xiongwei

thanks.
I used select dbmsinfo('dbms_cpu'), but I am not sure what the value of the dbmsinfo function means.

Don't use 'dbms_cpu'. If you ask for dbmsinfo('_cpu_ms'), you'll get a measure of the actual CPU time used by that session since it started. (The CPU time claims to be in milliseconds, but it's only good to about 0.01 second.) 'dbms_cpu' is supposed to return the CPU time used by all sessions, and I don't know how correct it is on OS-threaded servers.

[xiongwei]

Thank the reply from Karl and denjo.
I want to get some performance informations about a single query, such as the CPU time, I/O times and so on. Can you tell me some advice?

[kschendel]

select dbmsinfo('_bio_cnt'),dbmsinfo('_dio_cnt'),dbmsinfo ('_cpu_ms'),dbmsinfo('_et_sec');
run the query;
select dbmsinfo('_bio_cnt'),dbmsinfo('_dio_cnt'),dbmsinfo ('_cpu_ms'),dbmsinfo('_et_sec');

and subtract the first set of dbmsinfo values from the second. The difference is the query time. _bio_cnt is the number of messages to and from the client (usually not very interesting), _dio_cnt is the disk IO count, _cpu_ms is the CPU time in milliseconds, and _et_sec is the wall clock time in seconds. All are for your session only.

[xiongwei]

thanks karl.