Microsoft PowerPoint - Oracle8i-tuning⁄U.ppt

(1)

1

Oracle8/8iパフォーマンスチューニングⅡ

パフォーマンスチューニングⅡ

日本オラクル株式会社

(2)

2

アジェンダ

• OLTPシステムのチューニング

• バッチ処理のチューニング

• DSSのチューニング

(3)

3

ＯＬＴＰシステムのチューニング

• ＯＬＴＰシステムの特徴

• チューニング事例

– STEP０エラーの回避

– STEP１共有プールの調整

– STEP２データベース・バッファの最適化

– STEP３Ｉ／Ｏ (ＤＢＷＲ) の最適化

– STEP４ＲＥＤＯＬＯＧ (ＬＧＷＲ) の最適化

– STEP５ロールバックセグメント待ちの解消

(4)

4

チューニング特性の理解

• OLTP処理

– 多同時ユーザー数サポート

– ショートトランザクション（更新処理）

– 定型処理

• DSS処理

– 比較的少ユーザー利用

– DWH

– 検索性能重視

– 非定型問合せ処理（Ad-hoc Query）

– バッチ処理による事前準備

(5)

5

OLTP処理の特徴

• 定型クエリーと定型更新

– あらかじめ予想可能な定型処理が中心

– インデックスの有効活用が可能

• データベースの規模より同時実行ユーザー数

– ショート・トランザクションの多重処理

– データベース・キャッシュの有効利用

• LGWRとDBWRの書き込み最適化

– 大量のランダムWriteリクエストが発生

(6)

6

ＯＬＴＰシステムのチューニング

• ＯＬＴＰシステムの特徴

• チューニング事例

– STEP０エラーの回避

– STEP１共有プールの調整

– STEP２データベース・バッファの最適化

– STEP３Ｉ／Ｏ (ＤＢＷＲ) の最適化

– STEP４ＲＥＤＯＬＯＧ (ＬＧＷＲ) の最適化

– STEP５ロールバックセグメント待ちの解消

(7)

7

チューニングモデル

システム（300M） stock（5,5G） Temp（4G） order_line（5G） ware（4M） iitem（2,5M） icustomer（100M） icustomer2（250M） inew_order（50M） REDOログ（2G） REDOログ（2G）アーカイブ・ログ（2G）アーカイブ・ログ（2G） customer（4G） rollback（300M） iorder_line（2G） history（400M） iorders（450M） iorders2（200M） istock（300M） orders（300M） new_order（125M） item（20M）

2GB

<ハードウェア環境> CPU 250MHz ＊ 4 1GB Main Memory DISK 2G × 18本ユーザ数:１２０によるOLTP処理のシュミレート

(8)

8

STEP０エラーの回避

• 予想されるエラーと回避方法

– ORA-04031（共有プールの不足）

• shared_pool_sizeを大きく取る

– ORA-00020（Processesの不足）

• processesを最大ユーザー数＋6システムプロセス）

まで大きく

– ORA-01552（rollback_segmentsの不足）

• rollback_segmentsの数を最大ユーザー数まで

• max_rollback_segmentsをrollback_segments + 1に

設定

(9)

9

• スタートポイントはデフォルトの初期化パラメータ

• 発生するエラーを回避するために、パラメータを

変更

• エンドポイントはエラーが発生しなくなるまで

スタートポイント

(10)

10

• スタートポイント

– shared_pool_size = 3500000

– processes = 50

– rollback_segments = 4

– max_rollback_segments = 30

• 120 ユーザのシステムを想定

今回のスタートポイント

(11)

11

• ユーザを増やしていった結果３０ユーザで「ORA-04031」

発生

• ３．５ＭＢで２０ユーザまでＯ．Ｋ．

• ２０×６＝１２０ユーザまで実行できるように

３．５ＭＢ×６＝２１ＭＢ

に設定

• 共有プールの更なるチューニングについてはＳＴＥＰ１で

行う

shared_pool_size の調整

(12)

12

• ユーザを増やしていった結果５０ユーザで「ORA-00020」

発生

• １２０ユーザ（１２０ユーザ＋６バックグラウンドプロセス）

まで実行できるように１５０に設定

processes の調整

(13)

13

• ユーザを増やしていった結果１００ユーザで「ORA-1552」

発生

• rollback_segments

– ４ → １２０に変更

• max_rollback_segmensts

– ３０ → １２１に設定

rollback_segments の調整

(14)

14

ＯＬＴＰシステムのチューニング

• ＯＬＴＰシステムの特徴

• チューニング事例

– STEP０エラーの回避

– STEP１共有プールの調整

– STEP２データベース・バッファの最適化

– STEP３Ｉ／Ｏ (ＤＢＷＲ) の最適化

– STEP４ＲＥＤＯＬＯＧ (ＬＧＷＲ) の最適化

– STEP５ロールバックセグメント待ちの解消

(15)

15

STEP1 共有プールの調整

SMON PMON DBWR LGWR ARCH CKPT

共有

プール

ＳＧＡ (システムグローバルエリア)

データベース

バッファ

(16)

16

共有プールの調整

• データベースバッファにできるだけメモリを使わせたい。

• パフォーマンスが落ちない程度に小さくする。

– ピーク時の共有プール使用量の確認

– ライブラリ・キャッシュのチューニング

• キャッシュ・ミスを減らす(SHARED_POOL_SIZE)

– ディクショナリ・キャッシュのチューニング

• キャッシュ・ミスを減らす(SHARED_POOL_SIZE)

(17)

17

17 SQL> select sum(250*users_opening) from v$sqlarea;

