「時間 = 道のり ÷ 速さ」がポイント！　オラクル精鋭技術者が教える、SQL高速化のコツ

全体のワークロードで最適なパーティションをアドバイス

柴田長氏（以下、柴田）：I/Oバイト、31GB読んでいたのが6GBに大幅に減ったので、データの読み込み時間が短縮しました。ただ、もともとCPU時間が占める割合が高いので、パーティション効果はそれほど大きくはない。ただ、速くはなっている。

I/Oバウンドは20秒かかってましたけれども、こちらのスライドは19秒ですけれども、もともと90秒以上かかっていたのものが19秒に短くなっている。これもI/Oバイト数が減っているからですね。I/O時間に占める割合が高いので。パーティション化の効果も高いと言えると思います。

答え合わせです。今回の実行しているそれぞれのSQLは同じ、表も同じですけれども、WHERE句のところを見てみるとSALES表は「T220」とついています。これに対して、T200……SALES表TIME_IDに対して、2014年1月1日よりも小さい値を引っ張ってくるようなクエリになっています。

SALES表には、2013年から2016年の4年間分のデータが入っているんですね。このWHERE句の条件はまさにそういったレコード、4年間分のデータのうちの2013年のデータにしかアクセスする必要がない。だから、TIME_IDでレンジ・パーティションを切って、実際に2014、2015、2016のデータにアクセスしなくて済むようになっています。そのため、高速化しているのです。

こうやってSQL文を見れば、TIME_IDでレンジ・パーティションを切ればいいよねとわかるとは思うんですけれども。実際のシステムは、このSQLだけじゃないんですよね。ほかにもデータウェハウス、BIの分析クエリがたくさん流れています。

Enterprise Managerは、単発のSQLだけじゃなくて、その直近で流れた全SQLのクエリに対して最適なパーティションをアドバイスしてくれます。少しのデモなのでたくさんのSQLを投げられていませんが、全体のワークロードで最適なパーティションをアドバイスしてくれる。これをEnterprise Managerを使って実行したSQLアクセス・アドバイザといったことになっています。

整理しますと、CPUバウンドなSQLは少しデモとは数字が違います。しかし、108秒から72秒と1.5倍速くなっています。I/OバウンドなSQLは、90秒から19秒で4.7倍ほどここで速くなっています。

Mission#3　パラレル化で複数CPUコアを使いこなせ！

続いていきましょう。「パラレル化で複数CPUコアを使いこなせ！」というMission#3です。

少し戻りますと、I/Oバウンドは「十分速くなったかな」と満足しちゃうかもしれないんです。しかし、CPUバウンドなSQLは速くならなかったですよね。この理由は、実はSQLモニタリング、Activityを見ていただくとわかります。

これは、横が時系列になっています。この緑色のところがCPUを使っている時間なんですね。ただ、縦はCPUコア数みたいなものだと思ってほしいんです。1CPUコアしか使われていない。この環境は、実は8CPUあり、ハイパースレッド・オンになっています。それも「CPUぜんぜん使ってないじゃん？」という話なんですよ。CPUバウンドなSQLは、8CPUコアを使いこなせていないんですよ。

これはなぜかというと、1つのSQLの実行は、1つのCPUコアしか基本的には使いません。これはシリアルの場合ですね。スライドにあるとおり、ClientからSQL文が投げられると、Oracle Instance上にサーバプロセスが1人だけフォークされます。

その1つのサーバプロセスが全データを読み込んで1人で処理する。1人ということは、1CPUコアしか同時に使えないんですよ。つまり、8CPUコアあるんだけれども、1CPUしか使われていない。

これを解決するのが、みなさんもご存じのパラレル実行ですね。パラレル実行は同じSQL文、SQL文書き換える必要もありません。SQL文が届いたらQuery Coordinatorというプロセスがフォークされ、それが親となって子分をたくさん作ります。

その子分が実際にデータを読み込んで演算処理を行います。子分はたくさんいるので、たくさんあるCPUを同時に使うことができる。これによってサーバに搭載されているCPUを、同時に複数のCPUコアを使うことができるようになります。

