oracle11g时间格式毫秒,如何在存储过程中统计一个SQL语句执行了多长时间，精确到毫秒？...

Doc ID:         Note:119441.1

Subject:         How to get the Time within an one-hundredth of a Second Accuracy

Type:         BULLETIN

Status:         PUBLISHED

Content Type:         TEXT/PLAIN

Creation Date:         11-SEP-2000

Last Revision Date:         08-MAY-2002

PURPOSE

-------

Sometimes it may be useful to get the current time with a greater than one

second accuracy.

SCOPE & APPLICATION

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

SQL programmers with special timing precision needs.

INTRODUCTION

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

Although Oracle does not currently support time with a better precision than

one second in its DATE datatype, there are several ways to get times with the

needed accuracy, e.g. 1/100th of a second. In this article we will discuss

three ways of doing this, two internal to the Oracle server, and one external.

One could wonder why such a precision may be needed at all. A greater than one

second precision cannot be guaranteed for database events happening during disk

I/Os, query execution, process spawning, network connections or sometimes even

procedure calls. So why an 1/100th precision? Well, there are cases when

moments in time need to be localized with a greater granularity than the

second, e.g. to be able to distinguish close events in a unambiguous way.

A sequence could be used too since obtaining the nextval is an atomic operation,

but computing a difference between sequence numbers is physically meaningless

- apart from the number of subsequent, possibly unrelated calls to nextval -

and hence cannot be used to denote a delay or elapsed amount of time.

GETTING 1/100th SECOND ACCURACY - THE INTERNAL WAYS

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

1. First way

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

The first, easiest way to get the time with such a precision is simply to call

the dbms_utility.get_time() function, whose package specification is located in

the SQL script file $ORACLE_HOME/rdbms/dbmsutil.sql and package body in

$ORACLE_HOME/rdbms/prvtutil.plb. One needs to explicitly recompile these two

files as INTERNAL or SYS AS SYSDBA to get access to the dbms_utility package.

dbms_utility.get_time() returns the current time in 100th of a second units

from some unspecified, arbitrary, operating system dependent epoch. Thus it

is not possible to convert such values to the traditional

�DD/MM/YYYY HH:MI:SS ss� picture representation (or rather, this is not

documented although it could be deduced from studying a representative set of

the function's returned values, but this is not guaranteed to stay unchanged

from version to version), but arithmetic between such dates is possible and

time uniqueness is likely preserved.

Here are a few usage examples:

SQL> select dbms_utility.get_time from dual

GET_TIME

---------

43118

SQL> /

GET_TIME

---------

43416

SQL> var x number

SQL> exec :x := dbms_utility.get_time

PL/SQL procedure successfully completed.

SQL> print x

X

---------

68050

2. The Second way

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

The second, somewhat less practical way consists in reading from the V$TIMER

public synonym, which points to the dynamic performance view V_$TIMER. It is

less practical because the view belongs to the SYS schema which does not grant

any SELECT access to it, even though it does on the public synonym V$TIMER,

or to the SYSDBA role.

Therefore, a set-up is needed, mainly publicly granting the SELECT object

privilege on V_$TIMER as INTERNAL or SYS, like in the following transcript:

SQL> connect sys as sysdba

Enter password: *****************

Connected.

SQL> grant select on v_$timer to scott;

Grant succeeded.

SQL> connect scott/tiger

Connected.

SQL> select * from v$timer;

HSECS

---------

687763

SQL> /

HSECS

---------

688002

SQL> select * from sys.v_$timer;

HSECS

---------

693354

SQL> /

HSECS

---------

693599

Oracle recommends using the V$* synonyms instead of their V_$* counterparts,

but even then no guarantee is given to support any dynamic performance view

in the future.

Writing a wrapper around the view is also possible though a bit more tedious:

SQL> connect sys as sysdba

Enter password: *****************

Connected.

SQL> revoke select on v_$timer from scott;

Revoke succeeded.

SQL> create or replace function timer return binary_integer AS

2     N binary_integer;

3  BEGIN

4     select hsecs into N from v_$timer;

5     return N;

6  end timer;

7  /

Function created.

SQL> create public synonym f_timer for timer;

Synonym created.

SQL> grant execute on f_timer to public;

Grant succeeded.

connect scott/tiger

Connected.

SQL> select f_timer from dual;

F_TIMER

---------

843737

SQL> /

F_TIMER

---------

843980

SQL> var x number

SQL> exec :x := f_timer;

PL/SQL procedure successfully completed.

SQL> print x

X

---------

79780

As V$TIMER stores the time value in a 4-byte long variable, it is subject to

wrap around after the value 232-1 has been reached, which occurs about every

497 days.

GETTING 1/100th SECOND ACCURACY - THE EXTERNAL WAYS

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

The third way to get times with such an accuracy is to communicate with

external processes that make available such a time routine. To this effect,

pipes or external procedures can be used. Those processes could be written in

a language where the needed routine is supplied, such as the C language, which

defines the time() function in the stdlib standard library. The called process

simply has to call one such function, properly format the result and pass it

back to the caller.

How to set up and use external procedures has been extensively treated in

precedent articles so we will not indulge into this here.

Pipes are even better because no dlls or shared library are needed though

they are primarily suited to asynchronous interprocess communications.

A good example of how to use pipes is exposed in the SQL script file

$ORACLE_HOME/rdbms/dbmspipe.sql which provides the package description.

CONCLUSION

----------

As shown above, the simplest and safest way to get the desired time precision

is to use the dbms_utility.get_time() package. No special setup is needed -

except compiling the package once - and no risk to use a deprecated view is to

be feared because the function isolates its user from the underlying

implementation.