SUM(SHARABLE_MEM) 1343625

SQL> select sum(sharable_mem) from v$db_object_cache; SUM(SHARABLE_MEM)

523848

SQL> select sum(sharable_mem) from v$sqlarea; SUM(SHARABLE_MEM) 3547088 TOTAL： 5,414,561 Bytes 使用状況の確認作業

ピーク時の共有プール使用量の確認

ピーク時５．５ＭＢ → 余裕を持って１０ＭＢまで小さくする

(18)

18

• ヒット率（PINHITRATIO）は90%以上が理想

• リロード（RELOADS）は0が理想

– ヒット率が低くリロードがある場合はshared_pool_sizeを増やす

LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS INVALIDATI -- ---BODY 980 .992 980 .981 0 0 CLUSTER 73 .986 107 .981 0 0 INDEX 0 1 0 1 0 0 OBJECT 0 1 0 1 0 0 PIPE 0 1 0 1 0 0 SQL AREA 4936 .921 75297 .99 1 4 TABLE/PROCED 1541 .907 30894 .991 0 0 TRIGGER 0 1 0 1 0 0 90%以上にゼロに近く

ライブラリキャッシュのチューニング

目標

(19)

19

– ミス（GET_MISS, SCAN_MISS）が多い場合はshared_pool_sizeを増やす

– CUR_USAGEがCOUNTに達している場合はshared_pool_sizeを増やす

NAME GET_REQS GET_MISS SCAN_REQ SCAN_MIS MOD_REQS COUNT CUR_USAG --- ---dc_tablespaces 912 10 0 0 0 12 11 dc_free_extents 272 95 36 0 84 64 57 dc_segments 885 90 0 0 41 248 238 dc_rollback_seg 3636 0 0 0 0 364 356 dc_used_extents 79 56 0 0 57 74 53 dc_tablespace_q 2 1 0 0 2 7 1 dc_users 1403 10 0 0 0 25 11 dc_user_grants 907 9 0 0 0 10 9 dc_objects 1252 117 0 0 5 243 238

GET_MISS／GET_REQS ＜０．１５ゼロに近く COUNT > CUR_USAGE

ディクショナリキャッシュのチューニング

(20)

20

ＳＴＥＰ１でのチューニング

• 無駄なメモリーは使わない

– shared_pool_size

• ３５ＭＢ → １０ＭＢ

• パフォーマンス劣化無し

(21)

21

ＯＬＴＰシステムのチューニング

• ＯＬＴＰシステムの特徴

• チューニング事例

– STEP０エラーの回避

– STEP１共有プールの調整

– STEP２データベース・バッファの最適化

– STEP３Ｉ／Ｏ (ＤＢＷＲ) の最適化

– STEP４ＲＥＤＯＬＯＧ (ＬＧＷＲ) の最適化

– STEP５ロールバックセグメント待ちの解消

(22)

22

STEP2 データベース・バッファの調整

共有

プール

データベース

バッファ

(23)

23

データベースバッファの調整

• キャッシュ・ヒット率

– バッファ数の調整(DB_BLOCK_BUFFERS)

• LRUラッチの競合

– LRUリスト数の調整

(DB_BLOCK_LRU_LATCHES)

(24)

24

キャッシュヒット率の確認

• ヒット率 = 1 - (‘physical reads’ / (‘consistent gets’ + ‘db block gets’))

– ヒット率が90%未満の場合はdb_block_buffersを増やす

Statistic Total Per Transact Per Logon Per Second --- ---consistent gets 831184 125.67 5894.92 689.21 db block gets 263825 39.89 1871.1 218.76 physical reads 809041 122.32 5737.88 670.85

I/O統計

ヒット率＝ 1－（809041／（831184＋263825））

＝ 1－809041／1095009

≒ 0.26（26%）

(25)

25

ページングの防止

• db_block_buffersを増やすほどパフォーマンスが向上

• db_block_buffersがあるサイズを超えるとOSのページングおよびスワッ

ピングが発生してパフォーマンスが極端に低下

db_block_buffersサイズと性能 0.00 500.00 1000.00 1500.00 2000.00 2500.00 3000.00 50 ₁₀₀ ₁₅₀ ₂₀₀ ₂₅₀ ₃₀₀ ₃₅₀ ₄₀₀ ₄₅₀ ₅₀₀ ₅₅₀ ₆₀₀ ₆₅₀ ₇₀₀ ₇₅₀ ₈₀₀ Buffer Size (MBytes)

ページング

の発生

(26)

26

データベースバッファの調整

• キャッシュ・ヒット率

– バッファ数の調整(DB_BLOCK_BUFFERS)

• LRUラッチの競合

– LRUリスト数の調整

(DB_BLOCK_LRU_LATCHES)

(27)

27

ロックとラッチ－ロック

・ロックは、Ｏｒａｃｌｅの各リソースをエンキューを使用して排他

制御する

・ ENQUEUEの取得例

enqueue

1

2 プロセスA

プロセスB

(28)

28

ロックとラッチ－ラッチ

・ラッチとは、Oracleのメモリ上のリソースを取得するために

必要な事前に取得する権利

プロセスA

プロセスB

latch

プロセスAがLatchを解放

するまでGetをリトライする

(29)

29

LRUチェーン

LRUWチェーン( ダーティリスト )

バッファ・キャッシュ

先頭

_最後

フリーブロックの検索

全てのバッファはどちらかのリストで

ポイントされている

① LRUリストの末尾から使用可能

バッファを探索する。

② その際使用済みバッファが存在すれ

ば、LRUWリストにエントリーする。

③ 使用可能バッファがあればデータを

