チューニング経験が無くてもパフォーマンス改善できる　SQL高速化の白熱ライブ

しばちょう先生がSQL高速化に挑戦

柴田長氏：みなさん、こんにちは。日本オラクルの柴田です。このセッションは「Live Challenge!!SQLパフォーマンスの高速化の限界を目指せ！」がテーマです。いかにSQLが高速化していくのかを体験してもらいたいと思っています。

改めまして、私は日本オラクルの柴田 長（つかさ）と申します。長という漢字が「つかさ」と読めないので、「しばちょう」と呼ばれています。

（スライドに）「5年以上52回」と書いてあります。これはOracle Technology Networkでほぼ毎月行っている「しばちょう先生の試して納得！DBAの道」で、主にデータベース技術者の方にコマンドレベルからデータベースの機能を理解してもらう記事を書いています。

「コマンドが非常に便利です」と読者の方からフィードバックが先日ありました。コマンドだけ抜き出して、「しばちょう先生からのプレゼント」でテキストにまとめてご活用いただいている話も聞いたので、たぶん便利だと思います（笑）。

もし興味があれば読んでみてください。Twitterアカウントも載せてありますので、ぜひフォローお願いします。本セッションのシナリオです。昨今データを分析するアナリティクスの世界がより加速してきています。分析ツールなどで、UIで見たいデータを並べて、簡単に分析できます。

そういった分析ツールで、SQLは自動生成されます。もしSQLが遅かったらどうするか。SQL文を細かくチューニングするのは、非常にスキルがいります。本セッションではそういったことをしません。SQL文は最初から最後までまったくいじりません。

そんな中、Oracle Database 12cの最新テクノロジーを駆使して、いかに高速化していくのか。そういったものをみなさんと一緒に体験したいと思います。

デモがスタート

さっそく、最初の問題。SQLは2つあるんですけども、そのうちの1つです。実行環境としてはマルチテナント型のデータベースを作っています。「V$VERSION」と打つと、「12c Release 2」と出ます。最新のデータベースを使っていますね。

こちらにプラガブルデータベース（PDB）であるPOCOを用意してあります。プラガブルデータベースにログインしましょう。「sh/oracle@poco」、こんなかたちで接続します。

さっそく、「@query_CPU」を流します。今流しているクエリは、どこで流しているか。Oracle Databaseがサンプルで提供しているSales HistoryというSHスキーマですね。（スライドを指して）売り上げの注文データ等を格納しているER図が書いていますが、こちらのサンプルスキーマSHがベースです。

ただ、小さいので画面の真ん中にあるSALES表にダミー列を2つ追加しています。CHAR(105)という微妙な数字を入れています。

その列データは、値の種類を30パターンに絞るために、MOD関数を実行して、余りを30で使って、30種類のデータを作っています。さらにこのSALES表をINSERTセレクトを繰り返して、だいたい32ギガほどまで増幅した環境を作っています。

今回のSQLは2つです。CPUバウンドとI/Oバウンド。CPUをよく使うSQL、SELECT文とI/Oをよく使うSELECT文があります。

基本的にはI/OバウンドのSQLがベースになっています。CPUをよく使うように、わざとCPUバウンドをI/OバウンドのSQLから作り直しているイメージですね。

2つのSQLですが、ミッションを5つに分けて体験していきましょう。まず1つ目が、「処理状況を確認せよ！」です。どういう問題が起きているのか。これが重要です。なので、先ほど流していたSQLがありますから、さっそく見てみましょう。

「Enterprise Manager」でどんなSQLが流れているかがわかります。最近やったのが画面の上にあります。こちらのSQLを今流しています。

もう1つ流しておきましょう。@query_IO。CPUバウンドを先に流しました。1分43秒、およそ100秒を要している状態からチューニングしていきます。

もう1つのI/Oバウンドも流しておきましょう。じゃあ戻りますよ。CPUバウンドのSQLは、みなさん覚えておいてください。SQL文を変えない限り、SQLIDは変わりません。

