[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了.越界發生的機率很小的.

[20171106]DBMS_UTILITY.GET_TIME().txt

相關文章