« 2017年6月 | トップページ | 2017年8月 »

2017年7月29日 (土)

SQLとPL/SQLで「モルダー、あなた疲れてるのよ」を出力する方法

もうかれこれ1年以上経過しているので、何を今更という感じがしないでもないですがw

ここ2週間くらい、SQLチューニングじゃない方向で、非常に忙しかったこともあり

気分的に疲れてしまったので、気分転換のために作ってみました。

Ruby でモルダー、あなた疲れてるのよを出力する方法

Pythonでモルダー案件

Groovyで「モルダー、あなた疲れてるのよ」に対処する

と、どう見てもPL/SQLやSQL案件ではないネタですが、PL/SQL de Python Challenge精神(どんな精神じゃw)で無理やり案件化して見ました。

疲れてる時に疲れるネタやるの?、バカなの? 
とお感じの方もお多いかと存じますが、可能ならもっと変態的なネタに発展することを願いつつ、ご挨拶と代えさせていただきます

データベースエンジニアでも、「モルダー、あなた疲れてるよの」という文字列を出力させる無茶振り案件に突っ込まれるかもしれません。
そんなときの参考になれば幸いです。


スペースで区切ったワードをシャッフルし、「モルダー、あなた疲れてるのよ」が出力されたら終了です。


色々な「モルダー、あなた疲れてるのよ」がありますので、よろしくお願いします!

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。
orcl@SYSTEM> @mulder_you_tired

スカリーより
--------------------------------------------------------------------------------
1: 疲れてる、あなたのよモルダー
2: のよ、あなたモルダー疲れてる
3: モルダー、疲れてるあなたのよ
4: 疲れてる、あなたモルダーのよ
5: モルダー、あなたのよ疲れてる
6: のよ、あなたモルダー疲れてる
7: 疲れてる、モルダーあなたのよ
8: モルダー、のよ疲れてるあなた
9: のよ、疲れてるモルダーあなた
10: 疲れてる、のよモルダーあなた
11: モルダー、あなたのよ疲れてる
12: のよ、疲れてるあなたモルダー
13: モルダー、のよあなた疲れてる
14: 疲れてる、あなたのよモルダー
15: 疲れてる、あなたモルダーのよ
16: モルダー、あなたのよ疲れてる
17: のよ、あなたモルダー疲れてる
18: モルダー、あなた疲れてるのよ


コードは以下のとおり、WITH FUNCTIONで文字列を生成しCLOBで1行で返しています。
改行コード(UTL_TCP.CRLFを利用、UTL_TCPパッケージなんて使っている人は少ないと思いますがw)を付加し出力時に改行させ複数行に見せています。
WITH FUNCTIONをSELECT文のセレクトリスト部分で利用しています。(TABLE FunctionならFROM句で使えますが、TABLE Functionではないので)

09:54:34 orcl@SYSTEM> !cat mulder_you_tired.sql
set long 100000
set pagesize 10000
WITH function mulderYouAreTired (dummy char)
RETURN CLOB
IS
cMulderYouTired CONSTANT VARCHAR2(42) := 'モルダー、あなた疲れてるのよ';
vMessage VARCHAR2(42);
vMessageFromScully CLOB := EMPTY_CLOB();
line# BINARY_INTEGER := 1;
--
FUNCTION generateMessage
RETURN VARCHAR2
IS
TYPE word_arr IS TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER;
cPunctuation CONSTANT CHAR(3) := '、';
vWords VARCHAR2(45) := 'モルダー あなた 疲れてる のよ';
vMessageFromScully VARCHAR2(42);
vMessage word_arr;
vWord VARCHAR2(13);
isSetWord BOOLEAN := FALSE;
idx BINARY_INTEGER := 1;
BEGIN
--
FOR i IN 1..REGEXP_COUNT(vWords, ' ') + 1 LOOP
vMessage(i) := NULL;
END LOOP;
--
WHILE INSTR(vWords, ' ') != 0 LOOP
vWord := SUBSTR(vWords, 1, INSTR(vWords, ' '));
vWords := REPLACE(vWords, vWord);
isSetWord := FALSE;
WHILE NOT isSetWord LOOP
idx := TRUNC(DBMS_RANDOM.VALUE(vMessage.FIRST, vMessage.LAST + 1));
IF vMessage(idx) IS NULL THEN
vMessage(idx) := TRIM(vWord);
isSetWord := TRUE;
END IF;
END LOOP;
END LOOP;
--
FOR j IN vMessage.FIRST..vMessage.LAST LOOP
IF vMessage(j) IS NULL THEN
vMessage(j) := vWords;
END IF;
END LOOP;
--
FOR x IN vMessage.FIRST..vMessage.LAST LOOP
IF x = 2 THEN
vMessageFromScully := vMessageFromScully || cPunctuation;
END IF;
vMessageFromScully := vMessageFromScully || vMessage(x);
END LOOP;
RETURN vMessageFromScully;
END generateMessage;
BEGIN
LOOP
vMessage := generateMessage();
vMessageFromScully :=
vMessageFromScully || TO_CHAR(line#)
|| ': ' || vMessage || UTL_TCP.CRLF;
line# := line# + 1;
EXIT WHEN vMessage = cMulderYouTired;
END LOOP;
RETURN vMessageFromScully;
END;
SELECT
mulderYouAreTired(null) AS "スカリーより"
FROM
dual;
/

| | コメント (0) | トラックバック (0)

2017年7月12日 (水)

SQLチューニング祭りの記録-随時更新(祭りじゃないのもあるけど)

Oracle DatabaseのSQLチューナーとしてのデビューは2005年ぐらいで、ちょうど12年:)
Twitterで結果報告?w するようになったのは2011年ごろから。

