Oracle Session的CUP使用率

查看Oracle数据库的各Session的CPU使用率

SELECT  A.SID || ',' || A.SERIAL# "SID/SERIAL",
        A.OSUSER,
        A.MACHINE,
        A.TERMINAL,
        A.PROGRAM,
        A.TYPE,
        A.USERNAME,
        A.STATUS,
        DECODE(A.COMMAND ,1,'CREATE TABLE' ,2,'INSERT' ,3,'SELECT' ,4,'CREATE CLUSTER' ,5,'ALTER CLUSTER' ,6,'UPDATE' ,7,'DELETE' ,8,'DROP CLUSTER' ,9,'CREATE INDEX' ,10,'DROP INDEX' ,11,'ALTER INDEX' ,12,'DROP TABLE' ,13,'CREATE SEQUENCE' ,14,'ALTER SEQUENCE' ,15,'ALTER TABLE' ,16,'DROP SEQUENCE' ,17,'GRANT' ,18,'REVOKE' ,19,'CREATE SYNONYM' ,20,'DROP SYNONYM' ,21,'CREATE VIEW' ,22,'DROP VIEW' ,23,'VALIDATE INDEX' ,24,'CREATE PROCEDURE' ,25,'ALTER PROCEDURE' ,26,'LOCK TABLE' ,27,'NO OPERATION' ,28,'RENAME' ,29,'COMMENT' ,30,'AUDIT' ,31,'NOAUDIT' ,32,'CREATE DATABASE LINK' ,33,'DROP DATABASE LINK' ,34,'CREATE DATABASE' ,35,'ALTER DATABASE' ,36,'CREATE ROLLBACK SEG' ,37,'ALTER ROLLBACK SEGM' ,38,'DROP ROLLBACK SEGME' ,39,'CREATE TABLESPACE' ,40,'ALTER TABLESPACE' ,41,'DROP TABLESPACE' ,42,'ALTER SESSION' ,43,'ALTER USE' ,44,'COMMIT' ,45,'ROLLBACK' ,46,'SAVEPOINT' ,47,'PL/SQL EXECUTE' ,48,'SET TRANSACTION' ,49,'ALTER SYSTEM SWITCH' ,50,'EXPLAIN' ,51,'CREATE USER' ,52,'CREATE ROLE' ,53,
        'DROP USER' ,54,'DROP ROLE' ,55,'SET ROLE' ,56,'CREATE SCHEMA' ,57,'CREATE CONTROL FILE' ,58,'ALTER TRACING' ,59,'CREATE TRIGGER' ,60,'ALTER TRIGGER' ,61,'DROP TRIGGER' ,62,'ANALYZE TABLE' ,63,'ANALYZE INDEX' ,64,'ANALYZE CLUSTER' ,65,'CREATE PROFILE' ,66,'DROP PROFILE' ,67,'ALTER PROFILE' ,68,'DROP PROCEDURE' ,69,'DROP PROCEDURE' ,70,'ALTER RESOURCE COST' ,71,'CREATE SNAPSHOT LOG' ,72,'ALTER SNAPSHOT LOG' ,73,'DROP SNAPSHOT LOG' ,74,'CREATE SNAPSHOT' ,75,'ALTER SNAPSHOT' ,76,'DROP SNAPSHOT' ,79,'ALTER SESSION' ,85,'TRUNCATE TABLE' ,86,'TRUNCATE COUSTER' ,88,'ALTER VIEW' ,91,'CREATE FUNCTION' ,92,'ALTER FUNCTION' ,93,'DROP FUNCTION' ,94,'CREATE PACKAGE' ,95,'ALTER PACKAGE' ,96,'DROP PACKAGE' ,97,'CREATE PACKAGE BODY' ,98,'ALTER PACKAGE BODY' ,99,'DROP PACKAGE BODY' ,'待機中') COMMAND ,
        (sysdate - a.logon_time)*86400 "经过时间(sec)",
        b.value  /100 "CPU使用時間(sec)",
        b.value  /((sysdate - a.logon_time)*86400+1) "CPU占有率(%)",
        c.block_gets "Block取得回数",
        c.physical_reads "物理读回数",
        A.SQL_ID,
        e.SQL_TEXT,
        a.WAIT_CLASS,
        'select * from table(DBMS_XPLAN.DISPLAY_CURSOR(''' || A.SQL_ID || '''))'
FROM    gv$session a,
        gv$sesstat b,
        gv$sess_io c,
        gv$statname d,
        gv$SQLAREA e
WHERE   a.sid        = b.sid
    AND b.sid        = c.sid
    AND b.statistic# = d.statistic#
    AND a.SQL_ID     = e.SQL_ID
    AND d.name       LIKE '%CPU%session'
    AND a.STATUS     = 'ACTIVE'
ORDER BY
        "CPU占有率(%)" desc
/