49
<Insert Picture Here> Oracle Direct Seminar オラクルコンサルタントが語るSQLチューニングの真髄 - 解決編Part4 - 日本オラクル株式会社

Oracle Direct Seminar · SQL文のwhere句内に等価条件を使用する複数のSQL文は、DECODE関数を使用するこ とによりまとめることができます。 DECODE

  • Upload
    others

  • View
    1

  • Download
    0

Embed Size (px)

Citation preview

<Insert Picture Here>

Oracle Direct Seminar

オラクルコンサルタントが語るSQLチューニングの真髄-解決編Part4 -

日本オラクル株式会社

Copyright© 2010, Oracle. All rights reserved. 2

Agenda

1. 前回までの振り返り

2. 目的とゴール

3. アプリケーション設計

• SQLの必要性

• SQLの発行回数を減らす

• SQL発行形態のチューニング

4. まとめ

・SQL Serverからの移行アセスメント・MySQLからの移行相談

・PostgreSQLからの移行相談・Accessからの移行アセスメント

・Oracle Database バージョンアップ支援・Oracle Developer/2000 Webアップグレード相談

・パフォーマンス・クリニック・Oracle Database 構成相談

・Oracle Database 高可用性診断・システム連携アセスメント・システムセキュリティ診断

・簡易業務診断・メインフレーム資産活用

無償技術サービスOracle Direct Concierge

http://www.oracle.com/lang/jp/direct/services.html

Copyright© 2010, Oracle. All rights reserved. 3

<Insert Picture Here>

これまでの振り返り

- Part1、Part2、Part3 -

Copyright© 2010, Oracle. All rights reserved. 4

コンサルタントのチューニング・アプローチ

アーキテクチャを意識した

SQLチューニング

定型的な

SQLチューニング

非定型的な

SQLチューニング

21 3

問題発生の原因と理由の考察現場のパフォーマンス問題

システムのパフォーマンス問題のほとんどがSQL

パフォーマンス問題に帰着する

SQLパフォーマンス問題が減らない

SQLパフォーマンス問題の解決に時間がかかる

何をどのように調査すればよいのかわからない

言語的特徴に起因する理由がある

• 記述に対する柔軟性が高い

• 処理ロジックを意識させないコーディングができる

• 同一の処理内容に対して、複数の記述方法が可能

• 処理方法はデータベースに任されている

開発時の状況や意識に及ぶ理由がある

• 正しい結果が返ることには気にしても、処理の効率化やデータベース内での処理を意識した開発に気が回らない

現場の声・現状 原因・理由

Part1/Part2 Part3

Copyright© 2010, Oracle. All rights reserved. 5

予防 チューニング

SQLパフォーマンス問題解決へのアプローチ定型的なSQLチューニング

問題がおきたら最低限これだけはチェックする

最低限これだけは守ってコーディングする

「定型的なSQLチューニング」 =「最低限のSQLコーディング・ルール」

• 柔軟な言語だからこそ、守るべきルールが存在する

• 大きく4つのカテゴリに分かれる• アーキテクチャに伴う性能問題を避けるためのルール

• 使用方法やノウハウを元に性能問題を避けるためのルール

• 可読性や管理性を高めるためのルール

• 運用ポリシーを考慮したルール

• ルールを活用するにはポイントがある

• 開発者にも直観的にわかりやすいものにする

• ルールが必要となる理由を明確にし、指針、注意点、例なども加える

• プログラムレビューと同様にSQLコーディングもレビュー(チェックシートでチェック)

Copyright© 2010, Oracle. All rights reserved. 6

SQLパフォーマンス問題解決へのアプローチ非定型的なSQLチューニング

• CBOのインプット、アウトプット情報• 「実行計画」を作成するために必要な情報は何か

CBO(コストベースオプティマイザ)

SQLテキスト

パラメータ

オブジェクト構造

環境

実行計画

レスポンス

統計情報データの実態

Copyright© 2010, Oracle. All rights reserved. 7

妥当性の判断

全パターンを検証するのは難しい

意識するのは

・SQL単体の最適化

・システム全体の最適化表結合方法

表結合順序

索引を利用して参照

表を直接参照

データアクセス方法

全表スキャン(TABLE ACCESS FULL)

索引のレンジスキャン(INDEX RANGE SCAN)

索引の一意スキャン(INDEX UNIQUE SCAN)

索引のフルスキャン(INDEX FULL SCAN)

索引の高速フルスキャン(INDEX FAST FULL SCAN)

ネステッドループ結合

索引のスキップスキャン(INDEX SKIP SCAN)

