データベースの性能を安定させるにはチューニングを行っていく必要があります。今回はどのような時にチューニングが必要なのか、そしてどのようにチューニングすればよいのかを確認していきましょう。
サーバーリソース
データベースはSQLを処理するミドルウェアである為、多くのSQLを処理できる方が性能が良いということができます
ではどのようにしてSQLの処理量を増やすことができるのでしょうか。
データベースサーバのCPUやメモリ、ハードディスクがDB性能の基礎となります。
上記は一般的なサーバのデバイスです。これらがデータベースの性能を担う要素です。
CPU
CPUはSQLの解析や実行で使用します。 CPU性能が高ければ多くのSQLを高速に処理することができます。
CPU使用率が高い場合はSQLの処理量が多くなってきているか非効率なSQLが実行されている可能性があります。
メモリ
メモリはSGAやPGAで使用されます。SGAなどに多くメモリ領域を割り当てると多くのデータブロックをキャッシュに置くことができます。 その為、キャッシュからデータブロックを取得する確率が高くなり、SQL文の結果を高速に返すことができます。 キャッシュに必要なデータブロックが存在しない場合、ディスク上からデータブロックを読み込む必要があるため低速になります。
ハードディスク
ハードディスクはデータを保存しておくためのデバイスです。メモリのアクセス速度と比較すると低速である為、ディスクアクセスが行われないよう、メモリを潤沢に割り当てることが理想です。
現在はHDDより高速なSSDが使用されることが多くなってきています。
上記のサーバリソースを効率的に使用し、多くのSQLを処理できるようDBAはデータベースのチューニングを実施します。
SQLに問題がなくサーバリソースの使用率が高い場合は、CPUやメモリなどを増設することを検討します。
また非効率なSQLが余計な負荷をかけている可能性もあるのでSQLのチューニングが必要である可能性もあります。
サーバリソースが全然使用されておらず、SQLが遅延している場合は何かデータベースに異常が発生している可能性があるため、データベースのチューニングが必要です。
パフォーマンスチューニングでは以下のことを実施します。
CPU使用率が高い場合
→ 非効率なSQLが無いか確認
CPU使用率が低い場合
→ 待機イベントを確認
データベースのパフォーマンスが低下している場合、OSコマンドのvmstatやiostatなどのコマンドを使用して、サーバのどのリソースが使用されているか確認します。
次にデータベースの状態を調べるために待機イベントを確認します。
待機イベント
待機イベントとはSQLが待機した要因をDB内で保持しており、
その待機イベントを確認することで、その時間帯にどのような処理が多く行われて待機していたのかを確認することができます。
待機イベントはV$SESSION_EVENT,V$EVENT_NAMEなどの動的パフォーマンスビューから確認することができます。
またAWRやSTATSPACKでも確認することができます。
待機イベントの数は非常に多いため、すべての待機イベントはご紹介できませんが、一部確認していきましょう。
| buffer busy waits | データベースバッファの空き領域取得に よる待機 |
|---|---|
| db file sequential read | 単一データブロックの読込みによる待機 |
| log file sync | LGWRがログ・バッファをREDOログ・ファイルに書き込む処理による待機 |
上記の他にもたくさんの待機イベントが存在しており、遅延しているときにこの待機イベントを確認するとなぜ遅延しているかを確認することができます。
DBメモリのチューニング
DBのメモリ領域はデータを格納するデータベースバッファキャッシュやSQLの解析情報を格納する共有プール、ソート処理などを行うPGA領域が存在します。
これらのメモリ領域が小さいとディスクへの読み込みが多くなり、SQLの処理が遅くなる可能性があります。
またメモリ領域が大きすぎても、サーバリソースを余分に使用することになるため、適切なサイズにすることが望ましいです。
それでは各メモリ領域が不足した場合どのような状態になるのでしょうか。
共有プール
共有プールはデータベース構造やユーザ情報、SQL情報が格納される領域です。
この領域が不足すると、解析処理のデータを格納できる量が少なくなる為、SQL処理が再度解析される可能性があります。
また深刻な領域不足の場合、ORA-4031を出力し、SQLがエラーになります。
shared_pool_sizeパラメータでサイズを指定することができます。
このパラメータはオンラインで変更が可能です。
データベースバッファキャッシュ
表データが格納されるメモリ領域です。この領域が不足すると、キャッシュできる量が少なくなり、ディスクへ必要なデータを読み込む必要があるため、SQL処理速度が遅くなります。 ディスクI/Oの速度はキャッシュから読み込む速度と比べると、約10万倍遅いといわれています。
共有プールのようにメモリ不足の場合、エラーは発生しませんが、深刻なパフォーマンスダウンが発生する可能性があります。
db_cache_sizeパラメータでサイズを指定することができます。このパラメータはオンラインで変更が可能です。
REDOログバッファ
DB変更情報を格納するキャッシュ領域となります。基本的にはこのメモリ領域は大きなメモリ領域は必要ありません。
DB変更情報はコミットのタイミングや、3秒ごとなど書き込まれるなど定期的に書き込みタイミングがあるため、そこまで大きな領域は必要ありません。
log_bufferパラメータでサイズを指定することができます。このパラメータを変更する場合、DB再起動が必要です。
PGA領域
サーバプロセスに割り当てられる、SQL作業用のメモリ領域です。
ORDER BY句やGROUP BY句などのソート処理で使用したり、索引を作成する為に使用します。その他にユーザセッションの情報も保持しています。
PGA領域が不足した場合、一時表領域を使用します。一時表領域はディスクアクセスの為、SQL処理速度は極端に遅くなります。
pga_aggregate_targetパラメータでPGA領域で使用する合計サイズを指定することができます。
ただしpga_aggregate_targetは指定したサイズを超える場合があります。
このパラメータはオンラインで変更が可能です。
このようにDBメモリはDB処理性能で重要な項目となります。各メモリのサイズは適切なサイズに設定しましょう。
システム特性によるDBチューニング
DBで使用するメモリ領域は様々あり、これを適切に設定するのは困難です。
例えば、日中帯はOLTP系の軽いSQL処理がメインで、夜間帯は集計処理などの時間がかかる重いSQLがメインというシステムがあるとします。 日中帯は軽いSQL処理をこなす為、データベースキャッシュサイズにメモリを多く割り当てる必要があり、
夜間帯は集計処理などによるソートやグループ化などのSQL処理がメインになる為、PGA領域に多くのメモリが必要になるということもあります。
このような特性のシステムは、日中帯と夜間帯で各メモリサイズを変更することが、ベストなメモリ構成です。
しかし現実的に考えると、手動で各時間帯で適切なメモリのサイズに設定することは非常に困難ですね。
ただしこのようにシステムの特性を十分に理解し、適切なチューニングを行ってことのがシステムを安定稼働させるためには必要です。
MEMORY_TARGET
今までチューニングのポイントについてご紹介してきました。
そしてチューニングが難しいということもわかってきたのではないでしょうか。
この難しいメモリチューニングに対しOracleではMEMORY_TARGETという
パラメータだけを設定するだけです。
MEMORY_TARGETパラメータにDB使用する合計のメモリサイズを設定するだけで、
SGA、PGAのメモリをOracleがシステムの特性から自動的に設定してくれます。
SGA_TARGET,PGA_AGGREGATE_TARGETというパラメータもあり、
SGAとPGAで最低保持したいメモリ領域を設定することも可能です。
SGA_TARGETはSGA内の各メモリサイズを自動的に変更管理してくれます。
SGA_TARGETでは以下のパラメータを自動調整します。
・SHARED_POOL_SIZE
・DB_CACHE_SIZE
・LARGE_POOL_SIZE
・JAVA_POOL_SIZE
・STREAMS_POOL_SIZE
MEMORY_TARGETを設定しても、各パラメータを設定することができます。 その場合は、その指定したパラメータは低しきい値(最小値)として確保され、足りない場合は自動的に増やされます。
また以下のパラメータは自動チューニング対象外ですが、SGA_TARAGETの合計値としては計上される為、SGA内の全コンポーネントのサイズがSGA_TARGET以内に収まっている必要があります。
・LOG_BUFFER
・DB_KEEP_CACHE_SIZE
・DB_RECYCLE_CACHE_SIZE
・DB_nK_CACHE_SIZE
では実際に実機で確認してみましょう。
SQL> show parameter memory_target NAME TYPE VALUE ------------------------------------ ----------- --------------------- memory_max_target big integer 0 memory_target big integer 0 SQL> show parameter sga_target NAME TYPE VALUE ------------------------------------ ----------- --------------------- sga_target big integer 768M SQL> show parameter pga_aggregate_target NAME TYPE VALUE ------------------------------------ ----------- --------------------- pga_aggregate_target big integer 256M
現状はSGA_TARGET,PGA_TARGETだけが有効となっており、MEMORY_TARGETは無効になっています。
SQL> alter system set memory_target = 1G ;
alter system set memory_target = 1G
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
エラーとなりました。MEMORY_TARGETはオンラインで変更が可能ですが、MEMORY_MAX_TARGETはDB再起動が必要です。
MEMORY_MAX_TARGETが0の場合、MEMORY_TARGETを設定すると同じ値が設定されます。
SQL> alter system set memory_max_target = 3G scope=spfile; System altered. SQL> startup force ORA-00845: MEMORY_TARGET not supported on this system
startup forceはDB強制終了後、DBを起動します。インスタンスリカバリが必要となるため、本番環境は使用しないでください。
ORA-00845で起動に失敗しました。実はMEMORY_TARGETはOS上の/dev/shmの領域を使用します。この領域はメモリ領域であり、
Oracleはこの領域にMEMORY_TARGETのメモリ領域を確保します。
$ df -h
Filesystem Size Used Avail Use% マウント位置
XXXG XXG XXG XX% /
tmpfs 2.9G 152K 2.9G 1% /dev/shm
ディスク領域を確認すると/dev/shmは2.9Gしかない為、memory_max_targetを3Gに設定使用としエラーとなりました。それでは今回は2GBにしてみましょう。
SQL> alter system set memory_target = 2G scope=spfile;
alter system set memory_target = 2G scope=spfile
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
DBが起動していないので、パラメータの変更がエラーとなってしまいました。
この場合は、一旦PFILEを作成して、SPFILEを再作成しましょう。
SQL> create pfile='/home/oracle/tmp/init.ora' from spfile;
File created.
その後、init.oraファイルに記載されているmemory_targetを削除しmemory_max_targetを2GBに設定します。
その後再度SPFILEを再作成します。
SQL> create spfile from pfile='/home/oracle/tmp/init.ora'; File created. SQL> startup ORACLE instance started. Total System Global Area xxxxxxxxxx bytes Fixed Size xxxxxxx bytes Variable Size xxxxxxxxx bytes Database Buffers xxxxxxxxx bytes Redo Buffers xxxxxxx bytes Database mounted. Database opened. SQL> show parameter memory NAME TYPE VALUE ------------------------------------ ----------- ------------------- memory_max_target big integer 2G memory_target big integer 0
memory_max_targetだけ設定ができました。それではmemory_targetをオンラインで変更してみましょう。
SQL> alter system set memory_target = 2G; System altered. SQL> show parameter memory NAME TYPE VALUE ------------------------------------ ----------- -------------------- memory_max_target big integer 2G memory_target big integer 2G
設定できました。これでOracleは合計2GBのメモリ領域をSGA,PGAに動的に割り当てます。またSGA_TARGET,PGA_AGGREGATE_TARGETもここに設定されているため、SGAは765MBは最低限ほしされ、PGAは256MB保持されます。
いかがでしたでしょうか。メモリパラメータの設定方法を書いていきました。