はじめに < 本 日 のテーマ> 超 高 速 データベース Hitachi Advanced Data Binder での SQLチューニング 方 法 を 情 報 の 取 得 から 問 題 点 を 見 つけて 対 策 するまでの 流 れと チューニング 事 例 をご 紹 介 いたします 本 資 料

db tech showcase 2015 Hitachi Advanced Data Binder 実 践 SQLチューニング 方 法 2015/06/12 株 式 会 社 日 立 製 作 所 情 報 通 信 システム 社 ITプラットフォーム 事 業 本 部 サービスイノベーション 統 括 本 部 IT 基 盤 ソリューション 本 部 DB 部 山 口 健 一

はじめに < 本 日 のテーマ> 超 高 速 データベース Hitachi Advanced Data Binder での SQLチューニング 方 法 を 情 報 の 取 得 から 問 題 点 を 見 つけて 対 策 するまでの 流 れと チューニング 事 例 をご 紹 介 いたします 本 資 料 では Hitachi Advanced Data Binderを HADB と 表 記 します 本 資 料 では Hitachi Advanced Data Binder 03-00を 対 象 としています また 製 品 の 改 良 により 予 告 なく 記 載 されている 仕 様 が 変 更 になることがあります 1

Contents 1. Hitachi Advanced Data Binderの 概 要 2. SQLチューニング 方 法 の 概 要 3. チューニング 事 例 4. おわりに 2

Contents 1. Hitachi Advanced Data Binderの 概 要 2. SQLチューニング 方 法 の 概 要 3. チューニング 事 例 4. おわりに 3

1.1 Hitachi Advanced Data Binderの 概 要 最 先 端 研 究 開 発 支 援 プログラム (*1) において 国 立 大 学 法 人 東 京 大 学 が 推 進 している 超 高 速 データベースエンジンの 研 究 開 発 (*2) の 成 果 を 利 用 して 日 立 が 製 品 化 したリレーショナルデータベースシステム Hitachi Advanced Data Binder プラットフォーム 自 社 従 来 比 100 倍 (*3) の 検 索 性 能 を 誇 る 超 高 速 データベースエンジン Hitachi Advanced Data Binderを 搭 載 可 用 性 の 高 い 日 立 のサーバと 高 速 ストレージをセット 化 Hitachi Advanced Data Binder プラットフォーム 超 高 速 データベースエンジン 日 立 ラックサーバ 日 立 ストレージ (*1) 世 界 のトップを 目 指 した 先 端 的 研 究 を 推 進 することで 産 業 安 全 保 障 等 の 分 野 における 我 が 国 の 中 長 期 的 な 国 際 的 競 争 力 底 力 の 強 化 を 図 るとともに 研 究 開 発 成 果 の 国 民 および 社 会 への 確 かな 還 元 を 図 ることを 目 的 として 創 設 された 国 の 研 究 開 発 プログラム (*2) 内 閣 府 の 最 先 端 研 究 開 発 支 援 プログラム 超 巨 大 データベース 時 代 に 向 けた 最 高 速 データベースエンジンの 開 発 と 当 該 エンジンを 核 とする 戦 略 的 社 会 サービスの 実 証 評 価 ( 中 心 研 究 者 : 喜 連 川 東 大 教 授 / 国 立 情 報 学 研 究 所 所 長 )の 成 果 を 利 用 (*3) 当 社 従 来 製 品 との 比 較 解 析 系 データベースに 関 する 標 準 的 なベンチマークを 元 に 作 成 した 各 種 のデータ 解 析 要 求 の 実 行 性 能 を 計 測 データ 解 析 要 求 の 種 類 によって 高 速 化 率 には 差 が 見 られるが データベースにおいて 特 定 の 条 件 を 満 たす 一 定 量 のデータを 絞 り 込 んで 解 析 を 行 うデータ 解 析 要 求 を 対 象 とした 結 果 4

