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

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

ホーム > アーカイブ - 2008年10月

スポンサーサイト

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

私が使うSQL文の捕まえ方&実行計画取得方法(後編)

前回に引き続いてSQL文の捕まえ方&実行計画取得方法です。
その0から3は、前編に載せています。

●その4 statspackから捕まえる
 注:snapのレベルは6以上がお勧めですからね!
   例:execute statspack.snap(i_snap_level=>6);

 この方法は、過去のある時点のSQLの実行計画を確認するために
 便利な方法です。
「昨日遅かった」とか、「前土曜日のバッチが
 遅かった」といった調査において力を発揮します。

 statspackやAWR(有償オプション)によって、過去のある時期の
 DB全体の稼動状況を確認できます。これはよく知られているかと
 思います。
 そのレポート上で(たしか9i以降から)SQLについても負荷が
 高いものなどを確認することができます。

 さらに個別のSQLについての詳細情報も各SQLごとに出力させる
 ことができます。例えば、statspackであれば、SQL*Plus上で、
 @?/rdbms/admin/sprepsql と入力し、
 表示されるガイドに従って、SQLのレポートを出力します。
 snapのレベルがレベル6以上であれば、”当時”の実行計画が
 見られるはずです。

 なお、statspackでは、情報取得の閾値が設けられており、
 デフォルトのままだと、軽いSQLは記録されません。
 テストなどにおいて、できるだけ多くのSQLを記録したい場合には
 snapのオプションで閾値の変更をしましょう。

 方法はマニュアルで確認してください。
 なお、記録されるSQLの数が増える代わりに、情報取得が重くなります。

●その5 SQL*Plusで実行して捕まえる

 開発などにおいては、いくつものSQLをSQL*Plusで実行しながら、
 逐次実行計画を確認することもあります。また、本番で流れている
 SQLが判明したとして、それを手軽に試してみたいこともあるでしょう。
 ヒント句を追加したSQLの実行計画を手軽に確認したいこともあるでしょう。

 SQL*PLUSを2つ立ち上げます。片方を「SQL実行用」として、もう片方を「SQL分析用」として使用します。

手順概要は次のとおりです。
・1 SQL実行側のSQL*PLUSで「SQL実行側 その1」を実行して、セッションIDを取得
・2 SQL分析側のSQL*PLUSで「SQL分析側 その1」を実行して、分析の準備をする
・3 SQL実行側のSQL*PLUSで「SQL実行側 その2」の形式で、実際のSQL文を実行する
・4 SQL分析側のSQL*PLUSで「SQL分析側 その2」と「SQL分析側 その3」を実行して、
   情報を確認する

※手順1と2は初回のみです。2回目以降は、手順3と4を繰り返すだけです。

・SQL実行側 その1
まず、事前準備としてセッションのIDを調べておきます。
SELECT SYS_CONTEXT('USERENV','SESSIONID') AUDSID from dual;

・SQL実行側 その2
セッションのIDを調べた方のSQL*PlusでSQLを実行します。バインド変数を
用いたSQLの場合でも、次のようにバインド変数を指定することができます。
variable a number
execute :a := xxxxxxx
実行したいSQL文を実行

・SQL分析側 その1
分析側のSQL*Plus上で、さきほど調べたセッションのIDを変数にセットします。
variable v_AUDSID number
execute :v_AUDSID := &AUDSID

・SQL分析側 その2
さきほど実行した分析対象SQLの実行計画を表示させます。
select PREV_HASH_VALUE HASH_VALUE, PREV_SQL_ADDR SQL_ADDR from v$session
where AUDSID = :v_AUDSID;
column id format 999 newline
column operation format a20
column options format a15
column object_name format a22 trunc
column optimizer format a3 trunc
select id
, lpad (' ', depth) || operation operation
, options
, object_name
, optimizer
, cost
from v$sql_plan, v$session
where hash_value = PREV_HASH_VALUE
and address = PREV_SQL_ADDR
and AUDSID = :v_AUDSID
start with id = 0
connect by
(prior id = parent_id
and prior hash_value = hash_value
and prior child_number = child_number
)
order siblings by id, position;