ハッシュ結合

ソートマージ結合

直積結合

判断のポイント 分岐の種類

EMP表から?DEPT表から?それとも・・

SQLパフォーマンス問題解決へのアプローチ非定型的なSQLチューニング

Copyright© 2010, Oracle. All rights reserved. 8

TAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3

SQLパフォーマンス問題解決へのアプローチ非定型的なSQLチューニング

アクセス1回⇒絞込でX件ヒット

NLアクセス X回⇒X*1件ヒット

TAB3

ID

CLASS

NLアクセス X回⇒X*63.5件ヒット

TAB4

CODE = :b2

FLAG = ‘Y’

CLASS

NL アクセスX*63.5回⇒絞込で2件ヒット

TAB5

NUM = :b1

CLASS

NL アクセス2回⇒絞込で1 or 2件ヒット

Start

Point

表 アクセス回数 カーディナリティ

TAB1 1回 X行

TAB2 X回 X×1行

TAB3 X回 X×63.5行

TAB4 X×63.5回 2行

TAB5 2回 1 or 2行

Copyright© 2010, Oracle. All rights reserved. 9

SQL解析

SQLパフォーマンス問題解決へのアプローチアーキテクチャを意識したSQLチューニング

PGA

DBバッファキャッシュ共有プール REDOログバッファ

SGA

SQL

実行計画

AP

65

43

21

65

43

21

ディスクからの読込み

バッファからの読込み

発生している待機「cursor: pin S」「latch : cache buffers chains」を考察

ここ!

サーバプロセス

ここ!

cursor: pin S

latch : cache buffers chains

• 何の(どこに関連した)処理か?

「cursor: pin S」 共有プール

「latch : cache buffers chains」 DBバッファキャッシュ

⇒メモリ領域に対する処理がボトルネックとなっている

Copyright© 2010, Oracle. All rights reserved. 10

<Insert Picture Here>

目的とゴール

Copyright© 2010, Oracle. All rights reserved. 11

目的とゴール

今回の目的

本セミナーでは、以下のスキルを習得することを目的としています。

• SQLパフォーマンス問題に対し、表面的なチューニングだけではなくその裏に潜む真の問題

を見つけて解決するスキル

• システム全体で発生するSQLパフォーマンス問題を減らすスキル

• 最終的にはSQLパフォーマンス問題を予防する仕組みを実現するスキル

今回のゴール

• SQLパフォーマンス問題が発生する理由を理解する

• SQLパフォーマンス問題の対処方法をアプリケーション設計を意識し理解・習

得する

Copyright© 2010, Oracle. All rights reserved. 12

アプリケーションチューニング

アプリケーションからSQLを発行する際に注意する観点:

① SQLを発行する必要があるのか

② SQLの発行回数を減らせないか

③ SQLの発行方法は効率的か

アプリケーションロジックとSQLの関連は、主に次の観点から注意して見ていきます。

本当にデータベースにアクセスする必要があるのかを検討してください。

データベースへのアクセス回数を減らすことができれば、データベースへの負荷も

低減します。SQLが不必要に発行されていないかを確認してください。

非効率な方法で、不必要なデータへまでアクセスしていないかを確認してください。

Copyright© 2010, Oracle. All rights reserved. 13

<Insert Picture Here>

アプリケーション設計

1. SQLの必要性

2. SQLの発行回数を減らす

3. SQL発行形態のチューニング

Copyright© 2010, Oracle. All rights reserved. 14

SQLの必要性

データベースへの負荷を低減するために、SQLの発行回数は必要最小限に止める必要があります。

たとえば、SQLの必要性事態に疑念が生じる使われ方として、以下のようなケースがあります。

そのSQLは本当に必要なのか

Case ①: SQL関数の使用

Case ②: アプリケーションの一時情報の格納

Case ①: SQL関数の使用例

SQL関数を使用するためにSQLを発行しているケース

例)

⇒ dual表に対するアクセスでも、SQLを実行するために処理を行う必要があります。

•実行計画の生成

•データの転送(データベースとクライアント間), etc

プログラム側のTRIM関数相当の機能を使用する

SELECT TRIM(:b1) INTO :b2 FROM dual;

Copyright© 2010, Oracle. All rights reserved. 15

SQLの必要性

そのSQLは本当に必要なのか

Case ①: SQL関数の使用

Case ②: アプリケーションの一時情報の格納

Case ②: アプリケーションの一時情報格納の例

アプリケーションの一時的な情報の格納場所として、データベースを使用している場合

永続的な保護が必要なデータのみデータベースに格納する

