目錄
前言
眾所周知，從本月初到現在我已經連續在墨天輪上連續發表了三篇原創博客文章，它們按發表的時間順序（也是遵循由易到難的層層遞進方式）是：
經過最近幾天的一番研究，我發現這個圖表的相關SQL查詢只能查看“最近1小時”和“最近1分鐘”的數據。從Oracle的官方文檔19c中找到的查看最近24小時、最近7天、最近31天和自定義時間段的視圖是DBA_HIST_WAITCLASSMET_HISTORY?，F將它的介紹描述如下：
DBA_HIST_WAITCLASSMET_HISTORY displays the history of the wait event class metric data kept by the Workload Repository.
從上面簡要的介紹來看，DBA_HIST_WAITCLASSMET_HISTORY的數據應該是保留到了AWR中。但是我在11gR2，19c和21c中卻發現這個視圖的查詢數據均為零。詳見我的SQL查詢和結果：
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 29 10:39:13 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:39:57 SQL> SELECT COUNT(*) FROM dba_hist_waitclassmet_history;
COUNT(*)
----------
0SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 29 10:40:52 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
10:40:52 SQL> SELECT COUNT(*) FROM dba_hist_waitclassmet_history;
COUNT(*)
----------
0SQL*Plus: Release 21.0.0.0.0 - Production on Mon Nov 29 10:41:12 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
10:41:12 SQL> SELECT COUNT(*) FROM dba_hist_waitclassmet_history;
COUNT(*)
----------
0于是，我在FreeLists的Oracle空間發了一個帖子，靜等專家朋友們的答復吧。所以目前僅能從兩個維度（最近1小時和最近1分鐘）來詳細闡述相關的可視化操作了。由于這個圖表只是基本的“面積堆疊圖”，因此我們仍然使用Oracle SQL Developer 21.2中的用戶自定義報告進行可視化即可。
維度目錄列表
最近1小時的每個活動類的活動會話
用ASH查看活動會話
-- Active Sessions (in ASH) Per Activity Class from EMCC 13.5 in Last 1 Hour.
SET LINESIZE 200
SET PAGESIZE 200
COLUMN sample_time FORMAT a19
COLUMN activity_class FORMAT a15
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SELECT TRUNC(CAST(sample_time AS DATE), 'mi') sample_time
, DECODE(wait_class, 'User I/O', 'User I/O', NULL, 'CPU', 'Wait') activity_class
, ROUND(COUNT(*)/6e1, 4) active_sessions
FROM v$active_session_history
WHERE (wait_class <> 'Idle' OR wait_class IS NULL)
AND CAST(sample_time AS DATE) >= SYSDATE - 1/24
GROUP BY TRUNC(CAST(sample_time AS DATE), 'mi')
, DECODE(wait_class, 'User I/O', 'User I/O', NULL, 'CPU', 'Wait')
ORDER BY activity_class
, sample_time
;
SAMPLE_TIME ACTIVITY_CLASS ACTIVE_SESSIONS
------------------- --------------- ---------------
......
2021-11-29 14:07:00 CPU .3833 <<==
......
2021-11-29 14:06:00 User I/O .0167 ?
......
2021-11-29 14:07:00 Wait 1.6333 <<==
......
123 rows selected.接著我們去查看同一時間（“2021-11-29 14:07”）EMCC 13.5圖表中顯示的數據（以便和上述ASH中的數據進行對比），如下三圖（依次為：CPU、User I/O和Wait）所示：
經過反復的對比，始終發現兩者之間存在一定的差異，這也間接說明一個問題，我們的SQL查詢不匹配EMCC的圖表數據，也有可能EMCC圖表的數據來源不是我們前面的那個SQL查詢。
通過AskTOM尋求幫助
于是，我到AskTOM上尋求Connor McDonald的幫助，請看這里或下面的兩張屏幕截圖：
從第二個圖中我們可以看到，Connor建議去查詢列usecs_per_row（從12.2版本開始提供）。于是，我稍微調整了前面的SQL代碼并在我的測試庫19.3中進行相應的查詢，結果同樣讓我大跌眼鏡，仍舊存在差異。索性又給Connor做了回復，如下圖所示：
后來，他這樣說道，已和EM團隊進行溝通，可能不只是SQL，或許有其他因素決定，數據不可能100%一致。
到此為止，我卡住了！??！難道止步不前嗎？
感謝Kyle Hailey
此時，我突然想到了Kyle Hailey的Oracle CPU Time這篇文章，尤其在最后的更新段落中，他的部分SQL代碼給了我一些非常有價值的線索，因此只截取這部分代碼，如下所示：
......
select
decode(n.wait_class,'User I/O','User I/O',
'Commit','Commit',
'Wait') CLASS,
sum(round(m.time_waited/m.INTSIZE_CSEC,3)) AAS,
BEGIN_TIME ,
END_TIME
from v$waitclassmetric m,
v$system_wait_class n
where m.wait_class_id=n.wait_class_id
and n.wait_class != 'Idle'
group by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait'), BEGIN_TIME, END_TIME
union
select 'CPU_ORA_CONSUMED' CLASS,
round(value/100,3) AAS,
BEGIN_TIME ,
END_TIME
from v$sysmetric
where metric_name='CPU Usage Per Sec'
and group_id=2
......從上面的代碼中我們可以看到，Kyle Hailey使用視圖v$waitclassmetric、v$system_wait_class和v$sysmetric來查詢CPU、User I/O和Wait。這里引用一下官檔19c中對這些視圖的簡要描述：
V$WAITCLASSMETRIC displays metric values of wait classes for the most recent 60-second interval. A history of the last one hour will be kept in the system.
V$SYSTEM_WAIT_CLASS displays the instance-wide time totals for each registered wait class.
V$SYSMETRIC displays the system metric values captured for the most current time interval for both the long duration (60-second) and short duration (15-second) system metrics.
通過前面的描述，我們已經知道這幾個視圖可以查詢最近60秒（也就是1分鐘）的度量數據。而我們這里要查詢的是最近1小時的數據，非常巧合的是，官檔中呈現的是一個視圖列表，在視圖V$WAITCLASSMETRIC后面緊接著的是另一個相關的視圖V$WAITCLASSMETRIC_HISTORY，它可以查詢最近1小時的度量數據，官檔的描述是這樣的：
V$WAITCLASSMETRIC_HISTORY displays metric values of wait classes for all intervals in the last one hour.
The columns for V$WAITCLASSMETRIC_HISTORY are the same as those for V$WAITCLASSMETRIC.
視圖V$SYSMETRIC_HISTORY同樣如此，它也可以查詢最近1小時的度量數據，在此也引用一下官檔的描述：
V$SYSMETRIC_HISTORY displays all system metric values available in the database. Both long duration (60-second with 1 hour history) and short duration (15-second with one-interval only) metrics are displayed by this view.
探索CPU
說到這里，讓我們先查詢一下度量名稱“CPU Usage Per Sec”的度量單位，詳見如下SQL代碼和查詢結果：
SET LINESIZE 200
COLUMN metric_name FORMAT a30
COLUMN metric_unit FORMAT a25
SELECT DISTINCT metric_name
, metric_unit
FROM v$sysmetric_history
WHERE metric_name LIKE '%CPU%'
ORDER BY 1
;
METRIC_NAME METRIC_UNIT
------------------------------ -------------------------
Background CPU Usage Per Sec CentiSeconds Per Second
CPU Usage Per Sec CentiSeconds Per Second
CPU Usage Per Txn CentiSeconds Per Txn
Database CPU Time Ratio % Cpu/DB_Time
Host CPU Usage Per Sec CentiSeconds Per Second
Host CPU Utilization (%) % Busy/(Idle+Busy)
6 rows selected.我們注意到，它的度量單位是“厘秒每秒”，所以我們在相應的SQL查詢中應該將“CPU Usage Per Sec”的值除以100折/換算成“秒每秒”。接著我們用視圖V$SYSMETRIC_HISTORY去查詢按CPU分類的活動會話數據，SQL代碼和查詢結果如下所示（仍以“2021-11-29 14:07”為例，這樣有利于和EMCC的圖表數據進行對比）：
-- 'CPU Usage Per Sec' is right to the legend 'CPU' from the "Active Sessions Per Activity Class" Graph of EMCC 13.5.
SET LINESIZE 200
SET PAGESIZE 100
COLUMN sample_time FORMAT a11
COLUMN metric_name FORMAT a11
COLUMN active_sessions FORMAT 999,999.9999
SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
, DECODE(metric_name, 'CPU Usage Per Sec', 'CPU') metric_name
, ROUND(value/1e2, 4) active_sessions
FROM v$sysmetric_history
WHERE metric_name = 'CPU Usage Per Sec'
AND group_id = 2
AND end_time >= SYSDATE - INTERVAL '60' MINUTE
ORDER BY sample_time
;
SAMPLE_TIME METRIC_NAME ACTIVE_SESSIONS
----------- ----------- ---------------
......
14:07:29 CPU .8005
......
60 rows selected.非常棒，這次終于（和前面呈現的EMCC中CPU為0.8005的取值相同）一致了，這正是我翹首以盼的結果，雖然它來得遲了一些，索性再貼一下吧，哈哈?。?！
探索User I/O
馬不停蹄，我們接著用視圖V$WAITCLASSMETRIC_HISTORY和V$SYSTEM_WAIT_CLASS進行關聯去查按User I/O分類的活動會話數據，具體的SQL查詢和查詢結果（只顯示“2021-11-29 14:07”這一行）是：
-- 'User I/O' is right as well to the legend 'User I/O' from the "Active Sessions Per Activity Class" Graph of EMCC 13.5.
SET LINESIZE 200
SET PAGESIZE 100
COLUMN sample_time FORMAT a11
COLUMN metric_name FORMAT a11
COLUMN active_sessions FORMAT 999,999.9999
SELECT TO_CHAR(wcmh.end_time, 'hh24:mi:ss') sample_time
, swc.wait_class metric_name
, ROUND(wcmh.time_waited/wcmh.intsize_csec, 4) active_sessions
FROM v$waitclassmetric_history wcmh
, v$system_wait_class swc
WHERE wcmh.wait_class_id = swc.wait_class_id
AND swc.wait_class = 'User I/O'
AND wcmh.end_time >= SYSDATE - INTERVAL '60' MINUTE
ORDER BY sample_time
;
SAMPLE_TIME METRIC_NAME ACTIVE_SESSIONS
----------- ----------- ---------------
......
14:07:29 User I/O .0064
......
60 rows selected.這個結果也和先前的EMCC圖表的顯示數據驚人的一致，不信你看！
探索Wait
戒驕戒躁，我們來想一想這個Wait分類如何查詢呢？與此同時，我在視圖V$SYSMETRIC_HISTORY的列metric_name中發現了兩個有用的度量名稱“Database Time Per Sec”和“Database Wait Time Ratio”，相關的查詢為：
SET LINESIZE 200
COLUMN metric_name FORMAT a30
COLUMN metric_unit FORMAT a25
SELECT DISTINCT metric_name
, metric_unit
FROM v$sysmetric_history
WHERE metric_name LIKE '%Database%'
ORDER BY 1
;
METRIC_NAME METRIC_UNIT
------------------------------ -------------------------
Database CPU Time Ratio % Cpu/DB_Time
Database Time Per Sec CentiSeconds Per Second
Database Wait Time Ratio % Wait/DB_Time將二者相乘，豈不是我們想要的Wait查詢？話不多說，詳見如下代碼和查詢結果（只顯示“2021-11-29 14:07”這一行）：
-- But 'Wait' is still not fully identical to EMCC 13.5 when using 'Database Time Per Sec' * 'Database Wait Time Ratio'.
SET LINESIZE 200
SET PAGESIZE 100
COLUMN sample_time FORMAT a11
COLUMN metric_name FORMAT a11
COLUMN active_sessions FORMAT 999,999.9999
WITH
wait AS
(
SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
, MAX(DECODE(metric_name, 'Database Time Per Sec' , value/1e2)) aas_value
, MAX(DECODE(metric_name, 'Database Wait Time Ratio', value/1e2)) wait_ratio
FROM v$sysmetric_history
WHERE metric_name IN ('Database Time Per Sec', 'Database Wait Time Ratio')
AND group_id = 2
AND end_time >= SYSDATE - INTERVAL '60' MINUTE
GROUP BY TO_CHAR(end_time, 'hh24:mi:ss')
ORDER BY sample_time
)
SELECT sample_time
, 'Wait' metric_name
, ROUND(aas_value*wait_ratio, 4) active_sessions
FROM wait
;
SAMPLE_TIME METRIC_NAME ACTIVE_SESSIONS
----------- ----------- ---------------
......
14:07:29 Wait .5928
......
60 rows selected.傻眼了，和先前的EMCC中顯示的數據完全不一樣！??！
失敗是成功之母。或者說，不經一番寒徹骨，怎得梅花撲鼻香。讓我們換位思考一下，既然User I/O是一種等待類別（CPU不是），那么這個Wait會不會是其余所有等待類別的總和？另外，這是我們從視圖V$SYSTEM_WAIT_CLASS中查詢到的所有等待類別：
SELECT DISTINCT wait_class
FROM v$system_wait_class
ORDER BY 1
;
WAIT_CLASS
---------------
Administrative
Application
Commit
Concurrency
Configuration
Idle
Network
Other
Scheduler
System I/O
User I/O
11 rows selected.基于這個猜想，讓我們去探索一番：
-- 'Wait' is fairly precise to the legend 'Wait' from the "Active Sessions Per Activity Class" Graph of EMCC 13.5.
SET LINESIZE 200
SET PAGESIZE 100
COLUMN sample_time FORMAT a11
COLUMN metric_name FORMAT a11
COLUMN active_sessions FORMAT 999,999.9999
SELECT TO_CHAR(wcmh.end_time, 'hh24:mi:ss') sample_time
, 'Wait' metric_name
, SUM(ROUND(wcmh.time_waited/wcmh.intsize_csec, 4)) active_sessions
FROM v$waitclassmetric_history wcmh
, v$system_wait_class swc
WHERE wcmh.wait_class_id = swc.wait_class_id
AND (swc.wait_class NOT IN ('Idle', 'User I/O'))
AND wcmh.end_time >= SYSDATE - INTERVAL '60' MINUTE
GROUP BY TO_CHAR(wcmh.end_time, 'hh24:mi:ss')
ORDER BY sample_time
;
SAMPLE_TIME METRIC_NAME ACTIVE_SESSIONS
----------- ----------- ---------------
......
14:07:29 Wait 2.2382
......
60 rows selected.果不其然，這回終于和EMCC上顯示的數據（請查看上一張屏幕截圖）相同了，瞬間會不會有一種欣喜的感覺？哈哈。。。
用公用表表達式整合CPU、User I/O和Wait
至此，我們已經把三個類別的SQL查詢都寫出來了，那就用“WITH ... AS ()”整合成一個完整的吧，請看下面（附帶查詢數據）：
-- At this moment 'CPU', 'User I/O' and 'Wait' are all same as the "Active Sessions Per Activity Class" Graph from EMCC 13.5. Congrats!!!
-- Note: using the column 'time_waited' (rather than 'time_waited_fg') of view "v$waitclassmetric_history" to acquire 'User I/O' and 'Wait'.
-- Active Sessions Per Activity Class (CPU, User I/O and Wait) from EMCC 13.5 in Last 1 Hour.
SET LINESIZE 200
SET PAGESIZE 200
COLUMN sample_time FORMAT a11
COLUMN metric_name FORMAT a11
COLUMN active_sessions FORMAT 999,999.9999
WITH
cpu AS
(
SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
, DECODE(metric_name, 'CPU Usage Per Sec', 'CPU') metric_name
, ROUND(value/1e2, 4) active_sessions
FROM v$sysmetric_history
WHERE metric_name = 'CPU Usage Per Sec'
AND group_id = 2
AND end_time >= SYSDATE - INTERVAL '60' MINUTE
ORDER BY sample_time
),
user_io AS
(
SELECT TO_CHAR(wcmh.end_time, 'hh24:mi:ss') sample_time
, swc.wait_class metric_name
, ROUND(wcmh.time_waited/wcmh.intsize_csec, 4) active_sessions
FROM v$waitclassmetric_history wcmh
, v$system_wait_class swc
WHERE wcmh.wait_class_id = swc.wait_class_id
AND swc.wait_class = 'User I/O'
AND wcmh.end_time >= SYSDATE - INTERVAL '60' MINUTE
ORDER BY sample_time
),
wait AS
(
SELECT TO_CHAR(wcmh.end_time, 'hh24:mi:ss') sample_time
, 'Wait' metric_name
, SUM(ROUND(wcmh.time_waited/wcmh.intsize_csec, 4)) active_sessions
FROM v$waitclassmetric_history wcmh
, v$system_wait_class swc
WHERE wcmh.wait_class_id = swc.wait_class_id
AND (swc.wait_class NOT IN ('Idle', 'User I/O'))
AND wcmh.end_time >= SYSDATE - INTERVAL '60' MINUTE
GROUP BY TO_CHAR(wcmh.end_time, 'hh24:mi:ss')
ORDER BY sample_time
)
SELECT * FROM cpu
UNION ALL
SELECT * FROM user_io
UNION ALL
SELECT * FROM wait
;
SAMPLE_TIME METRIC_NAME ACTIVE_SESSIONS
----------- ----------- ---------------
......
14:07:29 CPU .8005
......
14:07:29 User I/O .0064
......
14:07:29 Wait 2.2382
......
180 rows selected.用Oracle SQL Developer 21.2可視化
將這個XML文件（Active_Sessions_Per_Activity_Class.xml）導入（具體步驟：1. 下載剛提到的XML文件到本地電腦；2. 在SQL Developer中右擊用戶自定義報告，然后選擇打開報告，去選擇剛才的XML文件即可完成導入操作）到我的SQL Developer 21.2的用戶自定義報告當中，然后連接相應的數據庫，就可以看到下面的可視化圖表：
最近1分鐘的每個活動類的活動會話
完整的SQL查詢代碼
-- Active Sessions Per Activity Class (CPU, User I/O and Wait) from EMCC 13.5 in Last 1 Minute.
SET LINESIZE 200
SET PAGESIZE 10
COLUMN sample_time FORMAT a11
COLUMN metric_name FORMAT a11
COLUMN active_sessions FORMAT 999,999.99
WITH
cpu AS
(
SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
, DECODE(metric_name, 'CPU Usage Per Sec', 'CPU') metric_name
, ROUND(value/1e2, 2) active_sessions
FROM v$sysmetric
WHERE metric_name = 'CPU Usage Per Sec'
AND group_id = 2
ORDER BY sample_time
),
user_io AS
(
SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
, swc.wait_class metric_name
, ROUND(wcm.time_waited/wcm.intsize_csec, 2) active_sessions
FROM v$waitclassmetric wcm
, v$system_wait_class swc
WHERE wcm.wait_class_id = swc.wait_class_id
AND swc.wait_class = 'User I/O'
ORDER BY sample_time
),
wait AS
(
SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
, 'Wait' metric_name
, SUM(ROUND(wcm.time_waited/wcm.intsize_csec, 2)) active_sessions
FROM v$waitclassmetric wcm
, v$system_wait_class swc
WHERE wcm.wait_class_id = swc.wait_class_id
AND (swc.wait_class NOT IN ('Idle', 'User I/O'))
GROUP BY TO_CHAR(end_time, 'hh24:mi:ss')
ORDER BY sample_time
)
SELECT * FROM cpu
UNION ALL
SELECT * FROM user_io
UNION ALL
SELECT * FROM wait
;
SAMPLE_TIME METRIC_NAME ACTIVE_SESSIONS
----------- ----------- ---------------
14:27:29 CPU .32
14:27:29 User I/O .00
14:27:29 Wait .59來和EMCC的對比一下唄，嘿嘿?。?！
那么，總的活動會話值是多少呢？在EMCC的“負載和容量”標簽頁中已經告訴了我們：
用Oracle SQL Developer 21.2可視化
因為在前一維度的可視化操作中導入的XML文件里已經包含了這一維度的圖表可視化設置功能，所以我們找到相應的報告直接進行可視化，詳見如下兩圖（1分鐘的時間過的真得很快，我只能截取另一個時間點15:10，而不是前面的14:07，哈哈）：
活動會話負載總覽
在EMCC的首頁有一個Oracle Load Map，它列出了每一個數據庫的活動會話總覽情況，如：CPU、IO和Wait，還是這三個分類呀，說來夠巧的。但是也很奇怪，我觀察了一段時間，發現它們三個值和總計值（它們的總和）始終沒有任何變化。看來它不應該是最近1分鐘的情況，因為最近1分鐘的值總是會有變化的，有可能是最近1小時的平均值，這個我沒有具體去研究，只是猜測，等抽空看看EM的官檔中有關這個Load Map的解釋，所以這里就只貼一個圖吧。
獲取圖表的圖例顏色
有的小伙伴可能會問，SQL Developer里的可視化圖表的圖例顏色為什么和EMCC圖表的圖例幾乎一樣，你是怎么做到的呢？這里，我用了一個小竅門兒。那就是將EMCC圖表的每一個圖例都截圖保存成圖片，然后在這個網站上傳我保存的圖片讓其識別出相應的RGB顏色值（十六進制），接著我在SQL Developer的用戶自定義報告中進行可視化設置時輸入了對應圖例的RGB顏色值（十六進制）。那么，這篇文章中用到的圖表圖例RGB顏色值是：
-- Each Legend Color from the Graph of "Active Sessions Per Activity Class" of EMCC 13.5.
--
-- CPU , #00CF30 -> RGB (0 , 207, 48 )
-- User I/O, #004CE6 -> RGB (0 , 76 , 230)
-- Wait , #FA5F00 -> RGB (250, 95 , 0 )至此，可視化Oracle性能圖表之“每個活動類的活動會話”篇今天就分享到這里結束了，其中最核心的SQL源代碼您也可以從我的GitHub上查看，歡迎各路親朋好友提出寶貴意見和建議，小編一定會再接再勵，竭盡所能給大家奉獻更多更具有價值的深度原創技術好文。
參考文章
- What is the SQL statement for the Active Sessions Per Activity Class from EMCC 13.5?
- Oracle CPU Time
- 如何獲取圖表的圖例顏色
更新于 2021年11月30日 下午：
授人以魚不如授人以漁，在AskTOM上Connor McDonald還沒有解決我的困惑之前，我在最近的評論中這樣回復道：
更新于 2021年12月9日 下午：
關于視圖DBA_HIST_WAITCLASSMET_HISTORY的返回行為什么為0？后來Jonathan Lewis（世界級的Oracle大師）這樣回復道，以下直接是翻譯后的引用內容：
這一定是一個經過深思熟慮的編碼決策，可能有一個隱藏參數（或對內部包的調用）改變了Oracle訪問相關x$表的方式，但如果跟蹤快照代碼，您可以看到應該從x$表填充相關wrh$表的SQL語句，然后看到那里沒有任何內容。
接著是Kyle Hailey的回復，也引用如下：
是的，我記得空的 dba_hist_waitclassmet_history 令人困惑。
我相信 dba_hist_waitclassmet_history 僅用于在違反某些限制時提醒條目。
我已經很久沒有看這些東西了。
統計數據
DBA_HIST_SYSMETRIC_SUMMARY – 最大、最小、平均標準偏差
DBA_HIST_SYSSTAT（累計）
DBA_HIST_SYSMETRIC_HISTORY（警報）
等待
WAITCLASSMETRIC_HISTORY（警報）
DBA_HIST_SYSTEM_EVENT（累計）
文件IO
DBA_HIST_FILEMETRIC_HISTORY（警報）
DBA_HIST_FILESTATXS（累計）
隨后，我也進行了相關的回復，內容為：
抱歉，DBA_HIST_FILEMETRIC_HISTORY 也返回了 0 行，Kyle！ 我調用了 DBMS_METADATA.GET_DDL() 來檢查定義的視圖。
SET VERIFY OFF
SET LONG 1000000000
SET LINESIZE 200
SET PAGESIZE 200
PROMPT ==================
PROMPT Running on SYS schema
PROMPT ==================
SELECT DBMS_METADATA.get_ddl(UPPER('&object_type'), UPPER('&object_name'), UPPER('&owner_name')) FROM dual
/
Enter value for object_type: view
Enter value for object_name: dba_hist_waitclassmet_history
Enter value for owner_name: sys
DBMS_METADATA.GET_DDL(UPPER('VIEW'),UPPER('DBA_HIST_WAITCLASSMET_HISTORY'),UPPER
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_HIST_WAITCLASSMET_HISTORY" ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "WAIT_CLASS
_ID", "WAIT_CLASS", "BEGIN_TIME", "END_TIME", "INTSIZE", "GROUP_ID", "AVERAGE_WA
ITER_COUNT", "DBTIME_IN_WAIT", "TIME_WAITED", "WAIT_COUNT", "TIME_WAITED_FG", "W
AIT_COUNT_FG") AS
select em.snap_id, em.dbid, em.instance_number,
em.wait_class_id, wn.wait_class, begin_time, end_time, intsize,
group_id, average_waiter_count, dbtime_in_wait,
time_waited, wait_count, time_waited_fg, wait_count_fg
from wrm$_snapshot sn, WRH$_WAITCLASSMETRIC_HISTORY em,
(select wait_class_id, wait_class from wrh$_event_name
group by wait_class_id, wait_class) wn
where em.wait_class_id = wn.wait_class_id
and sn.snap_id = em.snap_id
and sn.dbid = em.dbid
and sn.instance_number = em.instance_number
and sn.status = 0
SQL> SELECT COUNT(*) FROM WRH$_WAITCLASSMETRIC_HISTORY;
COUNT(*)
-----------
0
SET VERIFY OFF
SET LONG 1000000000
SET LINESIZE 200
SET PAGESIZE 200
PROMPT ==================
PROMPT Running on SYS schema
PROMPT ==================
SELECT DBMS_METADATA.get_ddl(UPPER('&object_type'), UPPER('&object_name'), UPPER('&owner_name')) FROM dual
/
Enter value for object_type: view
Enter value for object_name: dba_hist_filemetric_history
Enter value for owner_name: sys
DBMS_METADATA.GET_DDL(UPPER('VIEW'),UPPER('DBA_HIST_FILEMETRIC_HISTORY'),UPPER('
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_HIST_FILEMETRIC_HISTORY" ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "FILEID", "CR
EATIONTIME", "BEGIN_TIME", "END_TIME", "INTSIZE", "GROUP_ID", "AVGREADTIME", "AV
GWRITETIME", "PHYSICALREAD", "PHYSICALWRITE", "PHYBLKREAD", "PHYBLKWRITE") AS
select fm.snap_id, fm.dbid, fm.instance_number,
fileid, creationtime, begin_time,
end_time, intsize, group_id, avgreadtime, avgwritetime,
physicalread, physicalwrite, phyblkread, phyblkwrite
from wrm$_snapshot sn, WRH$_FILEMETRIC_HISTORY fm
where sn.snap_id = fm.snap_id
and sn.dbid = fm.dbid
and sn.instance_number = fm.instance_number
and sn.status = 0
SQL> SELECT COUNT(*) FROM WRH$_FILEMETRIC_HISTORY;
COUNT(*)
-----------
0