（画面）真ん中の継続時間がSQLの実際の実行時間になります。実行時間が1.7分になっていますけど、計算するとおよそ100秒ほど。どこにどんな時間がかかったのか。

マウスのポインタを合わせると、緑色のところがCPUの処理です。76パーセントの時間を使いました。青いほうは、ユーザーI/Oですね。I/Oをするために待機していた時間で、23パーセント使っています。

なので私は最初に、このSQLはCPUバウンドで、CPUをよく使うSQLだと紹介しました。

リアルタイムでSQL監視がわかる

もう1つ。実際にこのSQLを実行した際に、どれだけディスク上からデータを読んだのか。これも出ています。これもIOバイト数。ここに31ギガと書いてあります。実際に平均I/Oサイズ、1発1発がだいたい1,022キロバイトです。

つまり1メガバイト単位でI/Oしているんですね。それを実際に読み取ってリクエストを31,750回やっています。合計が31ギガバイト。こんなかたちでリアルタイムSQL監視がわかります。

さらにくわしい方だと下に実行計画が並んでいます。31ギガは、画面のここにIOバイト数ってあるんですね。そのため、これをずっと下がっていくとありました。

31ギガバイトはなにかと言うと、SALES表に対してTABLE ACCESS FULL、フルスキャンしているために31ギガ使ったかたちがこのリアルタイムSQL監視からすぐにわかるわけですね。

もう1つ実行していますよね。I/OバウンドのSQLもおそらく実行が終わっています。1分22秒。こちらは、I/OバウンドのSQLIDがdhzで始まります。SQLIDが変わらないところも見ておいてください。

こちらはどうでしょうか。実行時間は、実際にCPUバウンドよりは1.4分と若干短いです。その内訳は少し違いますね。ユーザーI/Oのほうが76パーセントを占めていて、残りの24パーセントがCPUの時間です。つまりI/Oで待機している時間が非常に長いSQLだとわかります。

ただ、どうでしょう。実際にディスクから読み込んだ量は、SALES表は共通していますからCPUバウンドのSQLと同じです。2つのクエリで同じ表にアクセスしているかたちです。このSQLをみなさんと一緒にチューニングしていきたいと思います。

リアルタイムSQL監視は非常に便利なので、実行時間や内訳も、CPUによっているのか、I/Oによっているのかがわかります。ご紹介しませんでしたが、SQL単体での待機イベントも見られます。どの程度データを読んでいるのかも一発でわかります。便利なのでぜひ使ってみてくださいね。

I/Oバウンドも（画面を指して）ここに、今ご紹介したものがあります。このリアルタイムSQL監視はいつ動くのか。開始方法を選択します。次のどちらかの条件を満たす場合に、先ほどのEnterprise Managerで自動的にリアルタイムSQL監視になります。

すべてのSQLを監視するわけではなくて、SQL文がパラレルで実行されるケースでは必ずトラッキングされます。もしくは1回の実行、SQLの実行が5秒以上かかった場合にトラッキングされます。

なので5秒以内と非常に高速、そしてシリアル実行ができる、パラレルではない実行はトラッキングされません。ですので、オンライントランザクション（OLTP）処理等で、SQLを何万SQLパーセクなどで実行する場合にはトラッキングされません。そのため、オーバーヘッドはありませんのでご安心ください。

そのほか、SQL監視アクティブ・レポートの詳細については、下にあるFAQサイトに非常に便利なことが書いてありますので、ぜひ参考にしてみてくださいね。

パーティション化で処理料削減

ミッション2にいきましょう。「パーティション化で処理料削減を狙え！」です。そもそもパーティションとはなんなのか。マニュアルから抜粋した絵です。

画面の一番左側というのはパーティション化されていないものです。1つ下にTable1があります。そこにJanuaryからMarch、1月から3月のデータがすべて入っている状態ですね。上の家みたいなやつは、索引をイメージしているので今は無視してください。