キャッシュする。

④ 使用可能バッファが見つからない時

は、DBWRによる書込みを待つ

⑤ 使用済みバッファは、DBWRによって

データファイルに書き込まれると、

使用可能バッファとなり再利用される

使用可能バッファ：変更の加わっていないバッファ

使用済みバッファ： commitの有無に関わらず変更

の加わっているバッファ

(30)

30

30 LRUチェーン LRUWチェーン( ダーティリスト )

ラッチ競合

バッファ・キャッシュ

LRUリストはLRUラッチで管理

サーバプロセスA

LRUラッチ

ラッチを獲得したプロセスが

LRUリストにアクセスできる

サーバプロセスB

ラッチを獲得するまで

待ち状態

競合

(31)

31

31 LRUチェーン LRUWチェーン( ダーティリスト )

ラッチ競合の回避

LRUリストはLRUラッチで管理

サーバプロセスA

LRUラッチ

ラッチを獲得したプロセスが

LRUリストにアクセスできる

サーバプロセスB LRUチェーン LRUWチェーン( ダーティリスト )

バッファ・キャッシュ

LRUラッチ

LRUのセットを複数作成することにより競合を回避できる

・ DB_BLOCK_BUFFERSをLRUセット数で割った分のブロック数を

１つのLRUセットで管理

・ LRUセットの数は

db_block_lru_latches で設定する

(32)

32

LRUリスト数の調整

• データベースバッファにアクセスするプロセスは、まずラッチを獲得す

る必要がある。

• ラッチが獲得できなかったプロセスは、一定時間待った後で再度獲

得を試み、成功するまで繰り返す

• マルチCPU環境で、ヒット率（cache buffers lru）が低い場合、

db_block_lru_latches（デフォルトはCPU数の半分）をCPUと同じ数程

度まで増やしてみる。

LATCH_NAME GETS MISSES HIT_RATIO SLEEPS SLEEPS/MISS --- ---cache buffer handl 162239 97 .999 22 .227 cache buffers chai 32309956 19758 .999 5318 .269 cache buffers lru 90222 10400 .885 2267 .218

ラッチ統計

(33)

33

ＳＴＥＰ２でのチューニング

• データベースバッファは最大に(注)

– db_block_buffers

• ４００ＫＢ → ７５０ＭＢ (1875倍)

• ヒット率２６％ → ９４％

• ラッチ競合の回避

– db_block_lru_latches

• ２ → ４

• HIT_RATIO 89％ → 98％

• 性能１６倍ＵＰ

(注) ページングの発生に注意すること

(34)

34

ＯＬＴＰシステムのチューニング

• ＯＬＴＰシステムの特徴

• チューニング事例

– STEP０エラーの回避

– STEP１共有プールの調整

– STEP２データベース・バッファの最適化

– STEP３Ｉ／Ｏ (ＤＢＷＲ) の最適化

– STEP４ＲＥＤＯＬＯＧ (ＬＧＷＲ) の最適化

– STEP５ロールバックセグメント待ちの解消

(35)

35

STEP３Ｉ／Ｏ (ＤＢＷＲ) の最適化

共有

プール

データベース

バッファ

(36)

36

• ＲＥＤＯログファイルとデータベースファイルを別のディスク

に置く。

• １つのディスクに頻繁にアクセスする表が複数ある場合は、表

を別のデイスクに格納する。

• 索引付きの表に頻繁にアクセスする場合、表と索引を別のディ

スクに格納する。

• 大きな表に頻繁にアクセスする場合、表を複数のディスクに分

割して格納する。( 表のストライピング )

• ソート処理をするユーザの一時表領域を表が格納されているディ

スクと分け、更にストライピングを行う。

• 基本方針に従いファイル配置を決め、テストを実施。あるディ

スクにＩ／Ｏが集中している時は、頻繁にアクセスされるファ

イルをあまりＩ／Ｏが発生していないディスクに移動する。

Ｉ／Ｏ分散の基本方針

(37)

37

37 SVRMGR> Rem I/O should be spread evenly accross drives. A big difference between

SVRMGR> Rem phys_reads and phys_blks_rd implies table scans are going on. SVRMGR> select table_space, file_name,

2> phys_reads reads, phys_blks_rd blks_read, phys_rd_time read_time, 3> phys_writes writes, phys_blks_wr blks_wrt, phys_wrt_tim write_time, 4> megabytes_size megabytes

5> from stats$files order by table_space, file_name;

TABLE_SPACE FILE_NAME READS BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME --- --- --- --- --- --- --- -RBS /DISK2/rbs01.dbf 26 26 50 257 257 411 SCOTT_DATA /DISK4/scott_dat.dbf 65012 413752 38420 564 564 8860 SCOTT_INDEX /DISK4/scott_idx.dbf 8 8 0 8 8 0 SYSTEM /DISK1/sys01.dbf 806 1538 1985 116 116 1721 TEMP /DISK1/tmp01.dbf 168 666 483 675 675 0 USER_DATA /DISK3/user01.dbf 8 8 0 8 8 0 6 rows selected.

Ｉ／Ｏの監視

• ＵＴＬＢＳＴＡＴ／ＵＴＬＥＳＴＡＴ

ＵＴＬＢＳＴＡＴ／ＵＴＬＥＳＴＡＴ

ＵＴＬＢＳＴＡＴ／ＵＴＬＥＳＴＡＴにて特定ディスクにＩ／Ｏが発生していない

か確認する

(38)

38

ＳＴＥＰ３でのチューニング

• ディスク負荷のバランスを取る