Id STATUS

1 go

ステータス表

DBサーバ

APサーバ

SQL発行

Idt STATUS

1 go

ステータス情報

DBサーバ

APサーバ

実データ 実データ

SQL発行 SQL発行

Copyright© 2010, Oracle. All rights reserved. 16

<Insert Picture Here>

アプリケーション設計

1. SQLの必要性

2. SQLの発行回数を減らす

3. SQL発行形態のチューニング

Copyright© 2010, Oracle. All rights reserved. 17

SQLの発行回数を減らす

データベースはSQLが発行され

る度に、以下のようなオーバー

ヘッドがかかっています。

•文の解析

•インデックス評価

•変数のバインド

•物理的ブロックアクセス

•etc…

データベースへのアクセス回

数を減らすことが有効!

これらのオーバーヘッドを低減するには、、、

① DECODE関数 / CASE文の使用

②同一結果を返すSQL文の扱いを工夫する

③適切なコミット間隔へ調整する

以下のポイントを考慮することで、データベースへのアクセス回数を減らすことができます。

課題

④ MERGE文の活用

Copyright© 2010, Oracle. All rights reserved. 18

SQLの発行回数を減らす ~DECODE関数の使用~

DECODE関数を使用した構文:

SQL文のwhere句内に等価条件を使用する複数のSQL文は、DECODE関数を使用することによりまとめることができます。

DECODE ( , condition1, result1,

condition2, result2,

condition3, result3,

abc

default ※1 )

abcとconditionに記述された条件が1つずつ比較されます。

abcがcondition3と等しい場合:

① abcとcondition1は等価ではありません。

② abcとcondition2は等価ではありません。

③ abcとcondition3は等価です。

④ result3が返されます。

result3

※1 どのconditionにも一致しない場合は、defaultが返されます。defaultが指定されていない場合には、nullが返されます。

DECODE ( abc, condition1, result1,

condition2, result2,

condition3, result3,

default )

Copyright© 2010, Oracle. All rights reserved. 19

SQLの発行回数を減らす ~DECODE関数の使用~DECODE関数の構文:

DECODE関数の使用例を説明します。

SELECT SAL*2

FROM EMP

WHERE JOB = 'CLERK';

SELECT SAL/2

FROM EMP

WHERE JOB = 'SALESMAN';

SELECT SAL*3

FROM EMP

WHERE JOB = 'MANAGER';

修正後(DECODE関数を使用する場合):修正前(DECODE関数を使用しない場合):

SELECT DECODE

(JOB, 'CLERK', SAL*2,

'SALESMAN', SAL/2,

'MANAGER', SAL*3,

SAL)

FROM EMP;

DECODE ( abc, condition1, result1,

condition2, result2,

condition3, result3,

default )

例)EMP表のSAL値をJOB列の値によって演算し、結果を取得する。

Copyright© 2010, Oracle. All rights reserved. 20

SQLの発行回数を減らす ~CASE文の使用~

CASE文の構文:

CASE abc

WHEN condition1 THEN result1

WHEN condition2 THEN result2

ELSE default

END

SQL文のwhere句内に等価条件、及び不等号を使用する複数のSQL文は、CASE文を使用することによりまとめることができます。

CASE

WHEN condition2 THEN result2

WHEN condition3 THEN result3

abc

ELSE default ※2

abcとconditionに記述された条件が1つずつ比較されます。

abcがcondition3と等しい場合:

① abcとcondition1は等価ではありません。

② abcとcondition2は等価ではありません。

③ abcとcondition3は等価です。

④ result3が返されます。

result3

※2 どのconditionにも一致しない場合は、defaultが返されま

す。defaultが指定されていない場合には、nullが返されます。

WHEN condition1 THEN result1

END

Copyright© 2010, Oracle. All rights reserved. 21

SQLの発行回数を減らす ~CASE文の使用~CASE文の構文:

CASE文の使用例を説明します。

修正後(CASE文を使用する場合):修正前(CASE文を使用しない場合):

CASE abc

WHEN condition1 THEN result1

WHEN condition2 THEN result2

ELSE default

END

SELECT JOB,

SUM( CASE WHEN JOB = 'CLERK'

THEN 1 ELSE 0 END),

SUM( CASE WHEN JOB = 'ANALYST'

THEN 1 ELSE 0 END)

FROM EMP

GROUP BY JOB;

例)EMP表のCLEARK人数の合計とANALYSTの人数の合計を求める。

--CLERKの人数SELECT JOB,

COUNT(*)

FROM EMP

