Retrieve milliseconds

[Andrew Luis Arruza]

Our DBA's want to retrieve milliseconds for use in one of their queries.
Does anyone know of a % variable that would let them retrieve milliseconds.
Thanks for any/all help.
Points will definitely be assigned.
Thanks, andy

[Marco Paganini]

Hello Andy,

I never knew of a way (in hpux/shell) of getting the current number of ms. However, if you're trying to measure performance, you could use the 'time' command that gives you the elapsed time for a command with millisecond accuracy.

Hope it helps,
Paga

[A. Clay Stephenson]

I suppose about as close as you could come in the shell is this:

timex sqlplus scott/tiger myquery.sql

You would then need to create a baby sql command script which does everything the above script does EXCEPT the query.

timex sqlplus scott/tiger mydummy.sql. The difference between those should be close.

[Darrell Allen]

Hi Andy,

I believe seconds is the lowest level of precision you can get from the date command. That would make sense because time is kept in the number of seconds since the epoch.

Darrell

[A. Clay Stephenson]

Hi, I think I may have misread your question. If you truly want a high-resolution result within Oracle, about the only way I can think of in a Pro/C function that calls clock_gettime().

[Andrew Luis Arruza]

Clay,
Doesn't Oracle (and Sybase) use the OS time to get the time in the get_time command they use?

[A. Clay Stephenson]

They use time() which return epoch seconds but the clock_gettime system call returns seconds + nanoseconds. (You are not guaranteed nanosecond resolution and in any event the call overhead would obliterate that accuracy but millisecond is possible.)

[Jonas Linden]

In Oracle you can use

select DBMS_UTILITY.GET_TIME from dual;

which gives 100ths of seconde that have elapsed since an arbitrary time.

/Jonas

[Raju_6 · Accepted solution]

Hi:

Oracle doen't support milliseconds.
But you can get 100th of a second by using the DBMS_UTILITY.GET_TIME package.

If you need milli seconds conversion, you need to write your own function to manipulate sysdate.

The following PL/SQL block is self explainatory ::


DECLARE
start_date date;
end_date date;
millisec number;
gt1 number;
gt2 number;

BEGIN

gt1:= DBMS_UTILITY.GET_TIME;
dbms_output.put_line('Get_Time Value Before :: '||gt1);
start_date:= sysdate;
dbms_output.put_line('Start Date :: '||to_char(start_date,'DD-MON-YYYY HH24:MI:SS'));

FOR raju IN 1..5000000
LOOP
NULL;
/*
You can use dbms_lock.sleep(n) instead of this dump loop.
*/
END LOOP;

end_date:= sysdate;
dbms_output.put_line('End Date :: '||to_char(end_date,'DD-MON-YYYY HH24:MI:SS'));
millisec:= (end_date-start_date)*24*60*60*1000 ;
gt2:=DBMS_UTILITY.GET_TIME;
dbms_output.put_line('Get_Time Value After :: '||gt2);

dbms_output.put_line('Diff. in time in milli secs ::'||millisec);
dbms_output.put_line('Diff. in Get Time 100th of a sec ::'||to_char(gt2-gt1,'9999999'));
END;
/


Good Luck !
Raju Joseph