A SQLパフォーマンスのトラブルシューティング

この付録では、SQL問合せにおいてパフォーマンス問題の原因となる可能性のあるSQLコーディング・パターンのリストを示します。

• 副問合せでリテラル定数として定義され、後で主要問合せで結合される列
• LEFT OUTER JOINの冗長な表
• ファクタリングされたWITH副問合せ間の結合条件が多すぎる
• データ・セキュリティ述語が冗長な副問合せ内にラップされている
• 相関スカラー副問合せの集計関数
• CLOBのかわりにXMLTYPEデータ型を使用
• 単一SQLでの大量のUNION (ALL)問合せブロック
• WHERE句の述語のファンクション・コール
• SELECT句およびWHERE句の長いCASE式
• SQL句の長いINリスト
• DISTINCT修飾子またはROWNUMキーワードを使用したスカラー副問合せ
• SELECT句のカスタムPL/SQLコール

副問合せでリテラル定数として定義され、後で主要問合せで結合される列

単一の副問合せで列として定義され、後で主要問合せの結合で使用されるリテラル定数がある場合は、かわりに直接フィルタとして渡すことを検討します。このような使用方法については、次の例を参照してください。

(SELECT
	...
	UPPER('HXT') AS DUMMY
	...
) V1,
(SELECT
	T2.C2 AS C2
 FROM T2
) V2
WHERE
	V1.DUMMY = V2.C2
...

主要問合せに直接渡すのがより効率的なSQL設計です。

(SELECT
	...
) V1,
(SELECT
	T2.C2 AS C2
 FROM T2
) V2
WHERE
 V2.C2 = UPPER('HXT')
...

LEFT OUTER JOINの冗長な表

左側外部結合(LOJ)を使用して2つの表を結合するが、LOJのオプション側の表から属性を選択しない場合、LOJの一部が冗長になることがあります。次の例を確認してください。

SELECT
  b.book_title,
  b.author,
  b.language
FROM
  books b,
  book_languages l
WHERE
  b.language = l.language (+);

表BOOK_LANGUAGESはOUTER結合のオプション側にあり、そこからの列はSELECT句にありません。前述の問合せは、表とLOJの両方を削除しても機能的に同等です(つまり、同じ正確な結果セットが生成されます)。

SELECT
	b.book_title,
	b.author,
	b.language
FROM
	books b;

冗長なOUTER結合表を使用した複雑なSQL問合せでは、解析時および実行時にパフォーマンス・オーバーヘッドが増加する可能性があります。一般に、外部結合は普通の内部結合よりも処理が困難であるため、オプティマイザは未使用の外部結合表を削除できない場合があります。機能を慎重に検証して、レポート内のこのような結合および表の削除による影響を評価するようにしてください。

ファクタリングされたWITH副問合せ間の結合条件が多すぎる

2つのファクタリングされたWITH副問合せを結合するときに使用する結合条件が多すぎると、オプティマイザによってそれぞれの表の結合カーディナリティが低いと見積もられ、最適な実行計画が選択されないことがあります。次の例を参照してください。ここでは、2つのファクタリングされたWITH副問合せ間の結合が多すぎるために、最適には生成されないSQLパターンを示しています。