一方、左手の右側にあるTable2。これは大きな表が1つあるんですけども、その中にJanuary、February、Marchというかたちで月ごとにデータが分割されているイメージですね。パーティションはこのように同じ1つの表なんですが、内部的にデータを区切って管理するのがパーティションです。

これはあくまで日付で区切っています。それが画面右側のRANGEパーティショニングで日付や数字で切るパターンです。それ以外にも、LISTパーティションに書いてある、地域ごとに区切るパターンもあります。

あとは画面一番右手のHASHパーティショニングは、入れ物をHASH関数を使って均等にデータを散らすために使うパーティショニングです。

じゃあパーティションとは何の意味があるのか。パーティション・プルーニングという非常に興味深い機能があるんですよね。これは例えば、（画面）1番上のOracle Clientが、select * from TABLE1に対して、where COLORがREDなレコードを引っ張ってくるSELECT文を投げたとしましょう。

このときに大量データなので索引を張っていても、たくさんREDのレコードがあってヒットしてしまうので、索引が効かず意味がないケースです。

そういった場合には、（画面）左手のように全データをバーっと並べます。これがテーブルフルスキャン。その中で赤（RED）にヒットしたものをDBサーバーのCPUを使ってフィルタリングします。ディスクから全部読んで、その上でDBサーバーのCPU使うのは、無駄ですよね。

それがパーティション・プルーニングを使うことで効率良くできる。画面右側の1つの表、ピンク色が同じ表です。SQLは変えません。けれども内部的にRED、GRAY、YELLOWというかたちで分割されています。

そのため、SELECT文、where COLOR = 'RED'といったSQL文が飛んできた際に、Oracle Databaseが自動的にREDといった箱にだけアクセスすることができる。

余計なGRAYとかYELLOWのレコードが入っている箱を読む必要がないんですね。つまり、読み込み対象データを限定することでディスクI/O時間が圧倒的に減ります。しかもフィルタリングするためのDBサーバーのCPUコストもいらない。ですので、このパーティション・プルーニングは非常に有効です。

とはいっても、どうやってパーティションを組めばいいのか。今回のSQLはすごく長かったですよね。SALES表には列がたくさんあります。どの列をキーにしてパーティション切ったらいいのか、わかりませんよね。

Enterprise Managerの効果

そういった場合にはEnterprise Managerを使ってください。Enterprise Managerでアドバイザしてもらいましょう。どんなパーティションがいいのかをアドバイスしてくれる機能があります。

選ぶのはSQLアドバイスです。そうすると「SQLアクセス・アドバイザ」というのがあるので、ここにパーティショニングなどの検証が書いてあります。これで続行します。

直近で実行したSQLですが、画面の「最近のSQLのアクティビティ」を選びます。直近のSQLに対して最適なアドバイスををくれます。以降は、「次へ」のボタンを押すことで進みます。

さらに今回はパーティショニングのアドバイスをもらいます。「パーティショニング」にチェックを入れて、「次へ」のボタンを選びます。あとは、「次へ」と押すだけ。

今回はリネームでパーティショニング化した表をもう用意してあります。今切り替えました。SQLは変えずに、@query_CPUを投げときましょう。こんな感じでEM（Enterprise Manager）を使えばパーティション・アドバイザが出ます。

画面の赤いところが元のコストですね。それに対してパーティション・アドバイザで出たパーティション化をすることで、新規のコストを表す青いバーに、DBサーバーの仕事量を減らすことができるアドバイスが出ています。

今回はこの赤い四角で囲ったところです。SALES表に対してパーティションテーブルにするアクションが出ています。スキーマはSH。パーティションキーはSALES表のタイムIDを使って、どんなパーティションなのか。

PARTITION BY RANGEなので、RANGEパーティションでタイムIDをキーにしてパーティション化するアドバイスがでました。アドバイスだけではなく実際にSQL文も出ます。そのSQL文を使ってパーティション化することができます。

非パーティション表からパーティション表へ変換

