Oracle 歷史會話資訊

我想知道為特定快照提供活動和非活動會話總數的視圖或查詢。目前我正在使用以下查詢。

SELECT INSTANCE_NUMBER, count(*)*10 "DB Sessions"
FROM DBA_HIST_ACTIVE_SESS_HISTORY
where snap_id = '78131'
GROUP BY INSTANCE_NUMBER;

任何幫助將不勝感激。

不好，不優雅，但它仍然有效。希望這有助於任何在絕望的十字軍東征中迷失的 DB 冒險家，以獲取會話歷史報告。

COL METRIC_NAME FOR A40
COL BEGIN_TIME FOR A20
COL END_TIME FOR A20
COL METRIC_UNIT FOR A20
set lin 330 pages 5000

SELECT 
AVG_SES_COUNT.SNAP_ID,      AVG_SES_COUNT.DBID,     AVG_SES_COUNT.INSTANCE_NUMBER,      
AVG_SES_COUNT.BEGIN_TIME,   AVG_SES_COUNT.END_TIME, 
--AVG_SES_COUNT.METRIC_NAME,        
AVG_SES_COUNT.AVERAGE AVG_TOTAL_SESSIONS,       AVG_SES_COUNT.MAXVAL MAX_TOTAL_SESSIONS,
-----
AVG_ACT_SES.AVERAGE                AVG_ACTIVE_SESSIONS,     AVG_ACT_SES.MAXVAL            MAX_ACTIVE_SESSIONS,
-----
AVG_SERIAL_SESSIONS.AVERAGE        AVG_SERIAL_SESSIONS,     AVG_SERIAL_SESSIONS.MAXVAL    MAX_SERIAL_SESSIONS,
-----
AVG_PARALLEL_SESSIONS.AVERAGE      AVG_PARALLEL_SESSIONS,   AVG_PARALLEL_SESSIONS.MAXVAL  MAX_PARALLEL_SESSIONS,
-----
AVG_PQ_SESSIONS.AVERAGE      AVG_PQ_SESSIONS,   AVG_PQ_SESSIONS.MAXVAL  MAX_PQ_SESSIONS,
-----
AVG_PQ_SLV_SESSIONS.AVERAGE      AVG_PQ_SLAVE_SESSIONS,     AVG_PQ_SLV_SESSIONS.MAXVAL  MAX_PQ_SLAVE_SESSIONS
FROM 
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT 
from dba_hist_sysmetric_summary
where  METRIC_ID=2143 
order by INSTANCE_NUMBER,SNAP_ID ) AVG_SES_COUNT,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT 
from dba_hist_sysmetric_summary
where  METRIC_ID=2147 
order by INSTANCE_NUMBER,SNAP_ID ) AVG_ACT_SES,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT 
from dba_hist_sysmetric_summary
where  METRIC_ID=2148 
order by INSTANCE_NUMBER,SNAP_ID ) AVG_SERIAL_SESSIONS,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT 
from dba_hist_sysmetric_summary
where  METRIC_ID=2148 
order by INSTANCE_NUMBER,SNAP_ID )  AVG_PARALLEL_SESSIONS,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT 
from dba_hist_sysmetric_summary
where  METRIC_ID=2137 
order by INSTANCE_NUMBER,SNAP_ID )  AVG_PQ_SESSIONS,
(select  SNAP_ID,       DBID,       INSTANCE_NUMBER,        BEGIN_TIME,         END_TIME,       METRIC_NAME,        AVERAGE,        MAXVAL,         METRIC_UNIT 
from dba_hist_sysmetric_summary
where  METRIC_ID=2138 
order by INSTANCE_NUMBER,SNAP_ID )  AVG_PQ_SLV_SESSIONS
WHERE
   AVG_SES_COUNT.SNAP_ID           = AVG_ACT_SES.SNAP_ID       (+)
AND AVG_SES_COUNT.DBID              = AVG_ACT_SES.DBID          (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_ACT_SES.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID           = AVG_SERIAL_SESSIONS.SNAP_ID       (+)
AND AVG_SES_COUNT.DBID              = AVG_SERIAL_SESSIONS.DBID          (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_SERIAL_SESSIONS.INSTANCE_NUMBER (+)
--
AND AVG_SES_COUNT.SNAP_ID           = AVG_PARALLEL_SESSIONS.SNAP_ID         (+)
AND AVG_SES_COUNT.DBID              = AVG_PARALLEL_SESSIONS.DBID            (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_PARALLEL_SESSIONS.INSTANCE_NUMBER (+) 
--
AND AVG_SES_COUNT.SNAP_ID           = AVG_PQ_SESSIONS.SNAP_ID       (+)
AND AVG_SES_COUNT.DBID              = AVG_PQ_SESSIONS.DBID          (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_PQ_SESSIONS.INSTANCE_NUMBER (+)       
--
AND AVG_SES_COUNT.SNAP_ID           = AVG_PQ_SLV_SESSIONS.SNAP_ID       (+)
AND AVG_SES_COUNT.DBID              = AVG_PQ_SLV_SESSIONS.DBID          (+)
AND AVG_SES_COUNT.INSTANCE_NUMBER   = AVG_PQ_SLV_SESSIONS.INSTANCE_NUMBER (+)           
order by INSTANCE_NUMBER,SNAP_ID;       

DBA_ACTIVE_SESS_HISTORY僅來自活動會話，但您可以dba_hist_sysmetric_summary按照我在本文中的說明進行查詢：Oracle 歷史會話資訊

引用自：https://dba.stackexchange.com/questions/62982