・SQL分析側 その3
さきほど実行した分析対象SQLの各種統計情報を表示します。
select elapsed_time/executions/1000000 "elapsed_time(sec)",
cpu_time/executions/1000000 "cpu_time(sec)",
rows_processed/executions "rows_processed(rows)",
buffer_gets/executions "buffer_gets(blocks)",
disk_reads/executions "disk_reads(blocks)",
sorts/executions "sorts(times)",
first_load_time, executions
from v$sqlarea, v$session
where hash_value = PREV_HASH_VALUE
and address = PREV_SQL_ADDR
and AUDSID = :v_AUDSID;

SQLトレースを使用しなくても、このように手軽に確認することができます。

●その6 SQLトレースから確認

メジャーな方法であるSQLトレースです。そのため、方法は割愛します。注意しなければいけないのが、9i以降ではrow source operationと表示されている部分を確認することです。execution planは当時の実際の実行計画ではないかもしれないためです。この注意点は絶対忘れないようにしてください。

●その7 v$sessionとStatspackなどの併せワザ

最後が、過去のトラブルをもっとも良く分析できる(と私が思う)方法です。

ご存知の方もいらっしゃるかと思いますが、私はv$sessionを数秒おきに実行して、アクティブなセッションの情報を残しておく方法をお勧めしています(ミッションクリティカルシステムでは)。v$sessionのHASH_VALUEやSQL_IDやOLD_HASH_VALUE、ADDRESSなども記録します。

v$sessionを連続して記録することにより、どのアプリが、何時何分何秒に、どんな理由で、どのSQLが遅延したのかが、かなりの確率で判ります。ただし、v$sessionでは当時のSQLの情報が判りません。そこで、定期的に取得している「レベル6以上のstatspack」の登場です。v$sessionの情報から、SQLを特定するIDを調べ、上記sprepsqlで調べます。

これで、かなりの情報が得られるはずです。statspackのみでは、ある期間のレポートとなるため、SQLを特定できないことがあります。また、TOPいくつかのSQLしかレポートに表示されません。実は、statspackレポートに表示されていなくても、SQLの情報はDBに記録されていることは多いのです。そういったSQLを特定するためにもv$sessionを活用し、SQLを特定できたあとは、sprepsqlで情報を表示するというコンビネーションがとても有効です。

●おまけ

statspackを定期的にとっていると、情報がDBにどんどん溜まります。sppurge.sqlを使って、負荷の低く、かつ、スナップを取っていない時間に定期的に削除しましょう。削除処理は重いためです。

削除しても、stats$sqltext などの周辺テーブルの情報が増えていく場合、10g以降では、i_extended_purge パラメータを TRUE に設定して削除しましょう。9i以前では、サポートに問い合わせ(もしくはKROWN情報から調査)してみましょう。

長い時間(6秒以上)かかったSQL文を捕まえるビューv$session_longops や、v$sql_workarea という大きな作業領域(メモリなど)を使用したSQLを調査できるビューもあります。興味のある方は調べてみてください。

●告知させてください。

つい先日発売になったDBマガジン12月号の特集2を書きました。「14のアンチパターンに学ぶ データベースの開発/運用」です。軽い気持ちで読める、よくあるダメなトラブルを紹介した20ページあまりの記事です。データが多いフルスキャンのダメさ、インデックスが多い場合のトラブルはどうなるか、正規化をしないトラブルはどうなるか、などが手軽に読めます。

書き手は、意外と読み手がどう思ったのか気にしているものですが、実は私も同じです。もしよかったら、読者はがきが、ブログにコメントください。続きを書くかどうかなどの参考にさせてもらいたいと思っています。
スポンサーサイト
[ 2008/10/27 00:39 ] DBA | TB(0) | CM(0)

私が使うSQL文の捕まえ方&実行計画取得方法(前編)

※いただいたコメントを採用させていただきました。「 and a.sql_child_number = b.child_number」を追加しています。


今回は、私がよく使う、実行されたSQL文の捕まえ方、およびSQL文の実行計画取得方法をご紹介します。長いので前編と後編に分けます。今回は前編です。

