Oracle会话活动过去10秒

Question

我希望将此查询转换为过去10秒，而不是过去几个小时

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,                                                                             
'Configuration' AS CONFIGURATION,                     
'Administrative' AS   ADMINISTRATIVE,                                                                                 
'Network' AS  NETWORK,                                                                                 
'Queueing' AS   QUEUEING,                                                                                  
'Cluster' AS   CLUST,                                                                                      
'Other' AS  OTHER))
ORDER BY 1  

我尝试在SAMPLE_TIME > SYSDATE - (10)/(24*60*60)的地方使用它，但它不起作用。它能在几分钟内给出结果。但是我想要最后10秒(所以10行)

Answer 1

您需要更改外部选择列表，以包括秒数：

SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI:SS') AS SAMPLE_TIME,

您需要从内部查询选择列表中删除分钟级截断：

  FROM (SELECT SAMPLE_TIME,

您需要更改sample time过滤器(因为该列是一个时间戳，所以最好使用systimestamp和一个时间间隔，这样它就可以保持为1)：

         WHERE SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' SECOND
  )
  ASH PIVOT(COUNT(*) 

正如注释中指出的，你的COMMIT应该是'Commit' as COMMIT，否则你会得到"ORA-56901:非常量表达式不允许透视|非透视值“，至少在11gR2，12cR1和12cR2中是这样；而且你需要提供实际的wait_class名称，因为它无论如何都会出现在ASH视图中。

通过这些更改，查询似乎可以执行您想要的操作。它不一定会显示10行，因为可能不是每秒都有样本；例如，在非常安静的数据库上，我通常不显示行，或者只显示几行。

SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI:SS ') 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 SAMPLE_TIME 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 > SYSTIMESTAMP - INTERVAL '10' second
  ) 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  
/

SAMPLE_TI      OTHER      CLUST   QUEUEING    NETWORK ADMINISTRATIVE CONFIGURATION     COMMIT APPLICATION CONCURRENCY  SYSTEM_IO    USER_IO  SCHEDULER        CPU BACKGROUND_CPU
--------- ---------- ---------- ---------- ---------- -------------- ------------- ---------- ----------- ----------- ---------- ---------- ---------- ---------- --------------
17:57:26           0          0          0          0              0             0          0           0           0          0          0          0       .017              0