Dataは100GB、Indexは180GB　これってどういうこと？　DBのプロが語る、原因と改善法

統計情報と実態を乖離させない

高橋敏行氏（以下、高橋）：衝撃の1問20分という。終わる気がしない。ぜんぜん終わりませんよ。

（会場笑）

高橋：みなさんの質問をたくさんリストにしたんですが、無理です。申し訳ない。なので、できるかぎり解答したいと思います。

では、2つ目です。統計情報というか実行計画ですね。やっぱり実行計画が変わってしまうという質問はいくつかいただきました。

東京都 Y.Sさんからいただきました。「実行計画の変動によるパフォーマンス劣化はどう対策したらいいのか知りたい」。また、「実行計画がブレる。Cardinality Feedbackのような機能があるけど、こういうのは不安です」ということを東京都のY.Sさん、これはたまたまイニシャルが同じなんですが別の方ですね、からいただきました。

このライブ感すごくないですか。私もうヒヤヒヤです（笑）。

（会場笑）

高橋：これ大丈夫ですか？

柴田長氏（以下、柴田）：俺、1時間しゃべったあとだからね、頭動いてないんだよね。

高橋：そうですよね。

柴田：ええと、実行計画変動によるパフォーマンス劣化をどう対策したらいいか。いくつかあるんですよね。実行計画がブレる。Cardinality Feedbackのような実行計画……。これは5分で回答するのは無理なので、一部だけ切り出してお話ししようと思います。

すみません、断片的に話しますが、Cardinality Feedbackはどういうときに動くかというと、実際に実行した時と……。ある統計情報があって、それで実行計画を作って、実際に実行しました。そうしたら「おい、統計情報が言っていた内容と違うじゃないか」という状態が起きるわけです。

統計情報と実態が乖離している場合、次に実行する際に、乖離していた事実をもとにフィードバックするのがCardinality Feedbackです。

要は乖離していたからなんです。だから、乖離してさせないでくれというのが答えだったりする。だから、先ほどのような精度が高いものを取っておく運用にすれば、Cardinality Feedbackは基本的には動かないんですね。

あとはバインド変数というのもあるんですけれども、そうですねえ……。そろそろ次行かなきゃダメ？

高橋：大丈夫です。いきましょう。

統計情報を使用前に検証できる

柴田：バインド変数……バインドピークを無効にしていたから、ということもあるかもしれないですね。

先ほどのスライドに戻りますが、PENDING_ STATSはご存じですか？　

統計情報を収集すると、その収集した統計情報がすぐに反映されるとみなさん思っていると思いますが、それはデフォルトの動きです。

実は取ったものを使わせず、保留させることができるんですね。Pending Statisticsという機能があります。この使い方は連載を見てください。書いてあります（笑）。

これはなにを言っているかというと、取ったものをすぐ公開しないので、アプリケーション側はこれまでの統計情報をずっと使い続ける。よく実行計画が変動するという要素の1つとして、「統計情報を取ったら実行計画が変わっていました」という状況があると思います。

なので、取った統計情報を使っても、問題が起きないかどうか、実際に使う前に確認することができます。いったん保留しておきます。

その保留している統計を使って、ここですね、SQL*Plusでつないでもらって、alter session setでOPTIMIZER_USE_PENDING_STATISTICSというパラメータをTRUEにしてください。そうすると、そのセッションだけは保留統計を使えるようになります。

そこでSQLを実行すると、新しく取った保留中の統計を使って実行計画を確認することができます。それで問題がないのであれば、その保留中の統計を公開してあげる、というステップを踏むことができます。

過去の連載に書いてあるので確認してみてください。（時間は）大丈夫ですか？

高橋：大丈夫ですが、衝撃のもう半分を超えたというですね（笑）。

柴田：ぜんぜんダメですね。

高橋：ぜんぜん質問できない。やばい。

SPMの使い方について

柴田：じゃあバッサバサ切っていきましょう。一言で切っていきましょう。いいですかね？

高橋：一言で。今のお話にはなかったですが、SPM（SQL Plan Management）についての質問も何件かいただきました。

柴田：SPMは……どうしよう。SPMの使い方も実は秘伝の資料の中にあるんですけど、SPMってご存じですか？

こんなことできます。SELECT、FULLヒント。TABLEをフルスキャンしろとFULLヒントをつけているんですが、実行計画を見てください。 INDEX RANGE SCANです。こういうことができます。

要するに、SQL文は変えずにアプリケーション側で書いています。アプリケーションを変えなくても、データベース側で、「このSQLが来たらこの実行計画」を使えということを紐づけることができる。SQL Plan Managementという機能があります。

時間がないので、「こんなことができる！」というインパクトだけお伝えしますけれども（笑）。

（会場笑）

柴田：ただ、検索すると「『オラクル・コンサルが語る！ SQL 実行性能の安定化方式』鈴木健吾」という資料があるんですが、ここにSQLの使い方は完璧なものが書いてあるので、SPMを使いたい人は参考にしてください。はい、以上！（笑）。