まず、「v$sql_planから実行計画を表示する」方法を簡単に紹介し、その後、「実行されたSQL文の捕まえ方」を説明します。

●その0 v$sql_planから実行計画を表示する。
どの方法でも大体使える、v$ビューからの実行計画の表示方法です。

V$SQL_PLANの使い方(ハッシュ値とアドレスを指定するバージョン※)
※ハッシュ値とアドレスの解説は、あとで出てきます。
column id format 999 newline
column operation format a20
column options format a15
column object_name format a22 trunc
column optimizer format a3 trunc
select id
, lpad (' ', depth) || operation operation
, options
, object_name
, optimizer
, cost
from v$sql_plan
where hash_value = &hash_value
and address = '&address'
start with id = 0
connect by
(prior id = parent_id
and prior hash_value = hash_value
and prior child_number = child_number
)
order siblings by id, position;


V$SQL_PLANの使い方(SQL_IDを指定するバージョン※)
※SQL_IDの解説は、あとで出てきます。
column id format 999 newline
column operation format a20
column options format a15
column object_name format a22 trunc
column optimizer format a3 trunc
select id
, lpad (' ', depth) || operation operation
, options
, object_name
, optimizer
, cost
from v$sql_plan
where sql_id = '&sql_id'
start with id = 0
connect by
(prior id = parent_id
and prior sql_id = sql_id
and prior child_number = child_number
)
order siblings by id, position;



●その1 SQL文のテキストを指定して、v$sqlareaから捕まえる

この方法は、実行されたSQL文のテキストを知っている場合に使います。

"select 1 from test"が実行されたと仮定して、10g以降の場合は次のように入力します。
select SQL_ID from v$sqlarea where sql_text = 'select 1 from test';
9i以前の場合は、
select <ハッシュバリューなど(下記)> from v$sqlarea where sql_text = 'select 1 from test';
と入力します。

v$sqlareaなどで出てくる下記5つの列は、SQLを識別するためのものですが、各々意味があります。
SQL_ID ・・・比較的最近導入されたSQLを一意に識別するIDです。ハッシュ値よりも良いと思います。
HASH_VALUE ・・・古くから使われているIDです。10gから計算方法が変わりました。9iまででは、このHASH_VALUEを使って、statspackと突合せできます(10g以降は、HASH_VALUEではstatspackと突合せできません)。v$sqlareaなどでは、address列と併せてSQLを特定します。
OLD_HASH_VALUE   ・・・10gからできました。10g以降ではstatspackと突合せするときは、このOLD_HASH_VALUEを使用します。ハッシュ値のアルゴリズムが9iまでと同等であるため、OLDと呼ばれています。
PLAN_HASH_VALUE  ・・・実行計画のハッシュ値(ID)です。同じSQL文でも実行計画が異なることがあるため、このハッシュ値が必要なことがあります。
ADDRESS  ・・・HASH_VALUEと併せてSQLを特定します。

このあとは、「その0」で紹介した方法で、実行計画を表示します。

●その2 v$sessionから捕まえる。

これは今流れているSQLを(高い確率で※1)その場で捕まえるSQLです。今まさに遅延しているときなどに便利です
v$sessionのstatus列が、'ACTIVE'な場合、SQLを処理中もしくは
I/Oやロックなどを待機中がほとんどです。その条件に該当する
セッションが実行中のSQLを表示します。

ハッシュ値とアドレスを表示するバージョン
select a.sql_hash_value, a.sql_address, b.sql_fulltext
from v$session a,v$sql b
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sql_child_number = b.child_number
and a.username is not null
and a.status = 'ACTIVE'
order by a.username;


SQLIDを表示するバージョン(10g以降でお勧め)
select a.sql_id, b.sql_fulltext
from v$session a,v$sql b
where a.sql_id = b.sql_id
and a.sql_child_number = b.child_number
and a.username is not null
and a.status = 'ACTIVE'
order by a.username;

このあとは、「その0」で紹介した方法で、実行計画を表示します。

