株式会社インサイトテクノロジー 発行
http://www.insight-tec.com
┏┏┏┏┏━━━━━━━━━━━━━━━━━━━━━━━━…・・ ┏━
┏┏┏┏┛ 2008.02.13 ┏┛┛
┏┏┏┛ ☆おら！オラ！Oracle -どっぷり検証生活-★ ┏┛┛┛
┏┏┛ ┏┛┛┛┛
┏┛・・…━━━━━━━━━━━━━━━━Vol.372━…・・┏┛┛┛┛┛
◆┓━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┗◆ ２月２６日(火)「Insight World 2008 in 東京」開催決定！！ ┃
┃ 特別ゲストを迎え、ユーザ事例やコア技術をご紹介いたします。 ┃
┃ ★基調講演：元F1レーサーの片山右京氏 ┃
┃ ★Insight Night：究極のQUEENトリビュートバンド KWEEN Live開催 ┃
┃ ┃
┃ ≪参加費無料≫会場：グランドプリンスホテル赤坂 ┃
┃ http://www.insight-tec.com/news/seminar/iw080226.html ◆┓
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┗◆
◇目次◇
■Oracle検証生活・・Oracle 11g検証 新機能：SPMって何？ その１
■お知らせ・・・○ご質問について
■編集者より
◎バックナンバーココから！！
http://www.insight-tec.com/mailmagazine/ora3/mail_back_index.html
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 Oracle 検証生活 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
＜Oracle 11g検証 第3弾 新機能：SPMって何？ その１＞
ペンネーム： クリープ
先週に引き続き、今週も11gを検証していきます。
今週からは、11gの新機能にフォーカスして検証していきます。
今回、筆者が注目したのは、SPMという機能。
SPM？何それ？11gの新機能の中にそんな機能あったっけ？
というぐらい地味な機能ですが、これが意外と。。。！？
っと、詳細については検証で明らかにしていきますのでお楽しみに。
■■■■■概要■■■■■
１）SPMとは？
２）SPMを使ってみよう！
■環境
RedHatLinux ES4 Update 5
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
■テストテーブル作成
SQL> create table test_spm(seq_no number(10,0), flg number(10,0));
SQL> BEGIN
2 FOR i IN 1..10000 LOOP
3 insert into test_spm values(i,0);
4 commit;
5 END LOOP;
6 END;
7 /
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(
3 ownname => 'TEST'
4 ,tabname => 'TEST_SPM'
5 );
6 END;
7 /
１）SPMとは？
さて、今回とりあげたSPMという言葉を初めて聞いた方も多いと思います。
SPMとは、SQL Plan Managementの略で、SQLの実行計画を記録してそれを評価
した後に本番環境に適用することができる機能のことです。
簡単に言うと、実行計画がかわった時に、パフォーマンスがよくなるかを確
認して、よくなるものだけを適用することができるという機能です。
統計情報取得後、突然パフォーマンスが劣化した、などという話はよく聞きま
す。これは、統計情報の取得により、実行計画が改悪されてしまったことに
起因しています。１度でもこのような経験をしてしまうと、次に統計情報を取
得することを敬遠したり、取得自体をやめたりしてしまいます。
つまり、統計情報を取得する作業は、環境によっては非常にリスクの高い作業
といえます。
今回、11gの新機能特集でSPMを取り上げたのは、この統計情報の取得によるパ
フォーマンス劣化を防ぐことができると考え、それを検証してみようと考えた
為です。
前置きはこれぐらいにして、早速SPMを使ってみましょう。
２）SPMを使ってみよう！
っと、実際にSPMを使ってみる前に、SPMを使用する場合の処理の流れを抑えて
おきましょう。
１．実行計画を取得
２．取得した実行計画とは異なるものを保留
３．保留された実行計画を検証し、承認/拒否を判断
今回は、1つのSELECT文がフルスキャンとインデックススキャンになるように
実行して、その時の挙動を確認します。
では、上記流れを一つずつ見てきます。
１．実行計画を取得
まずは、実行計画を取得して、SYSAUX表領域のSPMのリポジトリ（SQL Management
Base）に実行計画を格納します。
実行計画の取得方法は色々ありますが、今回は自動取得モードをオンにして、
自動的に実行計画をサンプリングする方法を試してみます。
自動取得モードは、初期化パラメータのOPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
で変更することができます。デフォルトでは、FALSEになってます。
SQL> alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;
System altered.
SQL> show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
NAME TYPE VALUE
------------------------------------ --------- --------
optimizer_capture_sql_plan_baselines boolean TRUE
これで、実行計画の自動取得モードがONになりました。
これ以降に実行されたSQL文の実行計画は自動的にSQL Management Baseに格納
されるようになります。
また、SQL Management Baseに格納された実行計画を使用する為には、
optimizer_use_sql_plan_baselinesがTRUEである必要があります。
デフォルトでは、optimizer_use_sql_plan_baselinesはTRUEです。
SQL> show parameter OPTIMIZER_USE_SQL_PLAN_BASELINES
NAME TYPE VALUE
------------------------------------ --------- --------
optimizer_use_sql_plan_baselines boolean TRUE
それでは、実際にSQL文を実行してみます。
今回は、seq_noを条件にtest_spmテーブルをSELECTするSQL文を使用します。
SQL> set autotrace trace
SQL> select * from test_spm where seq_no = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1145642998
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_SPM | 1 | 5 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SEQ_NO"=1)
使用したSELECT文の結果は1/10000件ですが、インデックスがないため、フル
スキャンで実行されています。では、この実行計画がちゃんとSQL Management
Baseに格納されているか確認してみましょう。
dba_sql_plan_baselinesを使用して確認することができます。
SQL> set autotrace off
SQL> SELECT sql_text,sql_handle FROM dba_sql_plan_baselines;
no rows selected
自動取得モードをオンにしたのにサンプリングされていませんでした。
実は、自動取得モードは、2回以上実行されたSQL文のみSQL Management Base
に格納しています。これは、1回しか実行されないSQL文の実行計画を保存して
もあまり意味がないということと、仮に全てサンプリングしたとしたら、大量
のSQL文を格納しなければならなくなるからでしょう。
ちなみに、共有プールでも、同じような仕様にしてくれたら断片化の可能性も
低くなるのに。。。
と、余談はさておき、先のSQL文をもう一度実行してみましょう。
SQL> set autotrace trace
SQL> select * from test_spm where seq_no = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1145642998
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_SPM | 1 | 5 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SEQ_NO"=1)
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_a52a4eafeb1890ae" used for this statement
Noteの記載に「このSQL文はSYS_SQL_PLAN_a52a4eafeb1890aeというプランを
使用した」とあるように、実行したSQL文の実行計画がSQL Management Base
に格納され、それを使用したことがわかります。
この、SYS_SQL_PLAN...は、プラン名のことで、実行計画に対してユニークに
作成されます。
このプラン名を条件に、dba_sql_plan_baselinesで確認してみましょう。
SQL> set autotrace off
SQL> SELECT sql_text,sql_handle
2 FROM dba_sql_plan_baselines
3 WHERE plan_name = 'SYS_SQL_PLAN_a52a4eafeb1890ae';
SQL_TEXT SQL_HANDLE
---------------------------------------- -------------------------
select * from test_spm where seq_no = 1 SYS_SQL_5c11383aa52a4eaf
今度は、ちゃんと格納されていました。
抽出したSQL_HANDLEとは、SQL文に対するユニークな番号のことをさします。
つまり、1つのSQL_HANDLEに対して複数のPLAN_NAMEが作成されることがあり
ます。
ちなみに、dba_sql_plan_baselinesをと同じような情報はdbms_xplan.display_
sql_plan_baselineでも参照することができます。
このパッケージを実行すると、実行計画付きで表示されます。
SQL> select * from table(
2 dbms_xplan.display_sql_plan_baseline(
3 plan_name=>'SYS_SQL_PLAN_a52a4eafeb1890ae',
4 format=>'basic'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
---------------------------------------------------------------------
SQL handle: SYS_SQL_5c11383aa52a4eaf
SQL text: select * from test_spm where seq_no = 1
---------------------------------------------------------------------
---------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_a52a4eafeb1890ae
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
---------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 1145642998
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TEST_SPM |
--------------------------------------
19 rows selected.
それでは、次にこのSELECT文をフルスキャンではなくインデックススキャンに
なるように変更してみることにします。
。。。っと、今週はココまで。
来週は、今回実行したフルスキャンのSELECT文をインデックススキャンで実行
するところから再開します。
それではまた来週！
SPMとは... 社内でパッション蔓延中！？（パッション、パッション）
恵比寿にて
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 ご質問について 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
＜皆様からのご質問を受付けております＞
皆様のご質問にはできるだけ、お答えしたいと思っています。
すべてのご質問にお答えすることはできないかもしれませんが、
適宜メルマガ内でとりあげていく予定ですので、是非お気軽に下記アドレス
までお寄せください。
ご意見、ご感想などもお待ちしておりますっ！！
mailto:letter@insight-tec.co.jp
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 編集者より 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
最近、携帯ストラップをもらうことが多いんです。今までまったく付けない
か付けても１つくらいだったので、３～４つ増えただけでも違和感が・・。
ただ、さすがにそれだけ増えると１つくらいいなくなっても、まったく気づ
かないんですよねぇ。ふと見るとパーツが無くなっていることが多々。次は
何が無くなるんだろうと思いつつも、取り扱いに注意する気配のない自分に
相変わらずと納得。 by TI
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
登録・解除は以下のURLで行うことができます。
http://www.insight-tec.com/mailmagazine/ora3/mail_back_index.html
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜おら！オラ！Oracle－どっぷり検証生活－＞
発行／編集：株式会社インサイトテクノロジー
http://www.insight-tec.com
mailto:letter@insight-tec.co.jp
マガジンID：0000030093
本メールマガジンに掲載された記事を許可なく転載することを禁じます。
Copyright(c) 1995-2008, Insight Technology, Inc., All Rights Reserved.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
