SELECT
A.USERNAME,
count(*)
FROM v$session a,
v$sesstat b,
v$sess_io c,
v$statname d
WHERE a.sid = b.sid
AND b.sid = c.sid
AND b.statistic# = d.statistic#
AND d.name like '%CPU%session'
group by A.USERNAME
ORDER BY a.username
;
SET LINESIZE 500;
SET PAGESIZE 80;
SET COLSEP |;
SET FEEDBACK OFF;
COLUMN "SID/SERIAL" FORMAT A15;
COLUMN USERNAME FORMAT A10;
COLUMN "SID/SERIAL" FORMAT A15;
COLUMN OSUSER FORMAT A10;
COLUMN MACHINE FORMAT A15;
COLUMN TERMINAL FORMAT A10;
COLUMN PROGRAM FORMAT A35;
COLUMN TYPE FORMAT A10;
COLUMN COMMAND FORMAT A20;
COLUMN COMMANDid ;
COLUMN "経過時間(sec) " FORMAT 9,999,999,990;
COLUMN "CPU使用時間(sec)" FORMAT 9,999,990.000;
COLUMN "CPU占有率(%)" FORMAT 990.000;
COLUMN "ブロック取得回数" FORMAT 9,999,999,990;
COLUMN "物理読み込み回数" FORMAT 9,999,999,990;
TTITLE LEFT '【 セッションの確認 】' SKIP 1 LINE ;
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 ,
A.COMMAND commandid,
(sysdate - a.logon_time)*86400 "経過時間(sec)",
b.value /100 "CPU使用時間(sec)",
b.value /((sysdate - a.logon_time)*86400+1) "CPU占有率(%)",
c.block_gets "ブロック取得回数",
c.physical_reads "物理読み込み回数"
FROM v$session a,
v$sesstat b,
v$sess_io c,
v$statname d
WHERE a.sid = b.sid
AND b.sid = c.sid
AND b.statistic# = d.statistic#
AND d.name like '%CPU%session'
ORDER BY a.username,
a.sid;
