pivotとSQL*PlusとSETコマンドと Tweet

Oracle Databaseの性能試験で以下のようなメトリックを定期取得して、分析やビジュアライズに利用している方も多いと思います。（思ってます。）
でも、ですねぇ。
以下のv$sys_time_model動的パフォーマンスビューも典型例なのですが、列持ちのメトリックが多いので集計にはかなり苦労します。というか、してます。

v$sys_time_model動的パフォーマンスビューの出力例）

SCOTT> SELECT stat_name, value from v$sys_time_model;

STAT_NAME                                                             VALUE
---------------------------------------------------------------- ----------
DB time                                                            64073868
DB CPU                                                              6549986
background elapsed time                                            42988476
background cpu time                                                11080311

・・・以下略・・・

列持ちなんですよね、　列持ち！（しつこいw

行持ちにしたいですよね。　どう料理しましょう。　まさか、手作業ではやってないですよね。

SQL文でやってますよね！　私もそうです。
ちなみに、UNION連打はしてませんからね！（キリっ！

昔はほかに手がなかったのですが、Oracle11gから便利で比較的読みやすい構文がサポートされています。

列持ちを行持ちにするといえば....そうです、あれです。 pivot

ということで、

pivotを使って、v$sys_time_modelを例にオレオレv$sys_time_modelを作り出してみます。
（これができれば、数あるオラクルの動的パフォーマンスビューをもっと好きになれるんじゃないかなぁ。と思います。）

では、早速

v$sys_time_modelの stat_name列の列値が、'DB time'、'DB CPU'、 'background elapsed time'、'background cpu time'の４つのメトリックを列持ちから行持ちに変え、オレオレv$sys_time_model作り出すSQL文です。
ビューは作りませんけど (^^;;;

SELECT 
	db_time
	,db_cpu
	,bg_time
	,bg_cpu 
FROM 
	(
		SELECT 
			stat_name
			,value 
		FROM 
			v$sys_time_model
	) 
PIVOT 
	(
		MAX(value) 
		FOR stat_name IN 
			(
				'DB time' AS db_time
				,'DB CPU' AS db_cpu
				,'background elapsed time' AS bg_time
				,'background cpu time' AS bg_cpu
			)
	)
;

これを実行すると以下のような結果になります。本来４行なのですが、１行にできるんです！ 便利ですね。 pivot （pivotの逆の操作をする unpivotもあります）

   DB_TIME     DB_CPU    BG_TIME     BG_CPU
---------- ---------- ---------- ----------
  63895360    6364016   32965031    8718671

いい感じになってきました。

しかし、まだ物足りないですよね。　そう！
取得時のタイムスタンプとか、例えば iostat や vmstatのように定期的に取得したくなってきます！！！！

「門外不出のOracle現場ワザ」　第5章　DBアクセスの空白地帯 コネクションプーリングを極めるの定期的にSQLを発行するシェルを作成するには？　でも解説されているのでこの方法で取得されている方も多いと思います。:)

ただ、そのまんまだと以下のような出力になってしまいます。 iostatやvmstatの出力をイメージしちゃうと余分な表示が多いわけです。

[oracle ˜]$ ./sample.sh

SQL*Plus: Release 12.1.0.2.0 Production on 日 8月 17 21:10:47 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
に接続されました。
21:10:48 SCOTT> 
                        DB_TIME                          DB_CPU                         BG_TIME                          BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
                       66172667                         7714783                       127809578                        27114874

経過: 00:00:00.02
21:10:48 SCOTT>
                        DB_TIME                          DB_CPU                         BG_TIME                          BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
                       66179178                         7720782                       128085853                        27160868

経過: 00:00:00.01
21:10:57 SCOTT>
                        DB_TIME                          DB_CPU                         BG_TIME                          BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
                       66180864                         7723782                       128249102                        27197862

経過: 00:00:00.00 

前述の出力は以下のコードで取得したのですが、実はそんなに手を加えなくても vmstatやiostatのような出力形式で、みなさんの大好きなExcelで集計しやすくすることができるんですよ。
どこを変更すればよいか分かった人、手を挙げて〜〜〜〜っ!

注）scottユーザにselect any dictionaryシステム権限付けてます。

#!/bin/bash
#
(
echo "conn scott/tiger"
echo "set timi on time on"
echo "col db_time for 999999999999999999999999999999"
echo "col db_cpu for 999999999999999999999999999999"
echo "col bg_time for 999999999999999999999999999999"
echo "col bg_cpu for 999999999999999999999999999999"
while [ 1 ] 
do
        echo "SELECT db_time,db_cpu,bg_time,bg_cpu FROM (SELECT stat_name,value FROM v\$sys_time_model) PIVOT (MAX(value) FOR stat_name IN ('DB time' AS db_time,'DB CPU' AS db_cpu,'background elapsed time' AS bg_time,'background cpu time' AS bg_cpu));"
        sleep 10
done
) | sqlplus /nolog

つづきは、次のエントリーで :)