关于Oracle多租户架构下的每个PDB的dbtime查询
2022-07-26 16:48 AlfredZhao 阅读(283) 评论(1) 编辑 收藏 举报有客户咨询在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;
我没太细看客户的脚本，在假设原脚本正确的基础上，直接进行的修改，需要客户帮测试下，看是否还有问题，目前尚未有反馈。
大家如果感兴趣也可以测试下哈，若有任何问题都欢迎找我一起探讨。
