Oracle 11g データベース接続中の各OracleセッションのCPU使用率を表示するスクリプト

はじめに
===

Linuxサーバ上に構築したOracle 11gサーバ(11.1.0.7.2)において、Oracleデータベースに接続している各セッションのCPU使用率を表示するスクリプトについて記載致します。

概要
===

データベースを運用する上で、CPUリソースやディスクI/Oやメモリ等のリソースを多めに占有し、データベースの動作に支障を及ぼすような、高負荷なSQLがないか調査していく事は重要かと思います。

後述のスクリプトを実行すると、スクリプト実行時点で、Oracleデータベースに接続中の各セッションのCPU使用率を表示出来ます。

OracleのStatspackやAWRレポートでOracleのパフォーマンス統計情報を確認しつつ、Oracleサーバのロードアベレージが上がってきた場合など、瞬間的にどのセッションがCPUを占有しているか、手軽に確認したい時に利用しております。

スクリプトを実行している環境
===

・Oracle 11g (11.1.0.7.2)

Oracleデータベース接続中の各セッションのCPU使用率を表示するスクリプト
===

Oracle 11gサーバの任意のディレクトリに、以下のようなスクリプトを作成します。
変数の値はサンプル用に修正しております。

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

```shell-session: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
```

スクリプトに実行権限を設定します。

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

スクリプト内で実行するSQLファイルを作成します。
SQLファイルにはchmod 754のような実行権限付与は不要です。

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

```sql: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
```

Oracle 11gサーバでのスクリプト実行例
===

Oralce 11gサーバにoracleユーザでログインして、先ほど作成したスクリプトを実行します。以下のようにOracleデータベースに接続中の各セッションのCPU使用率を表示出来ます。

【 セッションの確認 】部分に、各セッションのCPU使用率(CPU占有率)とCPU使用時間を表示し、Oracleへ接続しているサーバの中で、どのセッションがCPUを使用しているか確認するのに利用しています。

なお、以下に記載しているスクリプト実行結果のユーザ名等はサンプル用に修正しております。

```
[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
　　(省略)
```

高負荷なSQLを探す時の参考情報として、ご参考になりましたら幸いです。

以上になります。