WHERE JOB = 'CLERK'

GROUP BY JOB;

--ANALYSTの人数SELECT JOB,

COUNT(*)

FROM EMP

WHERE JOB = 'ANALYST'

GROUP BY JOB;

Copyright© 2010, Oracle. All rights reserved. 22

SQLの発行回数を減らす~DECODE関数/CASE文の使用~

CASE文の構文:

CASE abc

WHEN condition1 THEN result1

WHEN condition2 THEN result2

ELSE default

END

DECODE関数の構文:

DECODE ( abc, condition1, result1,

condition2, result2,

condition3, result3,

default )

SQLをまとめたために構文自体が複雑になると、CPUの使用率が増えることになります。

注意

SQL自体の実行計画を採取し、検討することが大切

Copyright© 2010, Oracle. All rights reserved. 23

SQLの発行回数を減らす~DECODE関数/CASE文の使用~

データベースへのアクセス回数を減らすために、大量にアプリケーション側でメモリを

消費しないよう気をつけてください。

ガーベジ・コレクションなどで、逆に性能が劣化する場合があります。

データベースへのアクセス回数とメモリ使用量は、それぞ

れのトレードオフを考慮してください!

データベースへのアクセス回数とメモリ使用量の分析は、アプリケーションロジックから追うよりデータベースの稼動状況から追ったほうが効率的な場合があります。

例)アプリケーションの単体試験の際、SQLトレースを取得し、発行されるSQLをパラメータも含めて分

析することで、無駄にSQLが繰り返し実行されていないかを簡単に把握することができます。

注意

Copyright© 2010, Oracle. All rights reserved. 24

SQLの発行回数を減らす~同一の結果を返すSQLの扱い~

複数箇所で同一のデータが必要な場合、同一の結果を問い合わせるSQLを複数回実行してい

る場合があります。以下の形態のアプリケーションでは、気づかないうちにこのような状況

が発生しがちです。

①ループ内部でSQLを発行するアプリケーション

② 高度にモジュール化、サブプロシージャ化されたアプリケーション

必要なデータをアプリケーション内で保存しておくことが有効!

これらの状況を回避するには、、、

一度問い合わせたデータが再度必要になったとしても、SQLを発行することなくアクセス可能となります。

課題

Copyright© 2010, Oracle. All rights reserved. 25

同一の結果を返すSQLを複数回発行しているプログラムの例と、そのチューニング案を示します。

修正後:修正前:

モジュールA(int id)

SELECT name FROM masterA WHERE id = :id

モジュールB (int id)

SELECT name FROM masterA WHERE id = :id

SELECT … FROM tabA, tabB WHERE …

SELECT … FROM tabC WHERE …

SELECT … FROM tabD, tabE WHERE …

SELECT … FROM tabF WHERE …

メインモジュール

for (int id=0; id<10; id++) {

モジュールA(id);

モジュールB(id);

}

モジュールA(int id, String name)

モジュールB (int id, String name)

SELECT … FROM tabA, tabB WHERE …

SELECT … FROM tabC WHERE …

SELECT … FROM tabD, tabE WHERE …

SELECT … FROM tabF WHERE …

メインモジュール

for (int id=0; id<10; id++) {

モジュールA(id, name);

モジュールB(id, name);

}

SELECT name FROM masterA WHERE id = :id

SQLの発行回数を減らす~同一の結果を返すSQLの扱い チューニング案①~

メインモジュールからモジュールA、Bを呼び出

し、共に を発行しています。

メインモジュール内に記述した場合、このSQL

の実行回数は一回のみに減らすことができます。

Copyright© 2010, Oracle. All rights reserved. 26

修正後:修正前:

モジュールA(int id)

SELECT name FROM masterA WHERE id = :id

モジュールB (int id)

SELECT name FROM masterA WHERE id = :id

SELECT … FROM tabA, tabB WHERE …

SELECT … FROM tabC WHERE …

SELECT … FROM tabD, tabE WHERE …

SELECT … FROM tabF WHERE …

メインモジュール

for (int id=0; id<10; id++) {

モジュールA(id);

モジュールB(id);

}

SQLの発行回数を減らす~同一の結果を返すSQLの扱い チューニング案②~

メインモジュール

for (int id=0; id<10; id++) {

モジュールA(id, name[id]);

モジュールB(id, name[id]);

}

SELECT name FROM masterA

WHERE id BETWEEN :b1 AND :b2

モジュールA(int id, String name)

モジュールB (int id, String name)

同一の結果を返すSQLを複数回発行しているプログラムの例と、そのチューニング案を示します。