※1 where句にACTIVEという条件句を入れていますが、SQLの実行において、一時的にACTIVE以外のステータスになることがあります。そのため、100%ではありません。数回実行することで、ほぼ確実に捕まえることができるはずですし、私は困ったことはありません。
注:SQL文の出力が切れてしまう場合もあります。その場合はv$sqltextから見た方が早いかもしれません。

v$sqltextの例:
select sql_text
from v$sqltext
where sql_id = '&sql_id'
order by piece;

●その3 v$sqlareaから処理の重いものやI/Oの多いものを探す

過去のある程度の期間(※)に実行されたSQLの中から、
”悪玉”と私が呼んでいる、処理が重いSQLを抜き出す方法です。
少し前に実行されたSQL群の中から、重いものを抜き出せるため便利です。

※期間は、Oracleの共有プールから消えてしまうまでです。
 共有プールのサイズが大きければしばらく残っているでしょう(数日でも)。
 なお、インスタンス再起動、DDLやtruncate、共有プールのflushなどを
 行うと即座に消えてしまいます。このv$sqlareaに限らず、
 SQL文を表示するv$ビューは原則的に同じ動き(生存期間)です。

select sql_id,sql_text,
elapsed_time/executions/1000000 "elapsed_time(sec)",
cpu_time/executions/1000000 "cpu_time(sec)",
rows_processed/executions "rows_processed(rows)",
buffer_gets/executions "buffer_gets(blocks)",
disk_reads/executions "disk_reads(blocks)",
sorts/executions "sorts(times)",
first_load_time, executions
from v$sqlarea
where <抽出条件(下記)>


抽出条件の例:
executions != 0 and elapsed_time/executions > 1000000;
executions != 0 and disk_reads/executions > 100;

このあとは、「その0」で紹介した方法で、実行計画を表示します。

v$sqlareaには、その他にも optimizer_cost や direct_writes など
役に立つ情報が満載です。Oracleのリファレンスマニュアルで各列の意味を
調べることができるのでぜひ見てみてください。

長くなったので、今日はここら辺で。次回もSQLの捕まえ方と実行計画の表示の仕方の説明が続きます。
[ 2008/10/23 21:13 ] DBA | TB(0) | CM(4)

実行計画のTIPS

今回は実行計画のTIPSです。統計情報のノウハウ(過去3~4回くらいまで)も併せてごらんください。

●効率よくチューニングする/問題を見つける

SQLをチューニング/問題を見つけるときは、コスト(※)が上がっている箇所を見ると、初心者はあたりをつけやすいです。コストが大きく上がっているところは、Oracleが「重い」と予測しているポイントだからです。

※:コストをstatspackから表示するのは2回前の記事参照

●バインドピーク※ その1

※:バインドピークとは、実行計画を作るにあたって、バインド変数(:aとか:b)の値をピーク(peek:覗く)ことです。バインド変数にセットされた値(1とか'a'とか)を参考にして実行計画を立てます。この機能はデフォルトでONです。

バインドピークによるトラブルが起こりやすいのは選択率です。選択率とは、表の中のどれくらいのデータが選ばれるのか?です。そのため範囲検索を使ったSQLでよくトラブルが起こります。範囲検索とは、< や > や betweenといった条件句のことです。もともと、統計情報を取得すると、その列のデータの最高値と最低値がとられます。たとえば1から始まる主キーで、最大値が1000であれば、その列の最高値は1000で最低値は1となります。ヒストグラムを使用しない場合、分布は均一と想定しますから、between 1 and 50だと、ざっと1/20が選ばれると考えます。ぱっと考えると、インデックスが効率的に見えます。しかし、実際のデータでは、1から50までに99%のデータが含まれていることもあります。すると、実際はフルスキャンが効率的かもしれません。

対策としては、ヒストグラムの使用も考えられますが、バインドピークを止めるのも手です。実は、バインドピークを止める(止める件は3回ほど前に書いてます)と、ある一定の選択率で計算するため、実行計画の変動を抑制できるからです。

●バインドピーク その2