FROM SAWITH1 D1
  FULL OUTER JOIN SAWITH2 D2
  ON SYS_OP_MAP_NONNULL(D1.c3)   = SYS_OP_MAP_NONNULL(D2.c3)
  AND SYS_OP_MAP_NONNULL(D1.c5)  = SYS_OP_MAP_NONNULL(D2.c5)
  AND SYS_OP_MAP_NONNULL(D1.c12) = SYS_OP_MAP_NONNULL(D2.c12)
  AND SYS_OP_MAP_NONNULL(D1.c14) = SYS_OP_MAP_NONNULL(D2.c14)
  AND SYS_OP_MAP_NONNULL(D1.c11) = SYS_OP_MAP_NONNULL(D2.c11)
  AND SYS_OP_MAP_NONNULL(D1.c2)  = SYS_OP_MAP_NONNULL(D2.c2)
  AND SYS_OP_MAP_NONNULL(D1.c13) = SYS_OP_MAP_NONNULL(D2.c13)
  AND SYS_OP_MAP_NONNULL(D1.c15) = SYS_OP_MAP_NONNULL(D2.c15)
  AND SYS_OP_MAP_NONNULL(D1.c9)  = SYS_OP_MAP_NONNULL(D2.c9)
  AND SYS_OP_MAP_NONNULL(D1.c10) = SYS_OP_MAP_NONNULL(D2.c10)
  AND SYS_OP_MAP_NONNULL(D1.c8)  = SYS_OP_MAP_NONNULL(D2.c8)
  AND SYS_OP_MAP_NONNULL(D1.c7)  = SYS_OP_MAP_NONNULL(D2.c7)
  AND SYS_OP_MAP_NONNULL(D1.c18) = SYS_OP_MAP_NONNULL(D2.c18)
  AND SYS_OP_MAP_NONNULL(D1.c21) = SYS_OP_MAP_NONNULL(D2.c21)
  AND SYS_OP_MAP_NONNULL(D1.c4)  = SYS_OP_MAP_NONNULL(D2.c4)
  AND SYS_OP_MAP_NONNULL(D1.c6)  = SYS_OP_MAP_NONNULL(D2.c6)
  AND SYS_OP_MAP_NONNULL(D1.c22) = SYS_OP_MAP_NONNULL(D2.c22)
  AND SYS_OP_MAP_NONNULL(D1.c20) = SYS_OP_MAP_NONNULL(D2.c20)
  AND SYS_OP_MAP_NONNULL(D1.c16) = SYS_OP_MAP_NONNULL(D2.c16)
  AND SYS_OP_MAP_NONNULL(D1.c17) = SYS_OP_MAP_NONNULL(D2.c17)
  AND SYS_OP_MAP_NONNULL(D1.c19) = SYS_OP_MAP_NONNULL(D2.c19)

SQLロジック内のすべての結合を慎重に確認し、機能要件に対応するための必要最小限に抑えます。

データ・セキュリティ述語が冗長な副問合せ内にラップされている

レポートのデータを保護するには、徹底した分析とSQL問合せの慎重なコーディングが必要です。SQLでレポートを確実に保護するために追加するデータ・セキュリティ述語(DSP)またはセキュリティ句は、SQL実行にパフォーマンス上の影響を及ぼす可能性があります。たとえば、ビュー・オブジェクトのセキュリティ句を組み入れるDSPを他のSQLから再利用する場合は、次に示すパターンになる可能性があります。