メインモジュールからモジュールA、Bを呼び出し、共にを発行しています。

メインモジュール内で、masterAから結果をループ外で取得し、配列にあらかじめ確保しておきます。この場合も、SQL発行回数は一回のみに減らすことができます。

SELECT … FROM tabA, tabB WHERE …

SELECT … FROM tabC WHERE …

SELECT … FROM tabD, tabE WHERE …

SELECT … FROM tabF WHERE …

Copyright© 2010, Oracle. All rights reserved. 27

SQLの発行回数を減らす ~適切なコミット間隔~

すべての更新を一回でコミットするのではなく、適度な行数の更新ご

とに分割してコミットする

コミット処理はLGWRによりディスクへの同期処理を含むため、コミット頻度をあまり短くしすぎるとスループットが低下することに注意してください。

1つのトランザクションで多量の更新処理を行うと、以下のような弊害があります。

① UNDOセグメントのサイズ拡張、及びそれに伴う領域不足によるトランザクションの失敗

② トランザクション失敗時のロールバックにかかる時間の長大化

③ トランザクション失敗時の再実行量の増大

課題

注意

Copyright© 2010, Oracle. All rights reserved. 28

SQLの発行回数を減らす ~MERGE文~

MERGE文の構文:

データが存在している場合には既存データを更新(UPDATE)し、データが存在しない場合にはデータを挿入(INSERT)することが1つのSQLで実行できます。

Free

MERGE INTO 表名1

USING 表名2

ON (結合条件)

WHEN MATCHED THEN

UPDATE SET カラム名 = 値, …

WHEN NOT MATCHED THEN

INSERT (カラム名, … )

VALUES (値 , … ) ;

MERGE INTO Table1

USING Table2

ON (‘Job’)

WHEN MATCHED THEN

UPDATE SET ‘Emp’ = ‘Josh’

WHEN NOT MATCHED THEN

INSERT (‘Emp’)

VALUES (‘Free’) ;

No. Job Emp

1 Sales Smith

2 Manager Sean

No. Job Emp

1 Accountant Mike

2 Manager Jean

Table1

Table2

MERGE文の使用例:

Table1のJob列がTable2のJob列と比較し、条件に応じてTable1のEmp列を更新する。

① Table1のJob列とTable2のJob列を比較します。

②③ Table1のJob

列とTable2のJob

列が一致しない場合、Table1のEmp

列を挿入する。

Josh

② Table1のJob

列とTable2のJob

列が一致している場合、Table1のEmp列を更新する。

Copyright© 2010, Oracle. All rights reserved. 29

SQLの発行回数を減らす ~MERGE文~MERGE文の構文:

MERGE文の使用例を説明します。

修正前(MERGE文を使用しない場合):

DECLARE

w_cnt number := 0;

c_cnt number := 0;

BEGIN

FOR i IN ( SELECT empno from scott.emp_update ) LOOP

select count(*) into w_cnt from emp where empno = i.empno;

IF w_cnt = c_cnt THEN

insert into emp

select * from emp_update where empno = i.empno ;

);

ELSE

update set job =

select job from emp_update where empno = i.empno ;

END IF;

END LOOP;

END;

merge into scott.emp e

using

scott.emp_update u

on ( e.empno = u.empno )

when matched then

update set

e.job = u.job

when not matched then

insert (

e.empno,

e.ename,

e.job

) values (

u.empno,

u.ename,

u.job

);

例) scott.emp表のempno列とscott.emp_update表のempno列を比較し、以下の条件に従い、scott.emp表を更新する。

•e.empno = u.empnoの場合⇒ scott.emp_update表のJob列の値をそのままscott.emp表へupdateする•e.empno ≠ u.empnoの場合⇒ scott.emp_update表のempno列、ename列、Job列をscott.emp表へinsertする

MERGE INTO 表名1

USING 表名2

ON (結合条件)

WHEN MATCHED THEN

UPDATE SET カラム名 = 値, …

WHEN NOT MATCHED THEN

INSERT (カラム名, … )

VALUES (値 , … ) ;

修正後(MERGE文を使用した場合):

Copyright© 2010, Oracle. All rights reserved. 30

<Insert Picture Here>

アプリケーション設計

1. SQLの必要性

2. SQLの発行回数を減らす

3. SQL発行形態のチューニング

Copyright© 2010, Oracle. All rights reserved. 31

SQL発行形態のチューニング

大量データを扱うことによって

レスポンス時間の遅延だけでは

なく以下の影響が出ます。

•システムリソースの枯渇

CPUの大量消費

