接上一篇常用的数据库管理SQL语句（一）

九.统计联机前10名用户所使用CPU时间

SQL> SELECT * FROM
2 (SELECT P, S.SID,S.STATUS,T.VALUE / 100 / 60 "CPU TIME",
3 FLOOR (LAST_CALL_ET / 60 ) "LAST CALL ET",TO_CHAR(S.LOGON_TIME,MM/DD HH24:MI) "LOGON TIME",
4 S.USERNAME,S.PROCESS,P.SPID,S.MODULE,S.MACHINE,
S.SQL_HASH_VALUE
5 FROM V$SESSTAT T,V$SESSION S,V$PROCESS P
6 WHERE T.STATISTIC# = 12 AND S.SID = T.SID AND S.PADDR = P.ADDR AND S.TYPE = USER
7 AND S.SQL_HASH_VALUE != 1425819161 UNION SELECT N,
8 S.SID,S.STATUS,T.VALUE * -1 / 100 / 60 "CPU TIME",
9 FLOOR (LAST_CALL_ET / 60 ) "LAST CALL ET",TO_CHAR(S.LOGON_TIME,MM/DD HH24:MI) "LOGON TIME",
10 S.USERNAME,S.PROCESS,P.SPID,S.MODULE,S.MACHINE,
S.SQL_HASH_VALUE
11 FROM V$SESSTAT T,V$SESSION S,V$PROCESS P
12 WHERE T.STATISTIC# = 12 AND S.SID = T.SID AND S.PADDR = P.ADDR AND S.TYPE = USER
13 AND S.SQL_HASH_VALUE != 1425819161
14 AND T.VALUE < 0
15 ORDER BY 4 DESC)
16 WHERE ROWNUM < 11;

P SID STATUS CPU TIME LAST CALL ET LOGON TIME USERNAME PROCESS SPID MODULE MACHINE SQL_HASH_VALUE
--- ---------- -------- ---------- ------------ ----------- ------------------------------ ------------ ------------ ------------------------------------------------ ---------------------------------------------------------------- --------------
P 1533 INACTIVE 25.3363333 0 08/17 15:28 SYSMAN 1234 1822 OEM.SystemPool db1 0
P 1498 INACTIVE 20.5493333 13 10/26 16:23 SOLEILFUZHOU 8968 11046 java@WEBSERVER2 (TNS V1-V3) WEBSERVER2 4283697136
P 1493 ACTIVE 18.8611666 0 08/17 15:29 DBSNMP 3688 3866 emagent@db1 (TNS V1-V3) db1 0
P 1465 INACTIVE 12.3526666 121 10/26 20:43 SOLEILFUZHOU 32512 32638 java@WEBSERVER3 (TNS V1-V3) WEBSERVER3 0
P 1500 INACTIVE 12.3035 0 10/26 16:23 SOLEILFUZHOU 8968 11048 java@WEBSERVER2 (TNS V1-V3) WEBSERVER2 2314313480
P 1538 INACTIVE 11.9511666 227 10/26 20:43 SOLEILFUZHOU 32512 32640 java@WEBSERVER3 (TNS V1-V3) &nb