PowerPoint プレゼンテーション

1

パフォーマンス パフォーマンス

チューニング チューニング

Oracle

Oracle 実践研修 実践研修 3 3

2007.11.7~8

Oracle9i

2

ソリューション事業部 畠山基裕

連絡先：

[email protected]

[Oracle

]

– Oracle Applications(

E-business Suite)

4

– 販売系2つ、生産系3つ

– セットアップ、機能評価、Extension設計/テスト、チューニング、運用支援

–

自己紹介

3

Oracle のドキュメント (9i)

• 『 Oracle9i データベース・パフォーマンス・

プランニング (J06251) 』 (62 ページ )

• 『 Oracle9 i データベース・パフォーマ ンス・チューニング・ガイドおよびリ ファレンス(J06248)』 (798ページ)

• OTN(http://otn.oracle.co.jp/) からダウン

ロード可能

4

Oracle のドキュメント (10g)

• 『 2 日でパフォーマンス・チューニング・ガイ ド (B40062) 』 (142 ページ )

– Diagnositics Pack

– Database Tuning Pack

• 『パフォーマンス・チューニング・ガ イド (B19207)』 (474ページ)

• OTN(http://otn.oracle.co.jp/) からダウン ロード可能

←Enterprise Manager

5

実習環境

• インスタンス名 ： smpl

• ユーザー / パスワード

： scott/tiger

： oe/oe

： system/smpladmin

• Oracle ディレクトリ ：

c:\oracle\product\10.2.0\db_1

• 実習ディレクトリ ： c:\oracle\jisshu

• ポート ：

%ORACLE_HOME%\install\portlist.ini

ORACLE_HOME

6

チューニングの対象

• HW(メモリ、CPU、ディスク、ネット ワーク)

• Oracle 設定

• SQL 、 PL/SQL

• アプリケーションサーバー

7

アンケート

• 7/8/8i/9i/10g/11g ？

• Enterprise Edition/Standard Edition ？

• (EE の場合 )Diagnostics Pack/Tuning Pack を導入しているか？

各 375,000 円 ( オラクル セールスガイド

Ver5.0~9i)

8

カリキュラムの確認

•

1

• Oracle

1

•

6

• Oracle

Oracle

1

• StatsPack

2

•

0.5

• OEM 0.5

9

チューニング概要

10

パフォーマンス・プランニング

• パフォーマンスを考慮した設計と開発

• アプリケーションのパフォーマンスの監視 と改善

設計 開発 テスト 運用・保守

いつチューニングを行うか？

すべてのフェーズで行う 設計時が

最も重要

11

システム性能の変化

• データ量 / トランザクション量の増大

量

処理時間

理想 実際

12

チューニング対象

• HW 増設

• Oracle インスタンス設定 ( メモリなど )

• アプリケーション (SQL 、 PL/SQL 、インデッ クス )

• データベース ( 表領域、ファイル配置 )

13

HW による解決

• CPU 増設

• メモリ増設

• ディスク増設

一時的な解決

効果が出ないことも

14

Oracle インスタンス

• 共有プール領域 (SGA) ～共有 SQL

• データベースバッファキャッシュ (SGA)

• REDO ログバッファ (SGA)

• その他 (REDO ログファイル / チェックポイン ト )

※SGA= システムグローバル領域

15

Oracle データベース

• ブロックサイズ

• 記憶域パラメータ

• 表領域

• ファイル配置

16

アプリケーション

• アプリケーションの効率化が最も

効果的

17

アプリケーション設計の概要

18

表および索引の設計

• 非正規化

• 索引への列の追加または索引構成表の使 用

• 異なる索引タイプ(B*Tree/Bitmap..)の 使用

• 索引のコストに関する考察

• 索引内のシリアライズ化

19

非正規化

• 表の設計は、最低でも第3 正規形に正規 化する

• ただし、特定のトランザクションは、パ フォーマンス向上のために非正規化も検 討

• 例

商品

4

5

．．

2

3

20

索引への列の追加または 索引構成表の使用

• 索引に、必要なすべての列を入れる

• 表と索引をアクセス→索引だけをアクセ ス

• CREATE TABLE文にORGANIZATION INDEX句 を指定

(

)Oracle9i

[J06242]

15-25

26

SE

21

異なる索引タイプの使用

• B

– 標準的な索引タイプ。

• ビットマップ索引

– カーディナリティが低いデータに適する。AND操作とOR 操作を効率よく実行。

• ファンクション・ベース索引

– データ上の関数から導出された値に

B

– 例）

• （販売価格

-

• UPPER関数をデータに適用して大

/

SE

EE

22

異なる索引タイプの使用

• パーティション索引

– パーティションに分割し

I/O

• レンジ・パーティション～売上データを四半期分割

• リスト・パーティション～東北、関東などに分割

• 逆キー索引

– 列の順序は保ちながら、索引が定義されている 各列（ROWID を除く）のバイトを逆にする。挿 入値は索引のリーフ・キー全体に分散される。

– 挿入パフォーマンスに優れているが、レンジ・

スキャンには使用できない。

EE

Partitioning

SE

23

索引のコストに関する考察

• 索引作成と保守には、ディスク、CPU、I/O容量 などのリソースを消費する。コストより、索引 使用による利点が上回るように設計する。

• INSERT/DELETE/UPDATEで、索引には、表に対す る操作に比べ3倍のリソースが必要。3つの索引 がある表にINSERTすると、索引がない表に

INSERTする場合に比べ約10倍かかる。

• 更新DML、特にINSERT主体のアプリケーションの 場合、検索とINSERTのバランスを取る必要があ る。

24

索引内のシリアライズ化

• 順序またはタイムスタンプを使用して索引 を生成すると、応答時間やスループットに 影響を与える場合がある。単調なキーの増 加による結果。

• 回避するには、索引の全範囲にわたって挿 入するキーを生成する。

– 逆キー索引

– 接頭辞("部門コード＋順序")、サイクル順序を 使用して生成(1～１０００をサイクリックに)

25

ビューの使用

• 有効なプログラミング・インタ フェースを提供

• 最も不適切な例。ビューが他の複数

のビューを参照し、問合せ内で結合

されている

26

SQL の実行効率

• 適切なデータベース接続管理

– コネクションプーリング等

• 適切なカーソルの使用方法と管理

– アプリケーションでSQL 文を1回解析し、そ のSQL文を繰返し実行するように設計

– SQL文が共有プール内で共有されていること を確認～実行ごとに変化する問合せの部分 をバインド変数とする。文字列リテラルは 使用しない。

27

SQL の実行効率

• 例

• 文字列リテラルがある文

– SELECT * FROM emp WHERE ename LIKE 'KING%';

• バインド変数がある文

– SELECT * FROM emp WHERE ename LIKE :1;

 後でサンプル提示(P.135)

28

その他

• 頻繁にアクセスし、変更がほとんどな く、繰返し取り出すのにコストがかか るデータはローカルに持つ

– 本日の日付

– 現行ユーザー名

– 税率、値引率、位置情報など、繰返し使 用するアプリケーション変数および定数

29

その他

• 外部キー参照を使用。アプリケー ションから参照整合性を適用する のはコストがかかる。

– バッチ処理で、外部システムから受

注データImportなど

30

Oracle チューニング手法

31

ログファイルの確認

SQL> SHOW PARAMETERS background_dump_dest;

出力先（初期化パラメータ）

出力ファイル名 　1. Windows系OS 　2. UNIX系OS

BACKGROUND_DUMP_DEST ・システムログメッセージとエラー情報

　1. {SID}alert.log ・Oracleアーキテクチャ全プロセスから出力 　2. alert_{SID}.log ・常に出力される

BACKGROUND_DUMP_DEST ・エラー情報

　1. {SID}プロセス名.trc ・バックグラウンドプロセスより出力 　2. {SID}_プロセス名_{PID}.trc ・常に出力される

USER_DUMP_DEST ・ エラー情報とSQL文の統計情報 　1. {SID}_ora_{PID}.trc ・ サーバプロセス

　2. ORA{PID}.trc ・ 出力の制御による ログの内容

ユーザート レース

バックグラウ ンドトレース ALERTログ ログの種類

レスポンスが悪い→まずは、問題の切り分け

32

Oracleパフォーマンス 改善方法

• パフォーマンス改善の概要

• Oracle パフォーマンス改善の手順

• ワークロードのテスト

• パフォーマンス監視と改善

33

パフォーマンス改善の概要

• ユーザからのフィードバック

– オンラインのパフォーマンスが低いため、ス タッフの仕事が停滞しています。

– 請求処理の実行時間が長すぎます。

– Web

なるため、このままでは顧客を失いかねません

。

– 現在、

1

5000

システムが限界に達しています。来月は、すべ てのユーザーにロールアウトを行うため、取引 数は

4

34

パフォーマンス改善の概要

• 現実的なビジネス目標を設定

– 請求処理では、 3 時間で 1,000,000 件 を処理する。

– Web サイトのピーク時には、 1 つの ページ・リフレッシュに対する応答時 間は 5 秒以内にする。

– システムでは、 8 時間で 25,000 件の

取引を処理可能にする。

35

パフォーマンス改善の概要

• パフォーマンス目標を達成、またはそれ以上 の改善は不可能と判断するまで実施。

• 重要なボトルネックを正確かつ適時に特定す るスキルを養うには、時間と経験が必要。

• 今日のシステムは、それぞれが異なり複雑な ので、パフォーマンス分析のための確実で手 間のかからない規則は作成できない。

• 提供されたデータを利用し、様々な側面を検 討してパフォーマンスの問題を判断する。

36

Oracle パフォーマンス改善の手 順

1. ユーザーから率直なフィードバックを取得。

チューニングの適用範囲、今回の最終的な パフォーマンス目標、将来のパフォーマン ス目標を決定する。

2. オペレーティング・システム、データベー ス、アプリケーションの統計情報をすべて 取得。

3. 全マシンのオペレーティング・システムが

、健全であることをチェックする。

37

Oracle パフォーマンス改善の手 順

4. Oracle に関して最もよく見られる誤 りの上位 10 項目をチェック(後述)。

5. システム上で発生している状況を概念 的にモデル化し、問題の原因を把握。

6. 一連の修正処理とシステムに対して予

想される動作を提示し、アプリケー

ションに対して最も有効な処理から順

に適用する。

38

Oracle パフォーマンス改善の手 順

7. 変更によって予定通りにパフォーマン スが改善されたことを検証し、ユー ザーがパフォーマンスの改善を認識し たかどうかを確認。ユーザーが認識し ない場合は、継続。

8. パフォーマンス目標を達成するまで、

または他の制約によってそれ以上の改

善は不可能になるまで、5～7の手順を

繰り返します。

39

ワークロードのテスト

• データのサイズ設定、ワークロードの見積り

、テスト、デバッグおよび検証

– 本運用と同等のデータ量によるテスト

– 本運用と同じオプティマイザ・モードの使用 – シングル・ユーザーのパフォーマンスのテスト – 全

SQL

– マルチユーザーのテスト

– 本運用に近いHW構成でのテスト

– 安定した状態(キャッシュとか)でのパフォーマン スの測定

本運用前のテスト

40

パフォーマンス監視と改善

• 各種統計情報の利用

• 統計情報ツール

運用開始後の監視

41

OS統計情報

• CPU 統計情報

• 仮想メモリー統計情報

• ディスク統計情報

• ネットワーク統計情報

42

データベース統計情報

• バッファ・キャッシュ

• 共有プール

• 待機イベント

43

アプリケーション統計情報

• 各作業時間に処理されたユーザー・トラ ンザクションに関する日次サマリー

• 処理されたトランザクションの詳細およ び各トランザクションの応答時間

• 各トランザクションで要した時間をアプ リケーション・サーバー、ネットワーク

、データベースなどに分解した統計情報

• 計測手段は、最初からアプリケーション

に組み込むことをお薦め。

44

統計情報ツール

• オペレーティング・システム・デー タ収集ツール

• データベース・データ収集ツール

45

オペレーティング・システ ム・データ収集ツール

• UNIX

– CPU sar 、 vmstat 、 mpstat 、 iostat – メモリ sar 、 vmstat

– ディスク sar 、 iostat – ネットワーク netstat

• Windows

– Performance Monitor

46

データベース・データ収集

• Statspack ツール

– データベース・リポジトリ内のすべての統計情報を 格納。データベース統計情報を記録して収集するた めの最適な方法。

• Oracle Enterprise Manager

EM

– パフォーマンス・データの収集、格納およびレポー トを行うためのグラフィカル・ユーザー・インタ フェースを提供。

• BSTAT/ESTAT

–

10g

• %ORACLE_HOME%\RDBMS\admin\utlbstat.sql、utlestat.sql

47

履歴データとベースラインの重 要性

• 問題が発生した時、正常稼動時の

データと比較することで何が変化し

たかを見る

48

StatsPack

• パフォーマンス・データの収集、自動化、格納お よび表示ができるSQL、PL/SQLおよびSQL*Plus ス クリプトのセット

• パフォーマンス統計をOracle表に永続的に格納し

、後でレポート作成および分析用に使用

• レポートには、instance health and load サマ リー・ページ、リソースを多く使用するSQL 文お よび待機イベントと初期化パラメータ等が含まれ る

• 元々マニュアル記述少なく、10gでは記述削除

49

StatsPack

• 情報取得のたびにSNAP_IDが発行される

• SNAP_ID間の差異をレポートで見る Id Snap Started Level

- --- --- 1 06 Jul 2003 10:38 5 2 06 Jul 2004 10:41 5 3 06 Jul 2005 10:00 5

– 2003年～2004年(ID=1～2)と、2004年～2005年 (ID=2～3)の2つの期間のレポートで比較

– 2003年～2004年(ID=1～2)のレポート内で、2003年 と2004年の違いを見る項目もある

50

StatsPack

• 他の例

– 毎月取得して、どの月にどういう状況にな るかを見る

– 日ごとに取得して、1ヶ月の間で状況の変 化(月初、月中、月末)を見る

– 時間ごとに取得して、1日の間の状況の変 化を見る

51

StatsPack

• インストール



• データ取得



• レポート



• SQLレポート



52

StatsPack

• 実習

– c:\oracle\jisshu\jisshu_statspack.txt

53

StatsPack

• 定期的自動収集も可

• レベル

>= 5；リソース使用率の高いSQL 文に関するパフォーマ

>= 6；SQL計画およびSQL計画使用状況

>= 7；セグメント・レベルの統計(logical reads、db block changes、physical reads、physical writes、

physical reads direct、physical writes

direct、global cache cr blocks served（RAC 専用）、

global cache current blocks served（RAC 専用）、

buffer busy waits、ITL waits、row lock waits)

>=10;追加統計：親ラッチおよび子ラッチ

SQL> EXECUTE STATSPACK.SNAP(i_snap_level=>6);

54

StatsPack

• 不要データ削除

 SPPURGE.SQL、SPTRUNC.SQL

• StatsPack削除  SPDROP.SQL

• 統計収集自動化  SPAUTO.SQL

• マニュアル  SPDOC.TXT

55

オペレーティングシステムのチェック方 法

• システム全体と各

CPU

CPU

• ページングまたはスワッピングがないことを確 認。

• マシン間のネットワーク待機時間が許容範囲内 であることをチェック。

• 応答時間が長いか、キューが長いディスクを検 索する。

• ハードウェア・エラーがないことを確認。

補足

56

Oracle システムにおける誤りの上位 10 項 目

1. 不適切な接続管理

2. カーソルと共有プールの不適切な使用 (バインド変数不使用)

3. 不適切なSQL

4. 標準以外の初期化パラメータ(隠しパラ メータ)の使用

5. 誤ったデータベース I/O の取得(不適切 な配置)

補足

57

Oracle システムにおける誤りの上位 10 項 目

6. REDO ログ設定の問題

7. 空きリスト、空きリスト・グループ、トラン ザクション・スロット（INITRANS）、または ロールバック・セグメントの不足

8. 時間がかかる全表スキャン

9. 大量の再帰的

SQL

10. 配置・移行時のエラー(移行時の索引欠落、

古い統計)

補足

(10gで削除)ディスク内ソート

58

Oracle システムにおける誤りの上位 10 項 目

7. 空きリスト、空きリスト・グループ、トラ ンザクション・スロット（INITRANS）、ま たはロールバック・セグメントの不足

自動UNDO管理

UNDO_MANAGEMENTをAUTOに

自動セグメント管理(ASSM)～セグメント内空き 領域管理

create tablespace ～ segment space management auto/manual

補足

59

アプリケーション

60

チューニング

• たくさんの知識(ハッカー的)

• ツール、読み方

• 原因究明→対処、効果予測

総合力

61

SQLチューニング

• データベースのパフォーマンスは サービスに大きな影響を与える

• HW増設がすぐにできるわけでもなく

、増設だけで解決するわけでもない

• チューニングの目的は、「限られた

システム・リソースの中で、最大限

のパフォーマンス効果を出すこと

62

SQLチューニング

• チューニング

– 論理設計変更 – 物理設計変更

– 初期化パラメータの変更 – SQLチューニング

• SQLチューニング

– 最も効果が期待できる手段。場合によって は、数倍から数百倍のパフォーマンス向上

63

• OS（ハードウェア）リソースのチューニング

– CPUやメモリ、ディスク、ネットワークなど　

• Oracleインスタンス・チューニング

– SGA（システムグローバル領域）、

PGA（プログラムグローバル領域）、

各種バックグラウンド・プロセスなど 初期化パラメータの調整　

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

– プログラムロジックやアプリケーション・サーバの設定などのアプリ ケーションの最適化

• オブジェクトのチューニング

– 表や索引といったデータベース・オブジェクトの設定や設計の変更など 　

• SQLチューニング

– SQL文のチューニング

相互影響度

64

SQLチューニング手順

65

I/O単位

• I/O単位はデータブロック

• データブロック単位でキャッシュされ る

• select文が発行されると、まずキャッ シュを探し、なければファイルを探す

• キャッシュにあればキャッシュ・ヒッ

ト、なければキャッシュ・ミス

66

I/Oを減らす

• キャッシュにあれば速い

• キャッシュになくてもブロックが小 さければ負荷が低いし、キャッシュ が効率的に使える

• ブロック内の断片化は効率を下げる

67

断片化の例

適切な記憶領域パラメータの指定（

PCTFREE

マルチ・ブロック・サイズの使用によって、

SQL

68

オプティマイザ

• 表全体を読み込むのか、索引を使用 するのか、どの索引を使用するのか といった実行計画を決める

• ルールベース

– 一定のルールに従う

• コストベース

– 統計情報に基づき最適な計画を作る

10g

!!

69

レコードアクセス

• 全表スキャン

• 索引スキャン

• ROWIDスキャン

70

結合方法

• ネステッド・ループ結合

• ソート／マージ結合

• ハッシュ結合

71

ネステッド・ループ結合

1. 結合処理の基準となる外部表を決定し、

外部表から得られた結合条件列のデータ を基に索引スキャンが行われる表を内部 表とする。

2. 外部表のレコードごとに内部表にアクセ スし、結合条件を満たすか検査する。

3. 結合条件を満たすレコードを結合して結 果を返す。

4. 表の一部を結合するのに有効

72

ネステッド・ループ結合

件数の少な い方をフル 検索すると 効率がよい

73

ネステッド・ループ結合

• ルールベースでは、結合条件列に 対する索引が片方の表にしか存在 しない場合は、索引が存在する表 が内部表となり、双方に索引が存 在する場合には、FROM句の指定順 番が後ろの表が外部表となる

• コストベースでは、コストに基づ

いて外部表が決定される。

74

ネステッド・ループ結合

• どちらの表を外部表とするかによって

、アクセスするデータ・ブロック数は 大きく異なってくる(deptno=10とか)。

• ネステッド・ループ結合を効率化する

ためには、結合を試みるレコード数が

より少ない方を外部表、レコード数に

大差がない場合には、結合条件列の索

引スキャンが より効率的な方を内部表

とする必要がある。

75

ネステッド・ループ結合

select emp.empno,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno;

実行計画

dept表のdeptnoだけにインデックス(統計無し＝RBO) --- 0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (FULL) OF 'EMP'

3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

参考

76

ネステッド・ループ結合

実行計画

dept表のdeptnoとemp表のdeptno(emp_idx2)にインデックス (RBO)

--- 0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 2 1 NESTED LOOPS

3 2 TABLE ACCESS (FULL) OF 'DEPT'

4 2 INDEX (RANGE SCAN) OF 'EMP_IDX2' (NON- UNIQUE)

参考

77

ネステッド・ループ結合

統計取得後(CBO)

---

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=280)

1 0 HASH JOIN (Cost=5 Card=14 Bytes=280)

2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98)