– アクセスが高い表に対するDISKを増やす(stock表と

customer表)

– REDOログに格納しているディスクのBUSY率が低かっ

たので、これに割り当てられているDISKを減らす

• CPU使用率のWAIT 40% → 25%

• パフォーマンス１５％UP

(39)

39

ＯＬＴＰシステムのチューニング

• ＯＬＴＰシステムの特徴

• チューニング事例

– STEP０エラーの回避

– STEP１共有プールの調整

– STEP２データベース・バッファの最適化

– STEP３Ｉ／Ｏ (ＤＢＷＲ) の最適化

– STEP４ＲＥＤＯＬＯＧ (ＬＧＷＲ) の最適化

– STEP５ロールバックセグメント待ちの解消

(40)

40

ＲＥＤＯＬＯＧ (ＬＧＷＲ) の最適化

共有

プール

データベース

バッファ

ログ

バッ

ファ

(41)

41

ログバッファ・サイズの調整(1)

• ‘log buffer space’のAvg Timeが5秒（500）以上の場合

– log_bufferを大きくする

– REDOログをストライピングする

Event Name Count Total Time Avg Time --- ---log buffer space 1053 43223 41.05

log buffer space

LGWRがログバッファ中のログをREDOログファイルに書き出すより、バッ

ファが満杯になる速度が早く、バッファへのスペース・リクエストが発生す

るので、待ちが発生している状態。

(42)

42

ログバッファ・サイズの調整(2)

• redo log space requestsが0以外の場合

– log_bufferを大きくする

Statistic Total Per Transact Per Logon Per Second --- ---redo entries 2617867 23.49 18566.43 2054.84 redo log space requests 237 0 1.68 .19 redo size 782928712 7025.31 5552685.9 614543.73 redo small copies 413287 3.71 2931.11 324.4

redo log space requests

(43)

43

ＳＴＥＰ４でのチューニング

• STEP4 REDOログの最適化

(大量更新では効果大)

– log_buffer を8192(デフォルト)、32768、163840

と変化させたが性能の向上が見られなかった

(44)

44

ＯＬＴＰシステムのチューニング

• ＯＬＴＰシステムの特徴

• チューニング事例

– STEP０エラーの回避

– STEP１共有プールの調整

– STEP２データベース・バッファの最適化

– STEP３Ｉ／Ｏ (ＤＢＷＲ) の最適化

– STEP４ＲＥＤＯＬＯＧ (ＬＧＷＲ) の最適化

– STEP５ロールバックセグメント待ちの解消

(45)

45

STEP５ロールバックセグメント待ちの解消

• trans_tbl_waits = 0が理想

• trans_tbl_waits / trans_tbl_gets > 0.05 (5%)の場合は、ロー

ルバック・セグメントを追加する

UNDO_SEGMENT TRANS_TBL_GETS TRANS_TBL_WAITS UNDO_BYTES_WRITTEN SEGMENT_SIZE_BYTES XACTS SHRINKS WRAPS

0 8 0 0 407552 0 0 0 235 2665 1 1890278 796672 0 0 16 236 2783 0 1898696 264192 0 0 17

(46)

46

ＳＴＥＰ５でのチューニング

• STEP5 ロールバックセグメントの最適化

( MAX User を意識した設定にする)

– 初期の段階で十分に設定したためチューニン

グは行わなかった

(47)

47

チューニング効果の測定

• それぞれのステップを実施（倍率で表示）

STEP

0

1

2

3

4

5

15

30 倍倍倍倍

(注) このデータはある特定の条件下でのテスト結果ですあくまでも1つの例としてお考え下さい

(48)

48

アジェンダ

• OLTPシステムのチューニング

• バッチ処理のチューニング

• DSSのチューニング

(49)

49

バッチ処理のチューニング

ダイレクトロード

REDO情報の書込み中止

ロールバックセグメント

一時表領域

(50)

50

SQL*Loader

制御ファイル

テキスト・ファイル

データベース

表

**SQL*Loader**

INSERT

表

テキスト・ファイルのデータをデータベースの表に取りいれる

ためのユーティリティ

SQL*Loader 従来型パス

従来型パス

ダイレクト・パス

従来型パス

(51)

51

SQL*Loaderのダイレクト・ロード

• データベースのブロックイメージを直接作るの

で、処理が早い

sqlldr scott/tiger control=lineitem.ctl direct=true

従来型パス従来型パス従来型パス従来型パスダイレクト・パスダイレクト・パスダイレクト・パスダイレクト・パス 0 2000 4000 8000 6000 処理時間 (係数値)

400MBのデータをロード

のデータをロード

制御ファイル制御ファイル制御ファイル制御ファイルデータ・ファイルデータ・ファイルデータ・ファイルデータ・ファイル

データベース

表

SQL*Loader

ブロック

表

(52)

52

バッチ処理のチューニング

ダイレクトロード

REDO情報の書込み中止

ロールバックセグメント

一時表領域

(53)

53

REDO情報の書込み中止

REDOログを書き出さないため、高速な処理が可能

例) データ件数 200,731件の列にNon_Unique索引作成

Create Index …

LOGGING

Create Index …

NOLOGGING

27％短縮

大量データのロード後の索引作成などに効果大

索引作成後は、必ずバックアップの取得を運用に組み込むこと

(54)

54

バッチ処理のチューニング

ダイレクトロード

REDO情報の書込み中止

ロールバックセグメント

一時表領域

(55)

55

INITIAL , NEXTで指定するサイズは必ず同一サイズにする

専用の表領域に配置する

扱うデータよりも大きなものを数個用意する

OLTP用のRBSと使い分ける