もう1つの特徴は、下に絵があるとおり、TABLEのデータをそれぞれの子分たちが分割して読むことができます。パーティション化していなくても、非パーティション表であっても、子分たちは分割して読みます。

そのため、パラレル化したことでI/O量が増えるかというと、まったく増えないんですね。パラレル化する前とまったく変わらないと覚えておくと、けっこうおもしろいと思います。

自動パラレル度調整とは？

「では、このパラレルをどうやって使うの？」という話です。手動と自動があります。

手動は、SELECT文を実行する前に「alter session force parallel query parallel 〇〇」と数字を入れます。これで、このあとに実行するクエリがパラレル化されます。

一方、もう自動パラレル度調整というものがあります。PARALLEL_DEGREE_POLICYという初期化パラメータあるんですけれども、こちらのデフォルトはMANUALで無効になっているのです。しかし、これをLIMITED、AUTO、ADAPTIVEのいずれかにすることで自動パラレル度設定が動くようになります。細かな話はWhite Paperを見ていただければと思います。

ということで今回は……ここからSQL文が速くなっていきます。どんどん進みますのでご安心ください。あと20分しかない。

「alter session force parallel query parallel 16」にして、「@query_CPU」と投げます。これだけですね。これでもう実はパラレルで動いています。……動いているはずなんですけど。「こんなことしていないで、はやくEMを見なさい」という感じですかね。あ、終わりましたね。

どうでしょうか。CPUバウンド、相当遅かったのは、1CPUコアしか使ってなかったからですね。パラレルを使うことで、全CPUコアを同時に使えるようになり、19秒まで速くなっています。

もう1つ。みなさん、I/Oクエリを投げておきましょう。時間もないので、EMに戻っておきましょうね。EMで少し確認していきましょう。

Enterprise Managerで確認をするんですけれども。その際にCPUバウンドなSQL、cp……。これですね、20秒か19秒で終わっていましたよね。

実は右側を見ると、パラレル列に「16」と入っています。先ほどまではこのパラレル列、なにも入っていないです。シリアルですね。今度はパラレルで実行されたよ、というのがここで一目瞭然、簡単にわかることがわかります。

I/Oバウンドも終わりましたね。19秒でした。I/Oバウンドはぜんぜん進化していないですね。I/Oバウンドなので、いくらCPUコアを同時に使えたとしても意味がないということですね。このあたりを理解してください。

パラレルなときに子分たちが同時に動く

CPUバウンドを少し見ておきますと、CPUバウンドは20秒まで縮まっています。データベース時間としては「8.3？　おいっ」と思うんですけれども、これはパラレルなときに子分たちが同時に動くと言いましたよね。子分たちは複数のプロセスが同時に動くので、その累積なんですね。

現実の世界は20秒です。コンピュータのサーバの中では、たくさんの子分たちが同時に動きます。その累積としては8.3分、同時に動いているということですね。

I/O量は変わりませんと言いましたね。変わっていません。パラレル化してもI/O量は6GBのままですね。ここからもわかると思います。実際にパラレルしたというのも、実行計画もパラレル、複数人で実行したというのがなんとなくわかりますね。いいでしょうか。I/Oバウンドはあまり成長していないので省略しましょう。

CPUバウンドなSQLは、8つのCPUコアを使いこなせていませんでした。すいません、お見せしてませんけれども、こんな感じで複数のCPUコアを使えるようになっています。

ただ、これは緑色じゃなくて、薄い緑色のところからまた生まれてきていますね。これがdirect path readという待機イベントなのです。I/O待機がまた増えてきています。そのため、次はCPUじゃなくてI/O側をまたチューニングしないといけないということが、ここからわかります。

I/Oバウンドは、もともとI/Oの時間が占める割合が高いので、CPUパワーはそれほど必要なかったんですね。あまり成長しませんでした。ということで整理すると、CPUバウンドなSQLは20秒まで、5.4倍まで高速化してきています。I/Oバウンドは割愛しています。