バインドピークとパーティション機能の組み合わせも気をつけましょう。パーティション機能とは、表の内部を区切って、データメンテナンスしやすくしたり、競合を回避する機能のことです。
個々のパーティション単位で見ると、データが0件からスタートして膨大な件数になることもよくあります。例えば、1日ごとにパーティションにしている場合、毎日違うパーティションにデータを入れることになるでしょう。その場合、朝(データが少ない)ときは高速で、夜になる(データが多いと)低速ということが起こりえます。朝の段階で実行計画を立てて(例:フルスキャン)、それをそのまま使ってしまったりするからです。

これは統計情報を常に「データ件数が多い時点の統計情報」に固定しておくと防げたりします。また、バインドピークを止めると、デフォルトの一定の選択率となったりするため、実行計画の変動を防げることが多いです。

●バインドピーク その3

たとえば、1から100のデータはパーティション1、101から200まではパーティション2といった具合に、表の内部に複数のパーティションを作るとします。

そのパーティションを履歴データに使うことを考えます。最初のパーティションは、100まで入れるとします。100まで入ったので、次は2番目のパーティションに入れはじめました。このタイミングでselectの実行計画を立てるとします。その場合、表全体のデータが多くても、フルスキャンが選ばれるかもしれません。理由は、2番目のパーティションでデータ件数などを評価してしまうケースがあるからです。バインドピークにより、「どのパーティションか」ということも判断できます。そのため、このような実行計画が選ばれやすくなります。

フルスキャンという実行計画を立てたあと、そのselect文と同じSQLが1番目のパーティションにアクセスすると悲劇かもしれません。2番目のパーティションと違って、データ量が多いからです。このように、バインドピークとパーティション機能の組み合わせは統計情報の運用に気をつけた方がよいです。

これも統計情報をデータ件数が多い時点のものに固定しておくと防げたりします。バインドピークを止めると、パーティションの選択が行われなくなったり、一定の選択率となるため、この方法も効果があったりします。

●バインドピークなどによるexplain planの不確実さ

実行計画を表示させるためのコマンドとして有名なのが、explain planです。
explain plan for SQL文という簡単なコマンドで実行計画が表示されるため、愛用者は多いと思います。
しかし、explain planは、静的な情報のみで実行計画を表示します。そのため、バインド変数の値によって生成されるSQLが異なるような場合にはつかえません。たまたま初回のバインド変数の値がフルスキャン向きだったというケースの分析には向かないのです。つまり、当時の実行計画はどうだったかという分析にexplain planを使用すると、正しくないケースがあるのです。

explain planで表示すべきではないとすると、どうすべきかはこの次に。

[ 2008/10/20 22:22 ] DBA | TB(0) | CM(0)

Oracle SQL Developer Data Modeling

佐藤さんがトラックバックしてくれたので、SQL Developerのモデリング機能も書いちゃいます。

佐藤さんの記事のとおり、ERモデリング機能を持つSQL Developerのアーリーアクセスが出てきました。まだ製品版ではありませんが、ダウンロードして使えます。ダウンロードなどのURLはこちらです。
http://www.oracle.com/technology/products/database/sql_developer/files/Modeling.html

ダウンロードしようとしてアクセスするとアンケートを求められます。記入して進むとダウンロードできます。ダウンロードして解凍するだけで使えます。特にインストールは無かったです。Oracle9以上に接続できるそうです。

「バグを報告してくれ(英語)」と表記されています。まだ安定してはいないようですね。

以前、私はいくつかのモデリングツールを触っていたせいか、それほど違和感は感じない操作性だと思います。データをDBからimport直後はこんな感じです。

DBからインポート直後


ドメインの定義もできますね。メニューを見る限り、他社DBMSともつなげられるようです。

テーブルを作成し、リレーションをはってみます(リレーションをはる前後2枚の画像です)。

リレーション前

リレーション後


DDLも生成できます。こんな感じです。

DDL.jpg


SQLの構文を覚える必要もありませんし便利です。私は使ってみようと思います。みなさんもどうですか? Early Accessが終了し、SQL DeveloperのData Modelingは正式版・有償になりました。もしよかったら使ってみてください。
今日は2つ書いたので、1週間後にまた書きます。次はまた実行計画のTIPSです。