3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)

78

ネステッド・ループ結合

SQL> select emp.empno,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno and emp.empno=7698;

実行計画

dept表のdeptnoとemp表のdeptno(emp_idx2)にインデックス

（RBO)

--- 0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

3 2 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

参考

79

ネステッド・ループ結合

統計取得後(CBO)

---

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)

1 0 NESTED LOOPS (Cost=2 Card=1 Bytes=20)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=7)

3 2 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

(Cost=1 Card=1 Bytes=13)

5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

80

ソート・マージ結合

1. A表を結合条件列でソートする。

2. B表を結合条件列でソートする。

3. それぞれの表の結果が等しいレ

コードを結合して結果を返す。

81

ソート・マージ結合

82

ソート・マージ結合

• 表の大部分を結合するのに有効

• 結合対象が多く、なおかつ結合条 件が等価条件ではない（＜、＜＝

、＞、＞＝）場合に使用

• ＝の場合はハッシュ結合を使用

83

ハッシュ結合

1. レコード数の少ない表の結合条件列 をハッシュ関数にかけ、メモリ上に ハッシュ・テーブルを作成する。

2. もう一方の表の結合条件列をハッ シュ関数にかけ、結合できるかを ハッシュ・テーブルで確認する。

3. ハッシュ値が等しいレコードを結合

