Introduction of Oracle Database Architecture(抜粋版) - JPOUG Oracle Database入学式 2017
-
Upload
ryota-watabe -
Category
Technology
-
view
490 -
download
0
Transcript of Introduction of Oracle Database Architecture(抜粋版) - JPOUG Oracle Database入学式 2017
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 1 Copyright © 2017 CO-Sol Inc. All Rights Reserved.
2017年4月17日
株式会社コーソル 渡部 亮太
Introduction of Oracle Database Architecture
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 2
自己紹介+所属会社紹介
• 渡部 亮太(わたべ りょうた)
– JPOUG 共同創設者、ボードメンバー
– Oracle ACE
– 著書「プロとしてのOracleアーキテクチャ入門 [第2版]」 「プロとしてのOracle運用管理入門」
– ブログ「コーソルDatabaseエンジニアのBlog」 http://cosol.jp/techdb/
• 株式会社コーソル
– 「CO-Solutions=共に解決する」の理念のもと、Oracle技術に特化した事業を展開中。心あるサービスの提供とデータベースエンジニアの育成に注力している
– 社員数: 135名 (2017年4月時点)
– ORACLE MASTER Platinum 11g 取得者数 47名 ORACLE MASTER Platinum 12c 取得者数 32名 取得者数 日本 No.1
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 3
Agenda
I. OracleアーキテクチャとSQL処理
II. データとファイルI/O
III. テーブル、索引と実行計画
IV. オプティマイザ統計
V. データベースバッファキャッシュ
VI. SQLの解析と共有プール
VII. ソートとPGA、一時表領域
VIII.まとめ
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 4
RDBMSとは / Oracle Databaseとは
• RDBMS(Relational Database Management System) =表(テーブル)形式のデータを保管するデータベース管理システム
• データ操作用の言語SQLを用いて、クライアントアプリケーションからデータの検索および更新を実行できる
• Oracle DatabaseはRDBMS製品の1つ
テーブル
id ename ・・・
87 Steven ・・・
204 Hermann ・・・
ABC xxx ・・・
クライアント
アプリケーション
id ename ・・・
87 Steven ・・・
204 Hermann ・・・
205 Shelly ・・・
SELECT …
① SQL発行
②検索結果
RDBMS
以後 "Oracle Database"を"Oracle"と略記
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 6
SQL処理の全体像
SGA
データファイル
クライアント
アプリケーション
データベース
バッファ
キャッシュ
共有
プール
一時ファイル
サーバープロセス
②
解析
PGA
1 2 3 4
1 2 3 4
① SQL発行
SELECT …
③
フェッチ
②解析結果を共有プールに保管
③データファイルから
データを取得&キャッシュ
④(必要な場合) データをソート
⑤ 検索結果
を返送
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 9
Oracleアーキテクチャ - データ・記憶域構造
表領域
テーブル
データファイル
データブロック
行データ
id ename ・・・
87 Steven ・・・
204 Hermann ・・・
205 Shelly ・・・
1つの表領域が複数のデータファイルから構成される場合もあります。
1つのテーブルが複数の連続した領域から構成される場合もあります。
行データ
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 10
SQLの実行とファイルI/O
セッション
SGA SQLの発行
実行結果の返送
データファイル
サーバープロセス
SQL実行
クライアント
アプリケーション
データベースバッファキャッシュ
データブロック
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 11
Oracleのテーブル(ヒープ構成表)
• テーブルに格納される行の順序は不定 – ある行の位置を特定できない
• ある行を取得したい場合の処理 1. テーブルのHWM以下の全ブロックを読
み出し (=TABLE ACCESS FULL、 いわゆるテーブルフルスキャン)
2. 条件に合致した行のみを抽出 (=filter)
• データ量が多い場合はI/Oが大量に発生し、非効率 – テーブルフルスキャンは一般に避けるべき
ブロック1
ブロック2
id name ・・・
87 Steven ・・・
27 Hermann ・・・
21 Shelly ・・・
id name ・・・
20 Neena ・・・
3 Lex ・・・
1 William ・・・
…
ブロックn
id name ・・・
103 Alexandar ・・・
105 David ・・・
203 Susan ・・・
テーブル
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 13
索引の仕組み
<DBA5>
<DBA9> <DBA4>
id=1 ブロック1
ブロック2
rowid5
…
id=1
索引
ルートブロック
ブランチブロック
リーフブロック
<DBA8>
ROWID id name ・・・
rowid0 87 Steven ・・・
rowid1 27 Hermann ・・・
rowid2 21 Shelly ・・・
id アドレス
~199 DBA5
~340 DBA…
803~ DBA…
ROWID id name ・・・
rowid3 20 Neena ・・・
rowid4 3 Lex ・・・
rowid5 1 William ・・・
…
id アドレス
~20 DBA9
~35 DBA8
175~ DBA4
id アドレス
1 rowid5
3 rowid4
20 rowid3
…
…
id アドレス
21 rowid2
27 rowid1
35 rowid9
…
id アドレス
175 rowid6
177 rowid8
199 rowid9
…
…
…
索引を用いたアクセス
INDEX UNIQUE SCANオペレーション
TABLE ACCESS BY INDEX ROWID (BATCHED) オペレーション
A
B
ブロックn
ROWID id name ・・・
rowid97 103 Alexandar ・・・
rowid98 105 David ・・・
rowid99 203 Susan ・・・
C
A
id=1
B
表
表を用いたアクセス
TABLE FULL SCANオペレーション C
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 14
実行計画とは
• 実行計画は、SQLがOracleで実行される手順を示す • 実行計画はコストベースオプティマイザ(CBO; Cost-
Based Optimizer)が作成する – CBOはOracleの内部コンポーネント – オプティマイザ統計が最新でないと適切な実行計画が作成されないことに注意
• 意図したパフォーマンスが得られない場合、想定した実行計画で実行されているかチェックする必要がある
-------------------------------------------------------------- | Id | Operation | Name | Rows |... | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | |... | | 1 | NESTED LOOPS | | 2 |... | |* 2 | TABLE ACCESS FULL | PA | 1 |... | | 3 | TABLE ACCESS BY INDEX ROWID| CH | 2 |... | |* 4 | INDEX RANGE SCAN | IDX_CHPA | 2 |... | --------------------------------------------------------------
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 16
実行計画と物理I/Oブロック数の比較
-------------------------------------------------- | Id | Operation | Name | Rows |... | -------------------------------------------------- | 0 | SELECT STATEMENT | | 1 |... | |* 1 | TABLE ACCESS FULL| TESTTBL | 1 |... | -------------------------------------------------- 統計 ---------------------------------------------------------- 10098 physical reads
--------------------------------------------------------------------- | Id | Operation | Name | Rows |... | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 |... | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TESTTBL | 1 |... | |* 2 | INDEX RANGE SCAN | TESTIDX1 | 1 |... | --------------------------------------------------------------------- 統計 ---------------------------------------------------------- 3 physical reads
索引作成
(出力を整形しています)
(出力を整形しています)
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 19
選択率と最適なアクセス
…
BLUE BLUE BLUE BLUE
BLUE BLUE
RED BLUE
BLUE BLUE BLUE BLUE
SELECT …
WHERE color='RED'
SELECT …
WHERE color ='BLUE'
索引(索引列color)
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 24
オプティマイザ統計の注意点
• 大量データ更新するたびに、オプティマイザ統計を収集する必要がある
– DBMS_STATS.GATHER_xxxプロシージャを実行する
– データとオプティマイザ統計は独立して存在する
– 少量データ更新であれば、オプティマイザ統計を再収集する必要はない
• オプティマイザ統計はあくまでもデータの特徴を示す「サマリ情報」に過ぎないので、あまり神経質に再収集する必要はない
• Oracle Database 10g以降ではデフォルトで自動収集される
– 収集開始タイミング:平日22:00 + 土日
• バージョンにより詳細なスケジュールが異なることに注意
– 自動収集されるまでの間、データとオプティマイザ統計にズレが生じることに注意
• データ更新後にDBMS_STATS.GATHER_xxxプロシージャを実行すれば、ズレの発生を回避できる
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 25
Oracleアーキテクチャ – メモリ
セッション SGA
SQLの発行
実行結果の返送
データファイル
サーバープロセス
クライアント
アプリケーション
データベース
バッファ
キャッシュ
共有
プール
PGA
SQL実行
セッション
SQLの発行
実行結果の返送
サーバープロセス
クライアント
アプリケーション
PGA
SQL実行
専用サーバープロセス接続の場合
SGAの構成コンポーネントは省略して記載
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 26
データベースバッファキャッシュの役割
• データブロックのメモリキャッシュ
– 繰り返し同一のブロックにアクセスした場合の性能向上
• 論理I/Oと物理I/O
2回目のブロック読み出し
物理I/O
論理I/O
データファイル
SGA
データベース
バッファ
キャッシュ
1回目のブロック読み出し
データファイル
SGA
データベース
バッファ
キャッシュ
論理I/O
論理I/O=高速
物理I/O=低速
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 31
SQL解析と共有プール
• ハードパースで実施する処理 – SQL構文チェック
– アクセス対象オブジェクトのチェック
– SQLの最適化
– 実行計画の立案
• 解析結果は共有カーソルとして共有プールにキャッシュ
SGA サーバープロセス
クライアント
アプリケーション
共有プール
共有
カーソル
③キャッシュ
① SQL
解析
②ハードパース
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 32
共有カーソルのキャッシュ
• 一度実行されたSQLの解析結果(=共有カーソル)は再利用される – ソフトパースによる処理効率化:SQL処理時間の短縮、CPU負荷の軽減
SGA サーバープロセス
解析
クライアント
アプリケーション
共有プール
共有
カーソル
③キャッシュ
サーバープロセス
クライアント
アプリケーション
②ハードパース
②ソフトパース
① SQL
① SQL
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 36
WHERE条件のリテラル値のみが異なるSQL
• (類似していても)文字列として異なるSQLは、共有カーソルが共有されない – ハードパースによる処理効率低下:SQL処理時間とCPU負荷増加 – 共有メモリの非効率的な利用:使用メモリ量増加、キャッシュヒット率低下
SGA サーバープロセス
解析
クライアント
アプリケーション
共有プール
共有
カーソル
③キャッシュ
サーバープロセス
クライアント
アプリケーション
②ハードパース
①SELECT …
WHERE id = 1
①SELECT …
WHERE id = 2
共有
カーソル
③キャッシュ
②ハードパース
解析
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 37
バインド変数による共有カーソルの再利用
• 一度実行されたSQLの解析結果は再利用される – ソフトパースによる処理効率化:SQL処理時間の短縮、CPU負荷の軽減 – 共有メモリの効率的な利用:使用メモリ量削減、キャッシュヒット率向上
SGA サーバープロセス
解析
クライアント
アプリケーション
共有プール
共有
カーソル
③キャッシュ
サーバープロセス
クライアント
アプリケーション
②ハードパース
②ソフトパース
①SELECT …
WHERE id = :n
①SELECT …
WHERE id = :n
n=1
n=2
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 39
データのソート
SGA
一時ファイル
サーバープロセス
クライアント
アプリケーション
データベース
バッファ
キャッシュ
PGA
SQL実行
データファイル
1
2
3
4
1
2
3
4
セッション
SQLの発行
実行結果の返送
Copyright © 2017 CO-Sol Inc. All Rights Reserved. 45
まとめ
• SQL処理においてOracleの構成要素がどのように関係するかを理解しましょう
• 索引を適切に使うことで、ファイルI/Oを削減できます
• 適切な実行計画作成にはオプティマイザ統計を適切に取得することが必要です
• SQL処理の性能向上に寄与する各種メモリ領域(データベースバッファキャッシュ、共有プール、PGA)の役割を理解しましょう
• ハードパースを回避するにはSQLのバインド変数化が有効なケースがあります