专栏首页数据库新发现从 Oracle 到 PostgreSQL :从 Uptime 到数据库实例运行时间

从 Oracle 到 PostgreSQL :从 Uptime 到数据库实例运行时间

原文:http://www.enmotech.com/web/detail/1/686/1.html  (复制链接,打开浏览器即可查看)

在接触操作系统时,我们常常习惯通过 uptime 来看看系统的启动运间,例如:

 1 [oracle@zData ~]$uptime行时  2 17:00:17 up 656 days, 22:18,  4 users, load average: 0.16, 0.16, 0.14

在 Oracle 数据库中,同样类似的,可以计算出数据库的启动时间,以了解数据库实例连续运行的时间。以下的 SQL 查询,通过时间运算得出了数据库的相关运行时间值:

1 SQL> COLUMN STARTED_SINCE format A25 2 SQL> COLUMN UPTIME format A50 3 SQL> SELECT TO_CHAR (startup_time, 'DD-MON-YYYY HH24:MI:SS')started_since, 4 2         TRUNC (SYSDATE -(startup_time)) 5 3         || ' day(s), ' || TRUNC (  24 * ((SYSDATE - startup_time) - 6 4         TRUNC (SYSDATE - startup_time))) 7 5         || ' hour(s), '|| MOD (TRUNC (  1440 * ( (SYSDATE - startup_time) - 8 6         TRUNC (SYSDATE - startup_time))),60) 9 7         || ' minute(s), '|| MOD (TRUNC (  86400 * ( (SYSDATE - startup_time) - 10 8         TRUNC (SYSDATE - startup_time))),60) 11 9         || ' seconds' uptime 12 10 FROM v$instance; 13 14 STARTED_SINCE              UPTIME 15 --------------------------------------------------------------------------- 16 08-DEC-2018 21:36:19     164 day(s), 19 hour(s), 19 minute(s), 13seconds

在 PostgreSQL 中,同样可以通过查询得到类似的效果:

1 select pg_postmaster_start_time()as START_SINCE, 2 date_part('day',current_timestamp-pg_postmaster_start_time())||'day(s),'|| 3 date_part('hour',current_timestamp-pg_postmaster_start_time())||'hour(s),'|| 4 date_part('minutes',current_timestamp-pg_postmaster_start_time())||'minute(s),'|| 5 date_part('seconds',current_timestamp-pg_postmaster_start_time())||'second(s)' as UPTIME; 6          start_since          |                       uptime                         7 -------------------------------+-----------------------------------------------------  8 2019-04-25 18:13:25.968474+08 | 26day(s),23 hour(s),4 minute(s),1.279786 second(s)

在 PostgreSQL 中,关于时间处理的两个函数非常有用,date_part 可以将日期中的不同部分抽取出来,而 date_trunc 则类似 Oracle 中 Trunc 函数的作用,将时间进行截取处理。

1 select date_trunc('day',current_timestamp-pg_postmaster_start_time());  2 date_trunc 3 ------------ 4 26 days 5 (1 row) 6 eygle=# selectdate_trunc('hour',current_timestamp-pg_postmaster_start_time()); 7   date_trunc     8 ------------------ 9 26 days 22:00:00 10 (1 row)

也可以通过 extract 实现类似的功能:

1 selectextract(day from current_timestamp-pg_postmaster_start_time());  2 date_part  3 ----------- 4        26 5 eygle=# select extract(hour fromcurrent_timestamp-pg_postmaster_start_time()); 6 date_part 7 ----------- 8        23

PostgreSQL 中,用户返回当前时间的函数有 current_date、current_time 和 current_timestamp 等:

1 select current_timestamp;       2 current_timestamp       3 ------------------------------- 4 2019-05-22 17:10:40.575532+08 5 (1 row) 6  7 eygle=# select current_date; 8 current_date  9 -------------- 10 2019-05-2211 (1 row)

这和 Oracle 数据库非常相似,通过 sysdate 和 systimestamp 能够返回 Oracle 的当前时间,以下是 Oracle 数据库中的语法:

1 SQL> select sysdate,systimestamp from dual; 2 3 SYSDATE 4 ------------------- 5 SYSTIMESTAMP 6 --------------------------------------------------------------------------- 7 2019-05-22 17:25:47 8 22-MAY-19 05.25.47.109129 PM +08:00

注意,dual 表是Oracle中的特殊存在,而 PostgreSQL 的函数不需要这样的依托直接返回了结果。

在 PostgreSQL 中,功能近似的函数特别丰富,例如如下这些函数:

1 transaction_timestamp() 2 statement_timestamp() 3 clock_timestamp() 4 timeofday() 5 now()

此外,通过 interval 可以对时间进行推移:

1 select now() + interval '2 years';           ?column?           ------------------------------ 2021-05-22 17:51:13.98532+08 eygle=# select now() + interval '1 month';           ?column?            ------------------------------- 2019-06-22 17:52:12.737686+08(1 row) eygle=# select now() - interval '1 week';           ?column?            ------------------------------- 2019-05-15 17:52:26.425832+08(1 row)eygle=# select now() + '10 min';           ?column?            ------------------------------- 2019-05-22 18:02:35.013766+08(1 row)

