问题描述

    使用SQL语句在oracle数据库里面拉取dbtime数据，单节点拉取我用以下语句拉取，数值正确

set linesize 200;
set pagesize 20000;
col DATE_TIME  for a45;
col STAT_NAME  for a10;
WITH sysstat AS (
select 
sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.VALUE e_value,
lag (ss.VALUE, 1) over (ORDER BY ss.snap_id) b_value
FROM
DBA_HIST_SYS_TIME_MODEL ss,
dba_hist_snapshot sn
WHERE
trunc (sn.begin_interval_time) >= sysdate - 1
AND ss.snap_id = sn.snap_id
AND ss.dbid = sn.dbid
AND ss.instance_number = sn.instance_number
AND ss.dbid = (SELECT dbid FROM v$database)
AND ss.instance_number = 1
AND ss.stat_name = 'DB time'
) SELECT
to_char (
BEGIN_INTERVAL_TIME,
'yyyy-mm-dd hh24:mi'
) || to_char (
END_INTERVAL_TIME,
' hh24:mi'
) date_time,
stat_name,
round(
(e_value - nvl(b_value, 0))/60/1000/1000 ,2
) dbtime_value
FROM
sysstat
WHERE
(e_value - nvl(b_value, 0)) > 0
AND nvl (b_value, 0) > 0;

双节点，我换成以下SQL语句拉取，取出来的数据缺不正确

set linesize 200;
set pagesize 20000;
col DATE_TIME  for a45;
col STAT_NAME  for a10;
WITH sysstat AS (
select 
sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.VALUE e_value,
gv.instance_number instance_number,
lag (ss.VALUE, 1) over (ORDER BY ss.snap_id) b_value
FROM
DBA_HIST_SYS_TIME_MODEL ss,
dba_hist_snapshot sn,
gv$instance gv
WHERE
trunc (sn.begin_interval_time) >= sysdate - 1
AND ss.snap_id = sn.snap_id
AND ss.dbid = sn.dbid
AND ss.instance_number = sn.instance_number
AND ss.dbid = (SELECT dbid FROM v$database)
AND ss.stat_name = 'DB time'
) SELECT
to_char (
BEGIN_INTERVAL_TIME,
'yyyy-mm-dd hh24:mi'
) || to_char (
END_INTERVAL_TIME,
' hh24:mi'
) date_time,
stat_name,
instance_number,
round(
(e_value - nvl(b_value, 0))/60/1000/1000 ,2
) dbtime_value
FROM
sysstat
WHERE
(e_value - nvl(b_value, 0)) > 0
AND nvl (b_value, 0) > 0;

想要的结果

使用SQL语句，拉取多节点的dbtime，得到的值正确。