Mission#4　データ圧縮をしてI/O量の削減を狙え！

次、なにしましょうか。Mission#4の「データ圧縮をしてさらなるI/O量の削減を狙え！」に移ります。

圧縮というと、どうしてもデータ量を削減してディスクスペースを削減するのがたぶん最初に来ると思うのです。しかし、それだけじゃないんですね。今回の狙いは「I/O量を削減する」です。

パフォーマンスの観点でも、データの圧縮は非常に効果が高いです。なぜならば、8KBにデータに詰まっていますよね。Oracleのデータブロック、8KB。例えば、10レコードしか入りません。これを圧縮することで30レコードが入るようになる。つまり、3倍圧縮されたということなんですね。

8Kに今まで10レコードしか入っていなかったのが30レコード入るということは、全体のブロック数は3分1で済みます。そのため、読み込むブロック数が3分の1で済むんですね。つまり、3分の1のI/O時間で済む。だから圧縮するとスペースだけではなくて、I/O時間も減るといったところです。

総ディスクI/Oの回数、I/O待機時間が削減できますし、圧縮されたブロックのまま、バッファ・キャッシュにのせることもできるんですね。バッファ・キャッシュ上でキャッシュされているレコード数が、先ほどの例だと3倍に増えるわけですね。キャッシュ・ヒット率が上がりそうですよね。

圧縮は、パフォーマンス観点でも非常に効果が高いということです。ただ、更新が激しいと少しつらいので気をつけてくださいね。あくまで検索が多いワークロードで圧縮の効果は、パフォーマンス効果は高くなっています。

「高度な行圧縮」を使った実践へ

今回のデモで使う圧縮に関しては、たくさん表圧縮の方法があります。今回は「高度な行圧縮」といった圧縮を使っていきたいと思います。

高度な表圧縮を使っていきます。高度な表圧縮は、圧縮レベルは高くて、CPUのオーバーヘッドは低いです。アプリケーションとしてはOLTPとDSS、要はデータウェアハウスに向いてますよ、みたいな話です。

下のウェアハウス圧縮は、Exadata環境で使えるような圧縮モードですね。今回は高度な圧縮を使っていきましょう。キーワードは、ROW STORE COMPRESS ADVANCEDを使っていきます。

時間がかかるので、これはちゃんとその場で圧縮していきます。パーティション・プルーニングでパーティションの対象だったので、2013年のパーティションだけを圧縮していきます。

圧縮をするときのキーワードは、先ほどの「row store compress advanced」で、onlineという句をつける。そうするとこの処理中にもDMLを受け付けることができますよ、というお話です。

（話を）戻します。

おさらいですけれども、圧縮する方法は、表を作成する際にCREATE TABLE文で「row store compress advanced」をつければ、圧縮の表として作成されます。しかし、もう存在している表を圧縮したいですよね。そこでモードを変更する必要があります。

モードは2つあります。今後、その表にINSERTされる新規のデータだけを圧縮していく場合には、表の属性を変えるだけでいいです。

その場合にはALTER TABLE MODIFY、特定のパーティションのときはパーティションをつけてください。COMPRESSと打てば、一瞬でこのSQL文、このALTER TABLE文は終わります。そのあとにINSERTされてくるレコードだけが圧縮されます。

しかし、既存の格納されているレコードも圧縮していきたい合には、ALTER TABLE……MOVE PARTITIONといったかたちで、MOVEする必要があります。これは表が別の圧縮表のほうに移動していきます。終わったら自動的に切り替わります。

変更中にDMLを受付可能なオンラインを選択する場合には、ONLINE句をつけてください。これは、先ほどやったとおりです。

圧縮をすることで全体で12倍速くなった

ということで圧縮が終わりましたので、このまま@query_CPUを打っていきます。

どうでしょう。CPUバウンドなSQL、先ほど19秒、20秒ぐらいでしたけれども。圧縮をすることで、9秒切ってきました。速くなってきましたよ。I/Oバウンドも20秒ぐらいかかっていましたけれども圧縮することでI/O回数が減れば速くなるはず。はい、7秒ですね。