ロールバックセグメントのチューニング

(56)

56

ＲＢＳ RB_BIG

ＲＢＳ

RB_SMALL1

ＲＢＳ

RB_SMALL2

BEGIN

SET TRANSACTION USE RALLBACK SEGMENT

rb_big

…

END ；

<バッチ処理>

オンラインプロセス1

オンラインプロセス2

オンラインプロセス3

オンラインプロセス4

特定のRBSが設定されていない場合はOracle

が自動的に割り当てる

ロールバックセグメントの使い分け

(57)

57

バッチ処理のチューニング

ダイレクトロード

REDO情報の書込み中止

ロールバックセグメント

一時表領域

(58)

58

一時表領域

• INDEX作成やデータの集計などソート処理を伴うバッチ的

な処理には高速なディスクを用意する

• 専用の表領域を使用する

(CREATE TABLE SPACE ～ TEMPORARYを使用)

• INITIALとNEXTを同じ値にPCTINCREASEを０に設定

• INDEX作成などの大量のソートを伴うバッチ処理では、大

きなサイズにする

(59)

59

アジェンダ

• OLTPシステムのチューニング

• バッチ処理のチューニング

• DSSのチューニング

(60)

60

ＤＳＳのチューニング

• ＤＳＳシステムの特徴

• Ｉ／Ｏのチューニング

• パフォーマンスアップの為の機能

– ビットマップインデックス(チューニングⅠ参照)

– ハッシュジョイン

– パラレルクエリー

– パーティション化

– マテラアライズド・ビュー

• チューニング事例

(61)

61

チューニング特性の理解

• OLTP処理

• 多同時ユーザー数サポート

• ショートトランザクション（更新処理）

• 定型処理

• DSS処理

• 比較的少ユーザー利用

• DWH

• 検索性能重視

• 非定型問合せ処理（Ad-hoc Query）

• バッチ処理による事前準備

(62)

62

ＤＳＳ処理の特徴

• 非定型クエリーへの対応

– 全件検索処理の効率化

– 既存のインデックスの有効活用

• 大規模データベースへの対応

– 検索範囲の明確化と絞り込み

– パラレル検索処理の活用

• 日次バッチ処理の高速化

– データの大量一括処理(Update/Delete/Insert)

(63)

63

ＤＳＳのチューニング

• ＤＳＳシステムの特徴

• Ｉ／Ｏのチューニング

• パフォーマンスアップの為の機能

– ビットマップインデックス(チューニングⅠ参照)

– ハッシュジョイン

– パラレルクエリー

– パーティション化

– マテラアライズド・ビュー

• チューニング事例

(64)

64

Ｉ／Ｏのチューニング

• 特に注意すべき部分

– 大規模な表が格納されている表領域のＩ／Ｏを分散す

る。

– 一時表領域はＤＳＳではネックになりやすいのでＩ／Ｏ

を分散の効果大。

• ソート処理

• ジョイン処理

– ハッシュ・ジョイン／ソートマージジョイン

(65)

65

ソート処理の確認方法

• UTLBSTAT/UTLESTATにてディスクでソート処理

が起こっているか確認する

SVRMGR> Rem The total is the total value of the statistic between the time SVRMGR> Rem bstat was run and the time estat was run. Note that the estat SVRMGR> Rem script logs on as "internal" so the per_logon statistics will SVRMGR> Rem always be based on at least one logon.

SVRMGR> select n1.name "Statistic", ・

Statistic Total Per Transact Per Logon Per Second --- ---CPU used by this session 536 536 268 1.68

・・ sorts (memory) 94 94 94 1.43 sorts (disk) 1 1 1 0.01 sorts (rows) 3743 3743 3743 136.86

テンポラリー表領域を使用してソートが行われたことを示している

DSS

DSSでは、すべてのソート処理を

では、すべてのソート処理を

メモリ上で実行することは困難。

できるだけ

できるだけSORT_AREA_SIZE

SORT_AREA_SIZE

SORT_AREA_SIZEを

を

大きくする。

(66)

66

ＤＳＳのチューニング

• ＤＳＳシステムの特徴

• Ｉ／Ｏのチューニング

• パフォーマンスアップの為の機能

– ビットマップインデックス(チューニングⅠ参照)

– ハッシュジョイン

– パラレルクエリー

– パーティション化

– マテラアライズド・ビュー

• チューニング事例

(67)

67

SELECT

SELECT ename

ename

ename ,

, ,

, emp

emp

emp....deptno

deptno

deptno ,

deptno

, loc

, ,

loc

loc FROM

FROM

FROM emp

emp

emp , dept

, dept

WHERE

WHERE emp

emp

emp....deptno

deptno

deptno = dept.

= dept.

= dept.deptno

= dept.

deptno

ジョイン処理とは

EMP

EMP表

表

ENAME DEPTNO

SMITH 20

ALLEN 30

WARD 20

JONES 40

…

DEPT

DEPT表

表

DEPTNO LOC

10 BOSTON

20 NEW YORK

30 DALLAS

40 CHICAGO

…

結果

＋

ENAME DEPTNO LOC

SMITH 20 NEWYORK

ALLEN 30 DALLAS

WARD 20 NEWYORK

JONES 40 CHICAGO

…

３つのジョイン方法

１．

１．ネステッドループジョイン

ネステッドループジョイン

２．

２．ソートマージジョイン

２．

ソートマージジョイン

３．

３．ハッシュジョイン

３．

ハッシュジョイン

(68)

68

ネステッドループジョイン／

ソートマージジョイン

ネステッドループジョイン

① EMP表を全表走査する

