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

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

ホーム > アーカイブ - 2010年07月

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)

エンジニアは失敗してもいいと思います

最近、失敗しないことばかり求められますよね。また、新卒やまだ経験が少ない若手を見ていると、失敗することを極端に恐れているように見えます。たしかに、自分の若手のころを思い出すと、怖かったし、ダメダメだったなと思います。

「成功ばかりする=学ぶことはほとんどない」とも言えます。振り返っても、一番学んだのは苦労したときです。心配症の人は、まず研修を受けて用意万端に準備しようとします(皆さんもそうですか?)。しかし、座学だけでは、本物の技術は身につかないと思います(もちろん、座学も重要ですよ)。歳をとった今から思うと、座学は土台を作るだけであって、失敗することから本物を学ぶしかないと思うのです。失敗して周りに迷惑かける・・・だけでは駄目なので、失敗する(マイナス)に加え、努力して何か貢献する(プラス)をつくるのも重要です。トータルで、かなりのプラスであれば、多少の失敗は許容範囲だと思うのです。一部の日本企業では難しいかもしれませんが。

● どうやって一流の知識を手に入れるか?

スキルやノウハウを持っていない人からすると、一流の知識を持っていないと、一流のプロジェクトにアサインされない、一流のプロジェクトでないと一流の知識を手に入れることはできない、というジレンマがあるかと思います。

ではどうやって一流の知識を手に入れるのかというと、最初のうちは、力不足でも努力でカバーするしかないと思います。まずはある分野で一流になるべく努力する。失敗してマイナスを作っても、別の機会でプラスを作る。最初のうちは売りはないでしょうから、売りは努力しかないと思います。そのうち、ある分野で一流(もしくは一人前)になります。そうすると、一流のプロジェクトに入れるようになります。そうすればしめたものです。仕事の中で、他の一流の知識を手に入れることができます。

失敗しても、努力でカバー。それを繰り返して、一流になるんじゃないかと思っています。若手のエンジニアの皆さん、失敗してもいいから、がんばってみてください。
[ 2010/07/11 02:46 ] スキル強化・教育 | 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冊としてお勧めです。