关于Oracle多租户架构下的每个PDB的dbtime查询

Posted on July 26, 2022 by Alfred Zhao

有客户咨询在19c多租户这样的架构中，除了查询cdb本身外，还想查询具体pdb的负载（DB Time），但是使用之前的脚本发现查询不到，只显示cdb自己的结果，客户写的脚本如下：

SELECT i.instance_name instance_name_print,
       s.snap_id snap_id,
       TO_CHAR (s.startup_time, 'mm/dd/yyyy HH24:MI:SS') startup_time,
       TO_CHAR (s.begin_interval_time, 'mm/dd/yyyy HH24:MI:SS')
          begin_interval_time,
       TO_CHAR (s.end_interval_time, 'mm/dd/yyyy HH24:MI:SS')
          end_interval_time,
       ROUND (
          EXTRACT (DAY FROM s.end_interval_time - s.begin_interval_time)
          * 1440
          + EXTRACT (HOUR FROM s.end_interval_time - s.begin_interval_time)
            * 60
          + EXTRACT (MINUTE FROM s.end_interval_time - s.begin_interval_time)
          + EXTRACT (SECOND FROM s.end_interval_time - s.begin_interval_time)
            / 60,
          2)
          elapsed_time,
       ROUND ( (e.VALUE - b.VALUE) / 1000000 / 60, 2) db_time,
       ROUND (
          ( ( ( (e.VALUE - b.VALUE) / 1000000 / 60)
             / (EXTRACT (
                   DAY FROM s.end_interval_time - s.begin_interval_time)
                * 1440
                + EXTRACT (
                     HOUR FROM s.end_interval_time - s.begin_interval_time)
                  * 60
                + EXTRACT (
                     MINUTE FROM s.end_interval_time - s.begin_interval_time)
                + EXTRACT (
                     SECOND FROM s.end_interval_time - s.begin_interval_time)
                  / 60)
             / 40)),
          2)
          CPU_Carrying_Capacity,
       ROUND (
          ( ( ( (e.VALUE - b.VALUE) / 1000000 / 60)
             / (EXTRACT (
                   DAY FROM s.end_interval_time - s.begin_interval_time)
                * 1440
                + EXTRACT (
                     HOUR FROM s.end_interval_time - s.begin_interval_time)
                  * 60
                + EXTRACT (
                     MINUTE FROM s.end_interval_time - s.begin_interval_time)
                + EXTRACT (
                     SECOND FROM s.end_interval_time - s.begin_interval_time)
                  / 60))
           * 100),
          2)
          pct_db_time
  FROM dba_hist_snapshot s,
       gv$instance i,
       dba_hist_sys_time_model e,
       dba_hist_sys_time_model b,
       (SELECT (begin_interval_time) tt
          FROM dba_hist_snapshot
         WHERE instance_number =1) f
WHERE     i.instance_number = s.instance_number
       AND e.snap_id = s.snap_id
       AND F.tt = s.begin_interval_time
       AND b.snap_id = s.snap_id - 1
       AND e.stat_id = b.stat_id
       AND e.instance_number = b.instance_number
       AND e.instance_number = s.instance_number
       AND e.stat_name = 'DB time'
order by begin_interval_time;

因为自己前些年做运维的时候接触客户环境大部分还是使用的11g，没太关注这方面的更新，和同事讨论，第一想法看到 dba_hist_* 这类视图不包含pdb的信息，是否应该有 cdb_hist_*这类的视图包含呢？
这是一个比较正常的思考方向，但实际验证发现，在这个问题上并不是这样，这两个视图都没有对应的pdb信息。

SQL> select distinct CON_ID from dba_hist_sys_time_model;

    CON_ID
----------
     1

SQL> select distinct CON_ID from cdb_hist_sys_time_model;

    CON_ID
----------
     1

SQL> select distinct CON_ID from dba_hist_snapshot;

    CON_ID
----------
     0

SQL> select distinct CON_ID from cdb_hist_snapshot;

    CON_ID
----------
     0

看起来不是这个问题，那难道说19c多租户架构就查不到pdb层面的dbtime吗？
这也是不太可能的，毕竟AWR都能依据pdb层面做采集和分析呢。。
发现官方文档针对 “DBA_HIST_SYS_TIME_MODEL” 的描述，下面的See Also: 有提到另外一个相关视图 “DBA_HIST_CON_SYS_TIME_MODEL”，这有啥区别？
正在找不同，此时同事 Shine 发我的一段描述恰好说明了二者的区别：
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/gathering-database-statistics.html#GUID-18E1B278-B3C5-4FE1-8E51-BC8878C439F5

DBA_HIST Views

The DBA_HIST views show the AWR data present only on the CDB root.
When the DBA_HIST views are accessed from a CDB root, they show all the AWR data stored on the CDB root.
When the DBA_HIST views are accessed from a PDB, they show the subset of the CDB root AWR data, which is specific to that PDB.

DBA_HIST_CON Views

The DBA_HIST_CON views are similar to the DBA_HIST views, but they provide more fine grained information about each container, and thus, they have more data than the DBA_HIST views.
The DBA_HIST_CON views show the AWR data present only on the CDB root.
When the DBA_HIST_CON views are accessed from a CDB root, they show all the AWR data stored on the CDB root.
When the DBA_HIST_CON views are accessed from a PDB, they show the subset of the CDB root AWR data, which is specific to that PDB.

感谢同事 Shine 的帮忙，更快的找到了这个区别对应的官方解释。
看起来DBA_HIST_CON系列视图会有更完整的每个pdb的信息，跟客户解释，客户说好像之前也尝试过这个视图，但是结果有负数的情况，感觉上不太对。
于是我在自己测试环境测了下，初步改了下几个点，没有细看，但没有负数显示不正确的情况：
主要修改就是替换视图，以及标识不同的con_id，具体修改如下：

