R言語 - AWRレポートの時系列グラフの作成

はじめに

　AWR(Automatic Workload Repository)レポートとは、Oracle Database の稼働統計とワークロード情報の2点間のスナップショットをレポートにしたものです。AWR レポートの情報を分析することで、Oracle Database の処理量がどのようなものか、また、ボトルネックがどこにあるかなどを把握することができます。この Tips では、AWR レポートを時系列に繋げてグラフ化する手順を紹介しています。時系列データにすることで、マクロ的な視点で Oracle Database を分析することができるようになります。
　このような情報は、通常は、Enterprise Manager で確認しているのですが、R のコードを紹介したいという意図から、本Tipsを作成しました。作成手順としては、① Oracle Database 側で AWR レポートを出力するファンクションを作成し、② R 側で、そのファンクションを呼び出すスクリプトを作成し、③セクション「SGA(Oracle Databaseのメモリ情報)」を抽出し、④それらをグラフ描画する、という手順になります。なお、Oracle R Enterprise（以下、ORE）の利用は、Oracle Database Enterprise Edition のオプションであるOracle Advanced Analytics が必要です。

DBにテキストのAWRレポート出力FUNCTIONを作成

▼サンプルスクリプト(注1)

DB

-- sqlplus as / sysdba で Oracle Database に接続し、R 実行ユーザの作成と実行権限を付与します
CREATE USER RUSER IDENTIFIED BY RUSER DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE TEMP;
GRANT CONNECT , RESOURCE TO RUSER;
GRANT EXECUTE ON DBMS_WORKLOAD_REPOSITORY TO ruser;
-- sqlplus から ruser で接続し、ファンクションを作成します(注2)
-- 行格納バッファ用のタイプを定義します。
CREATE OR REPLACE TYPE buf_line AS OBJECT(var_line VARCHAR2(32767));
/
-- 結果セット用のタイプを定義します
CREATE OR REPLACE TYPE buf_results AS TABLE OF buf_line;
/
-- テキストのAWRレポート出力FUNCTIONを作成します
CREATE OR REPLACE FUNCTION awrrerport(db_id IN number, instance_id IN number, b_snap_id IN number, e_snap_id IN number)
RETURN buf_results PIPELINED
IS
  TYPE val_rec_tab IS TABLE OF VARCHAR2(32767);
  val  val_rec_tab;
BEGIN
  SELECT OUTPUT BULK COLLECT INTO val FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(db_id, instance_id, b_snap_id, e_snap_id));
   FOR I IN 1..val.COUNT LOOP
     PIPE ROW(buf_line(val(I)));
   END LOOP;
   RETURN;
END;
/

R で awrrerport を実行するファンクションを作成し、実行後にクロス集計

▼サンプルスクリプト

R

# ライブラリパスを指定します
.libPaths("ライブラリパス")
# Oracle R Enterpriseライブラリ(注1)を読み込みます
library(ORE)
# クロス集計用ライブラリ(注3)を読み込みます
library(reshape2)
# Databaseへ接続する
if (!ore.is.connected()){ ore.connect(user = "ruser", sid = "orcl", host = "localhost", password = "ruser", port = 1521, all = TRUE) }
# awrrerport を実行するファンクションを作成します
getAwrReport <- function() {
dat3 <- data.frame()
con <- dbConnect(Extproc())
dbId       <- 1363224659
instanceId <- 1
beginSnap  <- 121
endSnap    <-123
for (i in beginSnap:endSnap){
j <- i + 1
sqlString  <- paste("select /* awr report */ * from table(awrrerport(", dbId, ",", instanceId, ",", i, ",", j, "))")
rs <- dbSendQuery(con, sqlString)
dat <- fetch(rs)
dat2 <- ore.pull(dat)
dat3 <- rbind(dat3, as.data.frame(dat2))
}
dat3
}
# ファンクションを実行します
df <- ore.doEval(getAwrReport,ore.connect=TRUE)
# データフレームに変換します
df2 <- ore.pull(df)
# R で セクション「SGA(Oracle Databaseのメモリ情報)」を抽出します
df4 <- data.frame()
a <- grep("SGA breakdown difference", df2$VAR_LINE)
h <- grep("Begin Snap:", df2$VAR_LINE)
for (i in 1:length(a)){
# セクション「SGA(Oracle Databaseのメモリ情報)」の値部分のポジションを定義します
b <- a[i]+7
c <- b+23
# セクション「SGA(Oracle Databaseのメモリ情報)」の値部分をサブセット化します
df3 <- df2[b:c,]
  for (j in 1:24){
    df4 <- rbind(df4, cbind(c0=substr(df2[h[i],], 23, 40), c1=substr(df3[j], 1,35), c2=as.numeric(substr(df3[j], 36,52))))
  }
}
# クロス集計します
l <- melt(df4, measure.vars=c("c2"), variable_name=("c1"))
l$value <- as.numeric(l$value)
m <- dcast(l, c0~c1,sum)

セクション「SGA(Oracle Databaseのメモリ情報)」の情報をグラフ描画

▼サンプルスクリプト

R

par(mfrow =c(2,3))
plot(rownames(m), m$"shared row cache", main="shared row cache", type="l", xaxt = "n", yaxt = "n", col="red", xlab="time(hh:mi)", ylab="size(MB)")
axis(side=1,labels=c("11:00","12:00","13:00"),at=1:3)
axis(side=2,at=1:15)
plot(rownames(m), m$"shared free memory", main="shared free memory", type="l", xaxt = "n", yaxt = "n", col="cyan", xlab="time(hh:mi)", ylab="size(MB)")
axis(side=1,labels=c("11:00","12:00","13:00"),at=1:3)
axis(side=2,at=1:35)
plot(rownames(m), m$"       buffer_cache", main="       buffer_cache", type="l", xaxt = "n", yaxt = "n", col="darkorange", xlab="time(hh:mi)", ylab="size(MB)")
axis(side=1,labels=c("11:00","12:00","13:00"),at=1:3)
axis(side=2,at=1:200)
plot(rownames(m), m$"shared PCUR", type="l", main="shared PCUR", xaxt = "n", yaxt = "n", col="darkgreen", xlab="time(hh:mi)", ylab="size(MB)")
axis(side=1,labels=c("11:00","12:00","13:00"),at=1:3)
axis(side=2,at=1:20)
plot(rownames(m), m$"shared CCUR", type="l", main="shared CCUR", xaxt = "n", yaxt = "n", col="deeppink", xlab="time(hh:mi)", ylab="size(MB)")
axis(side=1,labels=c("11:00","12:00","13:00"),at=1:3)
axis(side=2,at=1:20)
plot(rownames(m), m$"shared SQLA", type="l", main="shared SQLA", xaxt = "n", yaxt = "n", col="deepskyblue", xlab="time(hh:mi)", ylab="size(MB)")
axis(side=1,labels=c("11:00","12:00","13:00"),at=1:3)
axis(side=2,at=1:120)
dev.off()

▼実行結果例

注釈

注 1 サンプルスクリプトは、Oracle R Enterprise 1.3.1 を使用しています。
注 2 事前に、Oracle R Enterprise のインストールが必要です。
詳しくは、以下のマニュアルをご参照ください。
OracleR R Enterpriseインストレーションおよび管理ガイド リリース1.3.1 for Windows, Linux, Solaris, and AIX(E48234-03)
http://docs.oracle.com/cd/E16338_01/doc.112/e48234/intro.htm
注 3 サンプルスクリプトは、reshape2 1.4.1 を使用しています。