感覚的に理解するOracleDB Part7

はじめに

今回はデータベース高速化の概要について解説します。DB高速化はシステムを使うユーザにとって重要となる部分です！まずは、その重要性について解説します！

足を引っ張るのは誰だ！

ここでは例として、私たちがECサイト等で買い物をする時のことを考えてみます。
おそらくECサイトのアーキテクチャは以下のような感じではないでしょうか？

WEBサーバやAPPサーバ、DBサーバとそれぞれを繋ぐネットワークあたりが構成要素となるでしょう。

さてここで、私たちがECサイトを訪問した際、WEBサイトの表示に10秒も20秒もかかったとします。そのようなサイトにまた訪問したいと思うでしょうか？絶対にしたくないですね！このような単純な例から分かる通り、システムの応答速度はユーザの満足度に直結する要素です。そのため、ITエンジニアはシステム全体の応答速度を監視し、あまりにも速度が遅かったらチューニングをすることが求められます。

そして、システム全体として早くなければいけないということは、どれか1つの要素でも遅いものがあってはいけないということです。そのため、ネットワークやサーバ、DBの各要素で速度を気にしなければなりません。今回はこれらの速度のチューニングのうちDBを扱うということで、実際には他の要素のチューニングも行わなければいけないということについては注意点として挙げておきます。

DBがボトルネックとなるのは？

とはいえ、DBの部分がボトルネックになりやすいのも事実のようです。その主な理由はハードディスクの読み書きが発生するためです。

上の図のように、ネットワークやサーバは主に光や電気という高速なもので動いているにも関わらず、DBはデータの読み書きに回転を伴うディスクを扱っていたりします。この物理的な回転が光や電気に比べて遅いためにDBはボトルネックになり得ます。

DBの速度を上げる方法について考える

では、処理が遅くなりやすいDBをボトルネックとしないためには、どのようなチューニングが必要になるでしょうか？Part5-1で紹介したSELECT文の実行順序をもとに考えてみましょう！

処理速度を変えそうな要素や手順はどこでしょうか？
1つ目は「2.SQLを解析する」の部分です。SQLを解析した結果、実行計画が立てられ、実行計画の手順通りにデータアクセスが行われます。そのため、実行計画が変われば、データアクセスの速度も変わりそうです。

2つ目はSGAです。SGAは解析結果を再利用したり、ディスク内のファイルを取りに行かなくてもいいように、データの中身を保持しておくものでした。つまり、ここのチューニングで解析時間の短縮やHDDの回転待ち時間の短縮が可能です（SGAが実行時間を大幅に短縮するといっても過言ではない！）。

3つ目は「4. データブロックを読み出す」の部分です。一番遅くなりそうなHDDからの読み書きの部分ですね！もしこの部分について、何らかの適切な形で複数のHDDを同時に扱えたらどうでしょうか？複数のHDDに読み書きが分散されるので、I/Oスピードは早くなりそうです！

4つ目は、PGAです。PGAでは、SQL実行結果のデータをユーザに返す前（fetch前）にソートなどの処理を行う場合があるのでした。このとき、もしもPGA内に収まりきれないデータ量があった場合、ディスクの力（TEMP表領域）を借りることになります。ディスクを使うということは、遅くなりうるということなので、PGAのチューニングも大事そうですね！

5つ目は、SQLの書き方そのものです。例えば不要なソート処理を挟んだり、遅くなりうる表結合を行なったりすると、その分の処理時間が加算されます。従って、処理効率の良いSQLを書く必要があります。

では、どうすれば良いのか？

ここまででDBの速度が上がりそうな要素を５つ述べました。ではそれらの要素を用いて速度上昇を達成するためには、どのようなチューニングを行えば良いのでしょうか？その答えの一例を以下に示します。

オプティマイザに最新の統計情報を与える

オプティマイザとは、SQLの実行計画を最適化してくれる機構です。このオプティマイザにこれまでのSQLの実行に関する情報を与えると、その情報を参考にしていい感じの実行計画を作ってくれます。ここで重要になるのが、オプティマイザに与える情報です。この情報の質が良いほどオプティマイザは良い実行計画を作ってくれるので、最新の統計情報を与え続けるということは大事な要素になります。

初期化パラメータでSGAの大きさを変える

SGAは、バックグラウンドプロセスが使う作業台のようなものなので、大きい方が作業効率は上がります。ただし、仕事量に対してSGAが大きすぎてもスペースの無駄になってしまうでしょう。そのため、適切な大きさのSGAを用意する必要があります。では、適切なSGAの値はどうやったら分かるでしょうか？

オラクルDBでは、この問題のヒントとしてSGAの構成要素である共有プールやDBバッファキャッシュのアドバイザを提供しています。DB管理者はこれらのアドバイザを利用して、SGAの大きさを決めることができます。

ここでは、共有プールのアドバイザを用いて、そのメモリサイズを決める例を示します。まず、以下のSQLで共有プールアドバイザを利用します。

SELECT shared_pool_size_for_estimate as pool_size, estd_lc_time_saved 
FROM v$shared_pool_advice;

