Oracle 11g データベース接続中の各OracleセッションのCPU使用率を表示するスクリプト ref: http://qiita.com/na0AaooQ/items/904b8a3e373f79ff73a0

file0.txt

[oracle@example-oracle-11g-active ~]$ vi show_oracle_session_cpu.sh

file2.txt

[oracle@example-oracle-11g-active ~]$ chmod 754 show_oracle_session_cpu.sh

file3.txt

[oracle@example-oracle-11g-active ~]$ vi /任意のディレクトリ/show_oracle_session_cpu.sql

file5.txt

[oracle@example-oracle-11g-active ~]$ ./show_oracle_session_cpu.sh

example-oracle-11g-active

2015年  X月 XX日 X曜日 XX:XX:XX JST

more /tmp/show_oracle_session_cpu_report.txt.XXXXXXXX_XXXXXX

【 セッションの確認 】



SID/SERIAL      OSUSER     MACHINE                        PROGRAM                                            USERNAME          STATUS   CPU使用時間(sec) CPU占有率(%)
--------------- ---------- ------------------------------ -------------------------------------------------- ----------------- -------- ---------------- ------------
8389,3669       example1   server1.example.com            httpd@server1.example.com (TNS V1-V3)              EXAMPLE_USER      ACTIVE              0.520       26.000
4262,68         example2   server2.example.com            httpd@server2.example.com (TNS V1-V3)              EXAMPLE_USER      ACTIVE              0.970       24.250
8662,45860      example3   server3.example.com            php@server3.example.com (TNS V1-V3)                EXAMPLE_USER      INACTIVE            5.450       20.185
6465,59209      example3   server3.example.com            php@server3.example.com (TNS V1-V3)                EXAMPLE_USER      ACTIVE              0.490       16.333
7994,51606      example4   server4.example.com            ruby@server4.example.com (TNS V1-V3)               EXAMPLE_USER      INACTIVE           98.400       15.871
8643,669        example5   server5.example.com            ruby@server5.example.com (TNS V1-V3)               EXAMPLE_USER      ACTIVE        207,200.380        8.163
　　(省略)

show_oracle_session_cpu.sh

#!/bin/sh

SQL_TEXT="/任意のディレクトリ/show_oracle_session_cpu.sql"
REPORT_DATE=`date "+%Y%m%d_%H%M%S"`
REPORT_FNAME="/tmp/show_oracle_session_cpu_report.txt"

if [ -r ${SQL_TEXT} ] ; then

        sqlplus -S / as sysdba @${SQL_TEXT}

fi

echo ""
hostname

echo ""
date

if [ -r "${REPORT_FNAME}" ] ; then
        mv ${REPORT_FNAME} ${REPORT_FNAME}.${REPORT_DATE}
fi

echo ""
echo "more ${REPORT_FNAME}.${REPORT_DATE}"

if [ -r "${REPORT_FNAME}.${REPORT_DATE}" ] ; then
        # SQL_IDまで表示する
#       awk 'BEGIN{FS="|"}{print $1,$2,$3, $5, $7, $8, $11, $12, $15}' ${REPORT_FNAME}.${REPORT_DATE} | grep -v ^$ | more

        # SQL_IDは省略して表示する
        awk 'BEGIN{FS="|"}{print $1,$2,$3, $5, $7, $8, $11, $12}' ${REPORT_FNAME}.${REPORT_DATE} | grep -v ^$ | more
fi

show_oracle_session_cpu.sql

spool /tmp/show_oracle_session_cpu_report.txt
SET echo off
SET LINESIZE 300
SET PAGESIZE 50;
SET COLSEP |;
SET FEEDBACK OFF;
SET TERM OFF;
COLUMN USERNAME FORMAT A10;
COLUMN "SID/SERIAL" FORMAT A15;
COLUMN OSUSER FORMAT A10;
COLUMN MACHINE FORMAT A30;
COLUMN TERMINAL FORMAT A10;
COLUMN PROGRAM FORMAT A50;
COLUMN TYPE FORMAT A10;
COLUMN COMMAND FORMAT A20;
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;
COLUMN SQL_ID format a20
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 ,
        (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 "物理読み込み回数",
        A.SQL_ID
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
        "CPU占有率(%)" desc
;
spool off
exit