#有償になったため、文章の一部を修正(2010/1/27)
[ 2008/10/12 23:00 ] DBA | TB(0) | CM(0)

統計情報のノウハウ(考え方)

実行計画ってなぜ最適にならないのか? について書いてみたいと思います。

●「実行計画のトラブル」とは?

「実行計画のトラブル」を「最適ではない(=より良い実行計画が存在する)」だと定義します。このトラブルはいろいろな理由により起こります。

実行計画とは、データの処理の仕方です。処理の仕方は何通りもあるため、最近のOracleでは、コストベースという考え方で、処理時間の指標(コスト)を計算して、「この実行計画が最短だ!」と推測します。このとき使用する情報を「統計情報」と呼びます。

なお、9iまではI/O回数ベースで判断していましたが、10g以降ではSQL処理時間の目安になりました。

推測の仕方ですが、基本的に、全ての組み合わせを調査します(※)。組み合わせには、表の結合の仕方や、どの列の条件で絞るか、インデックスでアクセスするかフルスキャンするかなどが含まれます。

※:各社のRDBMSごとに工夫が見られますが、ここは割愛します。

1つのテーブルしか含まないSQLでは、数~数十パターン程度の処理方法でしょうが、5つともなると数千という組み合わせになりかねません。いわゆる組み合わせ爆発です。

●コストとは

コスト(処理負荷の予測)は、実行計画でCOSTの列を見るとわかります。
COSTの数値が大きければ、それは大変だろうということです。たとえば、statspackのsprepsqlではこのように見えます(右の列)。COSTの数値が大きくなっているところが重い処理だとOracleが考えていることになります。


--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 1093152308 ----| | | 5 |
|HASH JOIN | | 1 | 36 | 5 |
| TABLE ACCESS FULL |DEPT | 1 | 22 | 2 |
| TABLE ACCESS FULL |EMP | 5 | 70 | 2 |




●所詮は予測に過ぎません。

実は、わざと「予測(推測)」と書いてきました。「予測」というのは、当たらないことがあるからです。よく言われるのが、予測ではなくて、断定する方法があるだろう?という疑問です。私も考えてみたのですが、例えば次のようなケース(反証)がありえます。

テーブルAとテーブルBを結合するSQLを考えます。さて、テーブルAに条件句がついています。where no = 1です。AとBの結合条件は、 A.no = B.noだとします。Bの表は何行選ばれると考えるのが妥当でしょうか?
通常、1行と答えるでしょう。しかし、場合によっては全行のこともありえます。これは検索してみるまで(AからBに辿ってみるまで)わかりません。このように表に関する統計情報をいくら集めてみても、実際に検索されるデータ量や最適な検索方法までは断定できないのです。「Aのこのデータを検索するとBではこれくらい選ばれる」という統計情報は量を考えると非現実的です。このように、「やってみるまでわからない」というのが、現実です。

●メジャーな理由

いくつか実行計画が悪くなる理由を考えてみます。

理由1:統計情報が無い or 古い
統計上が古い場合、実データの状況とあっておらず、効率の悪い計画を選ぶことが考えられます。表に10行程度しか入っていない状況で、統計情報を固定してしまうとフルスキャンをしやすいでしょう。その統計情報のまま、実データが100万行だと大変なことを起こすかもしれません。統計情報の固定も考え物です。

理由2:実行計画を作ったのが古い
SQLの解析作業(実行計画をつくる作業)は、CPUを大量に消費します。そのため、一度作った実行計画を再利用するのが一般的です。再利用により解析作業は減りますが、やはり、実データとの乖離が発生して、最速ではない実行計画をそのまま使い続けてしまうかもしれません。

理由3:実行計画の候補が多すぎて、途中であきらめた
表の数が多い場合など、解析作業が大変なときは、DBMSは途中ではしょります。そのため、最適ではない実行計画がえらばれやすくなるはずです。

