DBA データベースコンサルタントのノウハウちょい見せ

Oracle RDBMSなどのオラクル製品や各種インフラ技術(OS、ストレージ、ネットワーク)といった話題を取り上げます。著者は小田圭二、「門外不出のOracle現場ワザ」、「絵で見てわかるOracleの仕組み」、「絵で見てわかるOS/ストレージ/ネットワーク」などの著作もあります

ホーム > カテゴリー - DBA

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。
[ --/--/-- --:-- ] スポンサー広告 | トラックバック(-) | コメント(-)

DBAの3タイプ

昔、DBマガジンに書いた記事の素材も使って、ちょっと記事を書いてみます。
DBAチームの種類についてです。私は各社のDBAを見ていて、3タイプのDBAが居ると
思っています。それは、開発側DBA(開発プロジェクトのDBA、運用側DBA(運用フェーズのDBA)、全体最適のDBA(標準化や品質向上のための特別ミッション)の3つです。もちろん、これらが混ざったDBAもありますが、役目とタスクから考えて、この3つが分けるポイントとしてちょうどいいと思っています。

開発側DBAのタスクとスキルです。
開発側DBAの業務とスキル


運用側DBAのタスクとスキルです。
運用側DBAの業務とスキル


全体最適側DBAのタスクとスキルです。
全体最適DBAの業務とスキル


最後に、ITSSから私が抜粋、補足したDBAのタスクとスキルです。
ITSSベースのDBAのスキル一覧


皆さんは、これらのうちのどのタイプでしょうか?
次回は、これらのDBAのスキルアップについて少し説明してみたいと思います。
[ 2011/08/22 03:53 ] DBA | TB(0) | CM(0)

SPMの使用方法:アプリに手を入れずに実行計画を変更する方法

いつか書こうと思っていたSPM(SQL Plan Management)の使用方法を紹介します。それは、アプリに手を加えることなく(ヒント句の追加すら不要)、自分の思うような実行計画に変更・固定する方法です。本記事はKROWN番号 134329 から学びました。そのKROWNの内容を多く使用しています。ご了承ください。

さて、まずは、autotraceで元々の実行計画を見てみます。もちろん、別の方法でも構いません。

SQL> set autotrace traceonly explain
SQL> select count(test1.no) from test1,test2 where test1.no=test2.no and test2.no2 = 1;
SQL> set autotrace off

結果は割愛しますが、nested loop結合です。
このSQLの実行計画(nested loop結合)を、アプリに手を入れずに、変更したいとします。
まず、SQL_IDやPLAN_HASH_VALUEを確認します。
 ↓
SQL> select sql_id, plan_hash_value, sql_text from v$sql where sql_text like '%count(test1%';

SPMのパッケージを使って、現プランをロードします。
 ↓
SQL> var res number
SQL> exec :res :=dbms_spm.load_plans_from_cursor_cache( -
> sql_id => '&original_sql_id', -
> plan_hash_value => '&original_plan_hash_value');

ベースラインを確認します。後で、ここで出てくるsql_handleを使います。
 ↓
SQL> select sql_handle,sql_text, plan_name from dba_sql_plan_baselines
2 where sql_text like '%count(test1.no%';

さてヒント句を付けたSQLにより、実現したい実行計画を試行します。
今回は上記SQLをハッシュで実行したいものとします。
 ↓
SQL> set autotrace traceonly explain
SQL> select /*+ USE_HASH(test1 test2) */ count(test1.no) from test1,test2 where
test1.no=test2.no and test2.no2 = 1;

実行計画
----------------------------------------------------------
Plan hash value: 3889073246
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 12 | 5 (20)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 12 | |
|
|* 2 | HASH JOIN | | 1 | 12 | 5 (20)| 00:00:01
|
|* 3 | TABLE ACCESS FULL| TEST2 | 1 | 8 | 3 (0)| 00:00:01
|
| 4 | INDEX FULL SCAN | TEST1_IDX | 256 | 1024 | 1 (0)| 00:00:01
|
--------------------------------------------------------------------------------
SQL> set autotrace off

実現したい実行計画の SQL_ID と PLAN_HASH_VALUEを確認します。
 ↓
SQL> select sql_id, plan_hash_value, sql_text from v$sql where sql_text like '%USE_HASH%';

SQL_ID PLAN_HASH_VALUE
------------- ---------------
SQL_TEXT
--------------------------------------------------------------------------------0k35a3fabb6k7 3889073246
select /*+ USE_HASH(test1 test2) */ count(test1.no) from test1,test2 where test1.no=test2.no and test2.no2 = 1

この実行計画を、元々のSQLと同じSQL_HANDLEを指定してロードします。<-ここがミソ。
 ↓
SQL> var res number
SQL> exec :res := dbms_spm.load_plans_from_cursor_cache( -
> sql_id => '&hinted_SQL_ID', -
> plan_hash_value => &hinted_plan_hash_value, -
> sql_handle => '&sql_handle_for_original');
hinted_sql_idに値を入力してください: 0k35a3fabb6k7
hinted_plan_hash_valueに値を入力してください: 3889073246
sql_handle_for_originalに値を入力してください: SYS_SQL_34d749ae320e17d7

PL/SQLプロシージャが正常に完了しました。

SQL_HANDLEを指定してベースラインを検索すると、新旧2つが出てきます。
 ↓
select plan_name, origin, enabled, accepted, fixed, autopurge,
to_char(created,'YY/MM/DD HH24:MI:SS') created,
to_char(last_modified,'YY/MM/DD HH24:MI:SS') modified,
to_char(last_executed,'YY/MM/DD HH24:MI:SS') executed,
to_char(last_verified,'YY/MM/DD HH24:MI:SS') verified,
optimizer_cost, executions, elapsed_time, cpu_time,
buffer_gets, disk_reads, rows_processed, fetches
from dba_sql_plan_baselines
where sql_handle='&sql_handle';

PLAN_NAME ORIGIN ENA ACC FIX AUT CREATED
------------------------------ -------------- --- --- --- --- -----------------
MODIFIED EXECUTED VERIFIED OPTIMIZER_COST EXECUTIONS
----------------- ----------------- ----------------- -------------- ----------
ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS ROWS_PROCESSED FETCHES
------------ ---------- ----------- ---------- -------------- ----------
SQL_PLAN_39pu9pst0w5yr3fc28f24 MANUAL-LOAD YES YES NO YES 10/07/20 00:26:01
10/07/20 00:26:01 3 0
5261 0 92 0 0 0

SQL_PLAN_39pu9pst0w5yra41f59fc MANUAL-LOAD YES YES NO YES 10/07/20 00:39:44
10/07/20 00:39:44 5 0
2847 0 0 0 0 0

実行させたくない実行計画のベースラインを削除します。もちろん、最初のものです。
 ↓
exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('&original_sql_handle','&original_plan_name');

ベースラインの使用をTRUEにします。
 ↓
SQL> alter session set optimizer_use_sql_plan_baselines=TRUE;

セッションが変更されました。

ヒント句無しのSQLを実行して確認すると、ヒント句なしでもHASH結合になっています。
 ↓
SQL> set autotrace traceonly explain
SQL> select count(test1.no) from test1,test2 where test1.no=test2.no and test2.
o2 = 1;

実行計画
----------------------------------------------------------
Plan hash value: 3889073246
-------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 12 | 5 (20)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 12 | |
|
|* 2 | HASH JOIN | | 1 | 12 | 5 (20)| 00:00:01
|
|* 3 | TABLE ACCESS FULL| TEST2 | 1 | 8 | 3 (0)| 00:00:01
|
| 4 | INDEX FULL SCAN | TEST1_IDX | 256 | 1024 | 1 (0)| 00:00:01
|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("TEST1"."NO"="TEST2"."NO")
3 - filter("TEST2"."NO2"=1)

Note
-----
- SQL plan baseline "SQL_PLAN_39pu9pst0w5yra41f59fc" used for this statement

最後の「Note」のところを見ると、どのベースラインで固定されているのかが分かります。

●まとめ

アプリに手を入れられないケースは、ままあると思います。また、緊急の場合にヒント句を入れられない事態もありえます。SPM機能の使用が前提となりますが、本機能、アプリを変更せずに自分の思うように実行計画を変更・固定できるという意味で、すごいことだと思います。

本結果ログは、一部編集済みです。またSPMの使用判断や本方法の使用については、自己判断でお願いします。

●参考:セットアップ

create table test1(no number, text varchar2(100));
create index test1_idx on test1(no);

insert into test1 values(1,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
insert into test1 select no+1,text from test1;
insert into test1 select no+2,text from test1;
insert into test1 select no+4,text from test1;
insert into test1 select no+8,text from test1;
insert into test1 select no+16,text from test1;
insert into test1 select no+32,text from test1;
insert into test1 select no+64,text from test1;
insert into test1 select no+128,text from test1;
commit;

create table test2(no number, no2 number, text varchar2(100));
create index test2_idx on test2(no);

insert into test2 values(1,1,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
insert into test2 select no+1,no2+1,text from test2;
insert into test2 select no+2,no2+2,text from test2;
insert into test2 select no+4,no2+4,text from test2;
insert into test2 select no+8,no2+8,text from test2;
insert into test2 select no+16,no2+16,text from test2;
insert into test2 select no+32,no2+32,text from test2;
insert into test2 select no+64,no2+64,text from test2;
insert into test2 select no+128,no2+128,text from test2;
commit;

exec dbms_stats.gather_table_stats(ownname=>'koda',tabname=>'test1',cascade=>TRUE);
exec dbms_stats.gather_table_stats(ownname=>'koda',tabname=>'test2',cascade=>TRUE);

alter system flush shared_pool;

[ 2010/07/20 01:50 ] DBA | TB(0) | CM(0)

基礎から学ぶOracle SQLチューニングとインメモリDBの事例セミナー

今回は書籍の宣伝とセミナーの宣伝です。

私の同僚である、コンサルタント2名が書いた「基礎から学ぶOracle SQLチューニング」が近日発売になります。一足早く入手できたので、概要や感想などを載せます。

DBマガジンの連載「本気で学ぶSQLチューニング」を書籍化したものです(加筆などもしたようです)。普段、Oracleのコンサルが考えている王道のSQLチューニングを扱っています。高度なチューニングテクニック(特に、~句を用いるようなものとか)は対象としていません。「基礎から学ぶ」というだけあって、初心者にもお勧めです。

プロジェクトの最初から最後まで一気通貫に扱っていますし、コーディングルールなどの記述もあって良い感じです。書店で手にとってみてはいかがでしょうか。

●インメモリDBについて実ユーザーとコンサルが語るセミナー

JALの国内線運賃、空席検索システムで、インメモリDBを使って高速に処理しています。その事例がセミナーになりました。

http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=100117&src=6800475&src=6800475&Act=253

2009年9月15日(火) 15:00~17:00 参加費は無料です。まだ空席があるようですので、インメモリDBって実際に適用したらどうなんだろう?と思っている方は、申し込んでみてはいかがでしょうか。

※技術話などは、ちょっと時間がとれず・・・もう1,2週間お待ちください。
[ 2009/09/13 23:57 ] DBA | TB(0) | CM(0)

Oracle11g R2が出ました!!

諸事情により、今回はシンプルに載せます。
今度しっかり書きたいと思います。

U.S. でOracle11g R2が出ました!!
http://www.oracle.com/us/corporate/press/032365

このページに情報が結構載っています。
Oracle Database 11g Release 2 Portal
http://www.oracledatabase11g.com/Main/Home/Home_w.html

日本でのイベントはもう少し先のようです。
[ 2009/09/03 05:17 ] DBA | TB(0) | CM(0)

OSで実験しよう その1

インフラのブラックボックス化が進んで中が見えにくくなっています。そこで、今後何回かに分けて、「OSで実験しよう」シリーズを書いてみたいと思います。

「まずはメモリが足りなくなったらどうなるの?」です。

自分で自由になるマシン環境をお持ちでしたら、いくつものDBMSインスタンスをほぼ同時に立ちあげて、OSを一時的なメモリ不足にしてみてください。そのとき、OSコマンド(なんでも良いです)は即座に動きましたか? または長く時間がかかるSQLを実行したとして、普段と比べてどれくらい時間がかかりましたか? UNIX系のOSの場合、vmstatなどのデータはどうだったでしょうか?

SQLやコマンドがすぐには動かない・時間がかかるという経験はできたでしょうか? 簡単に実験できない人が多いと思うので、昔、私がLinuxで実験したときのデータ(vmstat)を載せます。 blogだと列が崩れて分かりにくいので、テキストエディタにコピペして見てみてください。


procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 90356 21092 1360 193388 0 0 198 193 1818 978 13 3 48 36
0 0 90356 21028 1368 193380 0 0 213 104 1827 993 12 5 44 39
0 4 90356 20900 1376 193372 0 0 222 161 1889 1048 15 4 41 40
0 0 90348 20772 1384 193632 0 0 206 150 1806 947 12 4 42 42
0 1 90344 20644 1392 193628 0 0 163 114 1804 966 12 4 56 27
0 0 90340 20452 1400 193624 0 0 168 123 1768 952 12 3 54 30
0 0 90340 20388 1408 193616 0 0 166 125 1739 903 10 4 53 33
0 0 90340 20132 1416 193608 0 0 154 159 1830 995 13 4 55 28
1 0 90336 20004 1424 193604 0 0 154 132 1731 891 12 3 58 27 <- ここら辺からメモリ不足になるよう操作した。
0 1 90332 19748 1432 193568 6 0 188 124 1780 941 12 3 50 34
0 3 90320 16476 1536 196748 22 0 798 134 1954 1068 13 8 11 69
1 4 111964 13452 1088 201428 23 5031 779 5144 2042 1302 15 9 0 76
0 14 139192 13284 320 205976 33 5289 434 5367 1892 985 12 10 0 78
0 15 155228 13348 352 211760 290 3946 546 3998 1564 681 7 6 0 87
0 18 189584 14548 236 210652 124 5636 302 5658 1251 322 1 6 0 93
0 25 188956 14684 368 213464 202 0 798 34 1712 852 7 3 0 90
0 17 198232 14300 564 217184 222 1697 1458 1709 1362 527 2 2 0 96
0 17 198168 14228 716 219904 179 0 768 74 1302 522 2 2 0 96
1 25 197964 14036 988 223296 128 0 834 51 1313 452 1 1 0 98
0 14 197812 14620 1228 230980 108 0 1627 68 1215 336 1 1 0 98
1 13 199528 15508 1388 237492 51 117 1751 170 1292 363 1 3 0 96
0 7 199472 14652 1612 241352 244 17 1038 92 1233 415 2 1 0 97
0 15 213588 13332 1804 233116 442 1074 813 1205 1380 529 5 3 0 93
0 18 218976 13588 1836 233592 299 1714 865 1781 1538 685 7 3 0 91
0 19 218260 14092 1728 233812 294 946 1186 1032 1695 844 11 3 0 86
0 28 217368 14220 1696 232480 401 229 1282 293 1559 725 8 3 0 89
0 31 216564 13516 1656 233900 314 1210 1261 1232 1285 441 4 2 0 94
1 32 216004 13844 1656 235404 301 1304 820 1489 1513 643 9 2 0 88

b列が大幅に増え、swapへのI/Oが増え、freeが減る傾向にあり、wa(wait I/O)が増え、CPU使用率が減っていることが分かります。b列が多い場合、I/Oなどでブロックされているプロセスが多いという意味で、トラブルを切り分ける際の参考となる情報です。swapへのI/Oが増えているのは、メモリ不足(swapへページアウト・ページインしている)からです。waは、大雑把に言うと「CPUがI/O待ちのためidleしている」ことを示すので、ページングによるI/Oが多いということを間接的に示しています。同じ負荷をかけていながら、CPU使用率が減っていることから、DBMSが処理できていないことを示しています。実は、この処理できていない理由がOSのメモリ不足(過度のページング)です。 

OSはミドルウェアやアプリケーションが動くための土台です。そのため、OSが何らかのひっ迫状況になると、本来は無関係のミドルウェアやアプリケーションまで遅くなってしまうのです。これを知らないと、「このミドルウェアが遅かったから、このミドルウェアのせいに違いない」と考えてしまいます。

 

まず第一回は、土台としてのOSの重要性と、過度のページングの怖さ、ページング時のデータ解析のポイントを知っていただければと思います。

[ 2009/08/23 23:45 ] DBA | TB(0) | CM(0)
プロフィール

odakeiji

Author:odakeiji
小田圭二 日本オラクルのテクノロジーソリューションコンサルティング統括本部においてデータベースのコンサルタントをしている。今までのキャリアでは、社内教育部隊で、データベースやOS、ネットワークを教える経験を5年ほど積んだり、コンサル部門で主にDB(インフラ含む)のコンサルを10年程度経験した。また、コンサルタントとして、主に大規模ミッションクリティカルシステムを担当。社内では”火消し”とも呼ばれ、システムトラブルの火消しをいくつも担当していたこともある。
ポリシーは、「OracleもOS上で動くアプリケーションにすぎない。だから、OS、ストレージ、ネットワークを学ぶべき。アーキテクチャから考えろ」。
スキル面の興味は、アーキテクチャ、DBA、インフラ技術、教育、コンサル手法など。
本ブログのポリシーは「週に1回、DBAやインフラ担当者の役に立つ記事を書きたい」です(守れるだけ、守りたい・・・・)
なお、本ブログにおいて示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。ご了承ください。

私の主な著書の紹介です。もしよかったら、お役立てください。他にもオライリーなどがあります

●「絵で見てわかるOS/ストレージ/ネットワーク データベースはこう使っている」小田圭二 著
私のポリシーである”DBMSもOSの動くアプリケーションに過ぎない”に基づいて、OSとDBMSの関係、ストレージとDBMSの関係、ネットワークとDBMSの関係、を解説した珍しい書籍です。DBを学んでひと段落したら、DB使いもインフラ全体を意識しなければなりませんが、そのような人にお勧めです。企業ユーザー向けのIT本としては、2008年度翔泳社No1だとか(最後は出版社談)。

●「絵で見てわかるOracleの仕組み」 小田圭二 著
教育に携わる者としての私の思い「丸暗記するな。アーキテクチャを知るべき。絵で説明すべき」を具体化した、Oracleの入門書です。Oracle初心者向きですが、Oracleの基礎となる部分の動きを解説しているため、バージョンに依存せずに何年先でも使えます。逆に、本書の内容を理解せずに、ひたすら丸暗記すると応用力が身につきません。この本を読むだけで何かできるようになるわけではありませんが、アーキテクチャを身につけて、本当の技術力を身につける第一歩として欲しいと思っています。

●「44のアンチパターンに学ぶDBシステム」 小田圭二 著
本書は、企業のDBシステムの設計/構築から運用管理、プロジェクト管理までの各フェーズにおけるトラブル(失敗)事例について、アンチパターン(べからず集)とその回避策/防止策として解説するものです。チェックリストとして使っていただいても構いません。分かっていてもアンチパターンは避けられないことも多いものです(政治とか)。そういう方には、同じ仲間は多いのだなと再認識していただくための一服の清涼剤としていただければと思います。

●「門外不出のOracle現場ワザ」 小田圭二 他 著
一番最初に出た本です。結構とがった内容を扱っています。
・パフォーマンス分析の考え方(私の担当)
・性能テストや障害テストの仕方、設計の注意点(主に私が執筆)
・コストベースオプティマイザ(10gベース)のアーキテクチャ
・コネクションプーリング
最新のOracleの内容は含んでいませんが、今でも性能の考え方やオプティマイザの考え方は使えるはずです。オプティマイザをここまで解説している本を私は知りません。

●「続・門外不出のOracle現場ワザ」 小田圭二 他 著
「続」の名前の通り、次に出た本です。ちょっと尖り過ぎたかもしれません^^; でも本当に使う内容を選んだつもりです。一流になりたい・他の人と差をつけたい人にお勧めでしょうか。
・性能の良いSQLの書き方
・文字化けの仕組み
・障害(特に性能やハング)の分析・対応方法(私が執筆)
・障害をリアルタイムに分析・対処する方法(私が執筆)
・オプティマイザの使い方ノウハウ
・アップグレードのノウハウ

●「データベース」小田圭二 他 著
私にしては堅い本です。なんせ、共同執筆者が大御所の國友義久先生です。階層型DBMS、ネットワーク型DBMS、リレーショナル型DBMS、XMLDBMS、OO(オブジェクト指向)DBMS、DBMSの持つ機能、DBMSのセキュリティ、データベースの著作権、監査、モデリング、正規化といった内容を網羅しており、深い記述は無いものの、DB技術全体を抑えるのに向いている一冊です。ある程度技術力がついたエンジニアの方が、DB全体を振り返りたい(勉強したい)というときの最初の1冊としてお勧めです。



上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。