[20171106]DBMS_UTILITY.GET_TIME().txt

--//有时候测试某个脚本运行时间,经常在这之前之后调用这个函数.今天奇怪的发现显示竟然是负数,感觉很奇怪做一个探究.

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

$ cat x1.sql
set numw 38
select dbms_utility.get_time() from dual ;
host sleep 5
select dbms_utility.get_time() from dual ;

SCOTT@book> @ x1.sql

               DBMS_UTILITY.GET_TIME()
--------------------------------------
                           -2136560314

               DBMS_UTILITY.GET_TIME()
--------------------------------------
                           -2136559814

SCOTT@book> select -2136559814 - (-2136560314) from dual ;
             -2136559814-(-2136560314)
--------------------------------------
                                   500

--//很明显两者相减是500,可以猜测这个单位是厘秒.
SCOTT@book> @ &r/desc_proc sys dbms_utility get_time
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats

OWNER      PACKAGE_NAME         OBJECT_NAME                    SEQUENCE ARGUMENT_NAME        DATA_TYPE            IN_OUT    DATA_TYPE            DEFAULTED
---------- -------------------- ------------------------------ -------- -------------------- -------------------- --------- -------------------- ----------
SYS        DBMS_UTILITY         GET_TIME                              1                      NUMBER               OUT       NUMBER               N

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_util.htm#i1002765
GET_TIME Function

This function determines the current time in 100th's of a second. This subprogram is primarily used for determining
elapsed time. The subprogram is called twice – at the beginning and end of some process – and then the first (earlier)
number is subtracted from the second (later) number to determine the time elapsed.

Syntax

DBMS_UTILITY.GET_TIME
  RETURN NUMBER;

Return Values

Time is the number of 100th's of a second from the point in time at which the subprogram is invoked.

Usage Notes

Numbers are returned in the range -2147483648 to 2147483647 depending on platform and machine, and your application must
take the sign of the number into account in determining the interval. For instance, in the case of two negative numbers,
application logic must allow that the first (earlier) number will be larger than the second (later) number which is
closer to zero. By the same token, your application should also allow that the first (earlier) number be negative and
the second (later) number be positive.

--//文档实际上已经讲的很清楚,范围-2147483648 to 2147483647, 相当于 -2^31 to 2^31-1. 这样存在一种可能就是越界.
--//你可以想像假设现在是2147483647,在下一秒就越界了,显示应该是负数.这样两者相减就是负数,而且可能大的离谱.当然遇到这种情
--//况概率还是很低的.^_^

--//我看了另外的机器:
SYS@XXXX> select dbms_utility.get_time() from dual ;
DBMS_UTILITY.GET_TIME()
-----------------------
             1010237048

--//(2147483647-1010237048)/100/86400 = 131.62576377314814814814,这样这套系统再过132天显示的就是负数.

SCOTT@book> select power(2,32)/86400/100 from dual ;
                 POWER(2,32)/86400/100
--------------------------------------
497.1026962962962962962962962962962963

--//过497天就回头.

--//继续测试关闭数据库,修改x1.sql,前面加入startup.脚本如下:
$ cat x2.sql
startup
set numw 38
select dbms_utility.get_time(),sysdate  from dual ;

               DBMS_UTILITY.GET_TIME() SYSDATE
-------------------------------------- -------------------
                           -2130748812 2017-11-07 08:33:39

--//再次重启看看.
               DBMS_UTILITY.GET_TIME() SYSDATE
-------------------------------------- -------------------
                           -2130658039 2017-11-07 08:48:47

--//再次重启看看.
               DBMS_UTILITY.GET_TIME() SYSDATE
-------------------------------------- -------------------
                           -2130493922 2017-11-07 09:16:08

--//两者日期,以及DBMS_UTILITY.GET_TIME()相减,非常接近.

SYS@book> select (to_date('2017-11-07 08:48:47','yyyy-mm-dd hh24:mi:ss')- to_date('2017-11-07 08:33:39','yyyy-mm-dd hh24:mi:ss'))*8640000 from dual ;
(TO_DATE('2017-11-0708:48:47','YYYY-MM-DDHH24:MI:SS')-TO_DATE('2017-11-0708:33:39','YYYY-MM-DDHH24:MI:SS'))*8640000
-------------------------------------------------------------------------------------------------------------------
                                                                                                              90800

SYS@book> select 2130748812 - 2130658039 from dual ;
                 2130748812-2130658039
--------------------------------------
                                 90773

SYS@book> select (to_date('2017-11-07 09:16:08','yyyy-mm-dd hh24:mi:ss')- to_date('2017-11-07 08:48:47','yyyy-mm-dd hh24:mi:ss'))*8640000 from dual ;
(TO_DATE('2017-11-0709:16:08','YYYY-MM-DDHH24:MI:SS')-TO_DATE('2017-11-0708:48:47','YYYY-MM-DDHH24:MI:SS'))*8640000
-------------------------------------------------------------------------------------------------------------------
                                                                                                             164100

SYS@book> select 2130658039 -2130493922 from dual ;
                 2130658039-2130493922
--------------------------------------
                                164117

--//修改x2.sql继续测试:

$ cat x2.sql
startup
set numw 38
select sysdate-(dbms_utility.get_time()+2147483648)/86400/100 from dual ;
select dbms_utility.get_time(),sysdate  from dual ;

--//再次重启看看.
SYSDATE-(DBMS_UTILI
-------------------
2017-11-05 10:04:32

               DBMS_UTILITY.GET_TIME() SYSDATE
-------------------------------------- -------------------
                           -2130296105 2017-11-07 09:49:07

--//你可以反复测试,时间都是按照2017-11-05 10:04:32作为起点.不知道为什么?做多相差+-2秒.下午在看看,先放一放....^_^.
SYSDATE-(DBMS_UTILI
-------------------
2017-11-05 10:04:34

               DBMS_UTILITY.GET_TIME() SYSDATE
-------------------------------------- -------------------
                           -2128493292 2017-11-07 14:49:38

--//在一台10g的环境测试:
SYS@test> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SYSDATE-(DBMS_UTILI
-------------------
2017-11-05 10:43:06

               DBMS_UTILITY.GET_TIME() SYSDATE
-------------------------------------- -------------------
                           -2128324889 2017-11-07 15:56:14

SYSDATE-(DBMS_UTILI
-------------------
2017-11-05 10:43:07

               DBMS_UTILITY.GET_TIME() SYSDATE
-------------------------------------- -------------------
                           -2128315466 2017-11-07 15:57:49

===========================

--//明天继续看看,重启数据库执行x2.sql,11g的测试环境:
SYSDATE-(DBMS_UTILI
-------------------
2017-11-05 10:04:44

               DBMS_UTILITY.GET_TIME() SYSDATE
-------------------------------------- -------------------
                           -2121864761 2017-11-08 09:14:33

--//今天的起点是"2017-11-05 10:04:44",与前面有40分钟差异.

--//在一台10g的环境测试:
SYSDATE-(DBMS_UTILI
-------------------
2017-11-05 10:43:17

               DBMS_UTILITY.GET_TIME() SYSDATE
-------------------------------------- -------------------
                           -2122079933 2017-11-08 09:17:14

SYSDATE-(DBMS_UTILI
-------------------
2017-11-05 10:43:17

               DBMS_UTILITY.GET_TIME() SYSDATE
-------------------------------------- -------------------
                           -2122075037 2017-11-08 09:18:03

--//10g有10秒差距.

--//放弃,这些细节知道没意思,仅仅知道以一个起点取时间.单位是厘秒就ok了.越界发生的概率很小的.