(SELECT ... 
   FROM T1, 
       (SELECT ... 
          FROM T2 ... 
         WHERE (DSP predicate1) OR (DSP predicate2)
        )
  WHERE ...

この例では、表T2は別の副問合せで2つのDSP述語を使用して保護されています。最適化プロセス全体の一環として、Oracleオプティマイザは主要問合せを最適化する前に、各インライン・ビュー(副問合せ)の再帰的最適化を実行します。このようなパターンは問合せの解析時間に影響し、効率性の低い実行計画が生成される可能性があります。次のようなより効率的なパターン・リライトの使用を検討してください。

(SELECT ... 
   FROM T1, 
        T2, ...
  WHERE (DSP predicate1) OR (DSP predicate2))
        ...

相関スカラー副問合せの集計関数

スカラー副問合せのMAX/MIN/COUNTなどの集計関数は、代替リライトを実行することによって、安全かつ効率的に置換できます。次の例を参照してください。

--Sub-optimal pattern:

SELECT t1.c1,
       (SELECT MAX(t2.c2) as maxc2
          FROM t2
          WHERE t2.id=t1.id
        ) as c2
FROM t1;

--Optimal pattern:

SELECT t1.c1,
       t2alias.max_c2
FROM t1,
     (SELECT MAX(t2.c2) as max_c2
             T2.id as id
        FROM t2
       GROUP BY t2.id
     ) t2alias
WHERE t2alias.id=t1.id;

スカラー副問合せは、囲み問合せブロックの行ソース(ここではt1)によって返されるすべての行に対して実行されます。つまり、集計されるMAXは、主要問合せでフェッチされた行数と同じ回数実行されるため、パフォーマンスが低下します。集計されるMAXを主要問合せのFROM句に移動すると、データは1回集計され、問合せ全体が高速になります。

ノート:

囲みブロックによって返される行(ここではt1)の数が中程度または少なく、スカラー副問合せが選択的に索引付けされた列に関連付けられている場合は、集計操作を相関スカラー副問合せ内に保持できます。

CLOBのかわりにXMLTYPEデータ型を使用

XMLデータの格納にCLOBデータ型を使用しないでください。レポート実行時におけるXMLTYPE関数を使用した即時のCLOBからXMLへの変換は、非常に負荷がかかる可能性があります。かわりに、XMLTYPEを使用してXMLドキュメントをデータベースに保存することを検討してください。

単一SQLでの大量のUNION (ALL)問合せブロック

複数のUNION (またはUNION ALL)問合せブロックを含む単一のSQLは、慎重に分析してUNIONを少数の副問合せに統合する必要があります。UNIONを過度に使用することは、ロジックの冗長性とSQLの複雑さを強く示す兆候です。デフォルトではそれぞれのUNION問合せブロックが1つずつシリアルに実行されるため、SQL文がかなり大きくなり、データベースでの解析および実行に時間がかかります。/*+ PARALLEL */ヒントを強制しても、複数のUNIONブランチを同時に実行することはできません。ヒント/*+ PQ_CONCURRENT_UNION */は、UNIONブランチの並列実行に役立つ可能性がありますが、特にUNIONブランチに非常に高負荷な表があり、選択的なフィルタがない場合、データベース・ワークロードが増加する可能性があります。

ほとんどの場合、UNIONブランチは、SELECTリスト内の列に対するフィルタがわずかに異なるまたは式が異なるといった小さな違いはあっても、ほぼ同じであることがあります。1つまたは2つの共通因子をファクタリングしてWITH副問合せに切り離し、それらを機能要件に従って再利用することで、複数UNIONの問合せを大幅に単純化することを検討してください。このようなアプローチでは、論理読取りが非常に少なくなり、データベースの影響が小さくなって、問合せのパフォーマンスが向上します。ファクタリングされた同じWITH副問合せが複数回呼び出された場合、Oracleはその結果セットを一時セグメントにマテリアライズし、SQL全体で使用します。

WHERE句の述語のファンクション・コール

WHERE句の式はフィルタ式および結合式を使用して、データ・ソース(表、ビュー、インライン・ビュー)からの行を制約します。索引付き属性を使用したフィルタ/結合操作の後の見積り行数が少ない場合、オプティマイザは索引を選択して必要なデータ・ブロックに直接アクセスします。フィルタ/結合式の列にSQLまたはPL/SQLファンクションを適用すると、索引の使用が無効になり、表アクセスが全表スキャンに戻ります。

または、ファンクション索引(FBI)が定義されているが、フィルタ式または結合式の結合述語で正確なファンクションを使用していない場合、オプティマイザでもほぼ必然的に全表スキャンが選択されます。たとえば、表PER_ALL_PEOPLE_FのPERSON_NUMBER列が、UPPER(PERSON_NUMBER)を使用したファンクション索引(FBI)により索引付けされているとします。WHERE句でのPERSON_NUMBERの適切な使用方法は、UPPER(PERSON_NUMBER)になります。WHERE句でのUPPERおよびTRUNCの推奨される使用方法に関する2つの別個の項を参照してください。

次のケースについて検討します。

SELECT * FROM XLA_AE_HEADERS WHERE AE_HEADER_ID = '1511989';

主キー列AE_HEADER_IDに一意の索引が定義されているため、INDEX RANGE SCAN操作を使用して必要なデータに迅速かつ効率的にアクセスできます。

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |     1 |   215 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| xla_ae_headers    |     1 |   215 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | XLA_AE_HEADERS_U1 |     1 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("AE_HEADER_ID"=1511989)

関数TO_CHAR()がAE_HEADER_ID列で使用されている場合、計画は次のように変わります。

SELECT * FROM XLA_AE_HEADERS WHERE TO_CHAR(AE_HEADER_ID) = '1511989';
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |   215 |  5788   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| xla_ae_headers |     1 |   215 |  5788   (1)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR("AE_HEADER_ID")='1511989')

