SQL の実行に時間がかかっている原因の一つとして、実行している SQL に対して、必要なメモリが不足してしまい、割当てができない状態になっていることがあります。SQL の実行には、対象のDBユーザに割当てられたリソースプールが利用されます。リソースプールは、メモリ割当て、同時実行、キュー（Queue）タイムアウト、優先順位の制御などを行い、デフォルトは GENERALプールが割当てられています。
以下は物理メモリ 128GB のサーバで、同一SQLを 10多重で同時実行した場合の実行例です。メモリ割当ての待ち状態となっている SQL はキューに格納され、V_MONITOR スキーマ内の RESOURCE_QUEUES システムテーブルを利用することで確認できます。
※GENERAL：ユーザークエリ実行時にデフォルトで使用されるプールです。他のプールでリソースが足りない場合、必要に応じてGENERALプールのメモリが使用されます。また、システムメモリの一部もこのプールを使用します。
RESOURCE_QUEUES の実行例
新規に作成した testプールを DBユーザに割当てています。キューを確認すると、5個の SQL が「memory_requested_kb=730131」となっており、メモリ不足で割当て待ちとなっています。|
1 2 3 4 5 6 7 8 9 10 |
dbadmin=> select * from resource_queues; node_name | transaction_id | statement_id | pool_name | memory_requested_kb | priority | position_in_queue | queue_entry_timestamp ----------------+-------------------+--------------+-----------+---------------------+----------+-------------------+------------------------------- v_ssb_node0001 | 45035996273740176 | 2 | test | 730131 | 0 | 1 | 2018-11-13 14:03:59.780098+09 v_ssb_node0001 | 45035996273740175 | 2 | test | 730131 | 0 | 2 | 2018-11-13 14:03:59.783441+09 v_ssb_node0001 | 45035996273740174 | 2 | test | 730131 | 0 | 3 | 2018-11-13 14:03:59.788056+09 v_ssb_node0001 | 45035996273740181 | 2 | test | 730131 | 0 | 4 | 2018-11-13 14:03:59.792429+09 v_ssb_node0001 | 45035996273740180 | 2 | test | 730131 | 0 | 5 | 2018-11-13 14:03:59.800246+09 (5 rows) Time: First fetch (5 rows): 8.650 ms. All rows formatted: 8.729 ms |
[RESOURCE_QUEUES]
| 列名 | 内容 |
|---|---|
| NODE_NAME | ノード名 |
| TRANSACTION_ID | トランザクションID |
| STATEMENT_ID | ステートメントID |
| POOL_NAME | リソースプール名 |
| MEMORY_REQUESTED_KB | リクエストされたメモリ量(KB) |
| PRIORITY | プールを定義するときに指定した優先パラメータの値 |
| POSITION_IN_QUEUE | キュー内のリクエストの順番 |
| QUEUE_ENTRY_TIMESTAMP | リクエストがキューイングされたタイムスタンプ |
なお、割当て待ちを解消する方法としては、以下の対応が考えられます。
・DBユーザに割当てたリソースプールのメモリサイズ変更
・メモリ使用量を抑止するために SQL チューニングの実施
・H/W の物理メモリ自体が不足している場合はメモリ増設の実施