理由4:データを見るとわかりますが、非均一のデータというものがあります。
ほとんどの人がある特定の商品ばかり頼んだようなものですね。その場合、インデックスアクセスではなく、フルスキャンが効率的だったり、その逆だったりします。この対処として、ヒストグラム(データの分布)という統計情報があるのですが、SQLの書き方やパラメータによっては、ヒストグラムが使われたり、使われなかったり、いろいろと条件があります(※)。

※:ブログに書くレベルの複雑さではないので、この程度の言い方でお茶をにごします・・・

理由5:製品のバグで遅い方を速いと評価した
無いとは言えません。バグであれば、直してくれるでしょう。

参考までに、Oracle以外のDBMSでも、実行計画のトラブルは存在します。

●大事なTIPS

今回の大事なTIPSです。statspackの取得レベル(※)のデフォルトはレベル5ですが、レベル6以上でとるようにしましょう。理由は”実行計画も記録してくれるから”です。トラブルを事後調査するような場合、非常に心強い見方になってくれます。「普段はこの実行計画だけど、当時はこの実行計画だったから遅かった」という証拠を示してくれます。私は大抵レベル7にしています。

※:statspackは、情報の取得レベルにより、記録するデータの範囲を変えられます。

[ 2008/10/12 22:47 ] DBA | TB(0) | CM(0)

統計情報の履歴やロックなど

今回から数回にわたって、統計情報に関するノウハウを少しご紹介します。
まず初回は元の実行計画に戻すTIPSと実行計画を変動させないTIPSです。

●統計情報履歴

まずは、統計情報の履歴です。これは便利です。
10g以降において、SQLの実行計画が悪化した場合、まずは過去の統計情報を戻すことを考えてみてください。Oracleが自動的に過去の統計情報を約1ヶ月保持しているはずです。今日の朝からSQLの性能が悪化したのであれば、昨日の統計に戻してみると改善するかもしれません。

例:
SELECT * FROM DBA_TAB_STATS_HISTORY WHERE TABLE_NAME='TEST_FOR_STATS';

SYSTEM TEST_FOR_STATS

08-10-09 22:55:38.859000 +09:00

SYSTEM TEST_FOR_STATS

08-10-09 22:57:48.906000 +09:00

このように統計情報がバージョン管理されています。
注:analyzeでは過去統計情報が残らないため注意してください。

統計を過去に戻す方法ですが、次のように行います。

例:
exec DBMS_STATS.RESTORE_TABLE_STATS(ownname=>'SYSTEM',tabname=>'TEST_FOR_STATS',as_of_timestamp=>'08-10-09 22:56:38.859000');

なお、DBMS_STATS.RESTORE_DATABASE_STATS で、データベース全体が、
DBMS_STATS.RESTORE_SCHEMA_STATS で、スキーマ全体が戻ります。

ここら辺のプロシージャの詳細は、PL/SQL パッケージ・プロシージャマニュアルをご覧ください。日本オラクルのOTNからマニュアルは無料でダウンロードできます。

なお、9i以前のOracleの場合、DBMS_STATSで手動でバックアップをとることができます。

●統計情報のロック

次は、統計情報のロックです。性能を変えたくない場合、ロックすることで統計が変わらないようにできます。これにより実行計画はほぼ固定となります。

DBMS_STATSのLOCK_SCHEMA_STATS や LOCK_TABLE_STATS、
UNLOCK_SCHEMA_STATS や UNLOCK_TABLE_STATSを用います。

例:
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('SYSTEM', 'TEST_FOR_STATS');

注意点は、データが少ないときに固定してしまうと、Oracleはフルスキャンを選びやすいという点があげられます。つまり、データが多くなってもフルスキャンのままになって性能がどんどん劣化する場合もありえます。

●ヒストグラム(分布に関する情報)

10g以降において、統計情報の収集では、ヒストグラムも収集されえるようになりました(デフォルト値)。ヒストグラムも、実行計画が変わる要素です。9iまでと同様に取らないというのも手です。

DBMS_STATSの METHOD_OPT というパラメータがあり、これがデフォルトで'FOR ALL COLUMNS SIZE AUTO'となっています。この場合、ヒストグラムをとってしまうことがあります。