収 集 / 加 工 1.2 Hitachi Advanced Data Binderプラットフォーム Hitachi Advanced Data Binder PFはDWHの 中 核 を 支 えるDBサーバです 大 量 データのローディング 処 理 を 高 速 化 多 種 多 様 なデータ 結 合 処 理 (JOIN)を 高 速 化 契 約 受 発 注 売 上 SNS センサー 稼 働 ログ 多 種 データ データ ソース 大 量 データ DWH 高 速 データアクセス 基 盤 Hitachi Advanced Data Binder プラットフォーム 超 高 速 データベースエンジン Hitachi Advanced Data Binder (RDBMS) 日 立 サーバ 日 立 ストレージ 高 速 検 索 価 値 を 創 造 BI ツール JDBC/ODBC/CLI (SQLインタフェース) 業 務 アプリケーション 5

1.2 Hitachi Advanced Data Binderの 高 速 化 技 術 サーバ ストレージの 能 力 を 最 大 限 に 使 いきるソフトウェア 技 術 東 京 大 学 との 超 高 速 データベースエンジンの 共 同 研 究 開 発 成 果 の 製 品 化 自 社 従 来 比 約 100 倍 (*1) のデータ 検 索 性 能 DB 検 索 (SQL) 処 理 を 並 列 実 行 単 位 (I/O 単 位 )に 自 動 分 割 し 高 多 重 で 実 行 従 来 方 式 : 順 序 実 行 方 式 顧 客 情 報 注 文 情 報 明 細 履 歴 情 報 従 来 方 式 でのストレージアクセストレース サーバ 検 索 処 理 (μs) ストレージ 同 期 I/O 処 理 (ms) 新 方 式 : 非 順 序 型 実 行 原 理 (*2) 新 方 式 でのストレージアクセストレース サーバ ストレージ 処 理 時 間 を 大 幅 短 縮 タスク 割 当 検 索 処 理 I/O 完 了 待 ち ディスクI/O 内 閣 府 の 最 先 端 研 究 開 発 支 援 プログラム 超 巨 大 データベース 時 代 に 向 けた 最 高 速 データベースエンジンの 開 発 と 当 該 エンジンを 核 とする 戦 略 的 社 会 サービスの 実 証 評 価 ( 中 心 研 究 者 : 国 立 大 学 法 人 東 京 大 学 喜 連 川 教 授 )の 成 果 を 利 用 (*1) 当 社 従 来 製 品 との 比 較 解 析 系 データベースに 関 する 標 準 的 なベンチマークを 元 に 作 成 した 各 種 のデータ 解 析 要 求 の 実 行 性 能 を 計 測 データ 解 析 要 求 の 種 類 によって 高 速 化 率 には 差 が 見 ら れるが データベースにおいて 特 定 の 条 件 を 満 たす 一 定 量 のデータを 絞 り 込 んで 解 析 を 行 うデータ 解 析 要 求 を 対 象 とした 結 果 (*2) 喜 連 川 東 大 教 授 / 国 立 情 報 学 研 究 所 所 長 合 田 東 大 特 任 准 教 授 が 考 案 した 原 理 6

1.2 Hitachi Advanced Data Binderの 高 速 化 技 術 非 順 序 実 行 原 理 では 発 行 したI/Oを 待 たずに 次 々にレコード 処 理 を 行 うた め 並 列 度 を 高 めやすい レコード 処 理 順 序 に 依 存 しない 集 合 演 算 や 結 合 処 理 が 得 意 < 順 序 実 行 > < 非 順 序 実 行 > 7

Contents 1. Hitachi Advanced Data Binderの 概 要 2. SQLチューニング 方 法 の 概 要 3. チューニング 事 例 4. おわりに 8

2.1 SQLチューニングの 前 に 画 面 のレスポンスが 遅 い こんな 時 にどうしますか? BIサーバ 利 用 者 : 画 面 のレスポンスが 遅 いなあ インデクスが 効 いていない? 検 索 量 が 多 すぎる? etc DB 管 理 者 データベース 9

