如何在一個時間範圍內每隔 5 分鐘檢索一次 Oracle 關鍵績效指標

我將 Oracle 11g 連接到性能測試環境。性能測試執行兩個小時。執行後，我想以程式方式從數據庫中提取各種關鍵指標來儲存結果。

在測試之前和之後我會執行什麼命令來收集關鍵指標，最好是在 5 分鐘內進行分組。

• 中央處理器
• 記憶
• SQL 請求總數
• 邏輯 IO
• 物理 IO

和瓶頸指標，（各種等待鎖和閂鎖）。

我將接受一個方便的連結到一篇文章或一組有用的 SQL 命令來探索。

此查詢在 11.1.0.7 Enterprise 上執行，並提供與需要診斷包的 OEM 網格性能頁麵類似的結果。通過 SQL Server 報告服務執行此腳本具有一定的諷刺意味，這超出了本問題的範圍。

SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI ') AS SAMPLE_TIME,
      ROUND(OTHER / 60, 3) AS OTHER,
      ROUND(CLUST / 60, 3) AS CLUST,
      ROUND(QUEUEING / 60, 3) AS QUEUEING,
      ROUND(NETWORK / 60, 3) AS NETWORK,
      ROUND(ADMINISTRATIVE / 60, 3) AS ADMINISTRATIVE,
      ROUND(CONFIGURATION / 60, 3) AS CONFIGURATION,
      ROUND(COMMIT / 60, 3) AS COMMIT,
      ROUND(APPLICATION / 60, 3) AS APPLICATION,
      ROUND(CONCURRENCY / 60, 3) AS CONCURRENCY,
      ROUND(SIO / 60, 3) AS SYSTEM_IO,
      ROUND(UIO / 60, 3) AS USER_IO,
      ROUND(SCHEDULER / 60, 3) AS SCHEDULER,
      ROUND(CPU / 60, 3) AS CPU,
      ROUND(BCPU / 60, 3) AS BACKGROUND_CPU
 FROM (SELECT TRUNC(SAMPLE_TIME, 'MI') AS SAMPLE_TIME,
              DECODE(SESSION_STATE,
                     'ON CPU',
                     DECODE(SESSION_TYPE, 'BACKGROUND', 'BCPU', 'ON CPU'),
                     WAIT_CLASS) AS WAIT_CLASS
         FROM V$ACTIVE_SESSION_HISTORY
        WHERE SAMPLE_TIME > SYSDATE - INTERVAL '1'
        HOUR
          AND SAMPLE_TIME <= TRUNC(SYSDATE, 'MI')) ASH PIVOT(COUNT(*) 
 FOR WAIT_CLASS IN('ON CPU' AS CPU,'BCPU' AS BCPU,
'Scheduler' AS SCHEDULER,
'User I/O' AS UIO,
'System I/O' AS SIO, 
'Concurrency' AS CONCURRENCY,                                                                               
'Application' AS  APPLICATION,                                                                                  
'Commit' AS  COMMIT,                                                                             
'Configuration' AS CONFIGURATION,                     
'Administrative' AS   ADMINISTRATIVE,                                                                                 
'Network' AS  NETWORK,                                                                                 
'Queueing' AS   QUEUEING,                                                                                  
'Cluster' AS   CLUST,                                                                                      
'Other' AS  OTHER))
ORDER BY 1  

這個查詢不如 Grid 顯示的那麼好，但它很接近。

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