② EMP表の各行について、DEPTNOが対応するものを探す

③ EMP表の各行と②で得たデータを結合し、結果を返す

SELECT

SELECT ename

ename

ename ,

, ,

, emp

emp

emp．

．

．deptno

deptno ,

deptno

, ,

, loc

loc

loc FROM

FROM

FROM emp

emp

emp , dept

, dept

WHERE

WHERE emp

emp

emp．

．

．deptno

deptno = dept

deptno

= dept

= dept．

．

．deptno

deptno

ソートマージジョイン

① EMP表とDEPT表を、DEPTNO順にソートする

② EMP表とDEPT表をマージし、その結果を返す

ソートが発生するので、

ソートが発生するので、SORT_AREA_SIZE

SORT_AREA_SIZE

SORT_AREA_SIZE で指定したメモリ領域

で指定したメモリ領域

で処理がしきれないと一時表領域を使用する

(69)

69

ハッシュテーブル

＜ＳＴＥＰ１＞

ハッシュテーブルの作成

（

（（

（ビルド・フェーズ

ビルド・フェーズ

ビルド・フェーズ）

））

）

＜ＳＴＥＰ２＞

外部表の読み込みとジョイン（

外部表の読み込みとジョイン（プローブ･フェーズ

プローブ･フェーズ

プローブ･フェーズ）

））

）

DEPTNO

modulo

modulo ３

３

３ ハッシュジョイン（１）

DEPT

DEPT表

表

DEPTNO LOC

10 BOSTON

20 NEW YORK

30 DALLAS

40 CHICAGO

…

＜ＳＴＥＰ１＞

ハッシュテーブルの作成

ハッシュテーブルの作成（

ハッシュテーブルの作成

（（

（ビルド・フェーズ

ビルド・フェーズ

ビルド・フェーズ）

ビルド・フェーズ

））

）

片方のテーブルを読み、ハッシュ関数をかけ、ハッシュテーブル