（会場笑）

高橋：こんな感じで。

柴田：インパクトだけお伝えします。SPMというのがあるんです。アプリのコードを変えなくても、実行計画だけいじることができる。非常に便利ですよね。アプリ屋さんに怒られなくて済みますよね。……そっちのほうが怒られるか（笑）。

EXPLAIN PLANは信用するな

高橋：はい。じゃあ次に行きますね。衝撃の細かい質問がきました。ちょっと伝わってきたので、ぜひお伝えしたいと思います。実行計画です。「EXPLAIN PLANと実際の実行計画が違うんだけど、困るんだよ」という質問です。ちゃんと読みますね。

「最近のOracleでは、EXPLAIN PLANで表示された実行計画と実際に使用された実行計画が異なることが多いです。とくにSQL*PlusのAUTOTRACE機能で表示される実行計画が異なることが不便です」という、非常に思いが伝わってきました。

「対策として、SQL*Plusを2つ立ち上げて、1つはAUTOTRACE有効でデバッグ用、もう1つはdbms_xplan.display_cursor、これを表示用として使っています」という、東京都O.Yさんからいただきました。

柴田：はい、正しいです（笑）。このとおりです。EXPLAIN PLANはあくまで、例えばバインド変数の中を見たかったりとか、あくまで簡単に実行計画を見るためのものなので信じないでください。

（会場笑）

柴田：信じないでください。実際に信じるのは、実際に実行させて、その実行計画をdbms_xplan.display_cursorで見る。これが一番正確です。あとは先ほどのSQL監視ですね。

EXPLAIN PLANで実行して「これが実行計画だ」といって、本番運用に変えたら違う動きをするので、EXPLAIN PLANは信用しないでください。ごめんなさい。これでいいですか（笑）。

高橋：O.Yさん、正しいそうです。いらっしゃるかわからないですけれども。

柴田：正しいです（笑）。

ビッグ・ファイルで管理するリスク

高橋：はい、次にいきます。今度はベストプラクティスでちょっとお話いただいていましたので持ってきました。初めて神奈川県ですね。M.Mさん。「Tablespace、これは小さいサイズをたくさん分割するのと、大きいサイズで小さい数を持つ、どっちのほうがいいの？」というご質問です。

柴田：これは難しいですね。非常に難しいですね（笑）。

従来のスモール・ファイル表領域というのは、8キロバイトのブロックサイズだったら、32K-1のブロック数。だから32ギガ未満のスモール・ファイルをたくさん作るか、ビッグ・ファイル表領域というのは1つの表領域、１つのデータファイルで何百ギガといった表領域を作る話だと思うんですけど。

まあ、データベースはレコードが少ないほうが速いですよね。ですから、それは少ないほうがいいです。ですが、そこまで大きな影響はないと感じています。

なので、絶対ビッグ・ファイルを使えとも僕は言わないですし。ただ管理性からすると、何十テラというデータベースで、「スモール・ファイルでものすごい数の表領域、データファイルを作るぞ」となると、それは非効率なので使うところは難しいですけれども。

リストアの時間を考えると、1つのビッグ・ファイル表領域で2テラの表領域を作ってしまえば、それはそれで管理は楽ですが、もしその表領域、データファイルが壊れてしまったら、2テラをリストアしないといけません。時間がかかりますよね。これは現実的ではない。

なので、もしビッグ・ファイル表領域を使うにしても、ある程度サイズ制限、リストアの時間を考慮したサイズ制限というのが必要になってくる。なので、だいたい500ギガとか、そのぐらいのビッグ・ファイル表領域をたくさん作っていく、という運用を見たことはありますね。

そのぐらい大きいと、基本的にパーティショニングを使っているはずです。なので、パーティショニングというのはパーティションごとに表領域を分けられますから、パーティション表で2テラバイトあっても、パーティションを4つに区切って、表領域を4つつけるということもできますの。

まあ、ちょっと難しい。あまり性能差はそこまで大きくないと思っています。

Indexが多すぎる問題

高橋：ありがとうございます。引き続き、もう1個ベストプラクティスです。Indexですね。「Indexが180GBもある。対してDataが100GB。これは正常なんですか？」という、とてもシビれる質問です。

（会場笑）

柴田：これは情報量が少なすぎてなんとも言えないんですけど、私が経験したのは、例えばOracle Databaseを使用するパッケージアプリケーションってもともとスキーマが用意されているんです。

それをどんどんカスタマイズしていったら、300列とか400列の1つの表ができて、そこにチューニングしていったら100個以上の索引がバーンッと並びました。いいですか。1つの表に100個の索引。

そんな表がたくさんだったんですけど、DBをやっていた私はテーブル用の表領域とIndex用の表領域を分けてみました。サイズはどうなったと思いますか？　サイズは4：6でした。つまり、表のほうが小さかったんです。索引のほうが大きい。だから、「おそらく索引の量が多すぎるんじゃないの？」」というのが1つあります。