# 実行結果
# POOL_SIZE ESTD_LC_TIME_SAVED
# --------- ------------------
#       464                122
#       480                126
#       496                128
#       512                130
#       528                132
#       544                132
#       560                132

このSQLは「もし、共有プールのサイズをPOOL_SIZEにしていたら、これまでESTD_LC_TIME_SAVED秒節約できていた」ということを示しています。ということは、このDBの場合「もし、共有プールのサイズを528MBにしていたら、これまで132秒節約できていた」ということです。そして、528MB以降節約できる秒数は変わらないので、「じゃあとりあえず共有プールは528MBにしよう！」とチューニングできるわけです。実際にチューニングをするときは、以下のように初期化パラメータを利用します。

ALTER SYSTEM SET shared_pool_size=528M SCOPE=MEMORY;

上記コマンドにより、共有プールの大きさを変えることができました。ここで、その設定のまま数ヶ月が経ったのち「実際に共有プールの大きさ変更がDB高速化に役立っているか見たい」という思いが出てきたとします。このとき、SQLの応答速度が要件に沿っているかを見てみるという方法もありますが、それだけではDBが最適なパフォーマンスを発揮しているかということはわかりません。このような場合に役立つのがキャッシュヒット率です。キャッシュヒット率とは、全てのデータ読み込みのうち、メモリ内のキャッシュを活用できた割合のことですが、簡単にいえばどれだけSGAが役に立ったかを表したものです。

ここでは、共有プール内のライブラリキャッシュについて、キャッシュヒット率をみてみましょう！

SELECT sum(gethits)/sum(gets) as cache_hit FROM v$librarycache;

# 実行結果
#  CACHE_HIT
# ----------
# .740222181

このDBの場合、キャッシュヒット率は約74％ということです。この数値を見て、SGAの大きさ（特に、共有プールのライブラリキャッシュの大きさ）について適切かどうかを判断していきます。例えば、「キャッシュヒット率は9割を超えることが望ましい」のように設定されていれば、共有プールの大きさを見直さなければいけなくなります。

このように、SGAはDBを稼働させながら動的にチューニングしていきます。

索引・表・読み取り専用表を別のディスクに分ける

「複数のディスクを用いて表領域を管理し、かつ各表領域のディスクへのアクセス頻度を均等にしよう！」ということです。そうすれば、各ディスクが並列して同じくらいのI/Oを行なってくれるため、DB高速化につながるわけです。とはいえ、各表領域のディスクへの読み書き数が分からなければ、そんなことはできそうにありません。では、表領域のディスクへの読み書き数がわかるような診断をoracleDBは提供してくれているのでしょうか...？もちろん提供してくれています！

SELECT d.name, f.phyrds, f.phywrts FROM v$datafile d
JOIN v$filestat f ON d.file# = f.file#

# 実行結果の例
# NAME                      PHYRDS      PHYWRTS
# ------------------- ------------ ------------
# USERS01.DBF                  436          440
# USERS02.DBF                   26          640

上のSQLでは、PHYRDSがディスクへの読み込み回数、PHYWRTSが書き込み回数を示しています。このような情報を参考に、「読み取りが多いUSER02.DBFはディスクAに配置しよう！」などなど、表領域の配置を決めていけば良さそうです！

初期化パラメータでPGAの大きさを変える

あるSQLを実行した結果PGA内に収まりきれないデータ量をあった場合、ディスクの力（TEMP表領域）を借りることになることは先に述べました。そのためPGAもSGA同様、無駄なスペースを生まない範囲でなるべく大きい方が良さそうです。そのようなPGAのサイズを決めるためのヒントとして、ディスクソート率が挙げられます。

SELECT d.value/m.value as disk_sort_ratio FROM v$sysstat , v$sysstat d 
WHERE m.name='sorts (memory)' AND d.name='sorts (disk)';

# DISK_SORT_RATIO
# ---------------
#      .078233212

この場合、ディスクソート率は約7.8％です。このディスクソート率を効率よく小さくするようにチューニングを行なっていけば良いわけです。

索引を適切に使う・オプティマイザにヒントを与える

目的の処理を行うSQLの書き方は複数存在し得ます。そのため、表の書き方を工夫して、索引を用いて表結合を行なったり、ヒント句を活用して内部処理を変えたりすることでDB速度を上げることが可能です。SQLのパフォーマンスについては、実行計画のコストを見ることやAUTOTORACE統計を確認することで確認することができます。

このように、オラクルが提供する様々な数値や情報を参照しながら、適切なKPI（目標数値）を決め、そのKPIに合わせてチューニングしていくということが重要そうです。

まとめ

今回は以下のことについて解説しました。
・顧客が満足するITシステムを構築するために、各システム構成要素をチューニングしなければいけない
・DBは、物理的に回転するディスクを含むため、ボトルネックになりやすい
・SQLの実行計画を作るオプティマイザには最新の統計情報を与えるべきである
・SGAはアドバイザやキャッシュヒット率を参考にしてチューニングする
・複数の表領域を異なるディスクに分けて配置し、I/O負荷を軽減する
・PGAは、ディスクソート率を参考にしてチューニングする
・SQLのパフォーマンスを確認し、索引やヒント句を適切に用いる