して結果を返す。

84

ハッシュ結合

85

ハッシュ結合

• 結合条件に等価条件（＝）が指定され

、大量のレコード、あるいは表の大部 分を結合する場合に有効

• コストベースの場合のみ使用可能

• ソート／マージ結合に比べ、事前の ソート処理が不要で効率的

• 小さいほうの表が使用可能なメモリー

内に収まる場合に最適

86

オブジェクト統計情報

• 表、索引などのレコード件数や、使用し ている領域、カーディナリティ、データ 分布 などのデータ特性を表す情報

• 9iからは、DBMS_STATSパッケージにてシ ステムの統計情報（システムのI/Oおよ びCPU性能など）を取得することが可能

• 10gでは自動取得～ DB作成時に

GATHER.STATS_JOBでスケジューリング

87

オブジェクト統計情報

• INS/UPD/DELで以下のときに取得

– 統計がない

– 行の10%以上が変更された

• select * from dba_scheduler_jobs where

job_name=‘GATHER_STATS_JOB’

• STATISTICS_LEVEL TYPICAL/ALL で有

効。

88

オブジェクト統計情報

• 手動取得の注意。以下の場合、最適計 画が作れない

– 統計情報取得後に、大幅にデータ件数が 増減した

– 複数の表の結合処理を実行する場合、一 部の表のみ統計情報が古かった

