DBからの情報取得、時間をとるか精度をとるか　どちらも両立させる至高のチューニング術

しばちょう＆ゆっきーが参加者からの質問に解答

柴田長氏（以下、柴田）：毎回、後半はディスカッション・ナイトということでやらせていただいていますが、今日は夏祭りということで、趣向を変えて2人で掛け合いながらやってみたいと思います。失敗したらすみません。許してください。今回でやめます。

今回は「パフォーマンス・チューニングの改善方法について語ろう」ということで、私、しばちょうと、隣にいるゆっきー。これ、「なぜ彼を選んだの？」という理由はそのうち出てきますので、楽しみにしてください。

本日はあまりスライドを用意してなくて、みなさんがこのイベントにお申込みいただいた際に書いていただいた質問に答えまくりたいと思います。

ということでさっそく始めたいと思います。「Live Q & A　しばちょう＆ゆっきー」。ゆっきーが質問を選んでくれます。僕が答えます。

高橋敏行氏（以下、高橋）：それでは、はじめまして。みなさんこんにちは。ゆっきーこと、高橋敏行と申します。

今日はたくさんの方に参加いただきありがとうございます。

さて、みなさんには質問をいろいろ書いていただいたかと思います。本日は、これをしばちょうさんにぶつけようと思います。実はほぼこれぶっつけ本番です。部下としては最悪ですが（笑）、柴田さんには報告せずに本日お持ちしました。

柴田：あ、そうだ、部下です、部下です。

高橋：あ、すいません。私、しばちょうの部下やっています。

この場で「こんな質問が来ています」とお伝えしようと思っているので、しばちょうさんにはバシバシと回答してもらおうと思っております。

柴田：ちょっと待って。答えられないものは答えられないって言いますからね。許してくださいね。

高橋：しばちょうさんをシビれさせる質問を出そうと思っています。

柴田：はい。

統計情報の取得、時間をとるか精度をとるか

高橋：今回はパフォーマンスの部分がメインだったので、それと紐づく大切なワード、オプティマイザ統計情報ですね。これに関しての質問が非常に多かったので、最初にこちらの質問をお持ちしました。1つ目の質問内容がこちらです。もうラジオ番組みたいですね。

「統計情報の取得タイミング等について知りたい」というところで、東京都のA.Iさんからいただきました。ご質問ありがとうございます。柴田さん、これいかがですかね？　やっぱり……。

柴田：ちょっと、準備をしてない（笑）。

高橋：（笑）。取得タイミングは重要だと思うんですけれども。

柴田：そうですね、取得タイミングは非常に重要ですね。タイミング等を知りたい？

高橋：そもそも統計情報は取るべきですか？

柴田：はい、取るべきです。オプティマイザ統計で性能が出ない話は数年前よりは聞かなくなっているので、コストベースオプティマイザは非常に安定してきている印象はあります。ただ、ちょっとOracle Database12.1にはいくつか問題があったんですが……。

高橋：実際にデータを取得すると正直重いですよね。すごく時間がかかるから、あまりデータを取りたくないケースも多いと思います。その場合、サンプルサイズをすごく小さくして取るんですが、その場合、あまり精度は高くなくなってしまいます。そういう人もいると思います。

私も少し前まで金融のお客さま担当をしておりまして。実際、そういうお客様もいらっしゃいました。そういう方のケースも、本日の質問に絡めてご回答いただきたいと思います。

柴田：オプティマイザ統計情報を取る際に、みなさん、ESTIMATE_PERCENTをご存知ですか？　実はパーセンテージを指定できます。

先ほどもお話したとおり、表を全部読んで統計情報を作るのか、サンプリングして統計情報を取るのか。単純に考えたらサンプリングしたほうが統計情報を取るのは速そうですよね。ただ、その精度はどうなんでしょうか？

その精度が問題で、要するに実際の表の統計情報をサンプリングしすぎてしまって、現状と乖離した統計情報が作られてしまうケースがあります。

それによって、SQL文が精度があまり高くない統計情報を使って実行計画を作るので、その実行計画が実態にそぐわない、あまり性能が出ないことが起きやすい。なので、なるべく精度の高い統計情報を取りたいですよね。

ですが、「じゃあ何百ギガの表の統計取るのかよ」と責められてしまいます。「そんなの取るのに何時間かかるんだよ？」という話ですね。それは実は（Oracle Database）11gから速くなっています。

精度とスピードを両立する

連載でも書いたことあるのでご存じの方いらっしゃると思いますが、例えばこんな感じです。今、即興で2012年のスライドを出しています。

ESTIMATE_PERCENTにAUTO_SAMPLE_SIZEというものがあります。とくに11gからのAUTO_SAMPLE_SIZE、これはぜひ使ってみてくださいって話です。

表があるんですけれども、ESTIMATE_PERCENT 1とすると、1パーセントサンプリングをします。実行時間は797秒で統計情報収集が終わります。