12c Release 2で便利な、非パーティション表からパーティション表へ変換するmodify句が追加されました。さらにonlineという、画面の一番下にあるonlineという句を追加することによって、パーティション化中にもアップデート、インサート、デリートを受け付けることができます。非常に便利なので、ぜひ使ってみてください。

パーティション表を用意に対してSQLを投げています。どうでしょう？　処理時間が1分8秒でした。少し速くなりましたね！　

もう1つ、@query_IOも流しておきましょう。

この実行状況のを先ほどと同じようにEMのほうで見ておきましょう。おもしろいんですよ。実行中のものはこうやってステータスがクルクル回るんですね。終わったものはチェックが入っています。

この1個下、CPUバウンドのほうが終わっています。もともとこれですよね。1.7分だったのが、1.1分まで改善している様子が一覧でもわかります。I/OバウンドのSQLは、もともと1.4分かかってたのが、19秒で終わりました。

もう少し見てみましょう。まずCPUバウンドのほう。CPUバウンドのほうはcq2でしたね。SQL変えていませんよ。実際に実行時間は1.1分まで短くなっています。

内訳としては、パーティションを使うことで読み込むデータ量が少なくなりました。I/O待ちの時間が減るわけですから、ユーザーI/Oの比率が3パーセント4パーセントまで短くなっている。CPUを使っている時間だけがずっと長い。そういう状況の変化が見てとれます。

ちなみに、これもおもしろいです。実際にそのSQLを流した際にどれだけCPUリソースを使ったのかが、アクティビティの画面のタブで見られます。1CPUコアにずっと時系列で張り付いて使い切っている状態が見られます。

これは要は1CPUコアのボトルネック状態です。今回お見せしているDBサーバは8コア積んでいて、ハイパースレッドオンになっていますが、実際に1コアしか使っていない。これは次のミッションでやっていきましょう。

I/Oはものすごく改善していましたよね。I/Oバウンドは、こちらはもともと80～90秒が19秒まで短くなっていると見られます。

ただ、まだまだユーザーI/Oの比率が66パーセントと高いです。改善の余地があると思います。IOバイト数はこちらも31ギガバイトから6ギガバイトまで短縮していることが確認できます。

こんな感じですね。もともと108秒、このスライド作ったときとデモで見せている数字は若干違いますけども、だいたい同じです。108秒から72秒と、I/Oのデータ量が削減したからです。

もともとCPUバウンドはCPUに要している時間の割合が大きいので、こちらをパーティション化してデータの読み込み時間が短くなったとしても、全体の効果は大きくありません。

ただ、I/OバウンドのほうはI/O時間に占める割合が大きかったので、ディスクI/O時間が大幅に減ることでSQL全体の高速化も非常に高いことが見えます。

アドバイザー機能も便利

答え合わせは、今回のSQLをよく見てみると赤文字で書いてあるところですね。SALES表のタイムIDに条件がWHERE句が付いています。2014年1月1日よりも前のタイムIDのレコードを引っ張っています。

このSALES表はどんなレコードが入っているかと言うと、2013年から2016年の4年間分のデータが入っています。ということは、このWHERE句は、2013年の4年間分のうちの1年間分のデータだけアクセスすればいいというSELECT文ですよね。

そのため、Enterprise Managerのパーティション・アドバイザの機能を使って、このタイムIDでRANGEパーティションを切る。そうすると年ごとに、正確には月ごとになっています。

月ごとにパーティション化をすることで、SQLの、I/OバウンドもCPUバウンドもこの条件は同じです。そのため、パーティション化することでディスクI/O時間を減らせることをEnterprise Managerがアドバイスしてくれました。

どんなパーティション化にしようかと悩んでいる場合には、ぜひEMでポチポチやってもらえればアドバイスが出ますので、ぜひ試す価値はあると思います。

整理すると、ここまではもともとCPUバウンドのSQLは108秒から72秒。1.5倍速ですね。I/Oバウンドのほうは90秒から19秒なので、4.7倍まで高速化している状態が見てとれます。