Enterprise Managerで確認してもいいんですけれども、スライドで確認していきましょう。SQLの実行時間、CPUバウンドなSQLは19秒から8秒、9秒まで改善することができました。

EMも見たほうがいいですね。EMに戻ります。

CPUバウンドは、どうでしょうかね、サクサク動きますね。cq2……のSQLですけれども、SQLは変わってないですね、8秒まで縮んでいます。

データのI/Oバイト数はどうでしょう。パーティション化して31GBが6GBになっていました。

さらにそこを圧縮することで、2GBまでI/O量を減らすことができている。3分の1を見せたかったので、MOD関数でダミー列を追加したんですよ。いいですか？

圧縮の効果は、お客様の表のデータにもよると思います。「本当にどのぐらい圧縮されるの？」は、Compression Advisorといったアドバイス機能がありますので、実際に圧縮しなくても圧縮率を見ることができます。DBMS_COMPRESSのパッケージかな。忘れちゃいましたけど、そういったパッケージがありますのでぜひそちらを確認してみてください。

同じように、I/Oバウンドもデータ量は、アクセスする量が6GBから2GBに減っています。そういった意味で7秒になってきています。

ということですが、I/Oバウンドはどうでしょうか。まだ青い帯が大きいですね。比率が多いですね。この青い帯はI/O時間でしたよね。このI/O時間が削減できればもっともっと速くなるんじゃないか。それは、ここのSQLモニタからもわかります。

整理しておきましょう。ここまで来ましたけれども、CPUバウンドなSQLは、圧縮をすることで全体で12倍まで速くなってきています。108秒だったのが9秒まで速くなってきます。I/OバウンドなSQLは、もともと90秒以上かかっていたものが7秒、12倍まで速くなっています。

Mission#5　インメモリ化で1秒の壁を越えろ！

ここからインメモリ化で1秒の壁を越えましょう。

Oracle Database in-Memoryという機能が12c Release 1から追加されています。こちら非常に技術者としては興味深い機能だと思います。

なぜならば、この右側にある絵を見ていただきたいんですけれども、下にあるSALES表、これは普通に今までどおりの表なんですね。レコード型で格納されているんですね。

サーバ上にインスタンスがあって、その中にバッファ・キャッシュがあったんですけど、それも今までどおり、左側にあるとおり、バッファ・キャッシュという領域はあります。

Transactionsが矢印で入ってきていますけれども、レコードをINSERTすることは普通にできます。要は普通のOracle Database。この普通のデータベースに対して、右上にあるNew in-Memory Column Storeというメモリ上にだけ存在する領域を作ることで、ここでカラム型でデータを保持する。ここが追加されただけなんですね。

これまでどおり行型・ロー型フォーマットで注文データをINSERTできます。並行して、カラム型でメモリで保持することができます。ストレージ側には、ロー型で保存されているんですね。

そのため、それほど大きな変更は加えられていないんですね。これまでのOracle Databaseのアーキテクチャに一部in-Memory Column Storeというメモリ上の領域を追加した。そこにカラム型で保持しているものがOracle Databaseのin-Memoryの機能です。

この絵のとおり、更新データ、INSERT……。新しいデータはロー型でどんどん入ってきます。並行して列型でも持っているので、Analyticsののクエリで列型でアクセスしたほうが効率がいいものは、そのまま並行して分析クエリ、in-Memoryで実行することができる。さらにハイブリッド構成をとることができます。

同時に利用可能なので、トランザクションの一貫性も保持できます。SQLにも制限はありません。in-Memory側にクエリをかけるSQLの制限はありません。

バッファ・キャッシュにもキャッシュされていますよね。in-Memory領域にもキャッシュされていますよね。どちらにアクセスしたほうが速いのか？　これはOracle Databaseが内部的に自動的に判断しますので、みなさんが気にすることはありません。