これを、もし100パーセントでやるとものすごい時間かかります。「1万8,000秒って、おいっ」て。「1時間って3,600秒だよね」みたいな。「何時間かかってるの？」「5時間？」みたいな。これは実際の運用では無理ですよね。

でも、精度はどうでしょう？　こちらは100パーセントのデータ読んでいるので、もちろん精度は高いです。なので、こちらの統計情報で実行計画作ってあげたほうが、より正しい実行計画になります。

ですが、1万8,000秒もかけられないので1パーセントでやる。でも、こんなに実態と乖離しちゃっていていいんですか？　そこなんですね。

それを改善するために、11gにはAUTO_SAMPLE_SIZEというものがあります。実行時間は1パーセントには及びませんが、100パーセントで実行した場合の10分の1で済んでいます。そして精度はどうでしょう？　完璧ですね。どうでしょう？　L_COMMENTは若干ずれていますが、1パーセントよりぜんぜん高いですね。こんなかたちで改善されています。

実は前バージョンから実装されていた？

これもマニュアルを見るのがおすすめです。12cから「APPROX_COUNT_DISTINCT」という関数が追加されています。

これは正確ではないですが、だいたい正確な値の種類をdistinct countしてくれる関数です。要は正確じゃないんですよ。けど、ある程度正確です。99.数パーセント。

これを11gの時からおそらく統計情報収集のところで使い始めている。正確じゃないのでCPUパワーが少なくて済みます。正確かつ厳密にやろうとすると、CPUパワーをものすごく使うんですが、ある程度曖昧でよければCPUパワーはそれほど使いません。

なので、先ほどのAUTO_SAMPLE_SIZEというのは、実は全データをわーっと読んでいます。読んでいますが、カウントするところのCPUパワーが圧倒的に減っているので、実行時間は短くなる。

なので、ぜひ11g以降のお客様はAUTO_SAMPLE_SIZEを使って、表の統計情報の精度を高く保っていただければ、実行計画の変動は防げるかなと思います。あっ、これだけじゃないね。

高橋：ですね。11gだけど、12のほうが？

柴田：そうですね。おそらく12でみなさんが使えるようにCOUNTファンクションとして提供されましたけど、オラクルって前のバージョンでけっこう内部的に使っているんですよ。それが成功すると表に出てきたりするんですね。

後ろで見えないところで使っていたりする話なので、確証はないんですけれども、おそらく12cで出たCOUNTの関数が11gの機能のSAMPLE_SIZEでも使われているんじゃないかな。ごめんなさい、推測ですけれども。

高橋：どこにも書いてない情報ですか？

柴田：どこにも書いてないです。

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

実は10パーセント以上の変更だけ収集している

柴田：あとはこの表はどんどんデータが更新されていきますよね。更新されていって、例えば100万行あります。1件だけアップデートしました。統計情報を取る必要ありますか？　ないですよね。じゃあどれくらい更新したら統計情報取ったほうがいいですか？

毎回すべての表から統計情報を取っていると意味がないですね。1件しか更新されていない表の統計を取り直す必要はありません。それが全体の統計情報収集の時間を圧迫していると思うので、本当に必要な索引だけ、統計情報を取り直せばいいんですね。

要は「どれだけ更新されたのか」というのが重要です。それを管理しているのがSTALE_PERCENT。

このスライドは今回初公開です。STALE_PERCENTというパラメータがあって、デフォルトは10パーセントです。10パーセントのレコードが更新されると、自動統計収集ジョブの中で統計情報が収集される対象になります。

なので、統計情報を自動で収集されているのであれば、実はすべての表を毎回収集しているのではなくて、10パーセントの乖離があったり、まったく統計情報がない空っぽな表に対してだけ、夜間、統計情報の収集が自動で行われていることを知っておいてください。

なので、すべて表をやっているわけではありません。データが10パーセント以上更新されたもの、空っぽなものだけが対象になっています。そういった意味では、実行時間を、統計情報の収集時間を本当に必要な部分に狭めることで、短くできるんじゃないかと思います。

自動統計収集の裏ワザ

ちなみにあともう1つ、「夜間の自動統計収集ジョブで、どのオブジェクトが統計情報収集の対象になるの？」ということ、ちょっと興味ないですか？　興味ありますよね。

そういったときに使うのが、例えばDBMS_STATSパッケージのGATHER_DATABASE_STATSプロシージャの……とか言うと、「これ、統計情報収集するジョブだよね？」と思うんですけど、実はそうじゃないんです。

おもしろいですよ。これは連載に書いていますが、GATHER_DATABASE_STATSやGATHER_TABLE_STATSというのは、実はオプションを指定することで、統計情報収集するか、これから統計情報収集しないといけないものをリストするのか、動きを変えることができます。

