Database

2017年4～5月開催「ブートキャンプ特別講座」の資料になります。

Transcript

1. (C) Recruit Technologies Co.,Ltd. All rights reserved. ブートキャンプ Database
2. (C) Recruit Technologies Co.,Ltd. All rights reserved. 自己紹介 • 氏名 鈴木 宏彰 、 森下 健太 • 所属 リクルートテクノロジーズ ITマネジメント統括部 テクノロジープラットフォーム部 プロジェクト基盤グループ
3. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  データベース  rdbmsの特徴  Oracle Databaseの基本  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニングの実践
4. (C) Recruit Technologies Co.,Ltd. All rights reserved. 今日伝えたいこと  リクルートのシステムの開発においてOracleの基礎技術は必須 • リクルートが持つ多くのカスタマ／クライアント情報が データベース上にある 特に大規模システムは、殆どOracle Databaseを利用  rdbmsを有効利用するための基礎知識を押さえる  事例／ノウハウからトラブル対応の基礎を学ぶ
5. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベース  データを統合的に管理して格納  複数のプログラム・人から同時に処理が可能 ⇒ただの「箱」 だけどサービスをする上では なくてはならない
6. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベース  大量データから、素早く必要なデータを取り出す ⇒最適な経路でデータを素早く取り出す ⇒多数のユーザが同時に利用するため、最小リソースで取り出す  同時実効性を高めつつも、データの整合性が必要 ⇒トランザクション内のデータ整合性は サービスの信頼性として必須 データの取り出し、管理の仕組みを データベース管理システムが提供
7. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースの種類  階層型、ネットワーク型  メインフレームとか  関係型 [リレーショナル型] • データを１つの表にまとめるという単純な構造 • 正規化により重複の排除が可能 • 厳密なデータの一貫性  Oracle Database、SQL Server、DB2  MySQL、PostgreSQLなど
8. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースの種類  NoSQL （Not Only SQL） • 分散DBによりスケーラビリティを享受 • 大量データ分析などのパフォーマンスに特化  KVS （memcached、redis、Amazon DynamoDBなど）  カラムストア （Apache Cassandra, Apache HBaseなど）  ドキュメント指向 （MongoDB、Apache CouchDBなど）
9. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  データベース  rdbmsの特徴  Oracle Databaseの基本  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニングの実践
10. (C) Recruit Technologies Co.,Ltd. All rights reserved. rdbmsの特徴 表形式でデータを格納 • 行-列という単純構造で理解しやすい データの一元管理 • １つのデータは１つの場所に存在 トランザクション制御 • 処理の一貫性を保証 SQL(DML/DDL/DCL)を使ってデータを操作 • 処理やフローではなく、データの操作を目的としている
11. (C) Recruit Technologies Co.,Ltd. All rights reserved.  データの一元管理 １つのデータは１つの場所に存在  データ中心アプローチにより、業務プロセスの変更による影響を排除  データの整合性を保持しつつ管理・操作をしやすくする • １つのデータが散在していると、更新時に全箇所を更新しなければならない  正規化・非正規化  第一正規化（非キーの排除） • 重複を排除し主キーを決める  第二正規化（関係性の分離） • 複合主キーを別々の表に分ける  第三正規化 • 主キー以外の重複を排除  非正規化 • 業務プロセスに沿った方が効率的な場合もある
12. (C) Recruit Technologies Co.,Ltd. All rights reserved.  トランザクション制御 １つのトランザクション内でデータの整合性を保証  ACID特性 • トランザクションで必要とされる要素 • NoSQLではACID特性を緩くしてパフォーマンスを重視  Atomicity（原子性） ：commit/rollback  トランザクションの全てが完了するか取り消されるかを保証  Consistency（一貫性）：UNDO  トランザクションの前後でデータの整合性を保証  Isolation（独立性） ：ロック  他のトランザクションからデータを独立することを保証  Durability（永続性） ：REDO／UNDO  完了したトランザクションは永続化されることを保証
13. (C) Recruit Technologies Co.,Ltd. All rights reserved.  トランザクション制御  トランザクション分離レベル  データの一貫性にはロックが必要だが、読み込み性能とは相対関係  分離レベルとは、一貫性と同時実効性の関係性を表したロック方式のこと • 殆どのRDBMSは、Read commited レベル ／現象 Dirty Read （ダーティー リード） Nonrepeatable Read （反復不可能な読み取り） Phantom Read （ファントムリード） 未コミット読み取り 更新済み読み取り 挿入済み読み取り Read uncommitted （コミットされていない読み取り） 発生する 発生する 発生する Read committed （コミットされた読み取り） 発生しない 発生する 発生する Repeatable read （反復可能な読み取り） 発生しない 発生しない 発生する Serializable （直列化可能） 発生しない 発生しない 発生しない
14. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  データベース  rdbmsの特徴  Oracle Databaseの基本  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニングの実践
15. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseの特徴 RAC（実用レベルなshared everything構成）  商用MWとあって、機能に細かいケアがされている  Shared everything構成でも一定の性能を満たしている レガシーで技術者が豊富  rdbmsの圧倒的なシェア  ノウハウがあちこちにある  My Oracle Supportはなかなか情報満載 バージョン  R2神話：10gR1や11gR1よりも、R2の方が安定…!?  12cR2もR1から、程よく”是正”が図られている ライセンス・保守料が高い  ある程度完成されているため、安心に対するコストに見える
16. (C) Recruit Technologies Co.,Ltd. All rights reserved. アーキテクチャ概要
17. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseのアーキテクチャ • Oracle Databaseを構成する要素 ＜インスタンス＞ o メモリ • SGA  表データや実行計画／SQL情報などを保持する共有メモリ領域 • PGA  プロセスごとに確保されるメモリ o プロセス • リスナープロセス  リクエストを受け付けサーバプロセスを起動 • サーバプロセス  ユーザのリクエストごとに処理を実行 • バックグラウンドプロセス  ディスクやメモリへの処理などの内部処理を実行
18. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseのアーキテクチャ • Oracle Databaseを構成する要素 ＜データベース＞ o データベースファイル • データファイル  ユーザデータなどを格納  通常規模により複数のデータファイルに分散 • 制御ファイル  DBの構造、変更反映状況(chekpoint)などの制御情報を格納 • REDOログ  変更履歴（DML/DDLなどによる）を格納
19. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseのアーキテクチャ データベースファイル インスタンス PGA Oracle Database SGA キャッシュ → 検索・更新 PGA PGA PGA PGA バックグラウンドプロセス PGA サーバー プロセス PGA サーバー プロセス PGA サーバー プロセス 書き込み 読み込み リスナー プロセス 制御ファイル REDOログ データ ファイル ▪バックグラウンドプロセス ユーザ処理とは非同期に内部 作業を行うプロセス群 ▪フォアグラウンドプロセス ユーザごとに起動され、ユーザか らリクエストされた処理を実行 ▪インスタンス ・メモリ領域＋書き込みを行う プロセス群 アラートログ 初期化パラメー タ アーカイブログ ▪データベースファイル ・データの格納先 ・データの種類によって幾つか のファイルが存在
20. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseの動作の仕組み ＜（例）更新処理の動作＞ ①REDOログバッファに変更履歴を順番に書き出し ②DBバッファキャッシュに変更前のUNDOデータを書き出し ③REDOログファイルに非同期でLGWが書き出し ④データファイルへ内部的なタイミングで反映（未コミットデータ含） ▪ユーザ処理 1) update test_tb set a = ‘Y’ where a = ‘X’ ; 2) commit; Oracle Database SGA PGA REDO DB REDO SQL X X Y DB Y SNC X → Y 1 2 3 4 X → Y ▪REDOログファイル ・変更履歴を格納 ▪制御ファイル ・チェックポイント ▪DBバッファキャッシュ 読み込んだデータはDB バッファにキャッシュ ▪データディクショナリ データベース内のオブジェクト定義や ユーザ情報などを管理するための内部表
21. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理
22. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理 • メモリ管理（自動メモリ管理、自動共有メモリ管理） • 自動メモリ管理（11g以降）  SGA内のメモリプールとPGAをOracleが自動で最適化  Hugepageは使えない • 自動共有メモリ管理（10g以降）  SGA内のメモリプールをOracleが自動で最適化 ※併用は不可。本講義では自動共有メモリ前提で進める。 OS/ PGA SGA DB java REDO REDO
23. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理  SGA • データベースプロセス間で共有するメモリ領域 • SGA_MAX_TARGETで上限を設定（＝SGA_TARGETが基本） • 自動共有メモリ管理と初期値の設定は必須 主なSGA内のプール 概要 ポイント DBバッファ db_cache_size ・dbブロックをキャッシュ ・LRUで管理 ・デフォルトはblock_size指定のサイズ ・キャッシュヒット率が90%以下の場合、 ディスク読み込みが頻発しているはず ・バッチ処理前後の状態は要考慮 共有プール shared_pool_size ・SQL文の解析結果や実行計画をキャッシュ ・ライブラリ、ディクショナリ情報をキャッシュ ・Buffer_cacheより優先的に領域を獲得 ・領域が不足すると、ORA-04031 ・内部的にヒープの断片化も起きやすく見積 もりが難しい ・空き領域のモニタリングは必要 REDOログバッファ log_buffer ・DBへの変更履歴をキャッシュ ・redoログファイルへは非同期でフラッシュ ・自動共有メモリ管理の対象外 ・動的に変更ができない領域なので、大きめ のサイズを確保しておく（128～256MB） dbkeepバッファ db_keep_cache_siz e ・db_cache_size以外でdbブロックをキャッシュ ・指定されたセグメントがキャッシュ ・自動共有メモリ管理の対象外 ・対象データの増減により、設定サイズを超 えるので考慮とモニタリングが必要
24. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理  SGA • 自動共有メモリ管理、自動メモリ管理 ▪自動メモリ管理（0 = 無効） SQL> show parameter memory_target NAME TYPE VALUE -------------- ----------- ------ memory_target big integer 0 ▪自動共有メモリ SQL> show parameter sga_max_size NAME TYPE VALUE ----------------- ----------- ------ sga_max_size big integer 700M SQL> show parameter sga_target NAME TYPE VALUE ----------------- ----------- ------ sga_target big integer 700M
25. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理  SGA • 自動調整されるSGAプール SQL> select component, user_specified_size,current_size 2 from v$sga_dynamic_components; COMPONENT USER_SPECIFIED_SIZE CURRENT_SIZE --------------------- ------------------- ------------ shared pool 0 197132288 large pool 0 4194304 java pool 0 4194304 streams pool 0 0 DEFAULT buffer cache 0 515899392 ・・・
26. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理  PGA • サーバプロセスのSQL作業メモリ領域  ソート処理やHASH結合の際に利用 • 各サーバプロセス固有のメモリ（カーソルやセッション情報等）領域は含まれない • PGA_AGGREGATE_TARGETの設定値は上限の目標  あくまでも目標であるため、不足すれば拡大  サーバプロセス • DBセッションに対するサーバプロセス • PROCESSESで上限を指定（バックグラウンドプロセス含） • DBセッション数は、AP/BATCHとFOの考慮が必要 • hugepageを設定しないと極端に使用メモリが肥大化  共有メモリ(SGA)を変更する場合はhugepageサイズに注意
27. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理  バックグラウンドプロセス • インスタンスを構成する内部処理をするプロセス群 主要プロセス 略称（nは複数） 役割 データベースライター DBWn db_buffer_cacheからデータファイルへ書き込み ログライター LGWR Redo_log_bufferからredoログファイルへ書き込み チェックポイント CKPT データファイルや制御ファイルへチェックポイントの記録 システムモニター SMON インスタンスリカバリを実行 プロセスモニター PMON 各種DBプロセスの監視、リカバリを実施 アーカイバ ARCHn アーカイブログの作成
28. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理  PGA • 自動共有メモリ管理、自動メモリ管理 ※詳細は、v$pga_statなどで統統計値の参照が可能  バックグラウンドプロセス • 以下Singleの場合（RACの場合、インスタンス間の管理プロセスが追加） SQL> show parameter pga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 70M $ ps ax | grep ora_ | grep -v grep 3640 ? Ss 0:00 ora_pmon_FTEX 3644 ? Ss 0:02 ora_psp0_FTEX 3648 ? Ss 0:03 ora_vktm_FTEX 3654 ? Ss 0:00 ora_gen0_FTEX 3658 ? Ss 0:00 ora_diag_FTEX 3662 ? Ss 0:00 ora_dbrm_FTEX 3666 ? Ss 0:00 ora_dia0_FTEX 3670 ? Ss 0:00 ora_mman_FTEX 3674 ? Ss 0:00 ora_dbw0_FTEX 3678 ? Ss 0:00 ora_lgwr_FTEX 3682 ? Ss 0:00 ora_ckpt_FTEX 3686 ? Ss 0:00 ora_smon_FTEX 3690 ? Ss 0:00 ora_reco_FTEX 3694 ? Ss 0:00 ora_mmon_FTEX 3698 ? Ss 0:00 ora_mmnl_FTEX 3716 ? Ss 0:00 ora_qmnc_FTEX 3790 ? Ss 0:00 ora_cjq0_FTEX 3794 ? Ss 0:31 ora_vkrm_FTEX 3814 ? Ss 0:00 ora_q000_FTEX 3818 ? Ss 0:00 ora_q001_FTEX 3866 ? Ss 0:00 ora_smco_FTEX 4053 ? Ss 0:00 ora_w000_FTEX
29. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル
30. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル データベースファイルは、以下３つを指す 「データファイル」 「制御ファイル」 「REDOログファイル」  データファイル • データベースのすべてのデータを格納する物理ファイル • サーバプロセスから読み込まれ、DBWnで書き込まれる • 自動拡張により運用負荷を下げれれるが、性能や領域監視も考慮
31. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル  制御ファイル • データベース名や構造に関する情報を格納 • チェックポイント情報（SCN） • REDOログファイル、データファイル、アーカイブログの名前と場所、ステータス • 多重化が基本だが、すべてにアクセス出来る必要がある  バックアップ • バックアップファイルから格納情報を確認 SQL> alter database backup controlfile to trace ; $ ls -lrt /u01/app/oracle/diag/rdbms/upgr/UPGR/trace/ UPGR_ora_*.trc
32. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル  REDOログファイル • データベースへの変更情報(DML,DDL)を順番に記録し、障害時の復旧に使用 • ファイルは循環利用されるため、更新量の考慮が必要 • 以下が書き込みタイミングとなり、更新パフォーマンスに直結  COMMIT時  REDOログバッファの1/3以上を使った場合  前回書き込み時から3秒経過した場合  ダーティバッファをDBWnが書き込む前
33. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル その他のデータベースのファイル  アーカイブログファイル • ARCHIVELOGモードで稼働している場合に作成 • 一杯になったREDOログファイルのオンラインコピー • 最新状態に復旧するためには、アーカイブログファイルが必要 • ということは最新まで復旧する必要がない場合は不要 • NOARCHIVELOGモード • 領域不足等で、REDOログのコピーができなくなるとインスタンスが停止
34. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル その他のデータベースのファイル  初期化パラメータ(pfile)、サーバパラメータファイル(spfile) • pfileはテキストであり動的にパラメータ変更ができない • $ORACLE_HOME/dbs配下に置かれ、pfile→spfileの優先度で読み込み • pfile⇄spfile、どちらからでも生成可能  アラートログファイル • Oracleのインスタンスが出力するログファイル • サーバ側のORA-エラーは、ここに出る
35. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル  表領域、データファイル • dba_tablespaces, dba_data_files. dba_temp_files select dt.tablespace_name, dt.bigfile, ddf.file_name, ddf.bytes/1024/1024 as mb, ddf.maxbytes/1024/1024 as max_mb, ddf.autoextensible as autoextend from (select tablespace_name, file_name, bytes, autoextensible, maxbytes from dba_data_files union select tablespace_name, file_name, bytes, autoextensible, maxbytes from dba_temp_files) ddf, (select tablespace_name, bigfile, initial_extent from dba_tablespaces ) dt where dt.tablespace_name = ddf.tablespace_name order by tablespace_name, file_name;
36. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル  制御ファイル • dba_controle_files SQL> select * from v$controlfile ; STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS --------------- ------------------------------ --- ---------- -------------- /oradata/FTEX/control01.ctl NO 16,384 594 /oradata/FTEX/control02.ctl NO 16,384 594 TABLESPACE_NAME BIG FILE_NAME MB MAX_MB AUT --------------- --- ------------------------------ ------------ ------------ --- AQ NO /tmp/aq01.dbf 25 266 YES SYSAUX NO /oradata/UPGR/sysaux01.dbf 600 32,768 YES SYSTEM NO /oradata/UPGR/system01.dbf 700 32,768 YES TEMP NO /oradata/UPGR/temp01.dbf 35 32,768 YES UNDOTBS1 NO /oradata/UPGR/undotbs01.dbf 365 32,768 YES USERS NO /oradata/UPGR/users01.dbf 5 32,768 YES
37. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル  REDOログファイル • メンバーの追加（冗長化）  グループ：循環方式で追記されるREDOログファイル  メンバー：グループのミラーファイル  TUREAD：RACの場合に各インスタンス毎に採番 SQL> select logfile.group#, log.thread#, logfile.member, log.sequence#, log.bytes/1024/1024 MB, log.status 2 from v$log log, v$logfile logfile where log.group# = logfile.group# order by 1,2,3 ; GROUP# THREAD# MEMBER SEQUENCE# MB STATUS ---------- ---------- ------------------------------ ---------- -------- --------------- 1 1 /oradata/UPGR/redo01.log 40 50 INACTIVE 2 1 /oradata/UPGR/redo02.log 41 50 CURRENT 3 1 /oradata/UPGR/redo03.log 39 50 INACTIVE select logfile.group#, log.thread#, logfile.member, log.sequence#, log.bytes/1024/1024 MB, log.status from v$log log, v$logfile logfile where log.group# = logfile.group# order by 1,2,3 ; alter database add logfile member '/oradata/UPGR/redo11.log' to group 1; alter database add logfile member '/oradata/UPGR/redo12.log' to group 2; alter database add logfile member '/oradata/UPGR/redo13.log' to group 3;
38. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル  REDOログファイル • メンバーの追加（冗長化）  グループ：循環方式で追記されるREDOログファイル  メンバー：グループのミラーファイル  TUREAD：RACの場合に各インスタンス毎に採番 SQL> select logfile.group#, log.thread#, logfile.member, log.sequence#, log.bytes/1024/1024 MB, log.status 2 from v$log log, v$logfile logfile where log.group# = logfile.group# order by 1,2,3 ; GROUP# THREAD# MEMBER SEQUENCE# MB STATUS ---------- ---------- ------------------------------ ---------- -------- --------------- 1 1 /oradata/UPGR/redo01.log 40 50 INACTIVE 1 1 /oradata/UPGR/redo11.log 40 50 INACTIVE 2 1 /oradata/UPGR/redo02.log 41 50 CURRENT 2 1 /oradata/UPGR/redo12.log 41 50 CURRENT 3 1 /oradata/UPGR/redo03.log 39 50 INACTIVE 3 1 /oradata/UPGR/redo13.log 39 50 INACTIVE
39. (C) Recruit Technologies Co.,Ltd. All rights reserved. 論理領域管理
40. (C) Recruit Technologies Co.,Ltd. All rights reserved. 論理領域管理  表領域 • 表領域は表の論理的な格納先、データファイルはデータの物理的な格納先 • 表領域とデータファイルの関係  SMALL表領域（１−ｎ）、BIG表領域（１−１） • 目的ごとにある程度表領域を分離して設計  アプリケーション、メンテナンス、用途(INDEX、LOB)単位など  IO分散は、ディスク側で。 • 事前定義の表領域  ユーザデータは格納しない 表領域 概要 ポイント SYSTEM ディクショナリ情報などの管理表を格納 領域不足になるとDB停止 SYSAUX AWRなどオプションコンポーネントで利用 UNDO 更新前ブロックを保持 一貫性のために更新前データを参照 ロールバックセグメンとを循環方式で利用 自動拡張はOFFか制限 ORA−1555による処理エラーが ないように大きめに設計 TEMP ソート処理や一時表を格納 リカバリには不要な表領域 自動拡張はOFF バッチ処理などの最大読み込み 量からサイジング
41. (C) Recruit Technologies Co.,Ltd. All rights reserved. 論理領域管理  データの論理構造 • セグメント • 物理的な領域を必要とするテーブルや索引などのオブジェクトの論理領域 • エクステント • セグメントを拡張する単位で、連続ブロックから構成 • ブロック • Oracleはブロック単位でアクセスする • ブロックへのデータ格納には注意が必要 • PCTFREEでブロックの使用効率を調整可能 OS Logical physical
42. (C) Recruit Technologies Co.,Ltd. All rights reserved. 論理領域管理  パフォーマンス観点のセグメントの領域管理 データはブロック単位でアクセスされるため、テーブルやインデックスが、 どのタイミングで検索されたり更新されるのか、保持期間はどの程度か 容量見積もりの際に考慮して設計する。 • ブロックの使用効率  PCTFREE：行の更新によるサイズ拡張に備えた空き領域割合 • レコード長の拡張やレコードの競合を避けるために領域を空けておく • レコード長の拡張により１ブロック内に納まらない場合は行移行が発生  ITL：トランザクションがブロックを更新する際に獲得するブロック内のリスト • トランザクションが多くITL獲得待ちが多発する場合は、PCTFREEなどを調整 （更新用） 4行目 3行目 2行目 1行目 PCTFREE:20 % （更新用） 4行目 3行目 2行目のポインタ 1行目 （更新用） 2行目増加 2行目 （更新用） 3行目 2行目 1行目 ①update ②insert maxtrans 2の場 合 ③delete enq ： TX – allocate ITL entry
43. (C) Recruit Technologies Co.,Ltd. All rights reserved. 論理領域管理  パフォーマンス観点のセグメントの領域管理 • HWMの考慮 Oracleはセグメントなどの過去の最大サイズを記憶しており、FullScanの際に たとえ領域を使用していない場合でもHWMまでブロックを読み込みんでしまう 何らかの事情でHWMが上がってしまいパフォーマンス問題となった場合は、 セグメントのshrinkやmoveなどでHWMを下げることを検討する。 セグメント エクステント 空き ブロック ブロック ブロック ブロック データ増加 未使用 HWM データ減少 ここまで読み込んでしまう ブロック ブロック ブロック ブロック
44. (C) Recruit Technologies Co.,Ltd. All rights reserved. REDOとUNDO  REDOとUNDOによるデータ一貫性の保証 • データ一貫性は、REDOとUNDOにの仕組みにより保障 • REDOログは、コミットやDBWr書き込みタイミング等で更新履歴をファイルへ永続化 • UNDOセグメント（ロールバックセグメント）は、変更前データを非同期でファイルへ永続化 • 更新ブロックは、非同期でデータファイルへ永続化 障碍時の動作 1. REDOログから変更をデータファイルへロールフォワード 2. UNDOから未コミットデータをデータファイルへロールバック Oracle Database SGA REDOログ DBライ ター DBライ ター ログライ ター ユーザ表領域 DBバッファキャッシュ REDOログ バッファ UNDO表領域
45. (C) Recruit Technologies Co.,Ltd. All rights reserved. スキーマ
46. (C) Recruit Technologies Co.,Ltd. All rights reserved. スキーマ設計  スキーマ ＝ ユーザ • 論理オブジェクトを所有するネームスペース • オブジェクトを変更することをスキーマ変更と言う（こともある）  権限・ロール • DBA権限 [sysdba] ＝＞ Databaseの管理者 • スキーマに対して、各種権限（アクセス、参照、更新、実行など）を付与  OS認証  DBの起動停止はログインせずに実行が必要  dbaグループに所属するOSアカウントのみ起動停止が可能  この他にプロダクト所有グループのoinstallがある
47. (C) Recruit Technologies Co.,Ltd. All rights reserved. スキーマ設計  スキーマオブジェクト ※代表例  Table • 表  Index • B*Tree：tree構造となっている索引 • Bitmap：各列値の情報をbitmap管理の索引 • 逆キー、ファンクション等…  View • 実態を持たない。使いすぎると可読性が低下  Materialized View • データの実態を持ったView（マテビュー） • 元表への変更を保持したMVIEWログを元にした差分更新と完全リフレシュ  Partition（Tbale/Index） • レンジ ：日付や範囲 などをキーに、セグメントを区分け • リスト ：任意のリストをキーに、セグメントを区分け • ハッシュ ：値のハッシュ値をキーに、セグメントを区分け • コンポジット ：各パーティション分割の組み合わせ
48. (C) Recruit Technologies Co.,Ltd. All rights reserved.  Index • 索引のアーキテクチャと特徴 • B*Tree索引 • ルートブランチ、ブランチブロック、リーフブロックのtree構造 • 範囲検索やカーディナリティが高い検索に効果 • ROWID情報と列値のデータをソートして格納（複合列値も同様） • NULL値は持たないため、IS NULL検索は効かない • リーフ分割などにより、階層が深くなると性能が劣化（BLEVEL4以内目安） ルート A-D E-H -Z … ROWID 列値 XXXXX A0001 XXXXY B0001 ルートブランチ ブランチブロック リーフブロック
49. (C) Recruit Technologies Co.,Ltd. All rights reserved.  Index • 索引のアーキテクチャと特徴 • Bitmap索引 • 列値をグルーピングして有無をbitmapで管理（ROWIDは持たない） • 実はtree構造 • カーディナリティが低い検索に効果 ※男女、都道府県など • リーフブロックの格納行が多いため、更新時にロックが広範囲となる ※一般的にあまりOLTP向きではない • ファンクション索引 • 関数索引 • 逆キー索引 • 通常の索引はソート済みであるが、逆ソートして格納
50. (C) Recruit Technologies Co.,Ltd. All rights reserved.  Partition  パーティション化のポイント  大規模表(数百GBレベル)の、パフォーマンス／メンテナンス性を目的に対象を選出  管理やメンテナンスとトレードオフとなるため、対象の見極めは慎重に • ＜メンテナンス性＞ • 月次パージなどでパーティションごと削除 • レンジの追加などのメンテナンス作業も必要（12cから自動設定も可能） • ＜パフォーマンス：パーティションプルーニング＞ • アクセス不要なパーティションを読み込まない • Where句や結合条件にパーティションキーを含ませる
51. (C) Recruit Technologies Co.,Ltd. All rights reserved.  Partition  パーティションインデックスの考慮  パーティションインデックスの種類 • ローカル索引 • 表のパーティションキーのサブセットでパーティショニング • グローバル索引 • 非パーティション表の索引（＝普通の索引）  考慮ポイント • 可能な限りグローバル索引とならないようにキー設計の段階で考慮する • グローバル索引の場合、メンテナンスコストがかかる
52. (C) Recruit Technologies Co.,Ltd. All rights reserved. Listener/tnsnames • クライアントからのリクエストを受け付け • サーバープロセスを起動 • 以降、クライアントとサーバプロセスでメッセージをやり取り • リスナーの設定ファイル • コンフィグ（${ORACLE_HOME}/network/admin/listener.ora） $ cat ${ORACLE_HOME}/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) ) データベースファイル Oracle Database PG A サーバー プロセス PG A サーバー プロセス リスナー プロセス ① ② ③ 1521 PG A サーバー プロセス sqlplus /as sysdba
53. (C) Recruit Technologies Co.,Ltd. All rights reserved. Listener • リスナーのサービス登録状況の確認 $ lsnrctl status LISTENER LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-APR-2017 18:16:48 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 12-APR-2017 18:14:25 Uptime 0 days 0 hr. 2 min. 22 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0.2/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521))) Services Summary... Service "UPGR" has 1 instance(s). Instance "UPGR", status READY, has 1 handler(s) for this service... Service "UPGRXDB" has 1 instance(s). Instance "UPGR", status READY, has 1 handler(s) for this service... The command completed successfully エンドポイント情報 登録されたサービス情報
54. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  データベース  rdbmsの特徴  Oracle Databaseの基本  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニングの実践
55. (C) Recruit Technologies Co.,Ltd. All rights reserved. クラスタ  複数のサーバを１つに論理的に見せることで、 可用性・負荷分散を高めるためのアーキテクチャ ▪可用性と負荷分散  可用性 障害時にもサービスを継続できる（可用性）ように、 act-act/act-stbyでクラスタやレプリケーションを構成  負荷分散 スループットを最大化させるために、複数サーバで処理を分散
56. (C) Recruit Technologies Co.,Ltd. All rights reserved. クラスタ ▪shared everything/shared nothing  shared everything  共有ディスクを利用し、複数サーバから全てのデータにアクセス  コールドスタンバイ構成（HA）  負荷分散を最大化は出来ず、FO時間にサービス断となるため 可用性が高いとは言い切れない  ホットスタンバイ構成（RAC）  全てのサーバで同一データに参照・更新が可能であるため、 負荷分散・可用性ともに高いものの、サーバ間の同期処理 （キャッシュフュージョン）によるオーバーヘッドがかかる shared everything （コールドスタンバイ） shared everything （ホットドスタンバイ）
57. (C) Recruit Technologies Co.,Ltd. All rights reserved. クラスタ ▪shared everything/shared nothing  shared nothing  各サーバ固有のディスクで処理を行ことで、各サーバのスループットを最大化する  障害時にデータの再配置を行う期間は利用が制限されるため可用性は低い shared nothing
58. (C) Recruit Technologies Co.,Ltd. All rights reserved. RAC
59. (C) Recruit Technologies Co.,Ltd. All rights reserved. Real Application Cluster Oracleのshared everythingクラスタ製品 • すべてのインスタンスから、全てのデータファイル／制御ファイル／REDOログ にアクセス・更新可能  PrivateNetwork（インターコネクト）、投票ディスクを使ってノード間でハートビー ト  REDOログ、UNDO表領域は、インスタンス毎に必要  Singleインスタンスとの違いは、共有ディスクとインターコネクトを使うところ  RACに登録されたリソースをsrvctlコマンドで管理
60. (C) Recruit Technologies Co.,Ltd. All rights reserved. Real Application Cluster Oracle Database リスナー プロセス インスタンス SGA バックグラウンドプロセス PGA 制御ファイル REDOログ データ ファイル ocr vote インスタンス SGA バックグラウンドプロセス PGA UNDO REDOログ UNDO アーカイブログ アーカイブログ 初期化パラメータ PGA サーバー プロセス PGA サーバー プロセス リスナー プロセス Private network
61. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACへのDB接続 • VIP • vipはnodeごとに1つ設定 • RACの場合、DB接続はVIPを指定 • SCAN−VIP（11g以降） • 端的にはVIPの上位のVIP • RACの接続にはサービス名を使う • ORACLE_SID • インスタンスの識別子（主にSingle） • サービス名（service_name） • サービスの識別子 • SCANを使用したサービス接続 • 特定ノードめがけた接続 DriverManager.getConnection("jdbc:oracle:thin:@db201xxx-scan:1522:<Service_name>",”mytuning","mytuning"); DriverManager.getConnection("jdbc:oracle:thin:@db201xxx:1522:<ORACLE_SID>",”mytuning","mytuning"); DriverManager.getConnection("jdbc:oracle:thin:@db201xxx-vip:1522:<Service_name>",”mytuning","mytuning");
62. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACのへのDB接続 • 接続時フェイルオーバー • クライアントからの接続が失敗した場合は、他リスナーへ接続 UPGR_LB = (DESCRIPTION = (LOAD_BALANCE=on) (FAILOVER=on) (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = UPGR) ) ) Oracle Database インスタンス SGA バックグラウンドプロセス PGA インスタンス SGA バックグラウンドプロセス PGA PGA サーバー プロセス PGA サーバー プロセス リスナー プロセス Private network リスナー プロセス 1521 ① ②
63. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能
64. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能  RACのリソース管理 • グローバルキャッシュサービス（GCS）  キャッシュフュージョン ノード間でキャッシュの一貫性を保つ為のサービス • グローバルエンキューサービス（GES）  キャッシュの一貫性以外の排他制御を保つ為のサービス プロセス 役割 CSSD Cluster Synchoronization Service Daemon インターコネクト通信でノード間の同期を行う CRSD Cluster Ready Service Daemon クラスタリソース（インスタンス、リスナー、VIP等）の起動停止、監視 LMON GESモニター。クラスタ内のインスタンス状態を監視し、グローバルリソース の再定義を行う LMD0 GESデーモン。クラスタ間のロック管理を行いGESリソースを調整する LMSn GCS。キャッシュフュージョンを行うプロセス
65. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能  キャッシュフュージョンの仕組み • インターコネクトでUDP通信 • リソースマスターが、最新ブロックの情報を管理 • リソースマスターは、必ずクラスタ内の１つのノードにある • 最新ブロックの問い合わせは、最大３回（2node-RACは２回）  2−way、3−way・・・ローカル含めやり取りされるインスタンス数 Oracle Database インスタンス SGA PGA インスタンス SGA PGA Private network ブロックA GRD GRD node1 node2 インスタンス SGA PGA node3 GRD ① ①このブロック の最新はどこ？ 最新 ブロックA ブロックB 最新 ブロックB Node2のインスタンスがブロックA,Bの リソースマスターの場合 ②node2にある から転送 ② ① ③node3にある から転送 ③ ②
66. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能  キャッシュフュージョンの仕組み • 各インスタンスでデータを更新するイメージ ①node1がAの更新を要求 ②node1がGRDに問い合わせ ③node1がA→Xに更新 ④node1がGRDを更新 ⑤node2がBの更新を要求 ⑥node2がGRDに問い合わせ ⑦node1にブロック転送要求 ⑧node1からnode2にブロック転送 ⑨node2がGRDを更新 ⑩node2がB→Yに更新 Oracle Database インスタンス SGA PGA インスタンス SGA PGA Private network ブロック A B ① GRD GRD node1 node2 ② ④ ブロック X B ③ ブロック X B ⑤ ⑥ ⑦ ⑧ ブロック X Y ⑩ ⑨
67. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能 • 待機イベント • グローバルキャッシュに関連した待機イベントは「gc～」 • gc cr/current block/grant 2-way/3-way  cr（consistent read） • 読み取り一貫性のための過去のブロック  current • 最新のブロック  block • ブロックそのもののやり取り  grant • ブロックの制御情報（ロックなど） 待機イベント イベントの種類 備考 gc cr/current block 2-way/3-way ブロック関連の待機イベント 別ノードからの転送遅延 gc cr/current grant 2-way メッセージ関連の待機イベント ディスクからの読み取り遅延 gc cr/current block busy 競合関連の待機イベント 別セッションの更新遅延 gc cr/current block congested ロード関連の待機イベント GCS（LMS）での処理遅延
68. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能 （その他）
69. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能 • DRM（Dynamic Resource Mastering） • 頻繁にアクセスされるインスタンスへ、リソースマスタを動的に再配置 • アプリケーションパーティショニングをしている場合は、自動で再配置を行 う方がオーバーヘッドになることがある • Reconfigration • インスタンスの起動／停止に伴い、データベース内の各インスタンスの構成 情報と付随するリソースの再定義を行う処理
70. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能 • Read Mostly Lock • 更新が少なく、読み込みが多いブロックは、リソースマスターへの問い合 わせはせず、直接ディスクから読み込むようにする機能 • デフォルトで有効 • In Memory Parallel Query（11gR2以降） • パレルクエリーは、直接ディスクからDirectPathReadする • バッファキャッシュの80%以内のセグメントは バッファキャッシュを使う *RAFTELはFALSE • InterNode Parallel Query（11gR2以降） • パラレル処理を、自動的に複数ノードで実行 • SQLは、単一インスタンスに向けて実行 • Parallel_force_local=TRUE（デフォルト） *RAFTELはFALSE
71. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACのスケール
72. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪拡張性  スケールアウト • リニアにスループットが伸びるのは、感覚的にせいぜい２～３ノード程度 • その後は、徐々にスループットの伸びが鈍化 • 分散ロックやキャッシュフュージョンが多くなり、スケールし辛い  スケールアップ • rdbmsの特性上、スケールアップによる性能向上が必要になることが多い • 分散処理やキャッシュフュージョンとか考える必要がないから • でもHWリソースには限界はある・・・
73. (C) Recruit Technologies Co.,Ltd. All rights reserved. Fail Over設計 意外とよく漏れがちな、FO時のAPからのセッション設計 インスタンス障害時に、どのnodeにFOするのか 例) ４ノードRACに1000セッション、１ノードで障害が起きた場合 １ノードあたり250セッション ↓障害発生↓ １ノードあたり333セッション （Oracleのバックグラウンド処理やAP以外からのセッションも考慮）
74. (C) Recruit Technologies Co.,Ltd. All rights reserved. APパーティショニング（シャーディング） • アプリケーションパーティショニングにより、負荷分散と拡張性を獲得 • 大規模データベースでは、RACで負荷分散や拡張性を得るためには、 キャッシュフュージョンを低減する必要がある • 分散DBのように、AP側でアクセスするデータ（ブロック）を制御する手法 キャッシュフュージョンの発生を極力減らすために、インス タンスごとにアプリケーションでアクセスするテーブルやイ ンデックスのセグメントを固定してしておくことで、スケー ルするようになる
75. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  データベース  rdbmsの特徴  Oracle Databaseの基本  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニングの実践
76. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪非機能  セキュリティ  監査（DBA監査、標準監査、統合監査、FGA監査）  職務分掌（Audit Vault）  アクセス制御（DBFirewall、VirtualPrivateDatabase）  暗号化（TDE）  可用性  サービス継続性（RAC）  DisasterRecovery（DataGuard）  バックアップリカバリ、Flashback  マイグレーション  EOSL対応、VerUp（exp/imp、GoldenGate）  性能  パフォーマンス改善、管理（OracleEnterpriseManager）  キャパシティ管理
77. (C) Recruit Technologies Co.,Ltd. All rights reserved.  マイグレーション  商用MWであるためEOSL対応が必要  リクルートでは、以前はVerUpに積極的だった  SustainingSupportを覚え、「これで良いのでは・・・」  既に11gR2は枯れているものの、いつかはVerUPが必要になる ▲Premier Support メジャーバージョン出荷から５年間 ▲Extended Support Premire Support終了から３年間 ▲Sustaining Support 無期限
78. (C) Recruit Technologies Co.,Ltd. All rights reserved.  マイグレーションのツール  GoldenGate  ほぼ無停止でVerUp可能だが、コストもかかりライトではない  exp/imp（DataPump）  昔ながらのやり方なので、現在の規模のデータ移行には向かない  論理バックアップ等では、現役  Upgrade script  RAFTELではupgrade scriptでVerUp  比較的ライトで短時間  NFSを使っている恩恵 10gR2 11gR2／12cR2 ①NFS同期 ②旧DB停止 ③NFS同期解除 ④新DB起動 ⑤upgradeスクリプト実行 (ディクショナリ情報更新） 同期
79. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪非機能  セキュリティ  監査（DBA監査、標準監査、統合監査、FGA監査）  職務分掌（Audit Vault）  アクセス制御（DBFirewall、VirtualPrivateDatabase）  暗号化（TDE）  可用性  サービス継続性（RAC）  DisasterRecovery（DataGuard）  バックアップリカバリ（rman等）、Flashback  マイグレーション  EOSL対応、VerUp（exp/imp、GoldenGate）  性能  パフォーマンス改善、管理（OracleEnterpriseManager）  キャパシティ管理
80. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪性能問題 性能問題は、そもそも何が問題か？ カスタマーやクライアントがやりたいことができない！
81. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪性能問題  性能が悪いとは… • 見込んだ時間／リソースに収まっていない • 処理の中に無駄な処理（待機）が発生している  事実確認が大事 • データの参照／更新処理を行うDBは悪者になりやすい(事実悪事を働く…) →そもそも見込みが正しいか、ボトルネックはどこなのか、 事実を確認して現状を分析 AP DB Disk 5 sec 4 sec ? ? sec
82. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪チューニングの目的  目的は２つに大別 • スループットやレスポンスタイムの改善 • リソース使用量の削減 何のためにやるのかは明確に！
83. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪スループットやレスポンスタイムの改善  スループットなのか、レスポンスなのか • スループット [TPSとかPV/sとか] • 単位時間当たりの処理量 • レスポンスタイム ＜= TAT • 処理開始から応答を受け取るまで（応答時間） • 処理開始から結果が出るまで（TAT） “リクルート” － － － コーヒー煎れ て待つか…
84. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪リソース使用量の改善  リソース使用量が増えている（もしくは減らしたい） • データの増加 • 商品／商材の増加 • カスタマ数が右肩上がり • 機能追加 • ワークロードの変化 • 利用者数の伸び • アクセスするデータに変化 • 機能追加
85. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪チューニングの進め方  性能問題の対策効果とコストは相反  一般的に、ビジネスロジックを見直しした方がHW増強等より効果が高い →処理をするから時間やリソースが必要となるので 極端な話、やめるというのが一番効果がある • “やめる”・・・やりたいことができない • “やる“ ・・・リソース増強やチューンナップ 本当にその処理の実行が妥当なのか、背景を早めに見極める必要がある ビジネス ロジック AP DB OS/HW 効果大 効果小 SQL パラメータ メモリ ディスク
86. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  データベース  rdbmsの特徴  Oracle Databaseの基本  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニングの実践
87. (C) Recruit Technologies Co.,Ltd. All rights reserved. 性能問題発生 → ボトルネック調査 → チューニング 実践前に基本技を理解するために 本章では、きっと現場で出会う チューニング策と、対応事例を紹介します
88. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle DBの動作 クライアント データ SQL Oracle ① ③ ② 上記３処理について、チューニングポイントを説明する ①SQLを解析し、実行計画を作成（Parse） ②実行計画を元に順番に処理を実行 ③実行計画の処理命令に従い、メモリもしくはディスクからデータを取得 実行計画 結果
89. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 - Parseとは - Parseが問題となった話 ②実行計画を元に順番に処理を実行 - 実行計画とは - 統計情報とは - 実行計画変化による性能劣化 - ヒント句とは - ヒント句の注意点 - 実行計画の読み方 - 代表的な結合方法 ③実行計画の処理命令に従い、メモリもしくはディスクからデータを取得 - データの読み込み - データのソート - 索引を利用した読み込み - 索引チューニングの話 - 索引が問題となった話
90. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 - Parseとは - Parseが問題となった話 ②実行計画を元に順番に処理を実行 - 実行計画とは - 統計情報とは - 実行計画変化による性能劣化 - ヒント句とは - ヒント句の注意点 - 実行計画の読み方 - 代表的な結合方法 ③実行計画の処理命令に従い、メモリもしくはディスクからデータを取得 - データの読み込み - データのソート - 索引を利用した読み込み - 索引チューニングの話 - 索引が問題となった話
91. (C) Recruit Technologies Co.,Ltd. All rights reserved. SQL=「問合せ言語」 アルゴリズムを書かずに、データ抽出条件のみを書けば良い 例：100行入ったテーブルからIDが3の行を出力 DBMSのparserが、SQLを解析(parse)し、アルゴリズム（実行計画）組み立てる 手続き型言語 For (i=0, i<100 i++){ if (intArray[i] = 3){ system.out.println (intArray[i]) }else if{ ・・・ } 問い合わせ言語 Select * From テーブル Where ID = 3 実行計画 1.TABLEを フルスキャン 2.取り出された行を 一つづつチェックし、 ID=3のものを フィルタリング parse
92. (C) Recruit Technologies Co.,Ltd. All rights reserved. Parse自体は必要な処理しかし、 性能影響が・・・ HARD PARSE（実行計画を作成） → CPU時間増大 （SQL次第だが◦◦◦msのイメージ） SOFT PARSE（キャッシュされた実行計画を利用） → CPUほぼ使用しない （◦msのイメージ） HARD PARSEを減らす → 無駄なCPUを減らす → 性能向上 parse
93. (C) Recruit Technologies Co.,Ltd. All rights reserved. どうやってHARD PARSEを減らす？ 過去に同一SQLが投げられており、その実行計画がメモリ上に残っていれば SOFT PARSEになる 同一SQLでは無い例 Select XX from XX where ID = tanaka Select XX from XX where ID = satou 解決法 Select XX from XX where ID = : A1 バインド変数を使用 Execute :A1 := ‘tanaka’ Execute :A1 := ‘satou’ parse
94. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 - Parseとは - Parseが問題となった話 ②実行計画を元に順番に処理を実行 - 実行計画とは - 統計情報とは - 実行計画変化による性能劣化 - ヒント句とは - ヒント句の注意点 - 実行計画の読み方 - 代表的な結合方法 ③実行計画の処理命令に従い、メモリもしくはディスクからデータを取得 - データの読み込み - データのソート - 索引を利用した読み込み - 索引チューニングの話 - 索引が問題となった話
95. (C) Recruit Technologies Co.,Ltd. All rights reserved. パースが問題となった話 1. テーブル作成が終わらない 1,000パーティション* 100サブパーティション= 約100,000パーティション のテーブル作成 が3時間待っても終わらない。（空のテーブル作成なのに。。） OEMからCPU処理（緑）が続いていることがわかる SQLトレースを取得すると、 実行時間のほぼ全てがparse時間という結果 parse part1 part… part1000 s1 .. s100 s1 .. s100 s1 .. s100 TABLE
96. (C) Recruit Technologies Co.,Ltd. All rights reserved. パースが問題となった話 1. テーブル作成が終わらない 1,000パーティション* 100サブパーティション= 100,000パーティション のテーブル作成 が3時間待っても終わらない。（空のテーブル作成なのに。。） create table a (id char(10),dy date) partition by hash(id) subpartition by range(dy)( partition p1 ( subpartition p1_2016_01_01 values less than (to_date('2016-01-01','YYYY-MM-DD')) ,subpartition p1_2016_01_02 values less than (to_date('2016-01-02','YYYY-MM-DD')) : ) ,partition p2 ( subpartition p500_2016_01_01 values less than (to_date('2016-01-01','YYYY-MM-DD')) ,subpartition p500_2016_01_02 values less than (to_date('2016-01-02','YYYY-MM-DD')) : ) ,partition p3 ( subpartition p500_2016_01_01 values less than (to_date('2016-01-01','YYYY-MM-DD')) ,subpartition p500_2016_01_02 values less than (to_date('2016-01-02','YYYY-MM-DD')) : ) : : ,partition p1000 ( subpartition p500_2016_01_01 values less than (to_date('2016-01-01','YYYY-MM-DD')) ,subpartition p500_2016_01_02 values less than (to_date('2016-01-02','YYYY-MM-DD')) : ) create table a (id char(10),dy date) partition by hash(id) subpartition by range(dy) subpartition template ( subpartition 2016_01_01 values less than (to_date('2016-01-01','YYYY-MM-DD')) ,subpartition 2016_01_02 values less than (to_date('2016-01-02','YYYY-MM-DD')) : ,subpartition 2016_04_08 values less than (to_date('2016-04-08','YYYY-MM-DD')) ,subpartition 2016_04_09 values less than (to_date('2016-04-09','YYYY-MM-DD')) ) ( partition p1 ,partition p2 : ,partition p1000 ); サブパーティションテンプレート利用で3分で完了 元のクエリイメージ parse 修正後のクエリイメージ
97. (C) Recruit Technologies Co.,Ltd. All rights reserved. パースが問題となった話 2. システムオープンを模した負荷試験でCPU高騰 DBサーバのCPU使用率がほぼ100%に ASHにより、負荷高騰タイミングで、CPUを利用しているほぼ全てのセッションが、 ハードパースを実施していることが判明。 parse SQLウォームアップやSQLの種類を削減する等の対策検討が必要
98. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 - Parseとは - Parseが問題となった話 ②実行計画を元に順番に処理を実行 - 実行計画とは - 統計情報とは - 実行計画変化による性能劣化 - ヒント句とは - ヒント句の注意点 - 実行計画の読み方 - 代表的な結合方法 ③実行計画の処理命令に従い、メモリもしくはディスクからデータを取得 - データの読み込み - データのソート - 索引を利用した読み込み - 索引チューニングの話 - 索引が問題となった話
99. (C) Recruit Technologies Co.,Ltd. All rights reserved. クライアント データ あるシステムにアクセスした 「20代の会員名」と「最終アクセス時間」 SQL 結果 Oracle 実行計画A 1.20代会員表をスキャン 2.会員IDの索引を使って、アクセス表を結合 処理を実現する実行計画は複数ありえる 会員 ID 名前 10 A 26 B 45 C ・・ 会員ID アクセス 時間 44 3:00 52 8:00 5 10:00 1 12:00 45 18:00 ・・ 実行計画B 1.アクセス表をスキャン 2.会員IDの索引を使って、会員表を結合 実行計画 20代会員表 アクセス表 会員ID アクセス時間 C 18:00 ・・
100. (C) Recruit Technologies Co.,Ltd. All rights reserved. データ Oracle 実行計画B 実行計画Bが良さそう 性能が良い実行計画は？ 会員 ID 名前 10 A 26 B 45 C 計 100件 会員ID アクセス 時間 44 3:00 52 8:00 計 10件 実行計画A 1.20代会員表をスキャン 100件 2.会員IDの索引を使って、アクセス表を結合 100回 実行計画B 1.アクセス表をスキャン 10件 2.会員IDの索引を使って、会員表を結合 10回 実行計画 20代会員表 アクセス表
101. (C) Recruit Technologies Co.,Ltd. All rights reserved. データ Oracle 実行計画A 実行計画Aが良さそう(データ量が違えば適切な実行計画は異なる) 性能が良い実行計画は？ 会員 ID 名前 10 A 26 B 45 C 計 100件 実行計画A 1.20代会員表をスキャン 100件 2.会員IDの索引を使って、アクセス表を参照 100回 実行計画B 1.アクセス表をスキャン 10000件 2.会員IDの索引を使って、会員表を参照 10000回 会員ID アクセス 時間 44 3:00 52 8:00 5 10:00 1 12:00 45 18:00 計 10000件 実行計画 20代会員表 アクセス表
102. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 - Parseとは - Parseが問題となった話 ②実行計画を元に順番に処理を実行 - 実行計画とは - 統計情報とは - 実行計画変化による性能劣化 - ヒント句とは - ヒント句の注意点 - 実行計画の読み方 - 代表的な結合方法 ③実行計画の処理命令に従い、メモリもしくはディスクからデータを取得 - データの読み込み - データのソート - 索引を利用した読み込み - 索引チューニングの話 - 索引が問題となった話
103. (C) Recruit Technologies Co.,Ltd. All rights reserved. データ量が違えば適切な実行計画は異なる → 良い実行計画を作成するためには、 各テーブルのデータ量をparserが知る事ができる必要がある アナライズ（統計情報収集） → 事前に各テーブルのデータ量等の情報を調査し、DBに保管 （定期実行 or 手動実行） オプティマイザ統計（統計情報） → アナライズにより蓄積される情報 実行計画 統計情報
104. (C) Recruit Technologies Co.,Ltd. All rights reserved. オプティマイザ統計には、データ量だけでなく、種々の情報が含まれます。 表統計 ・行数、データ・ブロック数、平均行長 列統計 ・列内の個別値数 ・列内のNULL数 ・データ分布(最大値 / 最小値 / ヒストグラム) 索引統計 ・リーフブロック数 ・レベル (ツリーの高さ) ・クラスタ化係数 システム統計 ・I/OやCPUのパフォーマンス 実行計画 統計情報 会員 ID 名前 10 A 26 B 45 C 計 100件 会員ID アクセス 時間 44 3:00 52 8:00 5 10:00 1 12:00 45 18:00 計 10000件
105. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実際は実行計画Aが良いのに、Bが選択されてしまう データ量が大きく変化した後は、再アナライズ 統計情報 アナライズ（統計情報収集） → 事前に各テーブルのデータ量等の情報を調査し、DBに保管 （もし、アナライズ時から、大きくデータ量が変わったら） 会員 ID 名前 10 A 26 B 45 C 計 100件 実行計画A 1.20代会員表をスキャン 100件 2.会員IDの索引を使って、アクセス表を結合 100回 実行計画B 1.アクセス表をスキャン 10000件 （10件のはずとparserは思う） 2.会員IDの索引を使って、会員表を結合 10000回 （10回のはずとparserは思う） 会員ID アクセス 時間 44 3:00 52 8:00 5 10:00 1 12:00 45 18:00 計 10000件 統計情報上は10件だったら 実行計画
106. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 - Parseとは - Parseが問題となった話 ②実行計画を元に順番に処理を実行 - 実行計画とは - 統計情報とは - 実行計画変化による性能劣化 - ヒント句とは - ヒント句の注意点 - 実行計画の読み方 - 代表的な結合方法 ③実行計画の処理命令に従い、メモリもしくはディスクからデータを取得 - データの読み込み - データのソート - 索引を利用した読み込み - 索引チューニングの話 - 索引が問題となった話
107. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実行計画変化による性能劣化 実行計画 オンライン影響を考え、バッチも統計情報収集も深夜に実行されることが多い 実行時間帯の重なりにより、意図しない統計状態になることに注意
108. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 - Parseとは - Parseが問題となった話 ②実行計画を元に順番に処理を実行 - 実行計画とは - 統計情報とは - 実行計画変化による性能劣化 - ヒント句とは - ヒント句の注意点 - 実行計画の読み方 - 代表的な結合方法 ③実行計画の処理命令に従い、メモリもしくはディスクからデータを取得 - データの読み込み - データのソート - 索引を利用した読み込み - 索引チューニングの話 - 索引が問題となった話
109. (C) Recruit Technologies Co.,Ltd. All rights reserved. 適した実行計画 ← 正しい統計情報をparserに教える とは言え、それでも誤った実行計画が生成されてしまうことはある （parserが実行計画を決定する際に検討する組み合わせには上限があったり） 対処策として、ヒント句で明示的に実行計画を誘導する方法がある 例： select /*+ LEADING(A B) */ A.名前 from 会員 A , アクセス B where A.会員ID = B.会員ID ; 先にA表、次にB表にアクセスするように 実行計画を作って 実行計画 スペルミス等でも、エラーは発生せずにSQLは発行されるので、 ヒント句が効かない場合は、SQLをよくチェック。 ヒント句
110. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実行計画が最適では無かったので、 「ヒント句を記載したが、実行計画が変わらない」 SQLファイル select /*+ LEADING(A B) */ A.名前～ ヒント句も必ず効くわけではないので、仕方ないかと思いつつ調査 実行計画 ヒント句の注意点
111. (C) Recruit Technologies Co.,Ltd. All rights reserved. DB上から発行されたSQLを見てみると、 select A.名前～ そもそもヒント句が消えている。。 アプリケーションフレームワークでコメントを省いてDBに投げる実装がされていた SQLのコメント select /* コメント */ ～ ヒント句 select /*+ ヒント句 */ ～ 利用するアプリフレームワークによっては、 select --+ヒント句 を利用しましょう。 実行計画 ヒント句の注意点
112. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 - Parseとは - Parseが問題となった話 ②実行計画を元に順番に処理を実行 - 実行計画とは - 統計情報とは - 実行計画変化による性能劣化 - ヒント句とは - ヒント句の注意点 - 実行計画の読み方 - 代表的な結合方法 ③実行計画の処理命令に従い、メモリもしくはディスクからデータを取得 - データの読み込み - データのソート - 索引を利用した読み込み - 索引チューニングの話 - 索引が問題となった話
113. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実行計画 実行計画の読み方 ・インデントで整形されたツリー構造 ・インデントが深いものから順に実行 （同一の深さならば上のものから実行） 上記例では ・まず、MEMBERS表をフルスキャン、 MAIL列の値でフィルタ（Id 3） ・残った行のMEMBER_IDを元に、ENTRIES表のMEMBER_ID列索引と結合（Id 4） ・MEMBERS_ID列の索引のrowidを元に、ENTRIES表にアクセス（Id 5）
114. (C) Recruit Technologies Co.,Ltd. All rights reserved. 代表的な表の結合方法 実行計画 ネステッド・ループ結合 1. 一つの表（外部表）にアクセス 2. 上記の出力結果1行ごとに、もう一つの表（内部表）にアクセスし結合 内部表の結合列に索引が利用できる場合に有効。 ハッシュ結合 1. 一つの表にアクセスし結合列を元にメモリ上にハッシュテーブルを作成 2. もう一つの表1行ごとに結合列にハッシュ関数をかけ結合 二つの表のアクセスは繰り返す必要なく、1回で完了。 ハッシュテーブルがメモリに収まりきらないと性能が極端に悪化。
115. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 - Parseとは - Parseが問題となった話 ②実行計画を元に順番に処理を実行 - 実行計画とは - 統計情報とは - 実行計画変化による性能劣化 - ヒント句とは - ヒント句の注意点 - 実行計画の読み方 - 代表的な結合方法 ③実行計画の処理命令に従い、メモリもしくはディスクからデータを取得 - データの読み込み - データのソート - 索引を利用した読み込み - 索引チューニングの話 - 索引が問題となった話
116. (C) Recruit Technologies Co.,Ltd. All rights reserved. データ取得方法 データ SGA buffer cache Oracle DISKスキャンを減らすために、SGAサイズを適切な値に NFS（HDD）環境で 1ブロック◦ms等 瞬間的 ・極力データはメモリから読み込ませたい ・buffer cacheサイズはOracleのアドバイザで推奨値を確認できる I/O データの読み込み OS/メモリ PGA SGA 共有プール DBバッファ ラージプール javaプール REDOログ バッファ
117. (C) Recruit Technologies Co.,Ltd. All rights reserved. Disk PGA ・データ量からソートサイズを見積もりDiskソートが発生しないように、PGAを適切な値に ・PGAサイズはOracleのアドバイザで推奨値を確認できる DISKスキャンを減らすために、PGAサイズを適切な値に K C A E B ・・・ J ばらばらのデータを 文字の昇順に取り出したい。 A B C D E ・・・ J メモリソート ディスクソート Oracle I/O データのソート OS/メモリ PGA SGA 共有プール DBバッファ ラージプール javaプール REDOログ バッファ
118. (C) Recruit Technologies Co.,Ltd. All rights reserved. データ Oracle A B I J C D K L E F M N G H O P A-H左 I-P右 A,B1 C,D 2 ・・・ I,J5 K,L6 ・・・ B-Tree INDEX TABLE 欲しいレコードが少ない場合のみINDEXスキャンが有効（指標10-15%） Oracleは ブロック単位でI/O （デフォルト8K） I/O 索引による読み込み
119. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 - Parseとは - Parseが問題となった話 ②実行計画を元に順番に処理を実行 - 実行計画とは - 統計情報とは - 実行計画変化による性能劣化 - ヒント句とは - ヒント句の注意点 - 実行計画の読み方 - 代表的な結合方法 ③実行計画の処理命令に従い、メモリもしくはディスクからデータを取得 - データの読み込み - データのソート - 索引を利用した読み込み - 索引チューニングの話 - 索引が問題となった話
120. (C) Recruit Technologies Co.,Ltd. All rights reserved. フルスキャンなので索引を貼りたいが、flagのバリエーションは3種類 単純計算で33%の行は読むので、軽くならないか。。 I/O ------------------------- |Id | Operation | Name |0 | SELECT STATEMENT | |*1 | TABLE ACCESS FULL | TABA | ------------------------- あるサービスの性能試験で下記SQLが遅かった クエリ（イメージ） 実行計画（イメージ） select * from taba where flag = :f SQL > select count(*) from (select 1 from taba group by flag) 3 索引によるチューニング事例
121. (C) Recruit Technologies Co.,Ltd. All rights reserved. チューニング前（左）と比較して、チューニング後（右）は 1実行あたりのバッファ読み取りが改善 I/O select * from taba where flag = :flag 開発担当者から、本クエリでは :flag に '3' しか入らないと教えられる SQL> select flag , count(1) from taba group by flag --- ------------------------ 1 4840 2 79263 3 85 ←(0.1%) ヒント句チューニング実施 select /*+ index(taba)*/ 索引によるチューニング事例
122. (C) Recruit Technologies Co.,Ltd. All rights reserved. I/O しかし、C/O当日 DBサーバのCPU高騰。負荷1位のSQLは例のチューニングしたもの。。 select * from taba where flag = :flag 実行時間 性能試験10ms → 本番120ms ヒント句が効かずに意図した索引が利用されず実行計画が劣化した？ -------------------------------------- |Id | Operation | Name |0 | SELECT STATEMENT | |*1 | INDEX RANGE SCAN | COLA_TABA | -------------------------------------- →意図した索引は使われていた。 索引が問題となった話
123. (C) Recruit Technologies Co.,Ltd. All rights reserved. I/O しかし、C/O当日 DBサーバのCPU高騰。負荷1位のSQLは例のチューニングしたもの。。 select * from taba where flag = :flag 実行時間 性能試験10ms → 本番120ms 生データを見ると、 flag = 3 の割合が数十％ ・試験環境とカーディナリティが異なる ・フルスキャンの方が良いクエリ ヒント句により無理やり索引スキャンとなっていたが、これを外し性能改善。 データの選択率によっては、索引スキャンよりもフルスキャンが適切な事も 性能試験環境のデータ作成方法は気をつけよう（件数、カーディナリティ） 索引が問題となった話
124. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  データベース  rdbmsの特徴  Oracle Databaseの基本  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニングの実践
125. (C) Recruit Technologies Co.,Ltd. All rights reserved. PC上の擬似サービスで発生する 障害・性能問題に対処しながら 調査から解決までの流れ実践しを感覚をつかむ
126. (C) Recruit Technologies Co.,Ltd. All rights reserved. 環境構築 • 割愛 実践
127. (C) Recruit Technologies Co.,Ltd. All rights reserved. 全体感 イベントエントリーシステムC/O後、次々と性能遅延/障害が発生。 DBAとして、原因を調査し、チューニング策を提示してください。 実践
128. (C) Recruit Technologies Co.,Ltd. All rights reserved. 問題１ □事象 会員が自分がエントリしたイベント一覧を確認する画面のレスポンスが 徐々に遅くなっている □簡易調査 OEMで負荷上位クエリを確認すると、 No1.sqlが1回当たり数秒かかり負荷を押し上げている事が判明。 SQLのコメントから、このクエリが上記画面で呼ばれている事も確認済み。 □依頼事項 DBAとして、No1.sqlが遅い原因を調査し、 チューニング策を提示してください。 □No1.sql 実践
129. (C) Recruit Technologies Co.,Ltd. All rights reserved. 問題１解説 実践 ◦データ量確認 ・メンバー表の行数 SQL> select count(*) from members; COUNT(*) ---------- 10000 ・メンバー表の行の中で、mailが:mail_addressであるものの行数 SQL> select count(*) from members where mail = :mail_address; COUNT(*) ---------- 1 ・エントリー表の行数 SQL> select count(*) from entries; COUNT(*) ---------- 2000000 ・エントリー表の中でキャンセルされていないものの行数 SQL> select count(*) from entries where is_canceled = 0; COUNT(*) ---------- 2000000
130. (C) Recruit Technologies Co.,Ltd. All rights reserved. 問題１解説 実践 ◦遅い状態での実行計画確認 ◦遅い実行計画の流れ 3. ENTRIES表を200万件スキャン 4. それぞれの行のMEMBER_IDを元に、MEMBERS表のMEMBER_ID索引と結合（200万回） 5. MEMBERS表の索引を元に、MEMBERS表にアクセス（3と4の結合の結果残った行数分） mailが「:MAIL_ADDRESS」のユーザだけフィルタ（1行になる）
131. (C) Recruit Technologies Co.,Ltd. All rights reserved. 問題１解説 実践 ◦遅い状態での実行計画確認 ◦良い実行計画の流れ 1. MEMBERSのmail列に貼られた索引をスキャンし、行を特定（1行） 2. MEMBERS表にアクセス（1行） 3. 2で得られた行のMEMBER_IDを元に、ENTRIES表をMEMBER_IDの索引でスキャン（1回）
132. (C) Recruit Technologies Co.,Ltd. All rights reserved. 問題１解説 実践 1. MEMBERSのmail列に貼られた索引をスキャンし、行を特定（1行） 2. MEMBERS表にアクセス（1行） 3. 2で得られた行のMEMBER_IDを元に、ENTRIES表をMEMBER_IDの索引でスキャン（1 回） なぜ上の実行計画になっていないか？ ・考えられる原因 MEMBERS.mail列に索引がない？ → ある。 ENTRIES.MEMBER_ID列に索引がない？ → ある parserが、MEMBERS表を過大に見積もっている？ parserが、ENTRIES表の件数が少ないと思っている？ →そう。dba_tablesを確認するとENTRIES表は0件と表示される（本当は200万件）。 ENTRIES表の統計情報を再収集すると、実行計画が変化し性能が改善
133. (C) Recruit Technologies Co.,Ltd. All rights reserved. 問題２～5 □割愛 実践