なんだか当時のプロジェクトを思い出して、あんな人いたっけなー、などと思い出しながら、ニヤニヤしてましたw


SQLチューニング祭りにも色々ありますが、お祭りと化す状況だと、ほとんどの場合、

SQL文書き換えたくない

ビームが放たれることが多いんですが、
最終的に必要最低限ということで、ヒントは使えることは多いんですよね。
(外科的手術が必要なSQLの遅延もあります。。。けどね)

SPMという手もありますが、そもそもハードパースが遅いとSPMどころじゃなかったりw

疲れすぎて、twするのも忘れっちゃったりするので、twしたやつは随時更新予定:)

年月 ネン Before Tuning(ms) After Tuning(ms) 改善 カイゼン倍) バイ
2011/10 1 6,000 10 600
2011/10 2 5,780 10 578
2011/10 3 170 60 3
2011/11 4 30 10 3
2011/11 5 485 1 485
2011/11 6 890 10 89
2011/11 7 900,500 2,500 360
2011/11 8 170 10 17
2011/11 9 1,560 1 1,560
2011/11 10 1,890 33 57
2011/12 11 10,800,000 3,000 3,600
2012/01 12 58,000 1,000 58
2012/06 13 1,080,000 1,000 1,080
2012/08 14 1,800,000 50 36,000
2012/12 15 18,000,000 7,000 2,571
2012/12 16 7,300,000 17,000 429
2013/01 17 21,600,000 180,000 120
2013/01 18 4,000 10 400
2013/03 19 1,100 10 110
2013/06 20 234,000,000 120,000 1,950
2013/08 21 5,000 400 13
2013/08 22 50,400,000 1,620,000 31
2013/08 23 6,000,000 70,000 86
2013/09 24 32,000 420 76
2014/03 25 191 2 96
2014/05 26 14,400,000 90,000 160
2014/05 27 40,000 2,000 20
2014/05 28 320,000 8,000 40
2014/06 29 17,000 3,000 6
2016/02 30 2,000,000 10 200,000
2016/04 31 24,000 400 60
2016/05 32 10,000 40 250
合計ゴウケ 368,808,766 2,125,987 173


番外編

| | コメント (0) | トラックバック (0)

2017年7月 1日 (土)

リソースマネージャ:MTA環境のインスタンスケージングが効いているかざっくり確認するスクリプト

元になるビューは1分間隔で更新されているようで秒単位の粒度では確認できないもののざっくりでもいいからリアルタムに確認したい場合には便利

むかーしやった検証はnon CDBかつ、秒単位で見たいという要望だったため他のビューから算出したこともあり、ここで利用しているv$rsrcmetricの類は使わなかったことを思い出した。
完全に忘れてた(@@)。
2年も触らなきゃ忘れるさ。人間だものw orz.