I/Oの増加

•システム全体の性能劣化

SQLの発行形態についても

考慮することが重要!

リソースの効率的な利用の為には

① ROWNUM関数の活用

②バルク処理の活用

③ カーソルキャッシュ・文キャッシュの使用

課題

Copyright© 2010, Oracle. All rights reserved. 32

SQL発行形態のチューニング ~ROWNUM関数~

自由検索型のSQL文

SELECT c1, c2 ,c3 … FROM tab1, ..

WHERE condition1 and condition2 … ;

SQLの検索結果が非常に多い

検索条件をユーザーが

自由に設定して必要な

データを取得できる

指定した検索条件が甘い場合:

ヒット件数が非常に多くなる

データを採取する為にリソースを大量に消費する

1回の検索単体では問題にならない場合でも

同様の処理が並列実行されるとシステムリソースが枯渇して

システムの全体的なスローダウンを招く恐れがあります

問題

Copyright© 2010, Oracle. All rights reserved. 33

SQL発行形態のチューニング ~ROWNUM関数~

自由検索型のSQL文

SELECT c1, c2 ,c3 … FROM tab1, ..

WHERE condition1 and condition2 …

and ROWNUM < n;

ROWNUM関数を活用してヒット件数に閾値を設けて検索を停止する

ROWNUM関数を使用して

返されるデータ件数の閾値

を設定します

ROWNUM関数を使うと・・・

• ヒット件数が閾値を超えた場合には検索をそこで停止します

• 閾値の件数内のデータのみを返します

• その結果、予期しないリソースの大量消費を防ぐことができます

Copyright© 2010, Oracle. All rights reserved. 34

SQL発行形態のチューニング ~バルク処理の活用~

大量データを読み込んで配列に代入する

1. emp_curで定義されたSELECT文を実行し、1件データをフェッチ

2. フェッチしたデータを代入

DECLARE

CURSOR emp_cur IS

SELECT /* normal */ empno, ename FROM emp4;

v_empno emp4.empno%TYPE;

v_ename emp4.ename%TYPE;

BEGIN

OPEN emp_cur;

LOOP

FETCH emp_cur INTO v_empno, v_ename;

EXIT WHEN emp_cur%NOTFOUND;

END LOOP;

CLOSE emp_cur;

END;

/

通常のフェッチの場合以下をトータルの件数分、繰り返します

データ件数が多いとオーバーヘッド

が大きくなりパフォーマンスに影響します

問題

Copyright© 2010, Oracle. All rights reserved. 35

DECLARE

CURSOR emp_cur IS

SELECT /* bulk */ empno, ename FROM emp4;

TYPE empno_tab_type IS TABLE OF emp4.empno%TYPE

INDEX BY BINARY_INTEGER;

empno_tab empno_tab_type;

TYPE ename_tab_type IS TABLE OF emp4.ename%TYPE

INDEX BY BINARY_INTEGER;

ename_tab ename_tab_type;

rows NATURAL := 100;

BEGIN

OPEN emp_cur;

LOOP

FETCH emp_cur BULK COLLECT INTO

empno_tab, ename_tab LIMIT rows;

EXIT WHEN emp_cur%NOTFOUND;

END LOOP;

CLOSE emp_cur;

END;

/

SQL発行形態のチューニング ~バルク処理の活用~

バルクフェッチを利用すると・・・

•複数データを一括代入することが可能

•ループ処理によるオーバーヘッドを低減

limit句に指定した件数分まで一括して

フェッチ・代入が可能になります

1. emp_curで定義されたSELECT文を実行しX件データをフェッチ

2. フェッチしたデータを一括代入

索引付き表や配列を利用してデータを一括してフェッチ・代入する(バルクフェッチ)

Copyright© 2010, Oracle. All rights reserved. 36

SQL発行形態のチューニング ~バルク処理の活用~

DML文をループで繰り返し実行する

1. バインド変数に値をセット

2. DML文を実行

通常の場合以下をトータルのデータ個数分、繰り返します

データ件数が多いとオーバーヘッド

が大きくなりパフォーマンスに影響します

BEGIN

FOR j IN depts.FIRST .. depts.LAST LOOP

DELETE FROM emp

WHERE deptno = depts(j);

END LOOP;

END;

問題

Copyright© 2010, Oracle. All rights reserved. 37

SQL発行形態のチューニング ~バルク処理の活用~

1. バインド変数にセットする値を取得

2. DML文を1回実行

通常の場合 バインド変数にセットする値をまとめてから

DML文を実行しますBEGIN