あとは断片化ですね。断片化して索引がちょっと非効率になっているので、もしかしたらRebuildをかけて断片化を解消したら小さくなるかもしれないし、「不要な索引たくさん貼ってない？」みたいなことが考えられます。

高橋：みなさん質問書いていただくときは、具体的に書いていただけるとよりリアルな回答をいただけると思いますので、ぜひ。

（会場笑）

パフォーマンス・チューニングは過去資料をチェック！

高橋：今日、実は8割ぐらいはふわっとした質問でした。

あとは、今回パフォーマンス・チューニングということで、やり方を知りたいという方が非常に多かったです。

例えば「性能トラブル発生の原因のアプローチ法が知りたい」「パフォーマンス・チューニングのポイントが知りたい」、それから「判断方法が知りたい」。こんな質問いただいています。

柴田：この場ではすぐ答えられないですね（笑）。津島博士（注：日本オラクルの津島浩樹氏）は夏休みなんですよ。実は津島博士をここに呼ぼうと思ったんですけど、夏休みだと断られてしまって。

なので、時間もないので簡単に言ってしまうと、去年のTech Nightかな。津島博士も連載をやっているんですが、その連載を読んでいただくのが非常に効果が高いと思います。

あとは、みなさん「Oracle Tech Night」で検索してもらうと、過去11回分の資料が全部載っています。津島さんが第2回にやっているんですね。ここに書いてあります。

（会場笑）

柴田：ごめんなさい（笑）。「パフォーマンス・チューニングの極意」なので、読んでおきましょう（笑）。

津島さんは連載60回ぐらいを迎えていて、60回分全部読むのはけっこう難しいので、スライドの中に「第◯回の連載読めよ」みたいなものが書いてあります。なので、これをヒントにして津島博士の連載をピックアップして読んでいただければ、今いただいたご質問は少しずつ解決していくのかなと思います（笑）。

高橋：ちなみに津島博士に質問すると、「第◯回を読んで」という、番号で返ってくることが増えてきました。

柴田：ありがとうございます。社内でもそんな感じなので。

高橋：そんな感じになってきました。全部書いてあるそうです。

ということで、「問題のあるSQLの特定が難しい」「改善方法がわからない」「チューニング方法を教えてほしい」。全部ここに書いてあるということですね。

（会場笑）

12.1は過度な最適化をしようとする

高橋：ありがとうございます。あとは、12c関連がやっぱり質問として多かったです。残り時間がないのでがんばります。

オプティマイザですね。12cのオプティマイザの新機能について、適応計画とダイナミックサンプリング機能。「効果はあるんだけど、一部過度なパフォーマンス劣化を引き起こすケースがある」という、お困りのご相談いただきました。

柴田：これも簡単に答えると……これです。リンクからいきますね。Tech Night第7回の「12cクエリー・オプティマイザの新機能と統計情報の運用戦略」ということで、コンサルのエキスパートに話してもらっていますので、こちらを見てください。

12c Release1は過度なチューニング、最適化を行おうとして若干踏み外すことがあるんです。12.2でそういった機能は実はデフォルトでオフになっているので、12.2を使う方は安心してください。ただ、12.1を使われている方は、その過度な動きをするのに注意してください。

そうしたトラブルも正直ありましたので、津島博士に実際のトラブルに対応してもらったあとに、「みんなぶつかるから記事書いて」ということで書いてもらったのが第57回。なので、こちらで12cの注意点は書いてありますので、ぜひこの57回を見てください。以上。

高橋：ありがとうございます。

柴田：答えになっていないですよね。すいません。ヒントばっかりで。

高橋：みなさん、調べてください、ということだそうです。

柴田：（笑）。

『津島博士のパフォーマンス講座』を読めばOK！

高橋：最後ですね、今ネガティブな質問が多かったので、なるべくがんばってポジティブなものを探したのがこちらでした。「過去バージョンより、12cに更新したら高速になるSQLのタイプを知りたい」。

柴田：答えを用意してるんだよね？

高橋：これ、正直、津島さんに聞きました。「パフォーマンスどうですか？」というところで、これ、さっきお話ししましたけど、「これを見て」というお話をいただきました。それがこちら。もう全部番号ですね（笑）。

（会場笑）

高橋：「34回、39回、42回、54回、56回、60回、61回。これを見ればわかるよ」という回答をいただいたので、ぜひみなさんこちら見てください。

柴田：ちゃんとダウンロードできるようにしますので、今メモしなくて大丈夫ですよ。

高橋：というところで、実はもう少し質問あったんですけど、すみません、割愛させていただきました。

柴田：すいません。時間が過ぎちゃっていますけれども、以上ですね。「パフォーマンス・チューニングの改善方法について語ろう」でした。ありがとうございました。