2.1 SQLチューニングの 前 に 問 題 箇 所 の 切 り 分 け 利 用 者 : 画 面 のレスポンスが 遅 いなあ BIサーバ まずは 端 末 BIサーバ DBサーバのどこで 処 理 時 間 が かかっているかを 切 り 分 けます インデクスが 効 いていない? 検 索 量 が 多 すぎる? etc BIサーバ DBサーバのログからSQL 発 行 時 刻 処 理 時 間 要 求 元 へのリタン 時 刻 等 をもとに 時 間 のかかっている 箇 所 を 調 査 します DBサーバで 処 理 時 間 がかかっていることを 確 認 してから チューニングに 着 手 します DB 管 理 者 データベース 10

2.2 SQLチューニングの 流 れ SQLチューニングの 基 本 的 な 流 れ チューニング 対 象 SQLの 特 定 対 象 SQLのアクセスパス( ) 取 得 と アクセスパス 観 点 の 問 題 点 の 調 査 対 象 SQLの 統 計 情 報 の 取 得 と 統 計 情 報 観 点 の 問 題 点 の 調 査 対 策 案 の 検 討 と 検 証 N 要 件 クリア? Y 終 了 SQLの 実 行 計 画 実 行 プランを アクセスパス と 呼 びます 11

2.2 SQLチューニングの 流 れ チューニング 対 象 SQLの 特 定 SQL 処 理 時 間 を 調 査 し 画 面 レスポンスとSQL 処 理 時 間 を 比 較 して レスポンスに 影 響 しているSQLを 特 定 します <SQL 処 理 時 間 の 取 得 方 法 > HADBの 統 計 解 析 コマンド(adbstat)でSQL 文 の 統 計 情 報 を 取 得 します HADBサーバ DB 管 理 者 adbstat -c sql -m ' 開 始 時 刻 ',' 終 了 時 刻 ' > log_adbstat_sql.csv データベース タイムスタンプ AP_name SQL# SQL_type SQL 時 間 [μ 秒 ] フェッチ 行 数 DBエリア 名 バッファ 名 要 求 回 数 ハ ッファヒット 率 read 回 数 write 回 数 2015/06/01 06:35:12 adbsql 1 SELECT 266,948 1 ADBDIC ##ADBOTHER#0000004096 8 100 0 0 2015/06/01 06:35:12 adbsql 1 SELECT ADBUIDX01 ADBUIDX01BUF 120,202 100 0 0 2015/06/01 06:35:25 adbsql 2 SELECT 112,899 1 ADBDIC ##ADBOTHER#0000004096 8 100 0 0 2015/06/01 06:35:25 adbsql 2 SELECT ADBUTBL01 ADBUTBL01BUF 75 100 0 0 2015/06/01 06:37:55 adbsql 3 SELECT 23,822,936 1 ADBDIC ##ADBOTHER#0000004096 16 100 0 0 2015/06/01 06:37:55 adbsql 3 SELECT ADBUIDX01 ADBUIDX01BUF 14,760,202 100 0 0 2015/06/01 06:37:55 adbsql 3 SELECT ADBUTBL01 ADBUTBL01BUF 14,520,000 100 0 0 <ポイント> 1つのSQLが 原 因 のケースや 複 数 のSQLで 少 しずつ 時 間 がかかるケースもあります 12