機能設計によってWHERE句でのSQLまたはPL/SQLファンクションが必要になる正当なケースが存在する場合があります。たとえば、WHERE句の集計関数(MAX、MIN、COUNT、AVG)が機能ロジックに必要になる場合があります。このような場合は、最適なパフォーマンスを確保するために、索引をサポートしている他の列に効果的なフィルタがあることを確認してください。

SELECT句およびWHERE句の長いCASE式

複雑なCASE式は、様々な方法で問合せのパフォーマンスに影響する可能性があります。SELECTリストにあるCASE式は、CPU負荷の高い操作です。WHERE句のCASE式(フィルタおよび結合述語)により、結合カーディナリティの見積りが不正確になり、コストが誤って計算されることがあります。その結果、最適ではない結合方法を選択することで問合せの実行時間が長くなる可能性があり、ORA-01555 (「スナップショットが古すぎます。」)エラーが発生し、SQLによって使用されるTEMP表領域が非常に大きくなる可能性が高まります。SQLで大規模なCASE句を注意深く調べ、ロジックを簡略化する選択肢を検討します。次の最適ではないCASE構造とリライトされたCASE構造を参照してください。

-- Sub-optimal structure:

(CASE WHEN .. THEN 
   (CASE WHEN .. THEN 
      (CASE WHEN .. THEN
       (...)
      END)
    END)
 ELSE ... END)

-- Rewritten optimal structure:

(CASE WHEN ... THEN
      WHEN ... THEN
      WHEN ... THEN
      ELSE ...
 END)

SQL句の長いINリスト

バインド・パラメータを介して複数の値をSQLに渡すオプションは、レポートの柔軟性および汎用性を高めます。ただし、渡される値の数が制限されていない場合、非常に多数のINリスト値を持つSQLが生成され、場合によっては、Oracleの内部的なINリスト値の最大限度である1,000に達する(および、ORA-01795「リストに指定できる式の最大数は1000です。」が表示される)可能性があります。

Oracleオプティマイザは、INリスト式をブールのOR式に変換します。たとえば、COL_A IN (1、2、3)はCOL_A = 1 OR COL_A = 2 OR COL_A = 3に変換されます。表とその列に対する統計の収集方法によっては、オプティマイザがそのINリスト式に対して誤ったカーディナリティ見積りを生成する場合があります。INリスト内のリテラルが多ければ多いほど、実際のカーディナリティからの逸脱が大きくなります。カーディナリティが正しくないと、オプティマイザでより結合の少ない方法が選択される可能性があります。次に示すCASE句の長いINリストの例を参照してください。

CASE WHEN d1.c40 NOT IN (
	'CALL',
	'CALL_IB',
	'DEMAND_GENERATION',
	'DEMO',
	'DEMO_IN_PERSON',
	'DISCOVERY',
	'DISCOVERY_PERSON',
	'EMAIL',
	'EMAIL_ELOQUA_ENGAGE',
	'EMAIL_ELOQUA_ENGCAMPAIGN',
	'EMAIL_IB',
	'EMAIL_OUTLOOK',
	'INPERSON_INTRO_MEETING',
	'INPERSON_MEETING',
	'INTRO_MEETING',
	'MANUAL_CALL',
	'MEETING',
	'RESEARCH',
	'SALES_CHAT',
	'SOCIAL_MEDIA',
	'TODO',
	'WORKSHOPS'
	) THEN ...