分析クエリのあらゆる側面を改善してくれます。データのスキャンは、ディスクだけじゃなくてメモリの速度の検索スピードになります。

ジョインもこれまでどおりできますし、さらにインメモリー独自のジョインも実装されてきています。そのため、これまでのジョインよりもより高速なジョインを実現していきます。

インメモリを集計を取れます。まあ、このあたりは宣伝なのでいいでしょう。

不要な索引を消すとオンライン・トランザクションの性能が速くなる

「どうやって使うの？」というと、これまでのデータベースインスタンスに対して、in-Memory Column Storeのメモリ容量を設定する。これが初期化パラメータのinmemory_sizeを設定します。

まず、バッファ・キャッシュとは別に、inmemory_sizeというメモリ領域を確保してください。その上で、「そのin-Memory Column Storeの領域にどの表をのせたいの？」をこのようなかたちで、ALTER TABLE文で書く。これはTIMES表、PRODUCTS表、CHANNELS表をインメモリ化しますよ、というような命令をするだけですね。

今回はとくにインメモリにのせたいSALES表には、2013・2014・2015・2016と4年間のデータがあります。今回のクエリは、2013年だけインメモリにのってればいいんですよね。そのため、2013年のパーティション、3ヶ月区切りのクオーターで区切っています。この4つをインメモリにのせていきます。

これはインメモリ化するためには、いったんディスクからバーンと読んで、メモリ上でカラム型に変換してのせておかないといけません。1分、2分くらいかかっちゃうので、先に打っておきましょう。

alter table、SALES表に対して、modify partition、2013年のクオーター4をインメモリ化します。priorityをhighで優先的にあげますよ。これで今、後ろでガーッとcompression、インメモリ化しています。

その間にスライドを説明しましょう。非常に簡単ですね。inmemory_sizeを設定して、インメモリ化したい表を指定して、インメモリ化するだけ。

3つ目はオプションとして、これまでの分析クエリで使用していた不要な索引を削除、というようなアドバイスがたまにあります。少し細くすると、インメモリなので全データ読んでしまっていいですよ。メモリからバーンと。これまで分析を速くするために使っていた索引は、使用しなくてよい可能性が出てきます。

まったく使用しなくていいかというと、そうではない。全部取っ払っていいと言うと少し語弊があるので、それは避けます。分析用のクエリで用意していた索引は使わなくてよくなります。

「では、それを放っといていいの？」というと……いいんですけど、そうじゃなくて、ハイブリッド構成ですよね、Oracleって。オンライン・トランザクションがINSERTされていきます。アナリティクス側でインメモリでクエリ返しますと言っていますけれども。索引が残っていると、そのオンライン・トランザクション 側でINSERTされた時に……。

極端な話、100個の索引があります。1レコードじゃあSALES表にINSERTしたら、SALES表のブロック更新します。しかし、100個の索引のブロックも更新しないといけないわけですよ。無駄ですよね。だったら、その100個の索引を消してしまいましょう。そういったことです。

そうすることで、インメモリ側のクエリが速くなるわけじゃないんですね。不要な索引を消すことでオンライン・トランザクション側の性能が速くなります。そういったところで不要な索引を消したほうがいいんじゃないですか、というアドバイスをしてくれるわけですね。

アクセス範囲を狭めるか、扱うデータ量を減らすか

「では、どの索引を消したらいいの？」は、索引の使用状況の確認していただければわかると思いますの。ここらへんテクニックの使ってみてください。

ということで、インメモリ化が終わったので、最後の@query_CPU。CPUバウンドなSQLはどうでしょうか？

これはね、ダメなんですよ。もともとCPUバウンドはCPUものすごく使っていますから、I/Oの時間もうぜんぜんないんですよね。だからいくらインメモリ化しても、そこまで速くならない。そのため、7秒。がっかりですよね。

しかし……これで成功すれば今日の僕は……いいですね。成功しましたね。0.41と0.43ですね。見事に1秒の壁を超えることができました。非常に安心しました。