在 PostgreSQL 中还有一个有趣的函数 age,可以用来计算年龄,1990年出生的同学们竟然马上要30岁啦,成家了没,同学们?

1 select age(now(),date '1990-01-01'); 2                   age                   3 ----------------------------------------- 4 29 years 4 mons 21 days17:58:43.875068

在计算机系统中,还有一个特殊的时间计算方法,叫做 Unix Time,这个时间是自 UTC 时间 1970-01-01 00:00:00至今的秒数,这个计时方式同样被传导到数据库中。(如下图所示)

在 PostgreSQL 中,可以通过 epoch(即特定时点 1970-01-01 00:00:00 UTC)为起点进行计算。以下是两个方向的转换方式:

1 select extract(epoch from now()); 2     date_part     3 ------------------ 4 1558519237.02995 5 6 eygle=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1558519237 * INTERVAL '1second'; 7        ?column?         8 ------------------------ 9 2019-05-22 18:00:37+08

在 MySQL 中,通过 FROM_UNIXTIME和 UNIX_TIMESTAMP 函数可以实现类似的转换和计算:

1 mysql> select FROM_UNIXTIME(1558519237,'%Y-%m-%d %H:%i:%S'); 2 +-----------------------------------------------+ 3 | FROM_UNIXTIME(1558519237,'%Y-%m-%d %H:%i:%S') | 4 +-----------------------------------------------+ 5 | 2019-05-22 18:00:37                           | 6 +-----------------------------------------------+ 7 1 row in set (0.00 sec) 8 9 mysql> select UNIX_TIMESTAMP('2019-05-22 18:00:37'); 10 +---------------------------------------+ 11 | UNIX_TIMESTAMP('2019-05-22 18:00:37') | 12 +---------------------------------------+ 13 |                           1558519237 | 14 +---------------------------------------+ 15 1 row in set (0.02 sec)

在 Oracle 的数据库中,UnixTime 同样是非常重要的,在 SYS 用户的 SMON_SCN_TIME字典中记录中 Unix Time 和 Date 时间的对应,TIME_MP 和 TIME_DP 两个字段记录的就是这样的信息,这些信息在恢复时非常重要,Oracle 又将时间和 SCN 关联了起来。

1 SQL> desc smon_scn_time 2 Name                         Null?   Type 3 ------------------------------------------------- ---------------------------- 4 THREAD                        NUMBER 5 TIME_MP                       NUMBER 6 TIME_DP                       DATE 7 SCN_WRP                       NUMBER 8 SCN_BAS                       NUMBER 9 NUM_MAPPINGS                       NUMBER 10 TIM_SCN_MAP                       RAW(1200) 11 SCN                                NUMBER 12 ORIG_THREAD                        NUMBER 13 14 SQL> select time_mp from smon_scn_time where rownum < 2; 15 TIME_MP 16 ---------- 17 1558502931 18 19 SQL> select time_mp,time_dp from smon_scn_time where rownum < 2; 20 TIME_MP TIME_DP 21 ---------- ------------------- 22 1558502931 2019-20 05-22 05:28:51  23 24 SQL>  select time_mp,time_dp fromsmon_scn_time 25 2 where time_mp = (select max(time_mp) from smon_scn_time); 26   27 TIME_MP TIME_DP 28 ---------- ------------------- 29 1558519988 2019-05-22 10:13:08

Oracle 数据库中没有提供转换函数,我们通过 PostgreSQL 转换一下验证:

1 select TIMESTAMP WITH TIME ZONE 'epoch' + 1558519988 * INTERVAL '1second'; 2        ?column?         3 ------------------------ 4 2019-05-22 18:13:08+08

注意到转换的时间和 Oracle 记录的 TIME_DP相差了 8 个小时,这是什么原因呢?这是因为数据库操作系统采用的是 CST 时间:

1 [oracle@zData ~]$ date 2 Wed May 22 18:27:35 CST 2019

CST 时间和 UTC 时间相差 8 小时(CST = UTC + 8),smon_scn_time 记录的时间按照 CST 时间进行了换算,实际上是非常精确的吻合。

在 Oracle 数据库中,还有一个动态性能视图 V$TIMER 记录了 epoch 时间,官方文档这样描述(来自 19c 文档):

V$TIMER displays the elapsed time in hundredths of a second. Time ismeasured since the beginning of the epoch, which is operating system specific,and wraps around to 0 again whenever the value overflows four bytes (roughly497 days).

这段描述说明 V$TIMER 记录的是厘秒,从 epoch 时间起点量度,这个值来自操作系统,由于在数据库中使用 4 bytes 记录,当主机连续运行大约 497 天之后,这个值会归零重新开始。在 Oracle 9i 中,因为 JOB 的时间定义依赖这个值,所以存在一个 BUG 是 497 天后所有 JOB 会停止执行。