– 複数の表の結合処理を実行する場合、一 部の表のみ統計情報が取得されていな かった

89

ヒント

• オプティマイザは全能ではない

• 適切な計画に対するヒントをユーザが 指定可能

– 最適化アプローチに関するヒント – アクセス・パスに関するヒント – 問い合わせの変換に関するヒント – 結合順序、結合操作に関するヒント – パレレル実行のヒント

– そのほかのヒント

90

ヒント

• 注意

– ヒントの指定方法が間違っていても

、エラー出力はされない

– ヒントの使用により、実行計画が固 定されてしまう　

– ヒント（RULEヒント以外）を使用す

ると、強制的にコストベース・アプ

ローチとなる

91

ヒント

ヒントの種類 ヒントの意味

ALL_ROWS 最高のスループットとなるように最適化される（全表スキャ ン、ソート／マージ結合が選択されやすくなる）

FIRST_ROWS(n) レスポンスタイムを最短にするように最適化される（索引ス キャンとネステッド・ループ結合が選択されやすくなる）

RULE ルールベースのアプローチを選択する FULL 全表スキャンを選択する

INDEX 索引スキャンを選択する HASH ハッシュスキャンを選択する

ORDERED FROM句に指定された順序で表を結合する LEADING 結合順序の最初の表を指定する

