はじめに
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
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
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を探す時の参考情報として、ご参考になりましたら幸いです。
以上になります。