2.2 SQLチューニングの 流 れ 対 象 SQLのアクセスパスの 取 得 と 調 査 対 象 SQLのアクセスパスを 取 得 して 適 切 なインデクスが 使 われているか といった アクセスパス 観 点 の 問 題 点 を 調 査 します <アクセスパスの 取 得 方 法 > SQL 実 行 コマンド(adbsql)のサブコマンド #set opt report on type=all で 対 象 SQLのアクセスパスを 取 得 します DB 管 理 者 adbsql -u ユーザID -p パスワード < SQL 文 テキスト.txt > log_adbsql.txt HADBサーバ データベース SQL 文 テキスト.txt #set opt report on type=all; select count(*) from T1 where C6='01'; log_adbsql.txtのアクセスパス 部 分 <<Tree View>> 1 QUERY : 1 2 SELECT STATEMENT 3 -KEY SCAN(USER01.T1) 4 +-GROUPING <ポイント> 以 下 のような 点 を 調 査 します 適 切 なインデクスが 使 用 されているか ジョイン 方 式 が 適 切 か 繰 り 返 し 実 行 される 重 たい 処 理 がないか <<Detail >> QUERY : 1 3 KEY SCAN(USER01.T1) INDEX NAME : T1_IDX03 INDEX TYPE : B-TREE INDEX COLUMN : C6 ASC (=) INDEX COLUMN : C5 ASC (none) 13

2.2 SQLチューニングの 流 れ 対 象 SQLの 統 計 情 報 の 取 得 と 調 査 対 象 SQLの 統 計 情 報 を 取 得 して バッファへのアクセス 要 求 回 数 や I/O 回 数 といった 統 計 情 報 観 点 の 問 題 点 を 調 査 します < 統 計 情 報 の 取 得 方 法 (SQL 処 理 時 間 の 取 得 と 同 じ)> HADBの 統 計 解 析 コマンド(adbstat)でSQL 文 の 統 計 情 報 を 取 得 します HADBサーバ DB 管 理 者 adbstat -c sql -m ' 開 始 時 刻 ',' 終 了 時 刻 ' > log_adbstat_sql.csv データベース タイムスタンプ AP_name SQL# SQL_type SQL 時 間 [μ 秒 ] フェッチ 行 数 DBエリア 名 バッファ 名 要 求 回 数 ハ ッファヒット 率 read 回 数 write 回 数 2015/06/01 06:40:29 adbsql 4 SELECT 266,948 1 ADBDIC ##ADBOTHER#0000004096 8 100 0 0 2015/06/01 06:40:29 adbsql 4 SELECT ADBUIDX01 ADBUIDX01BUF 120,202 100 0 0 2015/06/01 06:41:07 adbsql 5 SELECT 112,899 1 ADBDIC ##ADBOTHER#0000004096 8 100 0 0 2015/06/01 06:41:07 adbsql 5 SELECT ADBUTBL01 ADBUTBL01BUF 75 100 0 0 2015/06/01 06:42:31 adbsql 6 SELECT 23,822,936 1 ADBDIC ##ADBOTHER#0000004096 16 100 0 0 2015/06/01 06:42:31 adbsql 6 SELECT ADBUIDX01 ADBUIDX01BUF 14,760,202 100 0 0 2015/06/01 06:42:31 adbsql 6 SELECT ADBUTBL01 ADBUTBL01BUF 14,520,000 100 0 0 <ポイント> 以 下 のような 点 を 調 査 します 想 定 するDBへのアクセス 量 と 比 べて バッファアクセス 回 数 が 多 くないか バッファヒット 率 が 著 しく 低 くないか(I/O 回 数 が 極 端 に 多 くなっていないか) 14

2.2 SQLチューニングの 流 れ 対 策 案 の 検 討 と 検 証 見 つけた 問 題 点 の 対 策 案 を 検 討 し 効 果 を 検 証 します < 対 策 案 の 検 討 > 問 題 点 によって 対 策 方 法 は 様 々ですが 例 えば 以 下 のような 方 法 があります パラメタ 設 定 の 変 更 バッファ 面 数 の 割 当 の 変 更 拡 張 1SQLを 処 理 する 多 重 度 の 拡 張 定 義 の 変 更 インデクスの 構 成 列 の 追 加 並 び 順 の 変 更 インデクスの 追 加 SQL 文 の 書 換 え ジョインする 順 番 の 変 更 ジョイン 方 式 の 変 更 副 問 合 せの 書 換 え(ジョイン 化 ) グループ 化 処 理 のタイミングの 変 更 対 策 したSQLを 実 行 して 再 度 統 計 情 報 を 取 得 変 更 前 と 比 較 して 対 策 の 効 果 を 検 証 します 15