ということでCPUバウンドなSQLは、繰り返しになりますけれども、もともとI/Oの占める時間が非常に短いので、あんまりインメモリ化しても効果がない。これをより速くするためにはよりCPUを積むしかないですね。

アクセス範囲を狭めるかですね。扱うデータ量を減らすかですね。2013年ではなくて2013年の何月など、そういったかたちで扱うレコード数を減らすことで、それはSQL文を変えることにつながっていきます。しかし、このサーバのスペックではこれ以上は速くなりません。

一方、I/Oバウンドは見事に0.42秒、0.41秒まで改善できています。こちらは完全にI/Oが0にはなっていないですね。34Mも残っています。

これはすいません、このクエリの特徴で、集計処理中に一時表にいったん書いて読むというような、一部データの読み書きが起きてしまっています。34と出ていますけれども、基本的にはSALES表からの読み込みは0になっています。これはもともとI/O待機時間の割合が高かったので、DBIMの効果が高いですよね。

ということで、整理しましょう。CPUバウンドなSQLは108秒からスタートして14秒までなんとか速くすることができました。これはCOMPRESSIONまで使うことで、9秒までいっています。非常にいい。

I/Oバウンドなほうは、もともと90秒以上かかっていましたけれども、これがなんと214倍。SQLを書き直してないですからね。

証明しましょうか。念のために見ておくと、最後に実行したI/Oのクエリがこちらですね。「dhz……」でSQLIDは変わっていないですね。そのため、最後までSQL文は一切書き換えていない。しかも、1秒以内と丸められちゃうんですけれども、0.41秒と処理時間が短くなっていると確認できたかと思います。

はい。214倍、SQL文は書き換えていませんよ。

時間 = 道のり ÷ 速さ

最後に少しまとめに入りますけれども、パフォーマンス・チューニングの基本的な考え方は、僕はあまりそんな高度なSQL文書けないので、このように考えています。

どちらかというと、僕はインフラよりの人間なので超有名な公式と同じです。みなさん小学校で習いますよね。「時間 = 道のり ÷ 速さ」ですね。人生で初めて覚える公式じゃないかと思うんですけれども、これですね。

SQLの時間を短くしたければ、分子である処理量を減らしてください。あとは分母である速さを上げてください。これだけですね。

処理量を減らすところ今回使ったのが、パーティショニングでアクセス範囲を減らす、圧縮することで持ってくる量を減らす、ですね。あと高速化ではDatabase In-Memoryで、そもそもディスクI/O速度ではなくメモリの速度を活用する。さらには並列化で、1CPUのパワーじゃなくて8CPUのパワーを全開で使うことで速度を上げる。

この基本的な「時間 = 道のり ÷ 速さ」の公式があるので、みなさんどのチューニング、どの機能がどの要素を減らすのかを意識していただけると、意外とパフォーマンス・チューニングはよりおもしろくなってくるかなと思います。

今回ご紹介した機能は、パーティションであったりパラレル・クエリ、これEnterprise Editionの機能ですよね。CompressionもEnterprise Editionのオプション機能。Advanced Compressionというオプション。Database In-Memoryもオプション機能ですね。すいません、Enterprise Manager＋オプション機能です。

実は6年以上前にも同じようなパフォーマンス・チューニングを私自身がやっています。当時はこういった「EE使ってください。オプション使ってください」でけっこうお金のかかるチューニングをみなさんにお願いしていたと、非常に反省しています。申し訳ございませんでした。

今は謝りません。なぜならば、我々Oracle Cloudで安く始められるようになったからですね。こういったEnterprise Editionとかオプション機能が初期投資安く使うことができるようになる。

それがOracle Cloudなので、ぜひトライアル環境を申し込んでいただいて、しばちょう先生の53回の連載を見てスキーマを作っていただいて、実際にみなさん試してみてください。どれだけ高速化するか、私が嘘ついてないかというのが確認することができると思います。

ということでちょっと伸びちゃいましたけれども、以上の前半のセッションを終わりにしたいと思います。ご清聴ありがとうございました。

（会場拍手）