SELECT e.con_id con_id,
       s.snap_id snap_id,
       TO_CHAR (s.startup_time, 'mm/dd/yyyy HH24:MI:SS') startup_time,
       TO_CHAR (s.begin_interval_time, 'mm/dd/yyyy HH24:MI:SS')
          begin_interval_time,
       TO_CHAR (s.end_interval_time, 'mm/dd/yyyy HH24:MI:SS')
          end_interval_time,
       ROUND (
          EXTRACT (DAY FROM s.end_interval_time - s.begin_interval_time)
          * 1440
          + EXTRACT (HOUR FROM s.end_interval_time - s.begin_interval_time)
            * 60
          + EXTRACT (MINUTE FROM s.end_interval_time - s.begin_interval_time)
          + EXTRACT (SECOND FROM s.end_interval_time - s.begin_interval_time)
            / 60,
          2)
          elapsed_time,
       ROUND ( (e.VALUE - b.VALUE) / 1000000 / 60, 2) db_time,
       ROUND (
          ( ( ( (e.VALUE - b.VALUE) / 1000000 / 60)
             / (EXTRACT (
                   DAY FROM s.end_interval_time - s.begin_interval_time)
                * 1440
                + EXTRACT (
                     HOUR FROM s.end_interval_time - s.begin_interval_time)
                  * 60
                + EXTRACT (
                     MINUTE FROM s.end_interval_time - s.begin_interval_time)
                + EXTRACT (
                     SECOND FROM s.end_interval_time - s.begin_interval_time)
                  / 60)
             / 40)),
          2)
          CPU_Carrying_Capacity,
       ROUND (
          ( ( ( (e.VALUE - b.VALUE) / 1000000 / 60)
             / (EXTRACT (
                   DAY FROM s.end_interval_time - s.begin_interval_time)
                * 1440
                + EXTRACT (
                     HOUR FROM s.end_interval_time - s.begin_interval_time)
                  * 60
                + EXTRACT (
                     MINUTE FROM s.end_interval_time - s.begin_interval_time)
                + EXTRACT (
                     SECOND FROM s.end_interval_time - s.begin_interval_time)
                  / 60))
           * 100),
          2)
          pct_db_time
  FROM dba_hist_snapshot s,
       gv$instance i,
       dba_hist_con_sys_time_model e,
       dba_hist_con_sys_time_model b,
       (SELECT (begin_interval_time) tt
          FROM dba_hist_snapshot
         WHERE instance_number =1) f
WHERE     i.instance_number = s.instance_number
       AND e.snap_id = s.snap_id
       AND F.tt = s.begin_interval_time
       AND b.snap_id = s.snap_id - 1
       AND e.stat_id = b.stat_id
       AND e.instance_number = b.instance_number
       AND e.instance_number = s.instance_number
       --add
       AND e.con_id = b.con_id
       AND e.stat_name = 'DB time'
order by begin_interval_time;

我没太细看客户的脚本，在假设原脚本正确的基础上，直接进行的修改，需要客户帮测试下，看是否还有问题，目前尚未有反馈。
大家如果感兴趣也可以测试下哈，若有任何问题都欢迎找我一起探讨。

更新：
客户最终又简化了脚本，测试查询没有问题，脚本如下：

set linesize 200 pagesize 27
col CON_name for a10
col STARTUP_TIME for a30
col BEGIN_INTERVAL_TIME for a30
col END_INTERVAL_TIME for a30
select s.INSTANCE_NUMBER inst_nu,i.name CON_name,
       s.snap_id snap_id,
       to_char(s.startup_time, 'yyyy-mm-dd HH24:MI:SS') startup_time,
       to_char(s.begin_interval_time, 'yyyy-mm-dd HH24:MI:SS') begin_interval_time,
       to_char(s.end_interval_time, 'yyyy-mm-dd HH24:MI:SS') end_interval_time,
       round(extract(day from s.end_interval_time - s.begin_interval_time) * 1440 +
             extract(hour from s.end_interval_time - s.begin_interval_time) * 60 +
             extract(minute from s.end_interval_time - s.begin_interval_time) +
             extract(second from s.end_interval_time - s.begin_interval_time) / 60,2) elapsed_time,
       round((e.value - b.value) / 1000000 / 60, 2) db_time,
       round(((((e.value - b.value) / 1000000 / 60) /
            (extract(day from s.end_interval_time - s.begin_interval_time) * 1440 +
             extract(hour from s.end_interval_time - s.begin_interval_time) * 60 +
             extract(minute from s.end_interval_time - s.begin_interval_time) +
             extract(second from s.end_interval_time - s.begin_interval_time) / 60)) * 100),2 ) pct_db_time
from DBA_HIST_SNAPSHOT s,
     v$CONTAINERS i,
     DBA_HIST_CON_SYS_TIME_MODEL e,
     DBA_HIST_CON_SYS_TIME_MODEL b
where begin_interval_time>sysdate -50
   AND B.CON_ID=E.CON_ID
   AND B.CON_ID=I.CON_ID
   and s.INSTANCE_NUMBER=b.INSTANCE_NUMBER
   and s.INSTANCE_NUMBER=e.INSTANCE_NUMBER
   and s.snap_id = b.snap_id + 1
   and s.snap_id = e.snap_id
   and b.stat_name = e.stat_name
   and e.stat_name = 'DB time'
order by  s.snap_id,E.CON_ID;
This entry was posted in Oracle体系原理 and tagged , , , , . Bookmark the permalink.