SQL問合せに長いINリストが生成されないように、レポートに渡されるパラメータおよび値リストを慎重に設計します。このようなIN句が生成され、パフォーマンスに影響することになるため、長いチェックリストやすべてをチェックするオプションは作成しないでください。すべてを渡して長いINリストに伴うパフォーマンスの発生を回避するには、データ・モデルのパラメータを定義するときに「NULL値が渡されました」を選択します。これで、長いINリストではなくNULLがバインド変数に渡されます。

DISTINCT修飾子またはROWNUMキーワードを使用したスカラー副問合せ

スカラー副問合せは、SELECT句の列として使用されるSQL問合せブロックです。メインのSELECTで使用する1つの値(スカラー)を生成する必要があるため、スカラーと呼ばれます。副問合せが複数の行を返すように記述されている場合、ORA-01427例外が発生します。

スカラー副問合せの一般的な使用パターンは、一意(主キー)列を使用して副問合せ内の表を外部表に結合することで、単一の列値を渡すというものです。これにより、このような副問合せによって単一のスカラー値が返されることが保証されます。

スカラー副問合せで複数の行が生成される場合は、スカラー値を強制的に単一にするための回避策としてDISTINCTまたはROWNUMのいずれかが(誤って)使用されることがあります。どちらの回避策も、結果的に誤った機能になる場合があると同時に、副問合せによって生成される結果セットが非常に大きくなる可能性があり、それに対する最後の手順として適用されるため、パフォーマンスのオーバーヘッドをもたらす可能性があります。

SELECT
    mpm.level_member_id, 
    archive.archive_id,
    mat.category_name,
      ( 
      SELECT DISTINCT category_name 
        FROM msc_catalog_categories mcc1
        WHERE mcc1.category_id = mat.parent_category_id 
          AND mcc1.parent_category_id IS NOT NULL
      ) cgt_category_name,
...

ノート:

SELECTで相関副問合せを使用したSQLを設計する場合は、1行ごとに1回実行されるように索引アクセス・パスを使用してください。結合述語に索引がないために副問合せで全表スキャンを使用する場合、メインの副問合せでフェッチされた行数と同じ数の全表スキャンになります。その場合は、SELECTではなくWHERE句で結合ロジックを使用するようにリライトする必要があります。

SELECT句のカスタムPL/SQLコール

SELECT句でPL/SQLファンクションを使用すると、SQLのパフォーマンスが低下する可能性があります。オプティマイザには、PL/SQLファンクションから主要SQL問合せにコードをマージするメソッドがないため、ファンクションは主要問合せによってフェッチされた行数と同じ回数呼び出されます。PL/SQLの複雑度とその実行数の積が、SQL最適化で見落とされがちな大きなパフォーマンス要因になる可能性があります。

このようなファンクション・コールの使用を示す次の例を参照してください。ここでは、別のSELECTが内部にラップされており、ファンクションとSELECTの両方が、メインのSELECTからフェッチされた行ごとに1回呼び出されます。

SELECT ...
   ff_user_tables_pkg.get_table_value(
     (SELECT l_data_grp.legislative_data_group_id   
      FROM   per_legislative_data_groups_vl l_data_grp   
      WHERE  l_data_grp.name = 'US'),   
     'RETIREMENT_ELIGIBILITY',    
     'AGE',   
      to_char((months_between(sysdate, pp.date_of_birth) / 12)),  
      trunc(sysdate)
    ) l_data_grp_pkg_call,
    ....

このようなPL/SQLファンクションの使用を確認し、機能ロジックで使用する必要がある場合は、効果的なフィルタを適用して、SQLのフェッチされる行数およびPL/SQL実行の結果数を減らします。