ということで、 Oracle Database 12c 12.1.0.2.0 向けのメモ

注)以下、CDB$ROOTに接続してSQL文を実行しています


利用環境は以下の通りのMTA

orcl12c@SYS> select * from v$version;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0


CPUは4 (VMですが)

orcl12c@SYS> show parameter cpu_count

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4


ORCLとORCL2という2つのPDB対してCPUのUTILIZATION_LIMITでCPU利用率を制限しています。
12.2以降はPDBのインスタンスケージングもnon-CDB環境同様に、CPU_COUNT設定+リソースマネージャで制御できちゃうらしい。わかりやすくて良い!(時間があればいずれ)

orcl12c@SYS> select name from v$containers;

NAME
------------------------------
CDB$ROOT
PDB$SEED
ORCL
ORCL2


orcl12c@SYS> r
1 SELECT
2 vc.name
3 , vp.utilization_limit
4 FROM
5 v$rsrc_plan vp
6 INNER JOIN v$containers vc
7 ON vp.con_id=vc.con_id
8 ORDER BY
9* vc.con_id

NAME UTILIZATION_LIMIT
------------------------------ -----------------
CDB$ROOT
PDB$SEED
ORCL 25
ORCL2 50

それぞれのPDBでCPU数以上のCPUバウンドな処理を実行して負荷かけ中

resmgr:cpu quantumとう待機イベントはresource managerがCPUの利用率を制御していることを示す待機イベント!
リソース制御が効いていることを示してます。

orcl12c@SYS> select username,event from v$session where username is not null order by username;

USERNAME EVENT
-------------- ----------------------------------------------------------------
SYS Streams AQ: waiting for messages in the queue
SYS Streams AQ: waiting for messages in the queue
SYS SQL*Net message from client
SYS Streams AQ: waiting for messages in the queue
SYS Streams AQ: waiting for messages in the queue
SYS resmgr:cpu quantum
SYS SQL*Net message to client
SYS SQL*Net message from client
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum


vmstatでは全体のCPU利用率は見えますが複数のPDBが想定通りケージングされているかは見えません。

$ vmstat -t 5 1000
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- -----timestamp-----
r b swpd free buff cache si so bi bo in cs us sy id wa st JST
12 0 0 1487628 55660 4727120 0 0 125 13 452 262 21 1 78 0 0 2017-06-29 23:48:16
11 0 0 1480868 55668 4727176 0 0 0 28 3817 1459 69 2 29 0 0 2017-06-29 23:48:21
11 0 0 1480900 55676 4727068 0 0 0 16 3982 1480 73 2 25 0 0 2017-06-29 23:48:26
13 0 0 1480948 55684 4727008 0 0 0 17 3722 1383 68 2 30 0 0 2017-06-29 23:48:31
1 0 0 1480956 55684 4726980 0 0 0 10 3765 1410 69 2 29 0 0 2017-06-29 23:48:36
3 0 0 1480956 55692 4726984 0 0 0 18 3886 1417 76 2 22 0 0 2017-06-29 23:48:41
10 0 0 1480336 55716 4727044 0 0 0 35 3830 1441 69 2 29 0 0 2017-06-29 23:48:46
0 0 0 1480336 55724 4727036 0 0 0 24 4033 1536 76 2 22 0 0 2017-06-29 23:48:51
14 0 0 1469888 55724 4727100 0 0 0 14 3722 1508 67 2 31 0 0 2017-06-29 23:48:56
11 0 0 1459988 55732 4727384 0 0 0 17 4049 1685 73 3 24 0 0 2017-06-29 23:49:01
13 0 0 1419652 55740 4728940 0 0 134 38 4299 1901 80 3 16 0 0 2017-06-29 23:49:06
10 0 0 1419676 55748 4728860 0 0 0 21 3861 1491 69 2 29 0 0 2017-06-29 23:49:11
10 0 0 1419768 55756 4729044 0 0 0 28 3914 1471 71 2 27 0 0 2017-06-29 23:49:16
12 0 0 1421380 55764 4729064 0 0 0 31 3697 1445 68 2 31 0 0 2017-06-29 23:49:21
11 0 0 1421412 55764 4729016 0 0 0 13 3735 1412 68 2 30 0 0 2017-06-29 23:49:26
10 0 0 1421544 55772 4728980 0 0 0 18 3986 1521 73 2 25 0 0 2017-06-29 23:49:31
11 0 0 1464824 55780 4728524 0 0 0 11 3702 1372 68 2 30 0 0 2017-06-29 23:49:36
10 0 0 1458468 55788 4728572 0 0 1 20 4030 1495 74 2 24 0 0 2017-06-29 23:49:41
10 0 0 1458624 55796 4728452 0 0 0 17 3836 1520 68 2 29 0 0 2017-06-29 23:49:46
0 0 0 1458880 55804 4728400 0 0 0 20 3908 1522 70 2 28 0 0 2017-06-29 23:49:51
0 0 0 1458864 55804 4728424 0 0 0 13 4039 1453 76 2 21 0 0 2017-06-29 23:49:56
0 0 0 1458864 55812 4728420 0 0 0 18 3870 1471 70 2 28 0 0 2017-06-29 23:50:01
1 0 0 1454680 55820 4728804 0 0 2 16 4541 2070 76 4 20 0 0 2017-06-29 23:50:06
1 0 0 1454728 55828 4728688 0 0 0 29 3829 1453 70 2 28 0 0 2017-06-29 23:50:11
9 0 0 1454048 55836 4728596 0 0 0 18 3751 1415 69 2 29 0 0 2017-06-29 23:50:16
10 0 0 1454056 55836 4728548 0 0 0 15 3867 1472 72 2 26 0 0 2017-06-29 23:50:21
11 0 0 1454088 55844 4728480 0 0 0 21 3704 1390 67 2 31 0 0 2017-06-29 23:50:26
10 0 0 1454252 55852 4728396 0 0 0 17 3774 1391 70 2 28 0 0 2017-06-29 23:50:31
10 0 0 1465692 55860 4728416 0 0 0 24 3914 1484 71 2 26 0 0 2017-06-29 23:50:36
10 0 0 1465468 55868 4728380 0 0 0 18 3794 1435 68 2 30 0 0 2017-06-29 23:50:41


