Oracle Databaseのメモリ関係で、SGAやPGAという言葉がよく出てきます。
しかし、
- SQLを実行するとSGAとPGAはどう使われるのか？
- Buffer CacheとPGAは何が違うのか？
- ORDER BYやGROUP BYではどこに負荷がかかるのか？
あたりが少し分かりづらかったので、SQL実行の流れと合わせて整理します。
1. SGAとPGAの役割
まず、SGAとPGAの大きな違いを整理します。
Oracle Instance
│
├── SGA
│ ├── Database Buffer Cache
│ ├── Shared Pool
│ └── Redo Log Buffer
│
└── Server Process
└── PGA
├── Sort Area
├── Hash Area
└── その他の作業領域
SGA
SGA（System Global Area）は、Oracle Instanceで複数のプロセスから共有されるメモリ領域です。
代表的なものが、
- Database Buffer Cache
- Shared Pool
- Redo Log Buffer
です。
PGA
PGA（Program Global Area）は、Server Processなどが使用するプロセス固有のメモリ領域です。
SQLの実行では、ソートやハッシュ処理などの作業領域として利用されます。
ざっくり、
SGA
→ Oracle Instanceで共有されるメモリ
PGA
→ プロセスごとに使用される作業用メモリ
と考えると分かりやすいです。
2. SELECT文を実行すると何が起きるのか
例えば、以下のSQLを実行するとします。
SELECT *
FROM employee
WHERE employee_id = 100;
SQL実行の流れをかなり単純化すると、
SQL
↓
Server Process
↓
Shared Pool
↓
Database Buffer Cache
↓
必要なデータブロックを読み込む
↓
SQLの結果を返す
というイメージになります。
Shared Pool
SQLを実行すると、SQLの解析や実行計画などに関する情報がShared PoolのLibrary Cacheなどで扱われます。
すでに同じSQLの情報が存在すれば、それを再利用できる場合があります。
Database Buffer Cache
実際のデータを読み込む際には、Database Buffer Cacheが使用されます。
Data File
│
│ データブロックを読み込む
▼
Database Buffer Cache
│
▼
SQL処理
必要なデータブロックがBuffer Cacheに存在しなければ、Data Fileから読み込む必要があります。
つまり、SELECT文でも、
CPU
＋
SGA（Buffer Cacheなど）
＋
ディスクI/O
などのリソースが関係します。
3. ORDER BYやGROUP BYではPGAが活躍する
では、次のようなSQLの場合はどうでしょうか。
SELECT *
FROM employee
ORDER BY salary;
このようなSQLでは、取得したデータを並び替えるためのソート処理が必要になります。
ソートなどの処理にはPGAの作業領域が利用されます。
SELECT
│
▼
データ取得
│
▼
PGA
│
Sort Area
│
▼
ORDER BY
ただし、
PGAだけですべてのソートが完了するとは限りません。
PGAの作業領域だけでは処理しきれない場合、TEMP表領域を利用してディスク上で処理することがあります。
ORDER BY
│
┌───────┴───────┐
│ │
▼ ▼
PGAで処理 TEMPを使用
（メモリ） （ディスク）
そのため、大量データに対するORDER BYなどでは、
- CPU
- PGA
- TEMPへのI/O
など、複数のリソースが関係してきます。
4. SQLの性能を見るときの考え方
SQLの性能問題を考えるときは、
SQL
│
├── CPU
│
├── SGA
│ ├── Buffer Cache
│ └── Shared Pool
│
├── PGA
│ ├── Sort
│ └── Hash
│
└── Disk I/O
├── Data File
└── TEMP
というように、SQLを実行するためにどのリソースが使われているのかを見ると整理しやすくなります。
例えば、
SELECT *
FROM huge_table
ORDER BY created_at;
のようなSQLでCPU使用率が高くなっていたとしても、
「ORDER BYだからCPUが高い」
と単純に決めつけることはできません。
データ量、実行計画、インデックスの有無、PGAの使用状況、TEMPへの書き出しなど、複数の要因を確認する必要があります。
Oracleの性能分析では、こうした情報をAWRやStatspackなどの性能情報から確認していくことができます。
※Statspackについては、別の記事で詳しく整理する予定です。
ちなみに私の現職では、SE環境のためstatspackを利用して、どのSQLに負荷がかかっているかについて調査していました。
(EE環境であればAWRなどもっと便利に調査できるため、いつかは使用してみたいです。)
5. まとめ
今回のポイントをまとめます。
SGA
→ Oracle Instanceで共有されるメモリ
PGA
→ プロセスごとに使用される作業領域
Buffer Cache
→ Data Fileのデータブロックをキャッシュ
PGA
→ SortやHashなどの作業領域として利用される
TEMP
→ PGAだけでは処理しきれない場合などに利用される
特にSQLの性能を見るときには、
SQL
↓
実行計画
↓
├── CPU
├── SGA / Buffer Cache
├── PGA
└── Disk I/O / TEMP
というように、SQLとCPU・メモリ・I/Oを関連づけて考えることが重要です。
Oracleの性能問題を調査するときは、単純に「SQLが遅い」と考えるのではなく、
そのSQLが、CPU・SGA・PGA・ディスクI/Oのどこに負荷をかけているのか？
という視点を持つと、原因を追いやすくなります。