如何在一个时间范围内5分钟内检索Oracle关键性能指标

Question

我将Oracle 11g附加到性能测试环境中。性能测试持续两个小时。运行之后，我想以编程的方式从数据库中提取各种关键指标，以便与结果一起存储。

在测试之前和之后，我将运行哪些命令来收集关键指标，最理想的情况是按5分钟的总计进行分组。

• CPU
• 内存
• SQL请求总数
• 逻辑IO
• 物理IO

和瓶颈指示器(各种等待锁和锁)。

我将接受一篇文章或一组有用的SQL命令的方便链接。

Answer 1

此查询在11.1.0.7 Enterprise上运行，并提供类似于OEM Grid性能页面的结果，该页面需要诊断包。通过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显示的好，但已经接近了。

Answer 2

你最好的机会，没有任何花哨的许可证或第三方工具将是STATSPACK/PERFSTAT。

随着Oracle的安装，这些文档可以在$ORACLE_HOME/rdbms/admin/spdoc.txt中找到(使用12.1.0.2和11.2.0.4进行检查)。它不产生花哨的图形，它只是文本。

您可能想尝试使用模拟ASH (因此名)的oraSASH，这样ASH的脚本就可以工作了。我没有和oraSASH一起工作，所以自己仔细检查一下。