片方のテーブルを読み、ハッシュ関数をかけ、ハッシュテーブル((((一種の索引

一種の索引

一種の索引)

一種の索引

)

をメモリ内に作成する

０

１

２

２ (30

(30

(30 DALLAS )

DALLAS )

(10

(10 BOSTON (40 CHICAGO)

BOSTON (40 CHICAGO)

(20

(20 NEW YORK

NEW YORK

ハッシュインデックス

(70)

70

ハッシュジョイン（２）

ENAME DEPTNO LOC

SMITH 20 NEWYORK

ALLEN 30 DALLAS

WARD 20 NEWYORK

JONES 40 CHICAGO

…

ハッシュテーブル

００００１１１１２２２２ (10 (10 (10

(10 BOSTON (40 CHICAGO) BOSTON (40 CHICAGO) BOSTON (40 CHICAGO) BOSTON (40 CHICAGO) (20

(20 (20

(20 NEW YORK NEW YORK NEW YORK NEW YORK

EMP表

ENAME DEPTNO

SMITH 20

ALLEN 30

WARD 20

JONES 40

…

DEPTNO

DEPTNO にハッシュ関数をかけ

にハッシュ関数をかけ

該当するものを探索

ジョイン

ハッシュエリアを展開するメモリ

領域はPGA内に取られる

＜ＳＴＥＰ２＞

外部表の読み込みとジョイン（

外部表の読み込みとジョイン（プローブ･フェーズ

外部表の読み込みとジョイン（

プローブ･フェーズ

プローブ･フェーズ）

））

）

０１２ (30 DALLAS ) (10 BOSTON (40 CHICAGO) (20 NEW YORK

(71)

71

• ハッシュジョイン処理に使用されるメモリの最大値を指定する

初期化パラメータ

• サーバプロセス毎に動的に割り当てられる（ＰＧＡの領域を利

用）

• ハッシュジョイン処理が、ＨＡＳＨ_AREA_SIZE を越える領域が

必要な場合、一時表領域を使用する

• デフォルトはＳＯＲＴ_ＡＲＥＡ_ＳＩＺＥの２倍

• 一時表領域への書込みが発生している場合、大きく設定する

– スワップが発生するほど大きく設定しない事

– サーバプロセス毎に取られる事にも注意する事

ＨＡＳＨ_AREA_SIZE のチューニング

(72)

72

ジョイン処理に対するヒント

ORDERD FROM句で指定された順序で表を結合させる

USE_NL(table) ネスティッド・ループ・ジョインで結合させる

USE_MERGE(table) ソート・マージ・ジョインで結合させる

USE_HASH(table) ハッシュジョインで結合させる

(73)

73

ＤＳＳのチューニング

• ＤＳＳシステムの特徴

• Ｉ／Ｏのチューニング

• パフォーマンスアップの為の機能

– ビットマップインデックス(チューニングⅠ参照)

– ハッシュジョイン

– パラレルクエリー

– パーティション化

– マテラアライズド・ビュー

• チューニング事例

(74)

74

パラレル・クエリー

A A A A B B B B C C C C A A A A B B B B C C C C A A A A B B B B C C C C 問合わせ問合わせ問合わせ問合わせサーバーサーバーサーバーサーバー A AA A 問合わせ問合わせ問合わせ問合わせサーバーサーバーサーバーサーバー B BB B 問合わせ問合わせ問合わせ問合わせサーバーサーバーサーバーサーバー C C C C 問合わせ問合わせ問合わせ問合わせサーバーサーバーサーバーサーバー F FF F 問合わせ問合わせ問合わせ問合わせサーバーサーバーサーバーサーバー E EE E 問合わせ問合わせ問合わせ問合わせサーバーサーバーサーバーサーバー D D D D 問合わせ問合わせ問合わせ問合わせコーディネータコーディネータコーディネータコーディネータ

①

①SQL

SQL

SQL文の

文の

実行

②問合わせサーバーの確保

③読み込み範囲の指定

④データの読み込み

⑤ソートの実行

⑥検索結果

を戻す

SELECT deptno,sum(sal) FROM emp GROUP BY deptno;

並列度３

(75)

75

並列度の設定

① ＳＱＬ文でのヒント句での並列度

– SELECT /+ FULL(emp) PARALLEL(emp,5) / ～

② 表定義でのパラレル句での並列度

– CREATE TABLE ～

PARALLEL (DEGREE 3 )

③ データベースのデフォルト並列度

優

先

度

(76)

76

パラレル・クエリー関連の重要パラメータ

PARALLEL_MIN_SERVERS … インスタンス起動時に作成する

問合せサーバ数

PARALLEL_MAX_SERVERS … システムで使用できる最大の

問合せサーバ数

目安 PARALLEL_MAX_SERVERS = (使用する最大の並列度)

× (問合せの同時実行数) × ２

(77)

77

パラレルクエリーのチューニング

• 各リソースとのバランスを考え、並列度を決めていく

– ＣＰＵ

• ＣＰＵを使い切っているようだったら、並列度を下げ、

余裕があるようだったら並列度を上げる

– ＤＩＳＫ

• 少なくとも並列度と同じ台数のDISKにデータがスト

ライピングされている事が望ましい

– メモリ

• 問合わせサーバ毎にSORT_AREA_SIZE 、

HASH_AREA_SIZE で設定したメモリを使用する事

に注意

(78)

78

ＤＳＳのチューニング

• ＤＳＳシステムの特徴

• Ｉ／Ｏのチューニング

• パフォーマンスアップの為の機能

– ビットマップインデックス(チューニングⅠ参照)

– ハッシュジョイン

– パラレルクエリー

– パーティション化

– マテラアライズド・ビュー

• チューニング事例

(79)

79

パーティション化

• Oracle8 R8.0のパーティショニング

– レンジパーティショニング

• Oracle8

i

のパーティショニング

– ハッシュパーティショニング

– コンポジットパーティショニング

• Oracle8

i

での機能強化・変更点

– パーテイション・プルーニング

– パーテイション・ワイズ・ジョイン

(80)

80

パーティション化とは

• 大規模な表や索引を複数の小さな部

分(パーティション)に分割

• パーティション毎にセグメント

• 管理性向上

• 可用性向上

• パフォーマンス向上

(81)

81

レンジ・パーティショニング

• 管理性向上

• 可用性向上

• パフォーマンス向上

_{レンジ・パーティショニング}

パーティショニング・カラムの値が

どの｢レンジ｣に入っているか

どの｢レンジ｣に入っているかによって

によって

格納するパーティションを決定

salesdate

～ 1997-03-31 1997-04-01 ～ 1997-06-30 1997-07-01 ～ 1997-09-30 1997-10-01 ～ 1997-12-31 sales97q1 sales97q2 sales97q3 sales97q4 sales97 表領域q1 表領域q4 表領域q3 表領域q2 1997-04-01 1997-07-01 1997-10-01 1998-01-01

(82)

82

管理操作性の向上

• パーティション単位で管理可能

• 時系列データベースの定期メンテナンス

– 日付のレンジでパーティション化

sales 97q1 sales 97q2 sales 97q3 sales 97q4 sales 96q4

Partitioned Table

P1

P2

P3

DROP PARTITION ...

ローディング

ローディング((((sqlldr

sqlldr

sqlldr))))

sqlldr

ANALYZE...PARTITION

(83)

83

可用性の向上

• 定期メンテナンス時の可用性

– 管理作業をパーティション単位で実行

– 管理作業を複数のパーティションで同時に(パ

ラレルに)実行

• 不慮の障害時の可用性

– パーティション毎に別々のディスクを使用

– パーティションを細かく分割

•影響範囲の減少

•停止時間の短縮

Partitioned Table

P1

P2

P3

ローディングローディングローディングローディング((((sqlldrsqlldrsqlldrsqlldr)))) INSERT INTO ... INSERT INTO ...INSERT INTO ... INSERT INTO ... DROP PARTITION ... DROP PARTITION ...DROP PARTITION ... DROP PARTITION ...

(84)

84

パフォーマンスの向上

• 効果的なＩ／Ｏ負荷分散

• パーティション・エルミネーション

Hot Data Old Histrical Data 高速ストライプ高速ストライプ高速ストライプ高速ストライプボリュームボリュームボリュームボリューム低速大容量低速大容量低速大容量低速大容量ディスクディスクディスク

ディスク

sales97q1

sales97q2

sales97q3

sales97q4

sales97

SELECT ... FROM sales97

WHERE date=TO_DATE(‘1997-09-16’,’YYYY-MM-DD’);

必要なパーティションのみにアクセス

(85)

85

パーティション化

• Oracle8 R8.0のパーティショニング

– レンジパーティショニング

• Oracle8

i

のパーティショニング

– ハッシュパーティショニング

– コンポジットパーティショニング

• Oracle8

i

での機能強化・変更点

– パーテイション・プルーニング

– パーテイション・ワイズ・ジョイン

(86)

86

Oracle8

i

で追加されたパーティショニング・メソッド

コンポジット・

パーティショニング

ハッシュ・

パーティショニング

New

(87)

87

ハッシュ・パーティショニング

パーティション数

パーティション数:8

ハッシュ関数

パーティショニング・カラムとパーティション数を基に

Oracle内部のハッシュ関数で自動的にパーティションに分

割

データの分散が簡単に出来る

パーティション数:8

パーティショニング・カラム

番号

名前

1 川上

2 三好

3 島野

4 小野

5 大西

1 川上 2 三好 3 島野 4 小野 5 大西

(88)

88

パーティション・プルーニング

• =、INまたはIS NULL条件の時にパーティション・プルー

ニング(エリミネーション)可能

• 範囲条件の時はパーティション・プルーニングできない

ハッシュ・パーティショニングされたテーブルのハッシュ・パーティショニングされたテーブルのハッシュ・パーティショニングされたテーブルのハッシュ・パーティショニングされたテーブルのパーティション・プルニングパーティション・プルニングパーティション・プルニングパーティション・プルニング 0 5,000 10,000 15,000 20,000 25,000 30,000 35,000 40,000 45,000 "="条件 "IN"条件範囲条件ブロック P1 P2 P3 P4 P5 P6 P7 P8

1. col = 5

2. col IN (1,2,3,4)

3. col < 5

(89)

89

ハッシュ・パーティショニング

～可用性～

ハッシュ関数

(orderkey)

レンジ・パーティショニング

1～3月

(orderdate)

Q2～Q4のデータは使用可能

ハッシュ・パーティショニング

どのデータが使用可能か

わからない

4～6月

(orderdate)

7～9月

(orderdate)

10～12月

(orderdate)

(90)

90

ハッシュパーティショニング

• 簡単に必要なパーテイションにサイズの偏りなく、データ

Microsoft PowerPoint - Oracle8i-tuning⁄U.ppt

1

Oracle8/8iパフォーマンスチューニングⅡ

パフォーマンスチューニングⅡ

パフォーマンスチューニングⅡ

パフォーマンスチューニングⅡ

日本オラクル株式会社

日本オラクル株式会社

日本オラクル株式会社

日本オラクル株式会社

2

アジェンダ

• OLTPシステムのチューニング

• バッチ処理のチューニング

• DSSのチューニング

3

ＯＬＴＰシステムのチューニング

• ＯＬＴＰシステムの特徴

• チューニング事例

– STEP０ エラーの回避

– STEP１ 共有プールの調整

– STEP２ データベース・バッファの最適化

– STEP３ Ｉ／Ｏ (ＤＢＷＲ) の最適化

– STEP４ ＲＥＤＯ ＬＯＧ (ＬＧＷＲ) の最適化

– STEP５ ロールバックセグメント待ちの解消

4

チューニング特性の理解

• OLTP処理

– 多同時ユーザー数サポート

– ショートトランザクション（更新処理）

– 定型処理

• DSS処理

– 比較的少ユーザー利用

– DWH

– 検索性能重視

– 非定型問合せ処理（Ad-hoc Query）

– バッチ処理による事前準備

5

OLTP処理の特徴

• 定型クエリーと定型更新

– あらかじめ予想可能な定型処理が中心

– インデックスの有効活用が可能

• データベースの規模より同時実行ユーザー数

– ショート・トランザクションの多重処理

– データベース・キャッシュの有効利用

• LGWRとDBWRの書き込み最適化

– 大量のランダムWriteリクエストが発生

6

ＯＬＴＰシステムのチューニング

• ＯＬＴＰシステムの特徴

• チューニング事例

– STEP０ エラーの回避

– STEP１ 共有プールの調整

– STEP２ データベース・バッファの最適化

– STEP３ Ｉ／Ｏ (ＤＢＷＲ) の最適化

– STEP４ ＲＥＤＯ ＬＯＧ (ＬＧＷＲ) の最適化

– STEP５ ロールバックセグメント待ちの解消

7

チューニングモデル

2GB

2GB

2GB

2GB

2GB

2GB

2GB

2GB

2GB

2GB

2GB

2GB

2GB

2GB

2GB

2GB

2GB

2GB

8

STEP０ エラーの回避

• 予想されるエラーと回避方法

– STEP０エラーの回避

– STEP１共有プールの調整

– STEP２データベース・バッファの最適化

– STEP３Ｉ／Ｏ (ＤＢＷＲ) の最適化

– STEP４ＲＥＤＯＬＯＧ (ＬＧＷＲ) の最適化

– STEP５ロールバックセグメント待ちの解消

– STEP０エラーの回避

– STEP１共有プールの調整

– STEP２データベース・バッファの最適化

– STEP３Ｉ／Ｏ (ＤＢＷＲ) の最適化

– STEP４ＲＥＤＯＬＯＧ (ＬＧＷＲ) の最適化

– STEP５ロールバックセグメント待ちの解消

STEP０エラーの回避

• ユーザを増やしていった結果３０ユーザで「ORA-04031」

• ３．５ＭＢで２０ユーザまでＯ．Ｋ．

• ２０×６＝１２０ユーザまで実行できるように

３．５ＭＢ×６＝２１ＭＢ

• ユーザを増やしていった結果５０ユーザで「ORA-00020」

• １２０ユーザ（１２０ユーザ＋６バックグラウンドプロセス）

• ユーザを増やしていった結果１００ユーザで「ORA-1552」

– ４ → １２０に変更

– ３０ → １２１に設定

– STEP０エラーの回避

– STEP１共有プールの調整

– STEP２データベース・バッファの最適化

– STEP３Ｉ／Ｏ (ＤＢＷＲ) の最適化

– STEP４ＲＥＤＯＬＯＧ (ＬＧＷＲ) の最適化

– STEP５ロールバックセグメント待ちの解消