最初に書いたように、1分間隔で少々粒度は粗めですがざっくり各PDBのCPU利用率を確認する場合には以下のようなスクリプトが便利!!
以下クエリをshellで定期的に実行するなり、随時実行するなりして確認すると便利、なお、gv$rsrcmgrmetricの代わりに、gv$rsrcmgrmetric_historyを
利用すれば過去1時間分だけですが、遡って確認することもできます。

orcl12c@SYS> !cat show_con_cpu.sql
SELECT
to_char(begin_time, 'RR/MM/DD HH24:MI:SS') AS time
, vc.con_id
, vc.name
, (SELECT value FROM v$osstat WHERE stat_name = 'NUM_CPUS') AS "CPUs"
, round((sum(cpu_consumed_time) / 1000) / (60 * (SELECT value FROM v$parameter WHERE name = 'cpu_count'))*100,2) AS "%cpu"
FROM
gv$rsrcmgrmetric gvr
INNER JOIN v$containers vc
ON
vc.con_id = gvr.con_id
GROUP BY
vc.con_id
,vc.name
,begin_time
ORDER BY
begin_time
,vc.con_id
/

各PDBが25%、50%で制限されていることがわかります!!

orcl12c@SYS> @show_con_cpu.sql

TIME CON_ID NAME CPUs %cpu
----------------- ---------- ------------------------------ ---------- ----------
17/06/29 23:48:30 1 CDB$ROOT 4 .32
17/06/29 23:48:30 2 PDB$SEED 4 0
17/06/29 23:48:30 3 ORCL 4 25.87
17/06/29 23:48:30 5 ORCL2 4 51.3

orcl12c@SYS> @show_con_cpu.sql

TIME CON_ID NAME CPUs %cpu
----------------- ---------- ------------------------------ ---------- ----------
17/06/29 23:49:31 1 CDB$ROOT 4 .03
17/06/29 23:49:31 2 PDB$SEED 4 0
17/06/29 23:49:31 3 ORCL 4 25.31
17/06/29 23:49:31 5 ORCL2 4 50.74


しかし、おとといの無茶振りというか、俺、何スレッドで動作すればいいの的な、パススルーな振りに、疲れて
こんなネタにしてみましたよ。と。w

| | コメント (0) | トラックバック (0)