ブートキャンプDATABASE

2019年度リクルート新人ブートキャンプ エンジニアコースの講義資料です

Transcript

1. ブートキャンプ DATABASE
2. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  はじめに  データベース  Oracle Databaseの基本  環境構築  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニング・障害対応の実践 2
3. (C) Recruit Technologies Co.,Ltd. All rights reserved. 自己紹介
4. (C) Recruit Technologies Co.,Ltd. All rights reserved. 氏名 鈴木 宏彰（すー） 森下 健太 猪股 弘 所属 リクルートテクノロジーズ ITインテグレーション本部 サービスオペレーションエンジニアリング部 プロジェクト基盤グループ（PKG）
5. (C) Recruit Technologies Co.,Ltd. All rights reserved. 今日伝えたいこと リクルートのシステムの開発においてOracleの基礎技術は必須 • リクルートが持つ多くのカスタマ／クライアント情報は データベース上にある 特に大規模システムは、殆どOracle Databaseを利用 5
6. (C) Recruit Technologies Co.,Ltd. All rights reserved. 6 今日伝えたいこと  RDBMSを有効利用するための基礎知識を押さえる 2~6章  データベースの基礎 2章  Oracle Databaseの基礎 3章  アーキテクチャ/メモリ管理/データベースファイル/論理領域管理  スキーマ  Oracle RACの基礎 5章  非機能面 6章  チューニング、トラブル対応の基礎 6・7章
7. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  はじめに  データベース  Oracle Databaseの基本  環境構築  Oracle RACの基本  データベースの非機能  DBチューニングの実践 7
8. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベース データを統合的に管理して格納 複数のプログラム・人から同時に処理が可能 ⇒データを入れる、ただの「箱」 サービスを提供する上では なくてはならない 8
9. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベース 大量データから、素早く必要なデータを取得 ⇒最適な経路でデータを素早く取り出す ⇒多数のユーザが同時に利用するため、最小リソースで取り出す 同時実効性を高めつつも、データの整合性が必要 ⇒サービスを提供する者として、トランザクション内で データの整合性が担保できていることは必須 9 データの取り出し、管理の仕組みを データベース管理システムが提供
10. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースの種類 階層型、ネットワーク型 • メインフレームとか 関係型 [リレーショナル型] • データを１つの表にまとめるという単純な構造 • 正規化により重複の排除が可能 • 厳密なデータの一貫性  Oracle Database、SQL Server、DB2  MySQL、PostgreSQLなど 10
11. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースの種類 NoSQL （Not Only SQL） • 分散DBによりスケーラビリティを享受 • 大量データ分析などのパフォーマンスに特化  KVS （redis、Amazon DynamoDBなど）  カラムストア （Apache Cassandra, Apache HBaseなど）  ドキュメント指向 （MongoDB、Apache CouchDBなど） 11
12. (C) Recruit Technologies Co.,Ltd. All rights reserved. RDBMSの特徴 12
13. (C) Recruit Technologies Co.,Ltd. All rights reserved. RDBMSの特徴 表形式でデータを格納 • 行-列という単純構造で理解しやすい データの一元管理 • １つのデータは１つの場所に存在 トランザクション制御 • 処理の一貫性を保証 SQL(DML/DDL/DCL)を使ってデータを操作 • 処理やフローではなく、データの操作を目的としている 13
14. (C) Recruit Technologies Co.,Ltd. All rights reserved. データの一元管理 １つのデータは１つの場所に存在  データ中心アプローチにより、業務プロセスの変更による影響を排除  データの整合性を保持しつつ管理・操作をしやすくする • １つのデータが散在していると、更新時に全箇所を更新しなければならない 正規化・非正規化  第一正規化（非キーの排除） • 重複を排除し主キーを決定  第二正規化（関係性の分離） • 複合主キーを別々の表に分割  第三正規化 • 主キー以外の重複を排除  非正規化 • 正規化とは逆に、業務プロセスに沿った形 14
15. (C) Recruit Technologies Co.,Ltd. All rights reserved. トランザクション制御 １つのトランザクション内でデータの整合性を保証 ACID特性 • トランザクションで必要とされる要素 • NoSQLではACID特性を緩くしてパフォーマンスを重視  Atomicity（原子性） ：commit/rollback  トランザクションの全てが完了するか取り消されるかを保証  Consistency（一貫性）：UNDO  トランザクションの前後でデータの整合性を保証  Isolation（独立性） ：ロック  他のトランザクションからデータを独立することを保証  Durability（永続性） ：REDO／UNDO  完了したトランザクションは永続化されることを保証 15
16. (C) Recruit Technologies Co.,Ltd. All rights reserved. トランザクション制御 トランザクション分離レベル  データの一貫性にはロックが必要で、読み込み性能とは相対関係にある  分離レベルとは、一貫性と同時実効性の関係性を表したロック方式のこと • 殆どのRDBMSのデフォルトはRead commitedであり、変更することはまずない 16 レベル ／現象 Dirty Read （ダーティー リード） Nonrepeatable Read （反復不可能な読み取り） Phantom Read （ファントムリード） 未コミット読み取り 更新済み読み取り 挿入済み読み取り Read uncommitted （コミットされていない読み取り） 発生する 発生する 発生する Read committed （コミットされた読み取り） 発生しない 発生する 発生する Repeatable read （反復可能な読み取り） 発生しない 発生しない 発生する Serializable （直列化可能） 発生しない 発生しない 発生しない
17. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  はじめに  データベース  Oracle Databaseの基本  環境構築  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニング・障害対応の実践 17
18. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseの特徴 レガシーで技術者が豊富 RDBMSの圧倒的なシェア ノウハウがあちこちにある サポートサイト（My Oracle Support）が情報満載 ライセンス・保守料が高い RDBMSとしてはホボ完成しており、安心・安定に対するコストに見える RAC（実用レベルなshared everything構成）※5章で詳細説明 商用MWの中でも信頼性が高く、機能に細かいケアがされている Shared everything構成でも一定の性能を満たしている 18
19. (C) Recruit Technologies Co.,Ltd. All rights reserved. アーキテクチャ概要 19
20. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseのアーキテクチャ 20 データベースファイル インスタンス PGA Oracle Database SGA PGA PGA PGA PGA バックグラウンドプロセス PGA サーバー プロセス PGA サーバー プロセス PGA サーバー プロセス リスナー プロセス 制御ファイル REDOログ データ ファイル ▪インスタンス ・メモリ領域＋書き込みを行う プロセス群 アラートログ 初期化パラメータ アーカイブログ ▪データベースファイル ・データの格納先 ・データの種類によって幾つか のファイルが存在 全体のアーキテクチャ
21. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseのアーキテクチャ 21 データベースファイル インスタンス PGA Oracle Database SGA PGA PGA PGA PGA バックグラウンドプロセス PGA サーバー プロセス PGA サーバー プロセス PGA サーバー プロセス リスナー プロセス 制御ファイル REDOログ データ ファイル アラートログ 初期化パラメータ アーカイブログ ▪SGA 表データや実行計画／SQL情 報などを保持する共有メモリ 領域 ▪PGA プロセスごとに確保されるメ モリ領域 メモリ
22. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseのアーキテクチャ 22 データベースファイル インスタンス PGA Oracle Database SGA PGA PGA PGA PGA バックグラウンドプロセス PGA サーバー プロセス PGA サーバー プロセス PGA サーバー プロセス リスナー プロセス 制御ファイル REDOログ データ ファイル アラートログ 初期化パラメータ アーカイブログ ▪制御ファイル DBの構造、変更反映状況 (chekpoint)などの制御情報 を格納 ▪REDOログ 変更履歴（DML/DDLなどに よる）を格納 ▪データファイル ・ユーザデータなどを格納 ・通常規模により複数のデー タファイルに分散 データベースファイル
23. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseのアーキテクチャ 23 データベースファイル インスタンス PGA Oracle Database SGA キャッシュ → 検索・更新 PGA PGA PGA PGA バックグラウンドプロセス PGA サーバー プロセス PGA サーバー プロセス PGA サーバー プロセス 書き込み 読み込み リスナー プロセス 制御ファイル REDOログ データ ファイル ▪バックグラウンドプロセス ユーザ処理とは別にメモリや ディスク処理など内部処理を 非同期に行う ▪サーバープロセス ユーザごとに起動され、ユーザか らリクエストされた処理を実行 アラートログ 初期化パラメータ アーカイブログ ▪リスナープロセス リクエストを受け付け、サーバプ ロセスを起動（エンドポイント） プロセスと処理の流れ
24. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理 24
25. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseのアーキテクチャ（再掲） 25 データベースファイル インスタンス PGA Oracle Database SGA PGA PGA PGA PGA バックグラウンドプロセス PGA サーバー プロセス PGA サーバー プロセス PGA サーバー プロセス リスナー プロセス 制御ファイル REDOログ データ ファイル アラートログ 初期化パラメータ アーカイブログ ▪SGA 表データや実行計画／SQL情 報などを保持する共有メモリ 領域 ▪PGA プロセスごとに確保されるメ モリ領域 メモリ
26. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理 • メモリ管理（自動メモリ管理、自動共有メモリ管理） • 自動メモリ管理（11g以降）  SGA内のメモリプールとPGAをOracleが自動で最適化  Hugepage（OSのメモリ利用を軽減できる設定）は使えない • 自動共有メモリ管理（10g以降）  SGA内のメモリプールをOracleが自動で最適化 ※併用は不可。本講義では自動共有メモリ前提で進める。 26 OS/メモリ PGA SGA 共有プール DBバッファ ラージプール javaプール REDOログ バッファ ▪自動共有メモリ管理 ▪自動メモリ管理 REDOログバッファは、起動時に設定
27. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理  SGA • データベースプロセス間で共有するメモリ領域 • SGA_MAX_TARGETで上限を設定（＝SGA_TARGETが基本） • 自動共有メモリ管理と初期値の設定は必須 27 主なSGA内のプール 概要 ポイント DBバッファ db_cache_size ・dbブロックをキャッシュ ・LRU(参照されていない時間が最も長いデータ を置換対象にする方式)で管理 ・デフォルトはblock_size指定のサイズ ・キャッシュヒット率（指定のデータが キャッシュメモリにある確率）が90%以下の 場合、ディスク読み込みが頻発しているはず ・バッチ処理前後の状態は要考慮 共有プール shared_pool_size ・SQL文の解析結果や実行計画（SQLを内部でど う実行するか。7章で詳細説明。）をキャッシュ ・ライブラリ、ディクショナリ情報（DBのス キーマ定義情報などDBの管理データ表の集合） をキャッシュ ・Buffer_cacheより優先的に領域を獲得 ・領域が不足すると、ORA-04031 ・内部的にヒープの断片化も起きやすく見積 もりが難しい ・空き領域のモニタリングは必要 REDOログバッファ log_buffer ・DBへの変更履歴をキャッシュ ・redoログファイルへは非同期でフラッシュ ・自動共有メモリ管理の対象外 ・動的に変更ができない領域なので、大きめ のサイズを確保しておく（128～256MB） dbkeepバッファ db_keep_cache_siz e ・db_cache_size以外でdbブロックをキャッシュ ・指定されたセグメントがキャッシュ ・自動共有メモリ管理の対象外 ・対象データの増減により、設定サイズを超 えるので考慮とモニタリングが必要
28. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理  PGA • サーバプロセスのSQL作業メモリ領域  ソート処理やHASH結合の際に利用 • 各サーバプロセス固有のメモリ（カーソルやセッション情報等）領域は含まれない • PGA_AGGREGATE_TARGET（インスタンス内で使用可能なPGAの総量）の設定値は 上限の目標  あくまでも目標であるため、不足すれば拡大 28
29. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理  SGA • 自動共有メモリ管理、自動メモリ管理 ▪自動メモリ管理（0 = 無効） SQL> show parameter memory_target ▪自動共有メモリ SQL> show parameter sga_max_size SQL> show parameter sga_target 29 NAME TYPE VALUE -------------- ----------- ------ memory_target big integer 0 NAME TYPE VALUE ----------------- ----------- ------ sga_max_size big integer 700M NAME TYPE VALUE ----------------- ----------- ------ sga_target big integer 700M
30. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理  SGA • 自動調整されるSGAプール SQL> select component, user_specified_size,current_size 2 from v$sga_dynamic_components;  PGA • 自動共有メモリ管理、自動メモリ管理 ※詳細は、v$pga_statなどで統統計値の参照が可能 SQL> show parameter pga 30 COMPONENT USER_SPECIFIED_SIZE CURRENT_SIZE --------------------- ------------------- ------------ shared pool 0 620756992 large pool 0 16777216 java pool 0 16777216 streams pool 0 16777216 DEFAULT buffer cache 0 2516582400 ・・・ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 512M
31. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseのアーキテクチャ（再掲） 31 データベースファイル インスタンス PGA Oracle Database SGA キャッシュ → 検索・更新 PGA PGA PGA PGA バックグラウンドプロセス PGA サーバー プロセス PGA サーバー プロセス PGA サーバー プロセス 書き込み 読み込み リスナー プロセス 制御ファイル REDOログ データ ファイル ▪バックグラウンドプロセス ユーザ処理とは別にメモリや ディスク処理など内部処理を 非同期に行う ▪サーバープロセス ユーザごとに起動され、ユーザか らリクエストされた処理を実行 アラートログ 初期化パラメータ アーカイブログ ▪リスナープロセス リクエストを受け付け、サーバプ ロセスを起動（エンドポイント） プロセスと処理の流れ
32. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理  サーバプロセス • DBセッションに対するサーバプロセス • PROCESSESで上限を指定（バックグラウンドプロセス含） • DBセッション数は、AP/BATCHとFOの考慮が必要 • Hugepage（ラージページ）を設定しないと極端に使用メモリが肥大化  共有メモリ(SGA)を変更する場合はhugepageサイズに注意  バックグラウンドプロセス • インスタンスを構成する内部処理をするプロセス群 32 主要プロセス 略称（nは複数） 役割 データベースライター DBWn db_buffer_cacheからデータファイルへ書き込み ログライター LGWR Redo_log_bufferからredoログファイルへ書き込み チェックポイント CKPT データファイルや制御ファイルへチェックポイントの記録 システムモニター SMON インスタンスリカバリを実行 プロセスモニター PMON 各種DBプロセスの監視、リカバリを実施 アーカイバ ARCHn アーカイブログの作成
33. (C) Recruit Technologies Co.,Ltd. All rights reserved. メモリ管理  バックグラウンドプロセス • 以下Singleの場合（RACの場合、インスタンス間の管理プロセスが追加） $ ps ax | grep ora_ | grep -v grep 33 2292 ? Ss 0:03 ora_pmon_bcdb 2294 ? Ss 0:14 ora_psp0_bcdb 2296 ? Ss 0:20 ora_vktm_bcdb 2300 ? Ss 0:01 ora_gen0_bcdb 2302 ? Ss 0:01 ora_diag_bcdb 2304 ? Ss 0:01 ora_dbrm_bcdb 2306 ? Ss 0:07 ora_dia0_bcdb 2308 ? Ss 0:01 ora_mman_bcdb 2310 ? Ss 0:02 ora_dbw0_bcdb 2312 ? Ss 0:03 ora_lgwr_bcdb 2314 ? Ss 0:06 ora_ckpt_bcdb 2316 ? Ss 0:02 ora_smon_bcdb 2318 ? Ss 0:00 ora_reco_bcdb 2320 ? Ss 0:05 ora_mmon_bcdb 2322 ? Ss 0:06 ora_mmnl_bcdb 2334 ? Ss 0:00 ora_qmnc_bcdb 2402 ? Ss 0:03 ora_cjq0_bcdb 2897 ? Ss 0:00 ora_q000_bcdb 2899 ? Ss 0:00 ora_q001_bcdb 4042 ? Ss 0:01 ora_smco_bcdb 23377 ? Ss 0:00 ora_w001_bcdb
34. (C) Recruit Technologies Co.,Ltd. All rights reserved. Listener/tnsnames • クライアントからのリクエストを受け付け • サーバープロセスを起動 • 以降、クライアントとサーバプロセスでメッセージをやり取り • リスナーの設定ファイル cat ${ORACLE_HOME}/network/admin/listener.ora 34 # 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 PGA サーバー プロセス PGA サーバー プロセス リスナー プロセス ① ② ③ 1521 PGA サーバー プロセス sqlplus /as sysdba
35. (C) Recruit Technologies Co.,Ltd. All rights reserved. Listener • リスナーのサービス登録状況の確認 lsnrctl status LISTENER 35 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 エンドポイント情報 登録されたサービス情報
36. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseの動作の仕組み ＜（例）更新処理の動作＞ ① REDOログバッファに変更履歴を順番に書き出し ② DBバッファキャッシュに変更前のUNDOデータを書き出し ③ REDOログファイルに非同期でLGWが書き出し ④ データファイルへ内部的なタイミングで反映（未コミットデータ含） 36 リスナー プロセス ▪ユーザ処理 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 バッファにキャッシュ ▪データディクショナリ データベース内のオブジェクト定義や ユーザ情報などを管理するための内部表
37. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル 37
38. (C) Recruit Technologies Co.,Ltd. All rights reserved. Oracle Databaseのアーキテクチャ（再掲） 38 データベースファイル インスタンス PGA Oracle Database SGA PGA PGA PGA PGA バックグラウンドプロセス PGA サーバー プロセス PGA サーバー プロセス PGA サーバー プロセス リスナー プロセス 制御ファイル REDOログ データ ファイル アラートログ 初期化パラメータ アーカイブログ ▪制御ファイル DBの構造、変更反映状況 (chekpoint)などの制御情報 を格納 ▪REDOログ 変更履歴（DML/DDLなどに よる）を格納 ▪データファイル ・ユーザデータなどを格納 ・通常規模により複数のデー タファイルに分散 データベースファイル
39. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル データベースファイルは、以下３つを指す 「データファイル」 「制御ファイル」 「REDOログファイル」  データファイル • データベースのすべてのデータを格納する物理ファイル • サーバプロセスから読み込まれ、 DBWn（データベースライター）より db_buffer_cash経由で書き込まれる • 自動拡張により運用負荷を下げれれるが、性能や領域監視も考慮 39
40. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル  制御ファイル • データベース名や構造に関する情報を格納 • チェックポイント情報（SCN） • REDOログファイル、データファイル、アーカイブログの名前と場所、ス テータス • 多重化が基本だが、すべてにアクセス出来る必要がある  バックアップ方法 • バックアップファイルから、制御ファイルの情報が確認可能 40 alter database backup controlfile to trace ; $ ls -lrt /u01/app/oracle/diag/rdbms/bcdb/bcdb/trace bcdb_ora_*.trc
41. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル  REDOログファイル • データベースへの変更情報(DML,DDL)を順番に記録し、障害時の復旧に使用 • ファイルは循環利用されるため、更新量の考慮が必要 • 以下が書き込みタイミングとなり、更新パフォーマンスに直結  COMMIT時  REDOログバッファの1/3以上を使った場合  前回書き込み時から3秒経過した場合  ダーティバッファをDBWnが書き込む前 41
42. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル その他のデータベースのファイル  アーカイブログファイル • ARCHIVELOGモードで稼働している場合に作成 • 一杯になったREDOログファイルのオンラインコピー • 最新状態に復旧するためには、アーカイブログファイルが必要 • ということは最新まで復旧する必要がない場合は不要 • NOARCHIVELOGモード • 領域不足等で、REDOログのコピーができなくなるとインスタンスが停止 42
43. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル その他のデータベースのファイル  初期化パラメータ(pfile)、サーバパラメータファイル(spfile)  パラメータファイル … インスタンスやDBの構成情報をパラメータで指定したファイル • pfileはテキストであり動的にパラメータ変更ができない • $ORACLE_HOME/dbs配下に置かれ、pfile→spfileの優先度で読み込み • pfile⇄spfile、どちらからでも生成可能  アラートログファイル • Oracleのインスタンスが出力するログファイル • サーバ側のORA-エラーは、ここに出る 43
44. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル  制御ファイル SQL> select * from v$controlfile ; 44 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
45. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル  REDOログファイル • メンバーの追加（冗長化） 45 GROUP# THREAD# MEMBER SEQUENCE# MB STATUS ---------- ---------- ---------------------------------------- ---------- ---------------- -------------- -- 1 1 /u01/app/oracle/oradata/bcdb/redo01.log 31 50 CURRENT 2 1 /u01/app/oracle/oradata/bcdb/redo02.log 29 50 INACTIVE 3 1 /u01/app/oracle/oradata/bcdb/redo03.log 30 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 ;
46. (C) Recruit Technologies Co.,Ltd. All rights reserved. 論理領域管理 46
47. (C) Recruit Technologies Co.,Ltd. All rights reserved. 論理領域管理 47 データベースファイル インスタンス PGA Oracle Database SGA PGA PGA PGA PGA バックグラウンドプロセス PGA サーバー プロセス PGA サーバー プロセス PGA サーバー プロセス リスナー プロセス 制御ファイル REDOログ データ ファイル アラートログ 初期化パラメータ アーカイブログ 表領域の位置
48. (C) Recruit Technologies Co.,Ltd. All rights reserved. 論理領域管理  表領域 • 表領域は表の論理的な格納先、データファイルはデータの物理的な格納先 • 表領域とデータファイルの紐付け  SMALL表領域（１−ｎ）、BIG表領域（１−１） • 目的ごとにある程度表領域を分離して設計  アプリケーション、メンテナンス、用途(INDEX、LOB)単位など  IO分散は、ディスク側で。 • 事前定義の表領域  ユーザデータは格納しない 48 表領域 概要 ポイント SYSTEM ディクショナリ情報などの管理表を格納 領域不足になるとDB停止 SYSAUX AWRなどオプションコンポーネントで利用 UNDO 更新前ブロックを保持 一貫性のために更新前データを参照 ロールバックセグメンとを循環方式で利用 自動拡張はOFFか制限 ORA−1555による処理エラーが ないように大きめに設計 TEMP ソート処理や一時表を格納 リカバリには不要な表領域 自動拡張はOFF バッチ処理などの最大読み込み 量からサイジング
49. (C) Recruit Technologies Co.,Ltd. All rights reserved. 論理領域管理  データの論理構造 • セグメント • 物理的な領域を必要とするテーブルや索引などのオブジェクトの論理領域 • エクステント • セグメントを拡張する単位で、連続ブロックから構成 • ブロック • Oracleはブロック単位でアクセスする • ブロックへのデータ格納には注意が必要 • PCTFREEでブロックの使用効率を調整可能 49 表領域 データ ファイル域 セグメント エクステント ブロック OSブロック Logical physical 表領域 セグメント セグメント セグメント セグメント エクステント エクステント エクステント ブロック ブロック ブロック ブロック
50. (C) Recruit Technologies Co.,Ltd. All rights reserved. 論理領域管理  パフォーマンス観点のセグメントの領域管理 データはブロック単位でアクセスされるため、テーブルやインデックスが、 どのタイミングで検索されたり更新されるのか、保持期間はどの程度か 容量見積もりの際に考慮して設計する。 • ブロックの使用効率  PCTFREE：行の更新によるサイズ拡張に備えた空き領域割合 • レコード長の拡張やレコードの競合を避けるために領域を空けておく • レコード長の拡張により１ブロック内に納まらない場合は行移行が発生  ITL：トランザクションがブロックを更新する際に獲得するブロック内のリスト • トランザクションが多くITL獲得待ちが多発する場合は、PCTFREEなどを調整 50 （更新用） 4行目 3行目 2行目 1行目 PCTFREE:20% （更新用） 4行目 3行目 2行目のポインタ 1行目 （更新用） 2行目増加 2行目 （更新用） 3行目 2行目 1行目 ①update ②insert maxtrans 2の場合 ③delete <wait event> enq ： TX – allocate ITL entry
51. (C) Recruit Technologies Co.,Ltd. All rights reserved. 論理領域管理  パフォーマンス観点のセグメントの領域管理 • HWMの考慮  Oracleはセグメントなどの過去の最大サイズを記憶しており、FullScanの際に たとえ領域を使用していない場合でもHWMまでブロックを読み込みんでしまう  何らかの事情でHWMが上がってしまいパフォーマンス問題となった場合は、 セグメントのshrinkやmoveなどでHWMを下げることを検討する。 51 セグメント エクステント 空き ブロック ブロック ブロック ブロック データ増加 未使用 HWM データ減少 ここまで読み込んでしまう ブロック ブロック ブロック ブロック
52. (C) Recruit Technologies Co.,Ltd. All rights reserved. REDOとUNDO  REDOとUNDOによるデータ一貫性の保証 • データ一貫性は、REDOとUNDOにの仕組みにより保障 • REDOログは、コミットやDBWr書き込みタイミング等で更新履歴をファイルへ永続化 • UNDOセグメント（ロールバックセグメント）は、変更前データを非同期でファイルへ永続化 • 更新ブロックは、非同期でデータファイルへ永続化 障害時の動作 1.REDOログから変更をデータファイルへロールフォワード 2.UNDOから未コミットデータをデータファイルへロールバック 52 Oracle Database SGA REDOログ DBライ ター DBライ ター ユーザ表領域 DBバッファキャッシュ REDOログ バッファ UNDO表領域 ログライ ター
53. (C) Recruit Technologies Co.,Ltd. All rights reserved. 表領域とデータベースファイル  表領域、データファイル • dba_tablespaces, dba_data_files. dba_temp_files 53 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;
54. (C) Recruit Technologies Co.,Ltd. All rights reserved. スキーマ 54
55. (C) Recruit Technologies Co.,Ltd. All rights reserved. スキーマ設計  スキーマ ＝ ユーザ • 論理オブジェクトを所有するネームスペース • オブジェクトを変更することをスキーマ変更と言う（こともある） 権限・ロール • DBA権限 [sysdba] ＝＞ Databaseの管理者 • スキーマに対して、各種権限（アクセス、参照、更新、実行など）を付与  OS認証  DBの起動停止はログインせずに実行が必要  dbaグループに所属するOSアカウントのみ起動停止が可能  この他にプロダクト所有グループのoinstallがある 55
56. (C) Recruit Technologies Co.,Ltd. All rights reserved. スキーマ設計  スキーマオブジェクト  Table • 表  Index • B*Tree：tree構造となっている索引 • Bitmap：各列値の情報をbitmap管理の索引 • 逆キー、ファンクション等…  View • 実態を持たない。使いすぎると可読性が低下  Materialized View • データの実態を持ったView（マテビュー） • 元表への変更を保持したMVIEWログを元にした差分更新と完全リフレシュ  Partition（Tbale/Index） • レンジ ：日付や範囲 などをキーに、セグメントを区分け • リスト ：任意のリストをキーに、セグメントを区分け • ハッシュ ：値のハッシュ値をキーに、セグメントを区分け • コンポジット ：各パーティション分割の組み合わせ 56
57. (C) Recruit Technologies Co.,Ltd. All rights reserved. Index • 索引のアーキテクチャと特徴 • B*Tree索引 • ルートブランチ、ブランチブロック、リーフブロックのtree構造 • 範囲検索やカーディナリティが高い（例えば値がユニークな場合）検索に効果 • ROWID情報と列値のデータをソートして格納（複合列値も同様） • NULL値は持たないため、IS NULL検索は効かない • リーフ分割などにより、階層が深くなると性能が劣化（BLEVEL4以内が目安） 57 ルート A-D E-H -Z … ROWID 列値 XXXXX A0001 XXXXY B0001 ルートブランチ ブランチブロック リーフブロック
58. (C) Recruit Technologies Co.,Ltd. All rights reserved. Index • 索引のアーキテクチャと特徴 • Bitmap索引 • 列値をグルーピングして有無をbitmapで管理（ROWIDは持たない） • 実はtree構造 • カーディナリティが低い検索に効果 ※男女、都道府県など • リーフブロックの格納行が多いため、更新時にロックが広範囲となる ※一般的にあまりOLTP向きではない • ファンクション索引 • 関数索引 • 逆キー索引 • 通常の索引はソート済みであるが、逆ソートして格納 58
59. (C) Recruit Technologies Co.,Ltd. All rights reserved. Partition パーティショニング  １つの表やインデックスを論理的に分割して管理する方法  正しい使い方により、パフォーマンスやデータの管理性が向上する  大規模表(数百GBレベル)の、パフォーマンス／メンテナンス性を目的に対象を選出  管理やメンテナンスとトレードオフとなるため、対象の見極めは慎重に • ＜メンテナンス性＞ • 月次パージなどでパーティションごと削除 • レンジの追加などのメンテナンス作業も必要（12cから自動設定も可能） • ＜パフォーマンス：パーティションプルーニング＞ • アクセス不要なパーティションを読み込まない • Where句や結合条件にパーティションキーを含ませる 59
60. (C) Recruit Technologies Co.,Ltd. All rights reserved. Partition パーティションの種類  レンジ  日付などのレンジで分割し、データ分散やメンテナンス性を高める  リスト  コードや区分など分割し、データ分散やメンテナンス性を高める  ハッシュ  ハッシュアルゴリズムにより分割し、データを分散  コンポジット  各パーティションを組み合わせてパーティション化  組み合わせには制限がある（レンジ-リスト、レンジ-ハッシュなど） 60
61. (C) Recruit Technologies Co.,Ltd. All rights reserved. Partition  パーティションインデックスの考慮  パーティションインデックスの種類 • ローカル索引 • 表のパーティションキーのサブセットでパーティショニング • グローバル索引 • 非パーティション表の索引（＝普通の索引）  考慮ポイント • 可能な限りグローバル索引とならないようにキー設計の段階で考慮する • グローバル索引の場合、メンテナンスコストがかかる alter table … drop partition update global index ↑dropなどと同時にメンテナンスも可能ではある 61
62. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  はじめに  データベース  Oracle Databaseの基本  環境構築  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニング・障害対応の実践 62
63. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪環境準備 環境情報 63 VirtualBox 5.2.8 OS Oracle Linux 6.4 Kernel UEK2(2.6.39-400.17.1.el6uek) hostname ODIGettingStarted IP 192.168.56.101 ユーザ root / oracle oracle / oracle Oracle Database 11.2.0.4 Database（SID） bcdb Listenerポート 1521 スキーマ bcuser 表領域 t_bootcamp 100MB AUTOEXTENT [OFF]
64. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪環境準備  VirtualBoxの起動  VirtualBox環境設定 ・ローカルPCからつなぐためのネットワーク設定 -環境設定 - ネットワーク - ホストオンリーネットワーク IPv4アドレス ：192.168.56.1 IPv4ネットマスク ：255.255.255.0  VM起動 64
65. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪環境準備  ローカルPCから192.168.56.101へsshアクセス $ ssh –l oracle 192.168.56.101 65 $ ssh -l oracle 192.168.56.101 oracle@192.168.56.101's password: Last login: Sat Apr 7 12:18:00 2018 from 192.168.56.101 [oracle@ODIGettingStarted ~]$ id oracle uid=500(oracle) gid=500(oracle) groups=500(oracle),502(dba)
66. (C) Recruit Technologies Co.,Ltd. All rights reserved. DB構築 66
67. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪環境準備  データベースを作成する 1. /u01/app/oracle/admin/bcdb/scripts/ 2. dbca  DBの稼働状況を確認  バックグラウンドプロセス、リスナープロセスの確認  アラートログの確認  DBへの接続  sysでローカル接続(dba)  表領域／スキーマの作成 67
68. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪環境準備 環境情報  DB設計情報  dbcaインストール情報  dbcaでDB作成をする場合、以下項目以外はデフォルト 68 EnterpriseManagerの構成 する パスワード oracle ※sys/system/dbsnmp/sysman同じ FastRecoveryAreaの構成 しない Databaseコンポーネント EnterpriseManagerRepository以外 は構成しない Standard Database Components 全て構成しない パラメータ SGA：900MB PGA：200MB データベース名 bcdb ORACLE_HOME /u01/app/oracle/product/11.2.0/orcl 構築スクリプト /u01/app/oracle/admin/bcdb/scripts/ ※今回様に事前作成済み
69. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪環境準備 環境情報  ユーザ表領域  スキーマ 69 スキーマ bcuser パスワード oracle デフォルト表領域 t_bootcamp 一時表領域 temp 表領域名 bcuser データファイル名 /u01/app/oracle/oradata/bcdb/t_bootcamp01.dbf サイズ 100MB 自動拡張 OFF 自動セグメント管理 AUTO 表領域タイプ SMALL
70. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪環境準備  アラートログの確認 $ tail -300 /u01/app/oracle/diag/rdbms/bcdb/bcdb/trace/alert_bcdb.log  Oracle 接続確認 $ sqlplus bcuser/oracle@192.168.56.101:1521/bcdb 70
71. (C) Recruit Technologies Co.,Ltd. All rights reserved. appendix 71
72. (C) Recruit Technologies Co.,Ltd. All rights reserved. DB起動停止 • nomount • 初期化パラメータの読み込みとインスタンス起動 • mount • 制御ファイルの読み込みとREDOログのマウント • open • データファイル、REDOログのOPEN 72 SQL> startup nomount SQL> alter database mount ; SQL> alter database open ; ※予めalert.logをtailしておくと動作が分かり易い $ tail -f /u01/app/oracle/diag/rdbms/bcdb/bcdb/trace/alert_bcdb.log
73. (C) Recruit Technologies Co.,Ltd. All rights reserved. DB起動停止 • shutdown immediate • 既存トランザクションをロールバックして停止 • shutdown abort • 強制的に停止 73 SQL> shutdown immediate SQL> shutdown abort
74. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル  REDOログファイル • メンバーの追加（冗長化） 74 GROUP# THREAD# MEMBER SEQUENCE# MB STATUS ---------- ---------- ---------------------------------------- ---------- ---------------- -------------- -- 1 1 /u01/app/oracle/oradata/bcdb/redo01.log 31 50 CURRENT 2 1 /u01/app/oracle/oradata/bcdb/redo02.log 29 50 INACTIVE 3 1 /u01/app/oracle/oradata/bcdb/redo03.log 30 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;
75. (C) Recruit Technologies Co.,Ltd. All rights reserved. データベースファイル  REDOログファイル • メンバーの追加（冗長化） 75 GROUP# THREAD# MEMBER SEQUENCE# MB STATUS ---------- ---------- ---------------------------------------- ---------- ---------------- -------------- -- 1 1 /u01/app/oracle/oradata/bcdb/redo01.log 31 50 CURRENT 1 1 /u01/app/oracle/oradata/bcdb/redo11.log 31 50 CURRENT 2 1 /u01/app/oracle/oradata/bcdb/redo02.log 29 50 INACTIVE 2 1 /u01/app/oracle/oradata/bcdb/redo12.log 29 50 INACTIVE 3 1 /u01/app/oracle/oradata/bcdb/redo03.log 30 50 INACTIVE 3 1 /u01/app/oracle/oradata/bcdb/redo13.log 30 50 INACTIVE
76. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  はじめに  データベース  Oracle Databaseの基本  環境構築  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニング・障害対応の実践 76
77. (C) Recruit Technologies Co.,Ltd. All rights reserved. クラスタ 複数のサーバを１つに論理的に見せることで、 可用性・負荷分散を高めるためのアーキテクチャ ▪可用性と負荷分散 可用性 障害時にもサービスを継続できる（可用性）ように、 act-act/act-stbyでクラスタやレプリケーションを構成 負荷分散 スループットを最大化させるために、複数サーバで処理を分散 77
78. (C) Recruit Technologies Co.,Ltd. All rights reserved. クラスタ ▪shared everything/shared nothing shared everything  共有ディスクを利用し、複数サーバから全てのデータにアクセス  コールドスタンバイ構成（HA）  負荷分散を最大化は出来ず、FO時間にサービス断となるため 可用性が高いとは言い切れない  ホットスタンバイ構成（RAC）  全てのサーバで同一データに参照・更新が可能であるため、 負荷分散・可用性ともに高いものの、サーバ間の同期処理 （キャッシュフュージョン）によるオーバーヘッドがかかる 78 shared everything （コールドスタンバイ） shared everything （ホットドスタンバイ）
79. (C) Recruit Technologies Co.,Ltd. All rights reserved. クラスタ ▪shared everything/shared nothing shared nothing  各サーバ固有のディスクで処理を行ことで、各サーバのスループットを最大化する  障害時にデータの再配置を行う期間は利用が制限されるため可用性は低い 79 shared nothing
80. (C) Recruit Technologies Co.,Ltd. All rights reserved. RAC 80
81. (C) Recruit Technologies Co.,Ltd. All rights reserved. Real Application Cluster Oracleのshared everythingクラスタ製品 • すべてのインスタンスから、全てのデータファイル／制御ファイル／REDOログ にアクセス・更新可能  データベースはクラスタで１つ、各ノードにインスタンスを構成  REDOログ、UNDO表領域は、インスタンス毎に必要  PrivateNetwork(インターコネクト)、投票ディスクを使ってノード間でハートビート  Singleインスタンスとの違いは、共有ディスクとインターコネクトの存在  RACに登録されたリソースをsrvctlコマンドで管理 81
82. (C) Recruit Technologies Co.,Ltd. All rights reserved. Real Application Cluster 82 Oracle Database リスナー プロセス インスタンス SGA バックグラウンドプロセス PGA 制御ファイル REDOログ データ ファイル ocr vote インスタンス SGA バックグラウンドプロセス PGA UNDO REDOログ UNDO アーカイブログ アーカイブログ 初期化パラメータ PGA サーバー プロセス PGA サーバー プロセス リスナー プロセス Private network
83. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACへのDB接続 • VIP(Virtual IP: RACの場合、同じIPからそれぞれのRACに接続) • vipはnodeごとに1つ設定 • RACの場合、DB接続はVIPを指定 • SCAN−VIP（11g以降） • 端的にはVIPの上位のVIP • RACの接続にはサービス名を使う • ORACLE_SID • インスタンスの識別子（主にSingle） • サービス名（service_name） • サービスの識別子 • SCANを使用したサービス接続 • 特定ノードめがけた接続 83 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");
84. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACのへのDB接続 • 接続時フェイルオーバー • クライアントからの接続が失敗した場合は、他リスナーへ接続 84 TNS_BCDB = (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 ① ②
85. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能 85
86. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能  RACのリソース管理 • グローバルキャッシュサービス（GCS）  キャッシュフュージョン ノード間でキャッシュの一貫性を保つ為のサービス • グローバルエンキューサービス（GES）  キャッシュの一貫性以外の排他制御を保つ為のサービス 86 プロセス 役割 CSSD Cluster Synchoronization Service Daemon インターコネクト通信でノード間の同期を行う CRSD Cluster Ready Service Daemon クラスタリソース（インスタンス、リスナー、VIP等）の起動停止、監視 LMON GESモニター。クラスタ内のインスタンス状態を監視し、グローバルリソース の再定義を行う LMD0 GESデーモン。クラスタ間のロック管理を行いGESリソースを調整する LMSn GCS。キャッシュフュージョンを行うプロセス
87. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能 キャッシュフュージョンの仕組み • インターコネクトでUDP通信 • リソースマスターが、最新ブロックの情報を管理 • リソースマスターは、必ずクラスタ内の１つのノードにある • 最新ブロックの問い合わせは、最大３回（2node-RACは２回）  2−way、3−way・・・ローカル含めやり取りされるインスタンス数 87 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にある から転送 ③ ②
88. (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 • ブロックの制御情報（ロックなど） 88 待機イベント イベントの種類 備考 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）での処理遅延
89. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能 （その他） 89
90. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能 • DRM（Dynamic Resource Mastering） • 頻繁にアクセスされるインスタンスへ、リソースマスタ（リソースを管理し ているマスタノード）を動的に再配置 • アプリケーションパーティショニングをしている場合は、自動で再配置され るとオーバーヘッドになるケースもあり無効化も有効 • Reconfigration • インスタンスの起動／停止に伴い、データベース内の各インスタンスの構成 情報と付随するリソースの再定義を行う処理 • セッションは継続されるが、処理が一時停止したような状態となる 90
91. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACの機能 • Read Mostly Lock • 更新が少なく、読み込みが多いブロックは、リソースマスターへの問い合 わせはせず、直接ディスクから読み込むようにする機能 • デフォルトで有効 • In Memory Parallel Query（11gR2以降） • パラレルクエリ（複数のCPUを経由するクエリ）は、直接ディスク からDirectPathRead（バッファキャッシュを経由しない読み込 み）する • バッファキャッシュの80%以内のセグメントは バッファキャッシュを使う • InterNode Parallel Query（11gR2以降） • パラレル処理を、自動的に複数ノードで実行 • SQLは、単一インスタンスに向けて実行 • Parallel_force_local=TRUE（デフォルト） 91
92. (C) Recruit Technologies Co.,Ltd. All rights reserved. RACのスケール 92
93. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪拡張性  スケールアウト • リニアにスループットが伸びるのは、感覚的にせいぜい２～３ノード程度 • その後は、徐々にスループットの伸びが鈍化 • 分散ロックやキャッシュフュージョンが多くなり、スケールし辛い  スケールアップ • rdbmsの特性上、スケールアップによる性能向上が必要になることが多い • 分散処理やキャッシュフュージョンとか考える必要がないから • でもHWリソースには限界はある・・・ 93
94. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪拡張性？  Exadata • Oracle Databaseのappliance（中身はIAサーバ[spark版もある]） • インターコネクト通信にInfiniBandを使うことで キャッシュフュージョンなどのスループットを上げて、 スケールアウトでもスループットが落ちにくい（ ￥お高い） • 多少専門的な知識が必要になる • smart scanでストレージ側でもデータを絞り込みI/O量を削減 94
95. (C) Recruit Technologies Co.,Ltd. All rights reserved. Fail Over設計 意外とよく漏れがちな、FO時のAPからのセッション設計 インスタンス障害時に、どのnodeにFOするのか 例) ４ノードRACに1000セッション、１ノードで障害が起きた場合 １ノードあたり250セッション ↓障害発生↓ １ノードあたり333セッション （Oracleのバックグラウンド処理やAP以外からのセッションも考慮） 95
96. (C) Recruit Technologies Co.,Ltd. All rights reserved. APパーティショニング（シャーディング） • アプリケーションパーティショニングにより、負荷分散と拡張性を獲得 • 大規模データベースでは、RACで負荷分散や拡張性を得るためには、 キャッシュフュージョンを低減する必要がある • 分散DBのように、AP側でアクセスするデータ（ブロック）を制御する手法 96 キャッシュフュージョンの発生を極力減らすために、インスタンス ごとにアプリケーションでアクセスするテーブルやインデックスの セグメントを固定してしておくことで、スケールするようになる
97. (C) Recruit Technologies Co.,Ltd. All rights reserved. R大規模システムのAP−DBの構成例 97 割愛
98. (C) Recruit Technologies Co.,Ltd. All rights reserved. 例）待機イベントの状況（１） • とあるサイトのOEM状況（Cluster待機イベント多め） 98
99. (C) Recruit Technologies Co.,Ltd. All rights reserved. 例）待機イベントの状況（２） • とあるサイトのOEM状況（Cluster待機イベント少なめ） 99
100. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  はじめに  データベース  Oracle Databaseの基本  環境構築  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニング・障害対応の実践 100
101. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪非機能  マイグレーション  EOSL対応、VerUp（exp/imp、GoldenGate） 性能  パフォーマンス改善、管理（OracleEnterpriseManager）  キャパシティ管理 セキュリティ  監査（DBA監査、標準監査、統合監査、FGA監査）  職務分掌（Audit Vault）  アクセス制御（DBFirewall、VirtualPrivateDatabase）  暗号化（TDE） 可用性  サービス継続性（RAC）  DisasterRecovery（DataGuard）  バックアップリカバリ、Flashback 101
102. (C) Recruit Technologies Co.,Ltd. All rights reserved. 基本ポリシー マイグレーション  商用MWであるためEOSL対応が必要  リクルートでも対応を検討中  11gR2も2020年でExtendedSupport終了となる  SustainingSupportも選択肢の一つ  機能も進化しているため、性能やメンテナンス性も熟考する 102 ▲Premier Support メジャーバージョン出荷から５年間 ▲Extended Support Premire Support終了から３年間 ▲Sustaining Support 無期限 https://www.oracle.com/jp/support/lifetime-support/index.html
103. (C) Recruit Technologies Co.,Ltd. All rights reserved. マイグレーションのツール  GoldenGate ほぼ無停止でVerUp可能だが、コストもかかりライトではない  exp/imp（DataPump）  昔ながらのやり方なので、昨今のデータ移行には向かない  開発環境の構築や論理バックアップ等では、まだまだ現役  Upgrade script リクルートではupgrade scriptでVerUp  比較的ライトでサービス停止は短時間 103 10gR2 11gR2／12cR2 ＜Upgradeスクリプトの流れ＞ ①NFS同期 ②旧DB停止 ③NFS同期解除 ④新DB起動 ⑤upgradeスクリプト実行 同期
104. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪非機能  マイグレーション  EOSL対応、VerUp（exp/imp、GoldenGate） 性能  パフォーマンス改善、管理（OracleEnterpriseManager）  キャパシティ管理 セキュリティ  監査（DBA監査、標準監査、統合監査、FGA監査）  職務分掌（Audit Vault）  アクセス制御（DBFirewall、VirtualPrivateDatabase）  暗号化（TDE） 可用性  サービス継続性（RAC）  DisasterRecovery（DataGuard）  バックアップリカバリ、Flashback 104
105. (C) Recruit Technologies Co.,Ltd. All rights reserved. 性能問題発生 → ボトルネック調査 → チューニング 本章では、性能問題に対する 基本的な考え方を説明します
106. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪そもそも何が問題か カスタマーやクライアントがやりたいことができない！ 106 旅行の予約画面で 固まるんだけど・・・ 会社説明会のエントリーで きるのかな… 人生かかってるんだけど… 私（美容師）のスケ ジュール変更ができない んだけど… 賃貸の在庫データの 持ち方を変更したいん だけど… コストかけたくない…
107. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪性能問題 性能が悪いとは… • そもそもの見込んだ時間／リソースに収まっていない • 処理の中に無駄な処理（待機）が発生している状態 事実確認が大事 • データの参照／更新処理を行うDBは悪者になりやすい(事実悪事を働く…) →ボトルネックはどこなのか事実を確認して現状を分析 107 AP DB Disk 5 sec 4 sec？ ? sec
108. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪性能問題へのアプローチ 108 目標値の決定 現状の分析 チューニング 方針の策定 チューニング の実施 現在の状況を確認しつつ、並行して繰り返し行う →分析したが、どこが問題なのかを見極める ここをしっかり！
109. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪チューニングの目的 目的は２つに大別 • スループットやレスポンスタイムの改善 • リソース使用量の削減 何のためにやるのかは明確に！ 109
110. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪スループットやレスポンスタイムの改善 スループットなのか、レスポンスなのか • スループット [TPSとかPV/sとか]  単位時間当たりの処理量 • レスポンスタイム ＜= TAT  処理開始から応答を受け取るまで（応答時間）  処理開始から結果が出るまで（TAT） 110 “リクルート” － － － タバコ吸っ て待つか
111. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪リソース使用量の改善 リソース使用量が増えている（もしくは減らしたい） • データが増えている？  商品／商材の増加  カスタマ数が右肩上がり  機能追加 • ワークロードが変わった？  利用者数の伸び  アクセスするデータに変化  機能追加 111
112. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪チューニングの進め方 性能問題の対策効果とコストは相反  一般的に、ビジネスロジックを見直しした方がHW増強等より効果が高い →処理をするから時間やリソースが必要となるので 極端な話、やめるというのが一番効果があるが… • “やめる”・・・やりたいことができない • “やる“ ・・・リソース増強やチューニング 本当にその処理の実行が妥当なのか、背景を早めに見極める必要がある 112 ビジネス ロジック AP DB OS/HW 効果大 効果小 SQL パラメータ メモリ ディスク
113. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  はじめに  データベース  Oracle Databaseの基本  環境構築  Oracle RACの基本  データベースの非機能  DBチューニングの実践 113
114. (C) Recruit Technologies Co.,Ltd. All rights reserved. 準備 環境 先ほど構築したOracle導入済みVMを利用 流れ • VirtualBox上にVMをインポート 済 • 環境構築 • チューニング概要（講義/簡易演習） • 総合演習 • 問題クエリ１実行 • 調査、チューニング • 問題クエリ１解説 • 問題クエリ２実行 • ・・・
115. (C) Recruit Technologies Co.,Ltd. All rights reserved. 準備 Print 19/6/20 17時22分 環境構築 o ローカルPC上に任意のディレクトリ作成 o queryディレクトリをローカルにコピー o Virtualboxの「bc_vm」の設定から、共有フォルダーとして上記ディレクトリを 追加 o rootユーザで接続。フォルダをマウント 例：mkdir /media/sf_query/ mount -t vboxsf -o uid=500,gid=500 query /media/sf_query o oracleユーザで接続。マウントしたディレクトリに移動 $ cd /media/sf_query o sqlplusでDBに接続 $ sqlplus / as sysdba o SQL > @create_schema.sql（表領域とスキーマ作成） o SQL > @create_table_and_data.sql（テーブル作成とデータ投入。） o SQL > show user （mytuningである事を確認。）
116. (C) Recruit Technologies Co.,Ltd. All rights reserved. チューニング概要
117. (C) Recruit Technologies Co.,Ltd. All rights reserved. ORACLE DBの動作 アプリケーション ディスク SQL Oracle ① ③ ② 上記３処理について、チューニングポイントを説明する ① SQLを解析し、実行計画を作成（Parse） ② 実行計画を元に順番に処理を実行 ③ メモリもしくはディスクからデータを取得 結果
118. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 ②実行計画を元に順番に処理を実行 ③メモリもしくはディスクからデータを取得
119. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 ②実行計画を元に順番に処理を実行 ③メモリもしくはディスクからデータを取得
120. (C) Recruit Technologies Co.,Ltd. All rights reserved. SELECT 名前 , 属性 FROM 会員表 WHERE 会員ID = 26 SQL文 SQLは処理手順を書かずに、欲しいデータの抽出条件を書けば良い 会員ID26番のメンバーの名前と属性を知りたい PARSE 会員ID 属性 名前 10 C tanaka 26 B sato 45 A suzuki 67 B morishita 80 A inomata 会員表 UPDATE 会員表 SET 属性 = 'D' WHERE 会員ID = 26 会員ID26番のメンバーの属性をDに変更したい
121. (C) Recruit Technologies Co.,Ltd. All rights reserved. 1.会員表を上から1行ずつスキャン 2.取り出された行を一つづつチェック ID=26のものを抽出 3.テーブルの最終行まで来たら終了 Parseとは DBMSが、SQL文を解析し、処理手順（実行計画）を組み立てる処理 会員ID26番のメンバーの名前と属性を知りたい SQL 処理手順（実行計画） PARSE SELECT 名前 , 属性 FROM 会員表 WHERE 会員ID = 26 Oracle(Parser)
122. (C) Recruit Technologies Co.,Ltd. All rights reserved. Parseのチューニング キャッシュ ハードパース（実行計画を作成） → CPU時間大 （SQL文によるが◦◦◦msのイメージ） ソフトパース（キャッシュされた実行計画を利用） → CPUほぼ使用しない （◦msのイメージ） PARSE
123. (C) Recruit Technologies Co.,Ltd. All rights reserved. Execute :A1 := ‘田中’ Execute :A1 := ‘佐藤’ Parseのチューニング キャッシュ 過去に同一SQLが投げられており、その実行計画がメモリ上に残っていれば ソフトパースになる 同一SQLでは無い例 Select 会員ID from 会員 where 名前 = '田中' Select 会員ID from 会員 where 名前 = '佐藤' 解決法 Select 会員ID from 会員 where 名前 = : A1 バインド変数を使用 PARSE
124. (C) Recruit Technologies Co.,Ltd. All rights reserved. Parse 演習 PARSE
125. (C) Recruit Technologies Co.,Ltd. All rights reserved. Parse 問題となった例 1 システムオープンを模倣した性能試験（無風状態から突然高負荷をかける） でDBサーバCPU高騰 数十秒間レスポンス遅延ののち自然に復旧 CPU使用率 時間推移 PARSE
126. (C) Recruit Technologies Co.,Ltd. All rights reserved. Parse 問題となった例 1 システムオープンを模倣した性能試験（無風状態から突然高負荷をかける） でDBサーバCPU高騰 ASH情報からCPU利用プロセスのほとんどがハードパースを実施中だとわかる。 事前にキャッシュに乗せる事でCPU高騰を回避。 ASH 情報 ↓ハードパース中か否か(Y/N) PARSE
127. (C) Recruit Technologies Co.,Ltd. All rights reserved. CPU処理（緑）が1時間ほど続いていることがわかる SQLトレースを取得すると、 実行時間のほぼ全てがparse時間という結果 part1 part… part1000 p1s 1 .. p1s 100 pxs 1 .. pxs 100 p10 00s 1 .. p10 000 s10 0 TABLE Parse 問題となった例 2 テーブル作成が終わらない（データは空のテーブル作成なのに。。） 1,000パーティション* 100サブパーティション= 約100,000パーティション のテーブル作成が3時間待っても終わらない。 OEM SQLイベント推移 PARSE
128. (C) Recruit Technologies Co.,Ltd. All rights reserved. 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 p2_2016_01_01 values less than (to_date('2016-01-01','YYYY-MM-DD')) ,subpartition p2_2016_01_02 values less than (to_date('2016-01-02','YYYY-MM-DD')) : ) ,partition p3 ( subpartition p3_2016_01_01 values less than (to_date('2016-01-01','YYYY-MM-DD')) ,subpartition p3_2016_01_02 values less than (to_date('2016-01-02','YYYY-MM-DD')) : ) : : ,partition p1000 ( subpartition p1000_2016_01_01 values less than (to_date('2016-01-01','YYYY-MM-DD')) ,subpartition p1000_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分で完了 元のクエリイメージ （≒ 100,000行） 修正後のクエリイメージ （≒ 1,100行） Parse 問題となった例 2 テーブル作成が終わらない part1 part… part1000 p1 s1 .. p1s 100 px s1 .. pxs 100 p1 00 0s 1 .. p10 000 s10 0 TABLE PARSE
129. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 ②実行計画を元に順番に処理を実行 ③メモリもしくはディスクからデータを取得
130. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実行計画 実行計画とは SQL文を実行するための処理手順 （Oracle Databaseが使用するステップの組合せ）
131. (C) Recruit Technologies Co.,Ltd. All rights reserved. アプリケーション データ SQL 結果 Oracle 会員ID アクセス時間 45 18:00 ・・ 会員ID アクセス 時間 45 18:00 26 8:00 ・・・ ・・・ 会員表 実行計画 会員ID 属性 名 10 C 26 B 45 A 計 10,000件 属性A 100件 アクセス表 あるシステムにアクセスした 「属性A」の「会員のID」と「アクセス時間」
132. (C) Recruit Technologies Co.,Ltd. All rights reserved. アプリケーション データ SQL 結果 Oracle 会員ID アクセス時間 45 18:00 ・・ 会員ID アクセス 時間 45 18:00 26 8:00 ・・・ ・・・ 会員表 実行計画 会員ID 属性 名 10 C 26 B 45 A 計 10,000件 属性A 100件 アクセス表 あるシステムにアクセスした 「属性A」の「会員のID」と「アクセス時間」 SELECT 会員表.会員ID , アクセス表.アクセス時間 FROM 会員表 , アクセス表 WHERE 会員表.属性 = 'A' AND 会員表.会員ID = アクセス表.会員ID
133. (C) Recruit Technologies Co.,Ltd. All rights reserved. アプリケーション データ SQL 結果 Oracle 1. 会員表をスキャンし属性Aのメンバを見つける 2. 上記で得られた会員IDをアクセス表から探す 実効計画A 会員ID アクセス 時間 45 18:00 26 8:00 ・・・ ・・・ 会員表 実行計画 会員ID 属性 名 10 C 26 B 45 A 計 10,000件 属性A 100件 アクセス表 あるシステムにアクセスした 「属性A」の「会員のID」と「アクセス時間」 Select 会員表.会員ID , アクセス表.アクセス時間 From 会員表 , アクセス表 Where 会員表.属性 = 'A' AND 会員表.会員ID = アクセス表.会員ID 会員ID アクセス時間 45 18:00 ・・
134. (C) Recruit Technologies Co.,Ltd. All rights reserved. 処理を実現する表の結合順序（実行計画）は複数ありえる アプリケーション データ SQL 結果 Oracle 1. アクセス表をスキャン 2.上記で得られた会員IDを会員表から探し属性Aのメンバ を見つける 実効計画A 実効計画B 会員ID アクセス 時間 45 18:00 26 8:00 ・・・ ・・・ 会員表 アクセス表 会員ID 属性 名 10 C 26 B 45 A 計 10,000件 属性A 100件 実行計画 1. 会員表をスキャンし属性Aのメンバを見つける 2. 上記で得られた会員IDをアクセス表から探す あるシステムにアクセスした 「属性A」の「会員のID」と「アクセス時間」 Select 会員表.会員ID , アクセス表.アクセス時間 From 会員表 , アクセス表 Where 会員表.属性 = 'A' AND 会員表.会員ID = アクセス表.会員ID 会員ID アクセス時間 45 18:00 ・・
135. (C) Recruit Technologies Co.,Ltd. All rights reserved. テーブルのスキャン方法 1件ずつデータを見ていく（10000件） 会員表 会員ID 属性 名前 10 C 26 B 45 A 67 B 80 A 95 C 計 10,000件 属性A 100件 実行計画 実効計画A 1. 会員表をスキャンし属性Aのメンバを見つける 2. 上記で得られた会員IDをアクセス表から探す
136. (C) Recruit Technologies Co.,Ltd. All rights reserved. テーブルのスキャン方法 事前に作成した「索引」を利用する（100件） 処理を実現するアクセス方法（実行計画）も複数ありえる 会員表 会員ID 属性 名前 10 C 26 B 45 A 67 B 80 A 95 C 計 10,000件 属性A 100件 実行計画 実効計画A 1. 会員表をスキャンし属性Aのメンバを見つける 2. 上記で得られた会員IDをアクセス表から探す 会員表属性列索引 属性 場所 A A 属性 場所 A B C 属性 場所 B B
137. (C) Recruit Technologies Co.,Ltd. All rights reserved. データ Oracle 性能が良い実行計画は？ 会員表の「会員ID」,「属性」、アクセス表の「会員ID」に 索引は貼ってあるとする。 会員ID アクセス 時間 45 18:00 26 8:00 ・・・ 計 10件 会員表 アクセス表 会員ID 属性 名前 10 C 26 B 45 A ・・・ 計 10,000件 属性A 100件 実行計画 あるシステムにアクセスした 「属性A」の「会員のID」と「アクセス時間」
138. (C) Recruit Technologies Co.,Ltd. All rights reserved. データ Oracle 実行計画B 性能が良い実行計画は？ 実行計画Bが良さそう 会員ID アクセス 時間 45 18:00 26 8:00 計 10件 会員表 アクセス表 会員ID 属性 名前 10 C 26 B 45 A 計 10,000件 属性A 100件 実行計画 実行計画A 1. 属性の索引を使って会員表をスキャン 100件 2. 会員IDの索引を使って、アクセス表を参照 100回 実行計画B 1. アクセス表をスキャン 10件 2. 会員IDの索引を使って、会員表を参照 10回 あるシステムにアクセスした 「属性A」の「会員のID」と「アクセス時間」
139. (C) Recruit Technologies Co.,Ltd. All rights reserved. データ Oracle 実行計画A 実行計画A 1. 属性の索引を使って会員表をスキャン 100件 2. 会員IDの索引を使って、アクセス表を参照 100回 実行計画B 1. アクセス表をスキャン 10000件 2. 会員IDの索引を使って、会員表を参照 10000回 会員ID アクセス 時間 45 3:00 26 8:00 5 10:00 1 12:00 45 18:00 計 10000件 会員表 アクセス表 性能が良い実行計画は？ 実行計画Aが良さそう (データ量が違えば適切な実行計画は異なる) 実行計画 会員ID 属性 名前 10 C 26 B 45 A 計 10,000件 属性A 100件 あるシステムにアクセスした 「属性A」の「会員のID」と「アクセス時間」
140. (C) Recruit Technologies Co.,Ltd. All rights reserved. 統計情報 良い実行計画を作成するためには、 各テーブルのデータ量をparserが知る必要がある アナライズ（統計情報収集） → 事前に各テーブルの情報を調査し、parserが利用できるようにDBに保管 （定期実行 or 手動実行） オプティマイザ統計（統計情報） → アナライズにより蓄積される情報 実行計画
141. (C) Recruit Technologies Co.,Ltd. All rights reserved. 統計情報 表統計 ・行数、データ・ブロック数、平均行長 列統計 ・列内の個別値数、NULL数 、分布(最大値 / 最小値 / ヒストグラム) 索引統計 ・リーフブロック数 、レベル (ツリーの高さ) システム統計 ・I/OやCPUのパフォーマンス など、オプティマイザ統計には、データ量だけでなく、 種々の情報が含まれます。 実行計画
142. (C) Recruit Technologies Co.,Ltd. All rights reserved. 会員 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件だったら 統計情報 アナライズ（統計情報収集） → 事前に各テーブルのデータ量等の情報を調査し、DBに保管 もし、アナライズ時から、大きくデータ量が変わったら 実際は実行計画Aが良いのに、Bが選択されてしまう → データ量が大きく変化した後は、再アナライズ 実行計画
143. (C) Recruit Technologies Co.,Ltd. All rights reserved. 統計情報 演習 実行計画
144. (C) Recruit Technologies Co.,Ltd. All rights reserved. 代表的な表の結合方法 ネステッド・ループ結合 1. 一つの表（外部表）にアクセス 2. 上記の出力結果1行ごとに、もう一つの表（内部表）にアクセスし結合 内部表の結合列に索引が利用できる場合に有効。 会員 ID 名前 10 A 26 B 45 C 計 100件 会員ID アクセス 時間 26 3:00 52 8:00 5 10:00 10 12:00 45 18:00 計 10000件 外部表 内部表 会員ID 名前 アクセ ス時間 10 A 12:00 26 B 3:00 45 C 18:00 ・・・ 実行計画
145. (C) Recruit Technologies Co.,Ltd. All rights reserved. 代表的な表の結合方法 ハッシュ結合 1. 一つの表にアクセスし結合列を元にメモリ上にハッシュテーブルを作成 2. もう一つの表1行ごとに結合列にハッシュ関数をかけ結合 二つの表のアクセスは繰り返す必要なく、1回で完了。 ハッシュテーブルがメモリに収まりきらないと性能が極端に悪化。 会員 ID 名前 10 A 26 B 45 C 計 100件 会員ID アクセス 時間 26 3:00 52 8:00 5 10:00 10 12:00 45 18:00 計 10000件 ハッ シュID 会員ID、 名前 1 26、B , 45、C 2 5、F 3 10、A ・・・ ・・・ メモリ ハッシュ関数 ハッシュ関数 会員ID 名前 アクセ ス時間 10 A 12:00 26 B 3:00 45 C 18:00 ・・・ 実行計画
146. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実行計画を読んでみよう 演習 実行計画
147. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実行計画を読んでみよう 読み方 ・インデントで整形されたツリー構造 ・基本的にはインデントが深いものから順に実行（同一の深さならば上のものから）。 実行計画
148. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実行計画を読んでみよう 1. KAIIN表をフルスキャン、属性Aでフィルタ（ID:3） 実行計画 会員ID 属性 名前 10 C tanaka 26 B sato 45 A suzuki 67 A morishita KAIIN
149. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実行計画を読んでみよう 1. KAIIN表をフルスキャン、属性Aでフィルタ（ID:3） 2. 上記で得られた会員IDを、ACCESSES表から KAIIN_IDの索引を用いて探索。（ID:4） 実行計画 会員ID 属性 名前 10 C tanaka 26 B sato 45 A suzuki 67 A morishita 属性 場所 10 26 45 属性 場所 10 67 110 属性 場所 67 ・・・ KAIIN ACCESS表KAIIN_ID列索引
150. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実行計画を読んでみよう 1. KAIIN表をフルスキャン、属性Aでフィルタ（ID:3） 2. 上記で得られた会員IDを、ACCESSES表から KAIIN_IDの索引を用いて探索。（ID:4） 3. 索引のROWIDを持ちいてACCESSES表にアクセス （ID:5） 実行計画 会員ID アクセス 時間 45 18:00 26 8:00 67 21:00 ACCESSES表 属性 場所 10 26 45 属性 場所 67 ・・・ ACCESS表KAIIN_ID列索引 会員ID 属性 名前 10 C tanaka 26 B sato 45 A suzuki 67 A morishita KAIIN 属性 場所 10 67 110
151. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実行計画変化による性能劣化 オンライン影響を考え、バッチも統計情報収集も深夜に実行されることが多い 実行時間帯の重なりにより、意図しない統計状態になることに注意 実行計画
152. (C) Recruit Technologies Co.,Ltd. All rights reserved. 先にA表、次にB表にアクセスするように 実行計画を作って ヒント句 統計情報は正しくても、性能の悪い実行計画が生成されてしまう事はある （複雑なクエリになると特に） 対処策として、ヒント句で明示的に実行計画を誘導する方法がある 例： select /*+ LEADING(A B) */ A.名前 from 会員 A , アクセス B where A.会員ID = B.会員ID ; スペルミス等でも、エラーは発生せずにSQLは発行されるので、 ヒント句が効かない場合は、SQLをよくチェック。 実行計画
153. (C) Recruit Technologies Co.,Ltd. All rights reserved. ヒント句の注意点 実行計画が最適では無かったので、 「ヒント句を記載したが、実行計画が変わらない」 SQLファイル select /*+ LEADING(A B) */ A.名前～ ヒント句も必ず効くわけではないので、仕方ないかと思いつつ調査 実行計画
154. (C) Recruit Technologies Co.,Ltd. All rights reserved. ヒント句の注意点 DB上から発行されたSQLを見てみると、 select A.名前～ ヒント句が消えている。。 →アプリケーションフレームワークでコメントを省いて DBに投げる実装がされていた SQLのコメント select /* コメント */ ～ ヒント句 select /*+ ヒント句 */ ～ 利用するフレームワークによっては、select --+ヒント句 を利用しましょう。 実行計画
155. (C) Recruit Technologies Co.,Ltd. All rights reserved. ①SQLを解析し、実行計画を作成 ②実行計画を元に順番に処理を実行 ③メモリもしくはディスクからデータを取得
156. (C) Recruit Technologies Co.,Ltd. All rights reserved. データの読み込み データ取得方法 ・極力データはメモリから読み込ませたい ・運用中のシステムではOracleのアドバイザで推奨サイズを確認 DISKスキャンを減らすために、SGAサイズを適切な値に データ DBバッファ Oracle NFS（HDD）環境で 1ブロック◦ms等 OS/メモリ PGA SGA 共有プール DBバッファ ラージプール javaプール REDOログ バッファ SGA I/O
157. (C) Recruit Technologies Co.,Ltd. All rights reserved. データのソート ・データ量からサイズを見積もりディスクソートが発生しないように、PGAを適切な値に ・運用中のシステムではOracleのアドバイザで推奨サイズを確認 DISKスキャンを減らすために、PGAサイズを適切な値に Disk K C A E B ・・・ J ばらばらのデータを 文字の昇順に取り出したい。 A B C D E ・・・ J メモリソート ディスクソート Oracle OS/メモリ PGA SGA 共有プール DBバッファ ラージプール javaプール REDOログ バッファ PGA I/O
158. (C) Recruit Technologies Co.,Ltd. All rights reserved. 索引を利用した読み込み 欲しいレコードが少ない場合のみINDEXスキャンが有効（指標10-15%） Oracle A K H J I N F L D B M E O C G P A-H左 I-P右 A 1 B 3 C 2 I 2 J 5 B-Tree INDEX TABLE Oracleは ブロック単位でI/O （デフォルト8KB） データ Cを取得する I/O
159. (C) Recruit Technologies Co.,Ltd. All rights reserved. 索引によるチューニング事例 あるサービスの性能試験時に下記SQLが遅かった クエリイメージ select * from taba where flag = :f フルスキャンなので索引作成も考えられるが、flagの種類を確認すると3種類 単純計算だとテーブル全体の1/3(33%)の行を取得するので、 索引スキャンでも軽くならないはず。 ------------------------------------------------------------- |Id |Operation |Name | ------------------------------------------------------------- |0 |SELECT STATEMENT | | |*1 | TABLE ACCESS FULL |TABA | ------------------------------------------------------------- I/O
160. (C) Recruit Technologies Co.,Ltd. All rights reserved. 索引によるチューニング事例 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)*/ チューニング前（左）と比較して、チューニング後（右）は 1実行あたりのバッファ読み取りが改善 I/O
161. (C) Recruit Technologies Co.,Ltd. All rights reserved. 索引が問題となった話 C/O当日、DBサーバのCPU高騰。 負荷を押し上げているのは先ほどチューニングしたSQL。 実行時間 性能試験10ms → 本番120ms ヒント句が効かずに意図した索引が利用されず実行計画が劣化した？ ----------------------------------------------------------------------- |Id | Operation | Name | ----------------------------------------------------------------------- |0 | SELECT STATEMENT | | |*1 | INDEX RANGE SCAN | COLA_TABA | ----------------------------------------------------------------------- →意図した索引は使われていた。 I/O
162. (C) Recruit Technologies Co.,Ltd. All rights reserved. 索引が問題となった話 しかし、C/O当日、DBサーバのCPU高騰。 負荷1位のSQLは例のチューニングしたもの。 実行時間 性能試験10ms → 本番120ms tabaの生データを見ると、 flag列が 「3」 の割合は数十％ →試験環境とカーディナリティが異なっていた。 ヒント句を外しフルスキャンにする事で性能改善。 ・データの選択率によっては、索引スキャンよりもフルスキャンが適切な事も ・性能試験環境のデータ作成方法は気をつけよう（件数、カーディナリティ） I/O
163. (C) Recruit Technologies Co.,Ltd. All rights reserved. ▪ブートキャンプ～Database～  データベース  rdbmsの特徴  Oracle Databaseの基本  Oracle RACの基本  データベースの非機能  パフォーマンスチューニング概要  DBチューニング・障害対応の実践
164. (C) Recruit Technologies Co.,Ltd. All rights reserved. PC上の擬似サービスで発生する 性能問題・障害に対処しながら 調査から解決までの流れ実践しを感覚をつかむ
165. (C) Recruit Technologies Co.,Ltd. All rights reserved. 準備 環境 先ほど構築したOracle導入済みVMを利用 流れ • VirtualBox上にVMをインポート 済 • 環境構築 済 • チューニング概要（講義/簡易演習） 済 • 総合演習 • 問題クエリ１実行 • 調査、チューニング • 問題クエリ１解説 • 問題クエリ２実行 • ・・・
166. (C) Recruit Technologies Co.,Ltd. All rights reserved. 問題１ ▪事象 会員がエントリしたイベント一覧を確認する画面のレスポンスが徐々に遅くなっている ▪簡易調査 上記画面で呼ばれているNo1.sqlが1回当たり数秒かかり遅くなっている事が判明。 ▪依頼事項 No1.sqlが遅い原因を調査し、チューニング策を提示してください。 ▪調査方針 ・No1.sqlを実行し、完了までの時間を測定して下さい。 ・アクセスしているテーブルの定義を確認して下さい。 ・アクセスしているテーブルの件数(select count(*) from テーブル名)を確認して下さい。 ・アクセスしているテーブルのどのカラムに索引が貼られているかを確認して下さい。 ・No1.sqlの実行計画を確認して下さい。 ・遅い原因を調査し、改善策を実施して下さい。 実践
167. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実践 問題２ ▪事象 主催者がイベントに何名のエントリー者がいるか、確認する画面が重い。 ▪簡易調査 OEMで負荷上位クエリを確認すると、 No2.sqlが1回当たり数秒かかり負荷を押し上げている事が判明。 SQLのコメントから、このクエリが上記画面で呼ばれている事も確認済み。 ▪依頼事項 No2.sqlが遅い原因を調査し、チューニング策を提示してください。 ▪調査方針 ・No2.sqlを実行し、完了までの時間を測定して下さい。 ・遅い原因を調査し、改善策を実施して下さい。
168. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実践 問題３ ▪事象 日次のエントリー数集計バッチがエラー ▪簡易調査 バッチログからNo3.sqlが異常終了していることは確認済み。 ▪依頼事項 No3.sqlがエラーとなる原因を調査し、改善策を提示してください。 ▪調査方針 ・No3.sqlを実行し、エラー内容を把握して下さい ・エラー原因を調査し、改善策を実施して下さい。
169. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実践 問題４ ▪事象 事前にお気に入りに登録したイベントにまだエントリーしていない会員 を抽出するバッチを起動したところエラー終了。 ▪簡易調査 バッチログからNo4.sqlが異常終了していることは確認済み。 ▪調査方針 ・No4.sqlを実行し、エラー内容を把握して下さい ・エラー原因を調査し、改善策を実施して下さい。
170. (C) Recruit Technologies Co.,Ltd. All rights reserved. 実践 問題５ ▪事象 バッチを実行したところ、オンライン画面のレスポンスが返ってこなくなる事象が発生。 ▪簡易調査 OEMで負荷上位クエリを確認すると、No5.sqlが明らかに重くなっている。 ▪依頼事項 No5.sqlが遅い原因を調査し、チューニング策を提示してください。 ▪調査方針 ・ターミナルを二つ立ち上げ、MYTUNINGユーザでDBに接続して下さい。 ・片方のターミナルでNo5_batch.sqlを実行して下さい。（実行後放置して下さい） ・もう片方のターミナルでNo5.sqlを実行して下さい。 ・レスポンスが返ってこない原因を調査し、改善策を実施して下さい。