Contents 1. Hitachi Advanced Data Binderの 概 要 2. SQLチューニング 方 法 の 概 要 3. チューニング 事 例 4. おわりに 16

3.1 事 例 1 -グループ 化 処 理 のタイミングー 4 月 分 の 売 上 集 計 するSQLで 名 称 を 付 加 するためにマスタ 表 をジョインして いるが 処 理 時 間 がかかっている 改 善 するポイントがありますか? 1 対 1ジョインのはずなのに ずいぶん 時 間 がかかるなあ? select U. 大 分 類, U. 商 品 コード, SUM(U. 金 額 ), max(s. 商 品 名 ) from 売 上 TBL U LEFT JOIN 商 品 TBL S on U. 大 分 類 =S. 大 分 類 and U. 商 品 コード=S. 商 品 コード where U. 日 付 between '2014/04/01' and '2014/04/30' and U. 大 分 類 in ('01', '02', '03', '04) group by U. 大 分 類, U. 商 品 コード ; 17

3.1 事 例 1 -グループ 化 処 理 のタイミングー <ポイント1> 検 索 の 対 象 行 数 がどのくらいあるか ざっくりと 求 めて 統 計 情 報 の DBアクセス 量 (バッファ 要 求 回 数 )と 比 べてみましょう SQL# SQL_type SQL 時 間 [μ 秒 ] フェッチ 行 数 DBエリア 名 バッファ 名 要 求 回 数 ハ ッファヒット 率 read 回 数 write 回 数 1 SELECT 20,398,931 39,996 ADBDIC ##ADBOTHER#0000004096 14 100 0 0 1 SELECT ADBUIDX01 ADBUIDX01BUF 16,450,491 100 0 0 1 SELECT ADBUTBL01 ADBUTBL01BUF 8,219,200 100 0 0 2 SELECT 7,355,184 39,996 ADBDIC ##ADBOTHER#0000004096 14 100 0 0 2 SELECT ADBUIDX01 ADBUIDX01BUF 4,241,679 100 0 0 4 月 分 の 売 上 データは410 万 件 あります 2 SELECT ADBUTBL01 ADBUTBL01BUF 4,149,596 100 0 0 それに 対 して 統 計 情 報 のインデクス 要 求 回 数 は1645 万 回 約 4 倍 です ネストジョインの 内 側 である 商 品 TBLを 検 索 する 際 インデクス 段 数 が 3 段 として 売 上 データ1 件 当 たり 商 品 TBLのインデクスを3 回 参 照 売 上 テ ータ001 売 上 テ ータ002 売 上 テ ータ003 売 上 TBL 商 品 001 商 品 002 商 品 003 商 品 TBL 商 品 INDEX (3 段 ) 410 万 件 +410 万 件 3 段 1600 万 回 売 上 TBL 商 品 TBL 18

3.1 事 例 1 -グループ 化 処 理 のタイミングー < 改 善 策 > 集 計 前 の 売 上 データには 商 品 コードが 重 複 するので 集 計 後 に 商 品 TBLを ジョインするように 変 更 します(グループ 化 処 理 を 先 に 実 施 ) select U. 大 分 類, U. 商 品 コード, U. 金 額, S. 商 品 名 from (select 大 分 類, 商 品 コード, SUM( 金 額 ) from 売 上 TBL where 日 付 between '2014/04/01' and '2014/04/30' and 大 分 類 in ('01', '02', '03', '04') group by 大 分 類, 商 品 コード ) U left join 商 品 TBL S on U. 大 分 類 =S. 大 分 類 and U. 商 品 コード=S. 商 品 コード SQL# SQL_type SQL 時 間 [μ 秒 ] フェッチ 行 数 DBエリア 名 バッファ 名 要 求 回 数 ハ ッファヒット 率 read 回 数 write 回 数 1 SELECT 20,398,931 39,996 ADBDIC ##ADBOTHER#0000004096 14 100 0書 換 えたSQL 0 1 SELECT ADBUIDX01 ADBUIDX01BUF 16,450,491 100 0 の 統 計 情 0 報 1 SELECT ADBUTBL01 ADBUTBL01BUF 8,219,200 100 0 0 2 SELECT 7,355,184 39,996 ADBDIC ##ADBOTHER#0000004096 14 100 0 0 2 SELECT ADBUIDX01 ADBUIDX01BUF 4,241,679 100 0 0 2 SELECT ADBUTBL01 ADBUTBL01BUF 4,149,596 100 0 0 本 改 善 でインデクスへの 要 求 回 数 が1645 万 回 424 万 回 に 削 減 できました 4 月 分 の 売 上 データは410 万 件 で 集 計 結 果 は4 万 件 になるため 410 万 件 +4 万 件 3 段 =420 万 回 売 上 TBL 商 品 TBL 19

3.2 事 例 2 -ジョインの 順 序 ー <ポイント2> 事 例 1の 改 善 策 として ジョイン 順 序 を 変 更 する 方 法 もあります 事 例 1は 売 上 TBLを 起 点 にしていましたが 商 品 TBLの 方 が 件 数 が 少 ない ため 商 品 TBLを 起 点 としたジョインに 変 更 します select U. 大 分 類, U. 商 品 コード, U. 金 額, S. 商 品 名 from 商 品 TBL S INNER JOIN 売 上 TBL U on U. 大 分 類 =S. 大 分 類 and U. 商 品 コード=S. 商 品 コード where U. 日 付 between '2014/04/01' and '2014/04/30' and S. 大 分 類 in ('01', '02', '03', '04') group by U. 大 分 類, U. 商 品 コード SQL# SQL_type SQL 時 間 [μ 秒 ] フェッチ 行 数 DBエリア 名 バッファ 名 要 求 回 数 ハ ッファヒット 率 read 回 数 write 回 数 1 SELECT 20,398,931 39,996 ADBDIC ##ADBOTHER#0000004096 14 100 0書 換 えたSQL 0 1 SELECT ADBUIDX01 ADBUIDX01BUF 16,450,491 100 0 の 統 計 情 0 報 1 SELECT ADBUTBL01 ADBUTBL01BUF 8,219,200 100 0 0 2 SELECT 8,823,041 39,996 ADBDIC ##ADBOTHER#0000004096 14 100 0 0 2 SELECT ADBUIDX01 ADBUIDX01BUF 4,332,134 100 0 0 2 SELECT ADBUTBL01 ADBUTBL01BUF 4,149,596 100 0 0 本 改 善 でインデクスへの 要 求 回 数 が1645 万 回 433 万 回 に 削 減 できました 4 月 分 の 売 上 データは 商 品 コード 当 たり 平 均 103 件 あるため 4 万 件 + 4 万 件 (103 件 +4 段 )=432 万 回 商 品 TBL 売 上 TBL 20

3.3 事 例 3 -ジョイン 方 式 の 変 更 ー 商 品 TBLと 売 上 TBLの 突 き 合 わせをしたいが 両 方 とも 件 数 が 多 くて 処 理 時 間 がかかってしまう 改 善 するポイントがありますか? 商 品 001 商 品 002 商 品 003 商 品 004 商 品 005 商 品 TBL 売 上 テ ータ001 売 上 テ ータ002 売 上 テ ータ003 売 上 テ ータ004 売 上 テ ータ005 売 上 TBL 適 切 なインデクスを 使 っているけど ジョインがなんだか 遅 いなあ? 21

3.3 事 例 3 -ジョイン 方 式 の 変 更 ー <ポイント3> 大 量 データを 対 象 とする 場 合 内 側 表 外 側 表 の 件 数 に 応 じて 繰 り 返 し 処 理 の 回 数 が 増 えるネストジョイン 方 式 よりも 両 表 を1 回 ずつスキャン するハッシュジョイン 方 式 が 優 位 となる 場 合 があります <ネストジョイン 方 式 > <ハッシュジョイン 方 式 > 商 品 001 売 上 テ ータ001 商 品 001 売 上 テ ータ001 商 品 002 売 上 テ ータ002 商 品 002 売 上 テ ータ002 商 品 003 売 上 テ ータ003 商 品 003 売 上 テ ータ003 商 品 004 売 上 テ ータ004 商 品 004 売 上 テ ータ004 商 品 005 売 上 テ ータ005 商 品 005 ハッシュテーブル 売 上 テ ータ005 商 品 TBL 売 上 TBL 商 品 TBL 売 上 TBL 内 側 表 外 側 表 の 件 数 に 応 じて 結 合 回 数 が 増 加 商 品 TBLを1 回 スキャン してハッシュテーフ ルに 登 録 売 上 TBLを1 回 スキャン してハッシュテーフ ルで 突 き 合 わせ 22

3.4 事 例 4 - 演 算 を 含 むIN( 副 問 合 せ)の 書 換 えー あるメーカーの 商 品 の4 月 1 日 分 の 売 上 集 計 をしたいが IN 副 問 合 せを 使 うと 処 理 時 間 がかかってしまう 改 善 ポイントはありますか? IN( 副 問 合 せ)を 使 うと なんか 遅 い 気 がするなあ? select 大 分 類, 商 品 コード, SUM( 金 額 ) from 売 上 TBL where 日 付 between '2014/04/01' and '2014/04/30' and 大 分 類 商 品 コード in (select 大 分 類 商 品 コード from 商 品 TBL where メーカーコード='000456' ) group by 大 分 類, 商 品 コード 23

3.4 事 例 4 - 演 算 を 含 むIN( 副 問 合 せ)の 書 換 えー <ポイント4> 演 算 を 含 むIN( 副 問 合 せ)はインデクスで 評 価 できずに 思 わぬ 処 理 時 間 がかかってしまうことがあります SQL# SQL_type SQL 時 間 [μ 秒 ] フェッチ 行 数 DBエリア 名 バッファ 名 要 求 回 数 ハ ッファヒット 率 read 回 数 write 回 数 1 SELECT 58,134,960 400 ADBDIC ##ADBOTHER#0000004096 14 100 0 0 1 SELECT ADBWRK ADBWRK 54,657,604 100 0 0 1 SELECT ADBUIDX01 ADBUIDX01BUF 137,394 100 0 0 1 SELECT ADBUTBL01 ADBUTBL01BUF 137,185 100 1 0 2 SELECT 1,417,099 400 ADBDIC ##ADBOTHER#0000004096 14 100 0 0 2 SELECT ADBUIDX01 ADBUIDX01BUF 548,355 100 0 0 演 算 を 含 むIN( 副 問 合 せ)は 副 問 合 せの 結 果 を 作 業 表 に 格 納 して 2 SELECT ADBUTBL01 ADBUTBL01BUF 273,974 100 0 0 主 問 合 せの1 件 ごとに 作 業 表 と 突 き 合 わせて 評 価 します 副 問 合 せ 結 果 (あるメーカの 商 品 数 )は400 件 あり 4/1の 売 上 データは 136000 件 あります 400 件 136000 件 =5400 万 回 の 突 き 合 わせが 行 われます 統 計 情 報 からも 作 業 表 のバッファに5465 万 回 の 要 求 回 数 をだしており この 突 き 合 わせに 時 間 がかかっていることがわかります 24

3.4 事 例 4 - 演 算 を 含 むIN( 副 問 合 せ)の 書 換 えー < 改 善 策 > 演 算 を 含 むIN( 副 問 合 せ)は 外 への 参 照 を 使 ったEXISTS 述 語 で 書 き 換 えると 効 率 的 に 検 索 できるケースが 多 いです select 大 分 類, 商 品 コード, SUM( 金 額 ) from 売 上 TBL U where 日 付 = '2014/04/01' and EXISTS( select * from 商 品 TBL where メーカーコード='000456' and 大 分 類 =U. 大 分 類 and 商 品 コード=U. 商 品 コード ) group by 大 分 類, 商 品 コード SQL# SQL_type SQL 時 間 [μ 秒 ] フェッチ 行 数 DBエリア 名 バッファ 名 要 求 回 数 ハ ッファヒット 率 read 回 数 write 回 数 1 SELECT 58,134,960 400 ADBDIC ##ADBOTHER#0000004096 14 100 0 0 1 SELECT ADBWRK ADBWRK 54,657,604 100 書 換 0 えたSQL 0 1 SELECT ADBUIDX01 ADBUIDX01BUF 137,394 100 の 統 0 計 情 報 0 1 SELECT ADBUTBL01 ADBUTBL01BUF 137,185 100 1 0 2 SELECT 1,417,099 400 ADBDIC ##ADBOTHER#0000004096 14 100 0 0 2 SELECT ADBUIDX01 ADBUIDX01BUF 548,355 100 0 0 2 SELECT ADBUTBL01 ADBUTBL01BUF 273,974 100 0 0 本 改 善 で 作 業 表 (ADBWRK)へのアクセスそのものがなくなり 5467 万 回 の 突 き 合 わせ 処 理 が 削 減 できました その 分 は 外 への 参 照 の 部 分 で インデクスへのアクセスが 増 加 する 形 になります 25

3.5 事 例 5 -テーブルスキャンの 活 用 ー B-Treeインデクスはちゃんと 使 っていて 絞 り 込 みも 期 待 できるはずだけど なんとなく 遅 い 気 がします 改 善 ポイントはありますか? インデクスはちゃんと 使 っているんだけどなあ? 26

3.5 事 例 5 -テーブルスキャンの 活 用 ー <ポイント5> ビッグデータの 場 合 B-Treeインデクスを 適 切 に 使 用 して 条 件 も 絞 り 込 める ( 母 体 全 体 に 対 する 比 率 として) 場 合 でも 件 数 そのものが 膨 大 なため インデクス 経 由 のランダムI/Oよりも テーブルスキャンが 優 位 な 場 合 が あります <インデクス 経 由 の 検 索 > <テーブルスキャン> SQL 検 索 SQL 検 索 B-treeインデクス B-treeインデクス で 絞 り 込 み ヒント 句 でテーブル スキャン 指 定 ランダムI/O データ 部 データ 部 売 上 TBL 売 上 TBL 27

Contents 1. Hitachi Advanced Data Binderの 概 要 2. SQLチューニング 方 法 の 概 要 3. チューニング 事 例 4. おわりに 28

4.おわりに 1. 超 高 速 データベースエンジンとは Hitachi Advanced Data Binderプラットフォームと 高 速 化 の 技 術 について 概 要 をご 説 明 しました 2.SQLチューニング 方 法 の 概 要 SQLチューニング 方 法 を 問 題 のSQLの 特 定 から 問 題 点 の 調 査 対 策 案 の 効 果 の 検 証 までをご 説 明 しました 3.チューニング 事 例 実 際 に 現 場 で 適 用 した 際 のチューニング 事 例 をいくつかご 紹 介 統 計 情 報 の 結 果 も 併 せて 定 量 的 に 効 果 を 検 証 しました 29

END Hitachi Advanced Data Binder 実 践 SQLチューニング 方 法 2015/06/12 株 式 会 社 日 立 製 作 所 情 報 通 信 システム 社 ITプラットフォーム 事 業 本 部 サービスイノベーション 統 括 本 部 IT 基 盤 ソリューション 本 部 DB 部 山 口 健 一 30