STAR 可能な場合、スター問い合わせを選択する USE_NL ネステッド・ループ結合を選択する

USE_MERGE ソート／マージ結合を選択する USE_HASH ハッシュ結合を選択する

APPEND ダイレクト・パスINSERTを選択する

CACHE 取得されたブロックが、バッファ・キャッシュ内でLRUリストの 最後に使用されたものの位置に配置される

そのほかの ヒント

最適化アプ ローチに関す るヒント

アクセス・パ スに関するヒ ント

結合順序に 関するヒント 結合方法に 関するヒント

92

ヒント

SELECT /*+ INDEX(table名 index名)

*/ xx from …

SELECT /*+ APPEND */ xx from …

SELECT /*+ USE_NL(table名) */ xx

from …  指定tableが内部表

93

駄目なSQLとは？

• 検討の対象となるのは、

– 1実行当たりの実行時間が長いSQL – ディスク読み取りブロック数が多い

SQL

– バッファの読み取り数が極端に多い

– SQL 実行回数が極端に多いSQL

94

駄目なSQLを見つける

• アプリケーションが特定できている

– アプリケーションのSQLトレースを取 得する

• アプリが特定できていないか、ト レースの負荷が心配な場合

– 動的パフォーマンスビューからSQLを

抽出する

95

トレースと動的ビュー

