有客户咨询在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 发我的一段描述恰好说明了二者的区别
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;
我没太细看客户的脚本,在假设原脚本正确的基础上,直接进行的修改,需要客户帮测试下,看是否还有问题,目前尚未有反馈。 大家如果感兴趣也可以测试下哈,若有任何问题都欢迎找我一起探讨。