多年以前遇到过一个有趣的故事,在这里引用一下。

某日,同事告诉我一个发现,他说一台数据库的运行时间超过了操作系统的启动时间。

从数据库内部可以查询到数据库实例的启动时间:

1 SQL> SELECT TO_CHAR(startup_time, 'DD-MON-YYYY HH24:MI:SS') started_at, 2 , TRUNC (SYSDATE -(startup_time)) 3 2  || ' day(s), ' || TRUNC (  24 *((SYSDATE - startup_time) - 4 3 TRUNC (SYSDATE - startup_time))) 5 4  || ' hour(s), '|| MOD (TRUNC (  1440 *( (SYSDATE - startup_time) - 6 5 TRUNC (SYSDATE - startup_time))),60) 7 6  || ' minute(s), '|| MOD (TRUNC ( 86400 * ( (SYSDATE - startup_time) - 8 7 TRUNC (SYSDATE - startup_time))),60) 9 8  || ' seconds' uptime 10 9 FROM v$instance; 11 10 12  STARTED_AT                UPTIME 13  ------------------------- -------------------------------------------------- 14 05-JUL-2005 10:36:58      803 day(s), 2 hour(s), 27 minute(s),55 seconds

从这里看数据库实例启动了 803 天左右,也就是说自 2005-07-05 开始这个数据库一直在不间断的运行着。而从操作系统的 uptime 来看,系统不过启动了 306 天:

1 SQL> ! uptime 2 13:06:21  up 306 days, 19:00,  1 user,  load average: 0.00,0.00, 0.0

同事问我原因,首先我们检查 alert 文件,发现数据库的确是 2005 年启动的。再研究一下,发现这是又一次时间溢出的问题,由于某些 Linux 内核使用 32 位无符号长整型来计算时间,32 位的最大值就是 0xffffffff,再加 1 就将溢出变为 0。

以下一小段 C 代码可以解释这种溢出:

1 [root@jumper root]# cat a.c 2 int main(void){ 3 unsigned int num = 0xffffffff; 4 5 printf("num is %d bits long\n", sizeof(num) * 8); 6 printf("num = 0x%x\n", num); 7 printf("num + 1 = 0x%x\n", num + 1); 8 9 return 0; 10 } 11 [root@jumper root]# gcc -o un a.c 12 [root@jumper root]# ./un 13 num is 32 bits long 14 num = 0xffffffff 15 num + 1 = 0x0

在这个 Linux 发行版本上,这个时间就此溢出:

1 SQL> ! uname -a 2 Linux moto 2.4.21-15.ELsmp #1 SMP Thu Apr 22 00:18:24 EDT 2004 i686 i686 i386GNU/Linux 3 SQL> ! cat /etc/redhat-release 4 Red Hat Enterprise Linux AS release 3 (Taroon Update 2)

根据 497 天再来计算一下:

1 SQL> select 803 - 306 from dual;803-306----------497

当前数据库的显示是正确的,803 天减去 uptime 显示时间,得出的正好是 497 天。

关于时间,Oracle 中有很多有意思的话题,参考:

https://www.eygle.com/archives/2007/09/497_day_linux_limit.html

https://www.eygle.com/archives/2004/11/job_can_not_execute_auto.html

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle诊断案例----如何捕获问题SQL解决过度CPU消耗问题

    Last Updated: Sunday, 2004-10-24 0:37 Eygle

    数据和云01
  • DBA警世录:有些习惯DBA需要养成

    作者:eygle  原文链接: http://www.eygle.com/archives/2007/02/dba_need_some_habit.html

    数据和云01
  • Statspack报告主要参数指标简要说明

    全文链接:         http://www.eygle.com/more/statspack_list.htm

    数据和云01
  • 从 Oracle 到 PostgreSQL :从 Uptime 到数据库实例运行时间

    在接触操作系统时,我们常常习惯通过 uptime 来看看系统的启动运行时间,例如:

    数据和云
  • 《手把手教你》系列进阶篇之3-python+ selenium自动化测试 - python几种骚操作你都知道吗?(详细教程)

      这篇文章主要是给小伙伴或者童鞋们介绍和分享 python几种骚操:读取配置文件、获取根目录的相对路径、获取系统时间和格式化时间显示、字符串切割等等操作。为...

    北京-宏哥
  • python--time模块

    time.localtime([secs])  默认将当前时间戳转成当前时区的struct_time

    py3study
  • Python获取时间戳

    结果如下: 1573099239000 2019-11-07 12:00:39

    hankleo
  • Python——神奇的闭包(装饰器的应用)

    Ed_Frey
  • Python从0到1:GIL全局解释器锁

    上篇文章《Python从0到1:threading多线程编程》提及一个名词全局解释器锁GIL,很多Python爱好者私信给我说不理解它的原理,今天就对GIL单独...

    企鹅号小编
  • Python utc转化时间

    最近在开发项目的时候, 遇到了时区, 本地时间和UTC糅杂的情况, 研究之后做一些分享.

    py3study

扫码关注云+社区

领取腾讯云代金券