ただし、DWHなどでは、ヒストグラムがあるからこそ速くなるケースもあるため、きちんと検討した上で止めてください。OLTP系ではヒストグラムはあまり必要ないかもしれません。

● バインドピークの停止

実は、Oracleは9i以降、バインド変数の中をピーク(peek:のぞく)して、実行計画を立てる参考にしています。これが実行計画を変動させることがあります。この機能は目的があって導入されているので、いちがいに止める必要はありませんが、実行計画を変動させたくない場合には、これの停止も検討してよいでしょう。

個人的に止め方を書くことはできないので、Oracleのサポートやインターネットで検索して止め方を調べてみてください。「バインドピーク 無効」といったキーワードで出てくるはずです。

●そのほか

なお、実行計画はキャッシュされることもあるため、alter system flush shared_pool などでメモリ上のキャッシュをクリアすることも、必要に応じて実行してください。
また、統計を取得していない場合、Oracleはメモリ上に一時的な統計情報を置く(ダイナミックサンプリング)ことがあります。これも実行計画を変動させます。alter system flush shared_pool でこれも消すことができます。

今回紹介したノウハウを使いこなせば、実行計画はだいたい安定するのではないでしょうか? まだまだ統計情報のノウハウは続きます。
[ 2008/10/09 23:44 ] DBA | TB(0) | CM(0)

Oracle XEっていいですよ

意外と無償製品の話の受けがよいので、調子にのって、また無償製品の
話です。

このブログを読んでいる人はご存知かもしれませんが、
試用版ではなく、フリーに使えるOracleがあるって知ってました?
名前を エクスプレスエディション (略してXE) と呼びます。
数年前に発表されています。

下記URLに情報があり、リンクを辿ればダウンロードもできます。
http://www.oracle.com/technology/products/database/xe/index.html

Universal版を選べば、日本語データの格納もOKです!
メモリや格納データの上限はあるものの、小さなデータベースとしては充分です。機能は通常のOracleとほぼ同じですし、使用期限が切れることもありません。

主な必要要件はこんな感じです。

■Linux版
下記ディストリビューションのどれか。
Red Hat Enterprise Linux RHEL3 and RHEL4
Suse SLES-9
Fedora Core 4
Red Flag DC Server 5.0/MIRACLE LINUX V4.0/Haansoft Linux 2006 Server (Asianux 2.0 Inside)
Debian 3.1

メモリ要件:
Server component: 256 megabytes minimum, 512 megabytes recommended
Client component: 256 megabytes

ディスク要件:
Server component: Total: 1.5 gigabyte minimum
Client component: 100 megabytes

■Windows版

One of the following 32-bit Windows operating systems:
Windows 2000 Service Pack 4 or later
Windows Server 2003
Windows XP Professional Service Pack 1 or later

メモリ:
256 megabytes minimum, 512 megabytes recommended

ディスク要件:
Server component: Total: 1.6 gigabytes minimum
Client component: 75 megabytes

ダウンロードはU.S.のOTNのサイト( http://www.oracle.com/technology/software/products/database/xe/index.html )から行えます。

事前検証や個人的な検証用に最適です。
そして、インストールも楽です。
環境やライブラリに関しては多少手間がかかるものの、
実際にインストールに入ってしまえば、あれっ?って感じで
終わっちゃいます。

なお、自分のWindows PCに入れて使うのなら、普段は
Oracleのサービスを止めておくとPCが重くならなくていいですよ。

ぜひ、インストールして使い倒しましょう!!
私も検証に使ってます。

ところで、OOWもあったため、最近ブログのタイトルである、
”ノウハウちょい見せ”を、さぼっていたなと思います。
この次はノウハウに戻りたいと思います。

P.S. 1週間ほど前に出演したDBA2.0というセミナーですが、僕の
   役割は「5分で過去のトラブルの分析する」というものでした。
   僕の分析の後、EM(Enterprise Manager)が、同等の分析をしていました。
   最近、評価があがってきたEMの紹介はまた今度。
[ 2008/10/05 15:54 ] 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ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。