。

動的パフォーマンスビュー SQLトレース 調査

可能 な SQL

・ 現在共有SQL領域にキャッシュされてい るSQL

・ 使用するV$表 　- V$SQL

　- V$SQL_TEXT 　- V$SQL_PLAN

・セッションで実行されたすべてのSQL

・初期化パラメータSQL_TRACE=TRUEにする ことで、起動後確立したすべてのセッションの SQL

・DBMS_SYSTEMパッケージを使用し、指定し た別セッションのSQL

主に 確認 でき る項 目

- SQL

- 累積CPU時間 - 累積処理時間

- バッファからの累積読み込みブロック数 - ディスクからの累積読み込みブロック数 - このSQLが実行された累積回数

- 実行計画

- SQL - CPU時間 - 処理時間

- バッファからの読み込みブロック数 - ディスクからの読み込みブロック数 - 実行計画

96

トレースと動的ビュー

。

動的パフォーマンスビュー SQLトレース メ

リッ ト

・アプリケーションの処理速度に対する影響 が少ない

・SQLで簡単に確認可能

・アプリケーション中で実行されている各SQLに 関して、詳細な情報を取得可能

・アプリケーションの速度問題がRDBMS側にあ るか否かの切り分けに使用可能 ~セッション単 位で取得できるから

デメ リッ ト

・1回当たりの実行時間などは平均値しか取 得できない

・メモリ上にキャッシュされているSQLの情 報しか確認できない

・トレースの取得、ファイルへの書き込みに伴う オーバーヘッドがある

・トレースファイル取得のためのディスク領域が 必要

97

動的パフォーマンスビュー

。

• 主に

V$SQL、V$SQL_TEXT、V$SQL_PLANの 3つを使用

• これらは共有SQL領域に保持されて

いるSQLの情報を表示する

98

動的パフォーマンスビュー

。

ビュー名 主な格納情報 主な列名 列値の意味

・SQLの先頭1000bytes SQL_TEXT SQLの先頭1000bytes

・SQLの累積リソース使用

状況 SORTS SORTの回数

FETCHES FETCHの回数 EXECUTIONS 実行回数

USERS_EXECUTING 現在実行中のユーザー数 PARSE_CALLS 解析コールの回数

DISK_READS ディスク読み込み数 BUFFER_GETS バッファ読み込み数 ROWS_PROCESSED SQLが戻す行数 CPU_TIME

処理に使用したCPU時間（マイク ロ秒）

ELAPSED_TIME

処理に使用した経過時間（マイ クロ秒）

ADDRESS ほかのViewとの結合に使用 HASH_VALUE ほかのViewとの結合に使用 V$SQL

99

動的パフォーマンスビュー

。

ビュー名 主な格納情報 主な列名 列値の意味

SQL_TEXT 64bytes単位で分割されたSQL

PIECE 分割されたSQLの断片番号

ADDRESS ほかのViewとの結合に使用 HASH_VALUE ほかのViewとの結合に使用 OBJECT_NAME 実行計画中の表、索引名 OPERATION 実行計画オペレーション OPTIONS

実行計画オペレーションのオプ ション

OPTIMIZER オプティマイザのモード DEPTH 実行計画のTreeの深さ ADDRESS ほかのViewとの結合に使用 HASH_VALUE ほかのViewとの結合に使用 V$SQL_T

EXT

・SQLの全文

V$SQL_P LAN

・SQLの実行計画

100

動的パフォーマンスビュー

。

• 合計実行時間の長いSQLを見つける

– SET LINES 140