FORALL j IN depts.FIRST .. depts.LAST

DELETE FROM emp

WHERE dptno = depts(j);

END;

FORALL句を利用してDML文を一括実行(バルクバインド)

バルクバインドを利用すると・・・

• バインド変数ごとDMLをSQLエンジンに送信せずにすむ

• ループ処理によるオーバーヘッドを低減

Copyright© 2010, Oracle. All rights reserved. 38

SQL発行形態のチューニング ~カーソルキャッシュ~

セッション内で同一SQLを頻繁に実行する

カーソルのオープン

SQL解析(soft)

SQL実行

以下はSQL文が発行されてから実行されるまでの流れを表します

SQL発行

セッションにキャッシュされた

カーソルの有無を確認

共有プール上の共有カーソル

情報を確認

SQL解析(hard)

なし

あり

なし

あり

同一SQLをセッション内で

頻繁に再実行する場合に

カーソルの再オープンの

オーバーヘッドがかかります。

問題

Copyright© 2010, Oracle. All rights reserved. 39

SQL発行形態のチューニング ~カーソルキャッシュ~

カーソルキャッシュ(文キャッシュ)を使用する

カーソルのオープン

SQL解析(soft)

SQL実行

SQL発行

セッションにキャッシュされた

カーソルの有無を確認

共有プール上の共有カーソル

情報を確認

SQL解析(hard)

なし

あり

なし

あり

カーソル・キャッシュを有効にするには以下のパラメータを設定します

プリコンパイラ :HOLD_CURSOR=Y

PL/SQL :session_cached_cursors=n

カーソルキャッシュを使用すると・・・

• 共有プール上の共有カーソルに

直接アクセス

• カーソルの再オープンや解析を

省略してオーバーヘッドを低減

Copyright© 2010, Oracle. All rights reserved. 40

<Insert Picture Here>

まとめ

Copyright© 2010, Oracle. All rights reserved. 41

今回のまとめ

Part4

今回は、SQLの性能問題が発生した場合は、アプリケーションロジックやアプリ

ケーション設計を意識したチューニングが必要な場面もあります。その時の考え方を説明しました。

① SQLの必要性

・アンチパターン

② SQL発行回数を減らす

・DECODE関数/CASE文

・同一結果を返すSQLの扱い

・適切なコミット間隔

・MERGE文の活用

③ SQL発行方法のチューニング

・ROWNUM関数の活用

・バルク処理の活用

・カーソルキャッシュ(文キャッシュ)の使用

Copyright© 2010, Oracle. All rights reserved. 42

OTN×ダイセミ でスキルアップ!!

※OTN掲示版は、基本的にOracleユーザー有志からの回答となるため100%回答があるとは限りません。ただ、過去の履歴を見ると、質問の大多数に関してなんらかの回答が書き込まれております。

Oracle Technology Network(OTN)を御活用下さい。

・一般的な技術問題解決方法などを知りたい!・セミナ資料など技術コンテンツがほしい!

一般的技術問題解決にはOTN掲示版の

「データベース一般」をご活用ください

http://otn.oracle.co.jp/forum/index.jspa?categoryID=2

過去のセミナ資料、動画コンテンツはOTNの

「OTNセミナー オンデマンド コンテンツ」へ

http://www.oracle.com/technology/global/jp/ondemand/otn-seminar/index.html

※ダイセミ事務局にダイセミ資料を請求頂いても、お受けできない可能性がございますので予めご了承ください。ダイセミ資料はOTNコンテンツ オン デマンドか、セミナ実施時間内にダウンロード頂くようお願い致します。

Copyright© 2010, Oracle. All rights reserved. 43

Windows環境でもシェアNo.1!Databaseの合言葉はオラ98(キュッパ)」

日頃ご利用・ご提案頂いている皆様の声を投票にてお聞かせください。抽選でプレミアム・グッズをプレゼント。47都道府県を超えたら、投票総数が2,000件を超えたら、賞品がグレードアップ!!締め切りは11月30日(火)まで。皆様のご応募をお待ちしております!!

日本全国 オラ98

Copyright© 2010, Oracle. All rights reserved. 44

OTNセミナー オンデマンド コンテンツダイセミで実施された技術コンテンツを動画で配信中!!

ダイセミのライブ感はそのままに、お好きな時間で受講頂けます。

※掲載のコンテンツ内容は予告なく変更になる可能性があります。期間限定での配信コンテンツも含まれております。お早めにダウンロード頂くことをお勧めいたします。

OTN オンデマンド

最新情報つぶやき中

oracletechnetjp

・人気コンテンツは?