なので、例えばこの例だと、オプションに「LIST STALE」というものを指定すると、統計情報がSTALE、つまり失効しているもののリストを出すことができます。なので、これが自動統計収集ジョブのところで自動的に収集される対象であることがわかります。

あと「LIST EMPTY」とやると、統計情報がないオブジェクトがバーっと出ます。両方を、失効しているものと空のもので両方を出したかったら「LIST AUTO」みたいなことをやります。

オプションを指定しないと、「GATHER」といって、すべてのオブジェクトに関する統計情報を収集するのがデフォルトの動きです。自動統計収集ジョブでは、「GATHER」モードに設定されているはずです。

今は統計情報を取るのではなくリストするという、LISTのAUTO、STALE、EMPTYというものもありますよ、というお話をしたので、こちらで統計情報を取るとなると少し大変だから、「実行しているオブジェクトはどれ？」とか「空のオブジェクトはどれ？」というのを見るために、リストオプションを使ってみてはいかがでしょうか。

効率的な統計情報の取得テクニック

高橋：ちなみに、もっと効率的に統計情報を取れる技はありませんか？　実はそういう質問もいただいていて。「タイミングも合わせて、効率的な統計情報の取得方法はありませんか？」と。

柴田：効率的？　前半でご紹介したパーティションというのも1つあると思います。レンジ・パーティションで区切っていって、最新のデータは最新のパーティションに入っていて、過去のパーティションは更新されない場合には、過去のパーティションは統計情報を取り直す必要がないですよね。新しくデータが入ってきたパーティションだけ統計情報を取り直せばいいわけです。実はそういった指定をできます。

GRANULARITYというパラメータがあって、こちらはAPPROX_GLOBAL AND PARTITIONという、11gからの機能を使うと、統計情報のない、要は新しいデータが入ってきたパーティションだけを取ることで、表全体の統計情報を作り出すことができます。

例えばパーティションが1、2、3とあって、1と2は変わっていない場合は、3の統計情報だけを取ります。取るときにこれを指定するんですね。そうすると、3が新しく統計情報を取られるんですが、パーティションにはパーティション個別のものだけでなく、表全体の統計情報というのもあります。

その全体の統計を取るためにまた全部読まなきゃいけないは非効率なので、各パーティションの統計情報を合算したものを、このパーティションの統計情報にするオプションを使うことで実行することができます。統計情報を取る対象もこれはできます。

あともう1つは、前半でパラレル・クエリってありましたよね。CPUコアを一気に使って実行時間を短くする。統計情報収集のときにはパラレルではなくて、DEGREE属性というものがあります。

デフォルトはNULLなので動きません。けれども、数字を指定することでその数字でパラレル実行がされます。

なので、500ギガの表を統計情報取りたいときには、1CPUコアで少しづつやるのではなくて、DEGREE 16ってやると、16CPUコアを同時に使って一気に統計情報を取ることができる。

もう1つ……大丈夫？　いっていい？

高橋：ぜひとも（笑）。

DEGREEとCONCURRENTの合せ技

柴田：DEGREEは1つの表に対してパラレル・クエリのように並列で統計情報を取りますけれども、例えば、1つの表の中に何百個何千個のパーティションがあったら、そのDEGREEは実は意味がありません。

各パーティションに対して、DEGREE 16、DEGREE16、DEGREE、DEGREE……。1番目のパーティションが終わったら、2番目のパーティション、3番目のパーティション、というかたちですね。

小さいパーティションなので、DEGREEやる必要ないんですよ。この小さいパーティションを同時に処理したいと思いません？　統計情報取りたいと思いません？　それが11gからCONCURRENTというオプションが追加されているんです。

CONCURRENT、これはけっこう最初出たときはまともに動かなかったのを正直言っておきますが、そろそろ安定してきたかなって思います（笑）。

はい。CONCURRENTは複数のオブジェクトを同時に統計情報収集することができます。DEGREEはあくまで1つのオブジェクトごとに順番にやっていきます。だから、小さいオブジェクトがたくさんある環境だと、このCONCURRENTという設定が有効です。

そして、DEGREEとCONCURRENTは合わせることができます。赤いところがCONCURRENTなんですが、例えばGATHER_SCHEMA_STATSで、SHスキーマに対して統計情報を収集しにいこうとすると、SHスキーマには、CUSTOMERSテーブルとかSALESテーブルとか、COUNTRIESテーブルとか複数ありますけど、これを同時に統計情報収集しにいくことができるようになります。

さらにSALES表は、先ほど言ったときにパーティション表だったりしましたよね。このパーティションも並行して同時にやることができる。DEGREEを設定しておくと、さらにそのオブジェクトに対して、パラレル・クエリでデータを読み取ることができます。

本当に効果があるかは試してみてくださいね。効果がない場合もあって、環境に依存するのでぜひ試してみてください。