COL sql_text      FORM A140

COL buffer_per_run      FORM 999999999999 COL disk_per_run        FORM 999999999999 COL cpu_time      FORM 999999999999 COL elapsed_time        FORM 999999999999 SELECT * FROM

(SELECT

        sql_text,address,hash_value,parse_calls,executions,         buffer_gets,disk_reads,

        buffer_gets/executions buffer_per_run,

        disk_reads/executions disk_per_run,cpu_time,         elapsed_time

FROM v$sql

WHERE executions>0

ORDER BY elapsed_time desc)   -- ←この条件を変更する

WHERE rownum <= 10;       -- ←表示件数はこの値を変更する

101

動的パフォーマンスビュー

。

• 1実行あたりのバッファ読み取り数の多いSQL

– ORDER BY buffer_gets/executions desc

• 累積バッファ読み取り数の多いSQL

– ORDER BY buffer_gets desc

• ディスク読み取り数の多いSQL

– ORDER BY disk_reads desc

• 実行回数の多いSQL

– ORDER BY executions desc

102

SQL全文の取得

。

• V$SQLからADDRESS列、HASH_VALUE列の 値を確認

• V$SQL_TEXTを参照

– set pages 100 feed off timing off echo off lines 140

SELECT sql_text FROM v$sqltext

WHERE hash_value=1273901568 and address='54B4DF20'

ORDER BY piece;

103

取得方法の比較

。

実行計 画の取 得

取得 の手 軽さ

アプリケーショ ン処理全体の 情報取得

各SQLの実 行時間に関 する情報

取得による システム負 荷の低さ

情報取得範囲

SQLトレース＋

TKPROFユーティリ

ティ ◎ △ ◎ ◎ △

インスタンス、または 特定のセッションが実 行する全SQL

SQL*Plusの

AUTOTRACE機能 ○ ◎ × △(*) ◎ 自セッションのSQL 動的パフォーマンス

ビューの利用

（V$SQL、

V$SQL_TEXT、

V$SQL_PLAN） ○

インスタンスで実行さ れたSQL

○ △ ○ ○

(*) SQL*Plus

set timing on

104

SQLトレース使用方法

。

$ sqlplus scott/tiger

SQL> ALTER SESSION SET

TIMED_STATISTICS=TRUE;

SQL> ALTER SESSION SET SQL_TRACE=TRUE;

SQL> SELECT COUNT(*) FROM orders ….

SQL> ALTER SESSION SET SQL_TRACE=FALSE;

SQL> exit

時間に関連する統 計の収集を行う

(

)alter session set tracefile_identifier = 'hata';

→smpl_ora_3240_hata.trc

105

SQLトレース出力先

。

専用サーバ接続：初期化パラメータ

USER_DUMP_DEST で指定されたディ レクトリ

共有サーバ接続：初期化パラメータ

BACKGROUND_DUMP_DEST で指定

されたディレクトリ

106

tkprof使用方法

。

•

$ tkprof ora_11111.trc 11111.prf explain=scott/tiger aggregate=no sys=no sort=fchela

ora_11111.trc

11111.prf

explain=scott/tiger

aggregate=no

SQL

sys=no

sort=fchela

システム内部で 発行する

SQL

107

tkprof使用方法

。

オプション名 説明

EXPLAIN TKPROFユーティリティ実行時の実行計画を出力するためのユー ザー名／パスワードを指定する

DEFAULT：YES

YESを指定した場合、同一のSQLは集計されて、1回だけ出力される NOを指定した場合、SQL単位の集計は行われず、実行された回数 分出力される。SQLを個別に調査したい場合には、NOを設定する 指定したオプションによって降順でSQLが出力される

EXEELA：実行時の経過時間順

EXEDSK：実行時のディスクアクセスブロック数順 EXEQRY：実行時のアクセスブロック数順

FCHELA：フェッチ時の経過時間順

FCHDSK：フェッチ時のディスクアクセスブロック数順 FCHQRY：フェッチ時のアクセスブロック数順

（そのほかにも多数のオプションがある）

DEFAULT：YES

NOを指定すると、リカーシブコールを整形したファイルに含めない AGGREGATE

SORT

SYS

108

EVENT 10046でのSQLトレースの取得

。

• ドキュメントには記載なし

• EVENT と呼ばれるデバッグ用の機能によ

り、 SQL トレースで取得できる情報に加え て、より詳細な情報を取得可能

• EVENT は Oracle の内部動作変更や、デ

バッグなどに使用されるものであり、正式

にサポートされるものではない。使用する

場合は、自己責任において使用のこと。

109

EVENT 10046でのSQLトレースの取得

。

レベル 内容

LEVEL 1 SQL_TRACE機能と同等

LEVEL 4 LEVEL 1の情報に追加して、バインド変数情報が出力される LEVEL 8 LEVEL 1の情報に追加して、待機イベント情報が出力される LEVEL 12 LEVEL 1の情報に追加して、バインド変数情報、待機イベント

情報が出力される

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

SQL> +++

SQL

