しばちょう先生の試して納得!DBAへの道 indexページ みなさん、こんにちは。毎月気が付くと、この連載記事の締め切りがやってきている感じがする、“しばちょう”こと柴田長(しばた つかさ)です。 オプティマイザの挙動が新バージョンで変わったことが原因、という可能性もあるわけです。 「サイコロを振りなおす」くらいの意味合いしかないのではないでしょうか。, しかも、実行中のキャッシュはクリアされないようです。 いろいろと調べてみたらOracle 11gのstatspackレポートから 遅いといわれたプログラムが発行しているselect文がテーブルをフルスキャンして遅くなっていることが判明。 実行計画のコストが100万越えとかだったw 前日までは3万~4万の間で推移してた模様。 (つまりコストが低く見積もられます。optimizer_index_cost_adjと違ってパラメータを高くするほどコストは低くなる、という仕組みなので注意), SQLの実行計画を覗いてみて、結合方法がハッシュ結合等になっていて、そこがボトルネックであるようなら効果が期待できます。, 尚、結合方法はSQL側でヒント句を使用することでもコントロールできます。 なにがなんでもインデックスを使わせたいシステムであれば効果があります。, https://docs.oracle.com/cd/E49329_01/server.121/b71292/refrn10140.htm#CHDGCCCG, これについては正直挙動をよく理解していませんが、「壊滅的に遅い」という状況下でダメもとで0にしたところ、改善しました。, https://docs.oracle.com/cd/E49329_01/server.121/b71292/refrn10145.htm#i1131532, FIRST_ROWS_n => 最初のn行を最速で返そうとする(フェッチ開始までの時間を最短にする?) 2.2.4.7 動的統計 . Microsoft Ignite 2020の振り返りも「Azure Rock Star Community Day」, you can read useful information later efficiently. ¤Æ°ì³çÅÐÏ¿¤¹¤ë, SQL *Loader ¤ò¥Ð¥Ã¥¯¥¨¥ó¥É¤Ç¼Â¹Ô¤µ¤»¤Æ°ì³çÅÐÏ¿¤¹¤ë. 4.各オブジェクトの統計情報を確認したいのですが、統計情報はどのように確認できますか? sysユーザーが所有するディクショナリ・ビューから確認することができます。 以下の統計情報が、下記のビューに格納されています。 表の統計情報 → dba_tables 本記事では、ORACLEデータベースで、SQLのINSERT文をチューニングする方法をまとめて紹介しています。, SQLの実行計画の見方や確認方法については↓で紹介していますので参考にしてください。>>【ORACLE】SQLの実行計画の見方>>【ORACLE】SQLの実行計画を取得する方法, INSERT文をチューニングする方法はある程度限られているかなと思います。いくつか代表的なものを紹介します。, ダイレクト・パス・インサートとは、バッファキャッシュへの登録を省略し、DISK上のテーブルへ即時データを挿入するINSERTの方法です。, 通常、ORACLEデータベースでINSERT文を実行すると、メモリ上のSGAという領域の中のバッファキャッシュというエリアに、書き込みが行われます。, 一度、メモリ上のバッファキャッシュに書き込みを行った後、実際にデータが登録されているDISK上のテーブルに行が挿入されます。, ただし、ダイレクト・パス・インサートという、特殊なINSERTを行うことで、バッファキャッシュへのを登録を省略して、DISK上のテーブルへ直ちにデータを書き込むことが出来ます。, バッファキャッシュに登録する時間が短縮されことで、通常のINSERT文よりも格段に実効速度が速くなります。, ダイレクト・パス・インサートはAPPENDヒントを使うことで、実装することが出来ます。具体的には次のような形でヒントを追加します。, ※VALUESを使う場合は、INSERT /*+ APPEND_VALUES*/ INTO tab1 values ~という風にAPPEND_VALUESヒントを使うようです。, ちなみに、ダイレクト・パス・インサートには、いくつかの制約もあります。最も重要な制約は、INSERTするテーブルをロックすることです。, INSERT文にnologgingを指定することで、当該INSERT文によるREDOログ(更新ログ)の書き出しを止めることが出来ます。, ダイレクト・パス・インサートと同様に実行速度は格段に速くなりますが、制約もあります。, REDOログ(更新ログ)を出力しないので、REDOログ(更新ログ)によるデータ復元などが出来なくなります。, ・nologgingを指定する方法 具体提起には、次のように、INTOの後ろにnologgingを指定します。, たまに、アプリケーションやプログラムを分割して”パラレル化する”という方がいますが、ここで紹介するのは、SQLを複数のプロセスで同時実行するという機能です。, 例えば、データベースサーバのCPUが4つあり、夜間の時間帯の1つのバッチ処理のSQLに2CPU割り当てても問題ないといった場合に、2多重でパラレル実行を行います。, パラレル実行は、セッションでパラレル実行する方法とSQL単位にヒントでパラレル実行する方法の2種類があります。, ・セッション単位 ALTER SESSION文を使って、パラレル実行するように命令できます。パラレルの多重度は割り当てられるCPUの数を計算しつつ指定してください。, ・ヒント SQL単位には、PARALLELヒントを使って多重度を指定します。さらにDML文で実行する際には、”ENABLE_PARALLEL_DML”というヒントを追記しておく必要があります。, パラレル実行は、あくまでリソースに余裕があることを事前に確認した上で実行する必要があります。, リソースが枯渇すると、同じ時間帯に実行しているプログラムやバッチ処理が遅くなるなど、問題が発生する可能性があるので、十分に注意して実行してください。, パーティション化すると、データの格納領域を対象の範囲や値ごとに分割することが出来ます。, INSERT文のチューニング方法は ・ダイレクトパスインサート ・nologging ・パラレル実行 ・テーブルのパーティション化などがあります。, なお、INSERT INTO SELECT ~のような場合、SELECTでデータを取得している時間がかかっている場合があり、SELECT文のチューニングが必要なケースもあります。, SELECT文のチューニングについては↓で紹介していますのでぜひ参考にしてください。>>SELECT文のSQLチューニング方法まとめ. パフォーマンスに影響を与えます。, このパラメータを高くすればするほど、「ネステッド・ループ結合」が採用されやすくなります。 結論的には、OracleでもSQL Serverと同じ事象が発生します。 要約すると下記が問題になります。 ・Truncateした後に統計情報を更新しないと、DMLを処理するとき、残った不適切な統計情報により、効率的によくない実行計画を生成し性能が落ちる場合がある。 technology. しかし、インデックスを使うかどうかはOracleオプティマイザのさじ加減。, Oracleのオプティマイザはインデックスを使うか、使うまいか、という判断をそれぞれの実行コストを見積もって決めています。, とOracleが見積もれば、インデックスは使われません。 統計情報が収集されていないすべての表に対して動的サンプリングを実施(2がデフォルト)。 64: 3: 統計情報が収集されていない表、または収集済でもwhere句の述語で使用される式が1つ以上ある場合に動的サンプリングを実施。 64: 4 しかし、EXACTにするとこれらがすべて別々に扱われてしまいます。 「こっちは速いけどこっちは遅い」という現象が起こりえます。, EXACTであればそのような事態は防げるのですが、少しでも違うSQLは別物として処理されてしまうため、 Help us understand the problem. sqlが遅いと”統計情報はとってあるのか?”と口癖のようにいう人いますよね。 統計情報を取るとオプティマイザーが効率的なアクセスパスを選択してくれます。 今回はどのように統計情報が保存されているのか調査していきます。 OracleのチューニングはSQLの改善が基本です。 統計情報とは簡単に説明すると表(テーブル)や索引(インデックス)、列(カラム)の値やレコード件数などの情報のことです。これらの情報が記録されたものを統計情報と呼びます。 統計情報については↓で詳しく解説していますので参考にしてください。 >>【ORACLE】テーブルやインデックスの「統計情報」とは というところがあると思うので、これで回避できるなら僥倖といえるでしょう。, https://docs.oracle.com/cd/E49329_01/server.121/b71292/refrn10143.htm#CHDJAAAG, SQLを書くときには、全件取得でもしない限り、インデックスを使ってもらうようにするのが基本です。 そのオーバーヘッドが無駄になることがあります。, このような2つのSQLであれば内部の実行計画は通常同じで問題ないはずです。 インデックスを使わない => コスト70, これで、インデックスを使ってくれるようになるわけです。 値を下げれば下げるほどインデックスを使うほうによっていきます。 そういったシステムに対して効果がありそうなチューニングポイントについて、独断と偏見と経験で評価していきます。, https://docs.oracle.com/cd/E49329_01/server.121/b71292/refrn10025.htm#i1125803, FORCE => 意味的に同じSQLであれば同じと見なしてカーソルを共有する つまり、自分自身のセッションだけFORCEになったりEXACTになったりできます。 必要なところにインデックスを張り、そのインデックスが有効活用されるようなクエリを書く。, なのですが、SQLをおいそれと変更できないシステムってありますよね。 一時表と違って統計情報があればコストベース・オプティマイザが使われるのですが、この統計情報はあてにならないことがほとんどだからです。 よって、このような一時データを格納する表についてもパフォーマンスに気をつけなければなりません。 この1000, 1001のところの値が無数にあると、それを保持するメモリが枯渇する可能性もあります。, 通常、このような場合はバインド変数などを使ってSQL側からアプローチするべきですが。。。, このパラメータのいいところは、「ALTER SESSION文」で変えられるところです。 SQL Serverだとテーブルやインデックスの断片化が進むと、オプティマイザに影響し、インデックスが使われなくなったり、SQLがパラレル処理されなくなるなどで、SQLの実行時間が極端に長くなる事象を、以前、実験結果から得ていましたが、Oracleでも同じ事象が発生し得るのか、My Oracle Supportで確認してみました。, ・Truncateした後に統計情報を更新しないと、DMLを処理するとき、残った不適切な統計情報により、効率的によくない実行計画を生成し性能が落ちる場合がある。・インデックスを作成していないテーブルでも、結合処理を行う場合は統計情報の影響を受ける。  例えば統計情報により、Nest Loopを選択するのか、Hash Joinを選択するのかを判断しますので、統計情報が不正の場合は効率がよくない結合処理を選択する可能性がある。・統計情報が乖離する問題は、Truncateだけではなく、INSERT/UPDATE/DELETEいずれでも、大量に処理した場合は起こり得る。・デフォルトでは毎晩、自動で統計情報を収集するジョブが実行されるので、統計情報の乖離が原因でSQLの実行計画が良くない選択をするのはまれ。, ・テーブルの断片化が進むと索引のクラスタ化係数は大きくなります。実行計画を生成するとき、索引を使うかどうかはクラスタ化係数も考慮しているので、断片化が進むと実行計画に影響する。 ・Oracleは自動的に断片化を解消する仕組みはない。 ・DML(INSERT/UPDATE/DELETE)を繰り返すと、セグメントレベルの断片化が進行する。・TRUNCATE TABLEを実行すると、セグメントレベルの断片化は解消するが、表レベルの断片化が起きる。・セグメントレベルの断片化が進行すると、SQL実行時の実行計画が、インデックスを使用しない、SQLをパラレル実行しない、と選択し始める。・Delete,Insertによるセグメントの断片化より、TRUNCATEによる表領域の断片化の方がスループットを低下させない。 ・特定のテーブルでセグメントの断片化が進行しても、同じ表領域にある他のテーブルには影響しない。・断片化が進行した場合、インデックスを使わないDMLでもスループットは低下する。, 関連するOracleナレッジベースのドキュメントID1720974.11751631.1. 当サイトの情報によるいかなる損失に関して、免責とさせて頂きます。ご利用の際はあらかじめご了承ください。, 当然、一貫性を担保するために定義の変更は防止されます。ただどのように防止されるのかv$lockを見ながら理解を深めていきます。, SQL Developer Excelファイルを簡単にインポート/エクスポートする方法. (当然といえば当然ですが。。。), 最終手段、荒療治です。 ダイレクト・パス・インサートとは、バッファキャッシュへの登録を省略し、DISK上のテーブルへ即時データを挿入するINSERTの方法です。 通常、ORACLEデータベースでINSERT文を実行すると、メモリ上のSGAという領域の中のバッファキャッシュというエリアに、書き込みが行われます。 一度、メモリ上のバッファキャッシュに書き込みを行った後、実際にデータが登録されているDISK上のテーブルに行が挿入されます。 ただし、ダイレクト・パス・インサートという、特殊なINSERTを行うことで、バッ … (ここでは割愛します), https://docs.oracle.com/cd/E49329_01/server.121/b71292/refrn10141.htm#CHDFABEF, オプティマイザの機能を、Oracleのどのバージョンにするか、ということを決められるパラメータです。 行う処理のタイプに合わせて切り替えながら処理をすることで高速化することが期待できます。, ALTER SESSIONの良さは、即時反映(Oracleの再起動等が不要)、システム全体に影響を与えない(影響するのは自分のセッションのみ) そういうときは、このパラメータをバージョンアップ前の値にしてやることで解決することがあります。, いつまでも前のバージョンに縛られるということでもあるので、できれば使わずに済ませたいところですが。。。, http://www.shift-the-oracle.com/alter-system/alter-system-flush-shared-pool-buffer-cache.html, 遅くなったまま固定されている実行計画のキャッシュをクリアすることで、実行計画を作り直してもらう。 実は全く別の実行計画が最適なはずのSQLが十把一絡げで処理されてしまう可能性があるため、 通常、インストールされているOracleのバージョンのオプティマイザが使われるわけですが、それを変更することで、 経験則だと、極端な話「1」つまりインデックスコスト1/100でも、 こちらは皆さんも一度は耳にしたことがあるかもしれません。 索引の数が多ければ多いほどインサートが遅くなるといったものです。 こちらについて解説する前に先ず、そもそものインサートの動作に触れていきます。 インサートの動作としてテーブルに対してレコードを追加すると同時に、 索引の更新も行っています。また、索引の更新は表に紐づくすべての索引に対して行われます。 順を追って見てみると、 1.インサートを発行する。 2.テーブルにレコードが追加される。 3.テーブルに紐づく索引すべて … EXACT => 一言一句変わらないSQLのみを同じと見なしてカーソルを共有する, 「意味的に同じ」というところがアヤシイポイントです。 というエントリを書いたのですが、それとは別に insert の処理をどうしても高速化する必要に迫られました。今回高速化すべき要件として同一テーブルに大量のデータを一気に登録する作業です。 必ずしも正確性・信頼性等を保証するものではありません。 By following users and tags, you can catch up information on technical fields that you are interested in as a whole, By "stocking" the articles you like, you can search right away. sql文のコンパイル中に、オプティマイザが、適切な実行計画を生成するために既存の統計で十分かどうかを検討して、動的統計を使用するかどうかを決定します。 既存の統計が十分でない場合は、動的統計が使用されます。 ALL_ROWS => 全行取得を最速で返そうとする, なんとなくですが、FIRST_ROWS_nだと少ない件数取得のための最適化なので、インデックスが使われやすくなるような気がします。 Oracleのオプティマイザはテーブルの統計情報を元に実行計画作成のためのコスト計算をしているわけですが、 ところが、このパラメータを50にした場合、以下のようになります。, インデックスを使う => コスト100 * 50% = 50 久々に Oracle ネタです。以前 セッション管理に向いているデータベースは MySQL ? インストールされているバージョンとは別のバージョンのオプティマイザ機能を使うことができます。, これが特に効果を発揮するのはOracleをバージョンアップするケースかと思います。, SQLやパラメータ等何も変えていないのにバージョンアップしたら遅くなった、というようなときは、 ALL_ROWSは全行なので、フルスキャンでもいいか、という。, https://docs.oracle.com/cd/E49329_01/server.121/b71292/refrn10142.htm#i1131445, インデックスを使う/使わないはもちろんですが、テーブル結合時の結合方法というのも、 統計情報を消す. What is going on with this article? (このあたりは完全に想像です) その情報ソースを断ってしまう、というOracleオプティマイザに見切りをつける運用です。, 統計情報がなくなることで、「無難な」実行計画になることが期待できます。 「たまたま」実行計画が遅かった場合は効果が期待できるが、 Why not register and get more from Qiita? この表には1万のレコードがあります。ただし、EMPLOYEE_IDの値は10000が99件あり99999が9901件あります。つまり偏りが大きい状態にあります。, まずはBS_USER表の統計情報を取得します。今回は分かりやすいようにサンプリング率を100%でとります。, 以下のようなカラムに値がセットされていました。この情報でテーブルの件数がわかります。一度も統計を取らないと、これらのカラムはnullになっています。, 今回は、サンプリング率が100なのでNUM_ROWSが10000ですが、サンプリング率が下がればこの値は、不正確な値になります。, このディクショナリにも統計情報が格納されていますがOracle7時代の互換のためにあるものです。, GATHER_TABLE_STATSは、デフォルトでテーブルに紐付いたindexの統計も取得してくれます。, このディクショナリを見ると各カラムの偏り度が分かります。1万件あるテーブルでUSER_IDはNUM_DISTINCTが10000ということは値はすべて一意です。逆にEMPLOYEE_IDは2なので2種類の値しかありません。, このディクショナリを見ると各値の偏り度を見ることができます。ヒストグラムの情報があることで、件数が少ない場合はindex scanを行い、多いものはfull scanすることできるようになります。, 件数が多い場合はfull scanしてからfilterした方がindex scanするより早いのです。fullはわしづかみでindexは指でつまんでるイメージです。, 1行目は、EMPLOYEE_IDの値が10000のものが99行あり、2行目は99999が(10000-99)=9901行あるという意味になります。この値もサンプリング数とバケット数に精度が依存します。, またENDPOINT_VALUEはnumber型以外は数値に変換されてしまいますので見てもわかりません。, 今回は、統計情報を取得すると何処にどんな情報が格納されているか見てみました。テーブルの行数くらいしか言イメージしていない人が多かったと思いますが、実際はいろいろな情報が格納されています。, 今回は、ロック中にテーブルの定義を変更した時に、どのような結果になるのか調査します。当然、一貫性を担保するために定義の変更は防止されます。ただどのように防止されるのかv$lockを見ながら理解を深めていきます。, この3つが何が違うのか、いまひとつわらない方が多いと思いますので、今回はこの違いを中心に記事にしたいと思います。, 今回は、ロックの連鎖が発生した場合にv$sessionではどのように見えるのかを確認したいと思います。, 今回はロックが発生したときにv$lockはどのようになっているのか事実から紐解いていきます。, 今回は、このv$sessionで何がわかるのかを説明したいと思います。また具体的な値をサンプルとして記載します。値が見えないとイメージが沸きにくいですよね。, ソフトウェアベンダーでITコンサルタントとして働いています。製造業のお客様を中心に、業務アプリケーションのデリバリーを担当しています。これまでの経験をフィードバックしていきます。, 当サイトのすべてのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、情報が古くなったりすることもあります。