・お勧め情報

・公開予告 など

Copyright© 2010, Oracle. All rights reserved. 45

Oracle エンジニアのための技術情報サイト

オラクルエンジニア通信http://blogs.oracle.com/oracle4engineer/

• 技術資料

• ダイセミの過去資料や製品ホワイトペーパー、スキルアップ資料などを多様な方法で検索できます

• キーワード検索、レベル別、カテゴリ別、製品・機能別

• コラム

• オラクル製品に関する技術コラムを毎週お届けします

• 決してニッチではなく、誰もが明日から使える技術の「あ、そうだったんだ!」をお届けします

先月はこんな資料が人気でした

Oracle 10gR2がWindows 2008R2/Windows7に対応

【チュートリアル】意外と簡単!? Oracle Database 11g

Release2 - Windows版 「データベース構築編」

Oracle Database 11gR2 RAC インストレーション・ガイドASM 版 Microsoft Windows x86-64

オラクルエンジニア通信

最新情報つぶやき中

oracletechnetjp

Copyright© 2010, Oracle. All rights reserved. 46

■パフォーマンス診断サービス

•Webシステム ボトルネック診断サービス

•データベースパフォーマンス診断サービス

オラクル社のエンジニアが 直接ご支援しますお気軽にご活用ください!

オラクル 無償支援 検索

NEW

■システム構成診断サービス

•Oracle Database構成相談サービス

•サーバー統合支援サービス

•仮想化アセスメントサービス

•メインフレーム資産活用相談サービス

•BI EEアセスメントサービス

•簡易業務診断サービス

■バージョンアップ支援サービス

•Oracle Databaseバージョンアップ支援サービス

•Weblogic Serverバージョンアップ支援サービス

•Oracle Developer/2000(Froms/Reports)

Webアップグレード相談サービス

■移行支援サービス

•SQL Serverからの移行支援サービス

•DB2からの移行支援サービス

•Sybaseからの移行支援サービス

•MySQLからの移行支援サービス

•Postgre SQLからの移行支援サービス

•Accessからの移行支援サービス

•Oracle Application ServerからWeblogicへ移行支援サービス

ITプロジェクト全般に渡る無償支援サービス

Oracle Direct Conciergeサービス

NEW

NEW

Copyright© 2010, Oracle. All rights reserved.

11月30日まで!! 締め切り迫る

47

Enterprise Editionはここが違う!!

• 圧倒的なパフォーマンス!

•データベース管理がカンタン!

•データベースを止めなくていい!

• もちろん障害対策も万全!

Oracle Databaseのライセンス価格を大幅に抑えて

ご導入いただけます

詳しくはコチラ

http://www.oracle.co.jp/campaign/kurukuru/index.html

あのOracle Database Enterprise Editionが超おトク!!

お問い合わせフォームhttp://www.oracle.co.jp/inq_pl/INQUIRY/quest?rid=28

多くのお客様でサーバー使用期間とされる

5年間にライセンス期間を限定

•期間途中で永久ライセンスへ差額移行

• 5年後に新規ライセンスを購入し継続利用

• 5年後に新システムへデータを移行

Copyright© 2010, Oracle. All rights reserved. 48

http://www.oracle.co.jp/inq_pl/INQUIRY/quest?rid=28

Oracle Direct 検索

あなたにいちばん近いオラクル

Oracle Directまずはお問合せください

Web問い合わせフォーム フリーダイヤル

専用お問い合わせフォームにてご相談内容を承ります。

※フォームの入力には、Oracle Direct Seminar申込時と同じログインが必要となります。

※こちらから詳細確認のお電話を差し上げる場合がありますので、ご登録されている連絡先が最新のものになっているか、ご確認下さい。

0120-155-096

※月曜~金曜 9:00~12:00、13:00~18:00

(祝日および年末年始除く)

システムの検討・構築から運用まで、ITプロジェクト全般の相談窓口としてご支援いたします。

システム構成やライセンス/購入方法などお気軽にお問い合わせ下さい。

Copyright© 2010, Oracle. All rights reserved. 49

以上の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはできません。以下の事項は、マテリアルやコード、機能を提供することをコミットメント(確約)するものではないため、購買決定を行う際の判断材料になさらないで下さい。オラクル製品に関して記載されている機能の開発、リリースおよび時期については、弊社の裁量により決定されます。

Oracle、PeopleSoft、JD Edwards、及びSiebelは、米国オラクル・コーポレーション及びその子会社、関連会社の登録商標です。その他の名称はそれぞれの会社の商標の可能性があります。