+++

SQL> ALTER SESSION SET EVENTS '10046

trace name context off';

110

EVENT 10046でのSQLトレースの取得

。

• tkprof で整形

• バインド変数の値を確認する場合

は、 LEVEL 4 もしくは LEVEL 12 で取得した

SQL トレースを TKPROF ユーティリティで整

形せず、直接参照する。

111

EVENT 10046でのSQLトレースの取得

。

PARSING IN CURSOR #2 len=57 dep=1 uid=35 oct=3 lid=35 tim=1071814928612333 hv=1198499521 ad='58cdd7d0'

SELECT * FROM orders WHERE o_orderkey=:b2 AND o_clerk=:b1

END OF STMT

PARSE #2:c=10000,e=8105,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=0,tim=107 1814928612320

BINDS #2:

bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=13  oacfl2=1

size=24 offset=0 bfp=405e2a4c bln=22 avl=02 flg=05

   value=1

bind 1: dty=1 mxl=2000(1000) mal=00 scl=00 pre=00 acfl2=1 oacflg=13

size=2000 offset=0 bfp=405e2224 bln=2000 avl=15 flg=05

   value="Clerk#000000951"

EXEC #2:c=0,e=1637,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=107181492 8614269

FETCH #2:c=1720000,e=1933973,p=23248,cr=24063,cu=0,mis=0,r=1,dep=

1,og=4,tim=1071814930548284

112

別セッションのトレース

sysdba

$ sqlplus /nolog

SQL>connect / as sysdba

トレース取得対象セッションの

SID

SERIAL#

V$SESSION

SQL> SELECT sid,serial#,username,program,machine,status ,last_call_et FROM v$session

WHERE username='SCOTT';

  SID    SERIAL# USERNAME     PROGRAM                MACHINE      STATUS   LAST_CALL_ET

--- --- --- --- ---  --- ---

   10         12 SCOTT        sqlplus@linux006 (TNS V1-

V3)      linux006     INACTIVE      2

113

別セッションのトレース

SQL

SQL> EXECUTE

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESS ION(10,12,TRUE);

SQL

SQL> EXECUTE

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESS ION(10,12,FALSE);

※ http://www.oracle.co.jp/iSeminars/060407_1000/doc/060407_perfo.p df

Oracle Direct iSeminar

114

別セッションにEVENT 10046

EVENT 10046

LEVEL 12

SQL> EXECUTE

DBMS_SYSTEM.SET_EV(10,12,10046,12,'')

;

EVENT 10046

SQL> EXECUTE

DBMS_SYSTEM.SET_EV(10,12,10046,0,'');

シングルコー テーションが

2

つ

115

トレースファイルの見方

。

116

トレースファイルの見方

。

(1) SQL 実行したSQL文

(2) CPU時間（1/100秒単位）

Parse、Execute、Fetchの各CPU時間と合計CPU時間を表示。

1/100秒以下の場合、0秒として計算される (3) 経過時間（1/100秒単位）

Parse、Execute、Fetchの各経過時間と合計経過時間を表示。

1/100秒以下の場合、0秒として計算される (4) ディスクアクセスブロック数 ディスクI/Oが発生し、読み取られたブロック数 (5) バッファアクセスブロック数 メモリ上でアクセスされたブロック数

(6) 行数 このSQLを実行した結果、処理された行数 (7) ライブラリキャッシュでのミス

値が0の場合、共有プール上の解析結果が存在したため、解 析処理が排除されたことを表す

(8) オプティマイザモード SQLを実行したときのオプティマイザモードを表す (9) 解析したユーザー SQL文を解析したユーザーIDを表す

(10)

SQLが実行されたときの実

行計画 SQLが実行されたときの実行計画を表す（詳細は後述）

(11)

TKPROFが実行されたとき

の実行計画 TKPROFが実行されたときの実行計画を表す（詳細は後述）

117

SQL実行時の実行計画

。

•

10

SQL

• 2

TABLE ACCESS FULL

•

OBJ#

「

OBJECT_ID

DBA_OBJECTS

•

cr

r

w

time

118

SQL実行時の実行計画

。

親（ここでは

SORT GROUP BY

（

TABLE ACCESS FULL

実施すると

r

pr

w

pw

cr

r

time

119

TKPROF実行時の実行計画

。

•

11

TKPROF

SQL

• SQL

SQL

120

SQLの分析

。

•

•

•

LINEITEM

、GROUP BY処理が行われていることがわかる。

•

60.5

time

60530996

•

•

121

SQLトレース/tkprof

。

• 実習

– c:\oracle\jisshu\jisshu_trace.txt

122

SQL*PlusのAUTOTRACE

。

• SQL

• SQL

ディスクソートの発生回数なども確認できる

123

AUTOTRACEの設定

。

•初回のみ実施

SYS

plustrce.sql

PLUSTRACE

$ sqlplus /nolog

SQL> CONNECT / AS SYSDBA SQL>

@$ORACLE_HOME/sqlplus/admin/plustrce.s

ql