株式会社コーソル

KNOWLEDGE

コーソルの技術情報

コーソルでは経験豊かなエンジニアが、Oracle Databaseに関するお役立ち情報を発信しています。
データベースのチューニングや設定にお役立ていただけます。

【Oracle版】SQLの索引を最適化するOptimize Indexes利用手順

■概要
 Optimize Indexesでは、実行SQL文の索引最適化を図ることができます。
 Optimize IndexesをSQL文に対して実行すると、SQL文実行時の実行コスト、IOコスト、CPUコストを
 低減できる索引作成パターンがあるか否かを調べることができます。
 ToadにはSQL文を最適化するSQL Optimizerという機能もありますが、次のような順序で使うと効果的です。
  ① Optimize IndexesでSQL文の索引最適化実施
  ② SQL OptimizerでSQL自体の最適化実施

■利用可能エディション
 Toad for Oracle Xpert Edition
 Toad for Oracle Developer Edition
 Toad for Oracle DBA Edition
 Toad for Oracle DBA RAC Edition
 Toad for Oracle DBA Exadata Edition

■利用手順
 1) スタートメニューの検索バーに『Quest SQL Optimizer for Oracle』と入力し、
   Quest SQL Optimizer for Oracleを起動する。

図1:スタートメニューからの起動

 2) SQL Optimizer for Oracleが起動したら、『Optimize Indexes』をクリックする。

図2:SQL Optimizer起動画面

 3) Optimize Indexes画面が表示されたら、『Define New SQL Workload』をクリックする。

図3:Optimize Indexesトップ画面

 4) 分析対象SQLの取得先選択画面が表示されたら、取得したい先のソースを選択する。
   ここでは『Scan Code』を選択する。
     - AWR    :AWRからSQLを取込み
     - SGA     :SGAからSQLを取込み
     - Scan Code  :ファイルやクリップボードからSQLを取込み

図4:SQL取込み先選択画面

 5) Scan Code画面が表示されたら、該当DBへの接続情報を選択後、SQL取込み手段を選択する。
   ここではScan from Clipboardを選択する。
     - Scan from Clipboard  :Ctrl + CでコピーしたSQLを取り込む
     - Scan from File     :ファイルからSQLを取り込む
     - Scan from DB Object  :DBオブジェクトからSQLを取り込む

図5:SQL取込み手段選択画面

 6) ClipboardにコピーされたSQLが表示されたら、『OK』をクリックする。
   Scan Code画面に戻ったら、『Configure Search Process』をクリックする。

図6:Clipboard内SQL選択画面
図7:SQL取込み手段選択画面

 7) 実行プランの取得と代替インデックスの評価に使用するデータベース接続を選択する。
   選択後、『OK』をクリックする。

図8:接続先と接続ユーザ選択画面

 8) Search Process画面では、Stop Condition(停止条件)を任意で変更する。
   ここでは、improvement reachedを50%、after searchingを10分として停止条件を設定する。
     When XX% improvement reached      :指定した改善率を達した時
     When no further improvement found in XX  :指定時間検索し、それ以上の改善がみられない時
     After searching for XX            :指定時間検索をした後
   選択後、『Start』をクリックする。

図9:停止条件設定画面

 9) Search Progressの左下に棒グラフが表示されたら、Search Process完了。
   『Results』タブをクリックして結果を確認する。

図10:実行完了画面

 10) Results画面では、索引作成に関する情報が表示される。
   左上赤枠内の索引名をクリックすると、索引作成によって得られるコスト削減効果、作成SQL文が確認できる。
   表示された索引を実装したい場合、右下赤枠の『Create/Edit Index Generation Script』をクリックする。

図11:Results画面

 11) Create/Edit Index Generation Script画面では、表示された索引作成SQLの確認、編集が行える。
   索引名の編集は一番上の赤枠、作成する表領域、スキーマの編集は二番目の赤枠で行う。
   編集が完了したら、右下の『Copy』をクリックし、編集した索引作成SQLをコピーする。
   コピーしたSQL文はToad画面、もしくはテキスト等に貼り付け、該当環境で索引作成を実施する。

図12:索引作成SQL確認・編集画面

以上でOptimize Indexesが完了です。
索引チューニングをすることでSQL文の性能が格段に向上します。

本手順では下記SQL、テーブルデータを利用しています。

実行SQL
 SELECT A.col1, MAX(A.col1), MAX(G.col1), MAX(G.col1)
  FROM A_TABLE A
   INNER JOIN F_TABLE F
    ON A.col1 = F.col1
   INNER JOIN G_TABLE G
    ON A.col1 = G.col1
     AND (G.col1 = 1
     OR G.col1 IS NULL)
   INNER JOIN I_TABLE I
    ON A.col1 = I.col1
     AND (I.col1 = 1)
   INNER JOIN J_TABLE J
    ON A.col1 = J.col1
     AND (J.col1 > 0
     AND J.col1 < 2)
    INNER JOIN H_TABLE H
     ON A.col1 = H.col1
 WHERE A.col1 = 1
 GROUP BY A.col1;

テーブル件数
 A_TABLE  レコード件数:8,189,999
 F_TABLE  レコード件数:300,000
 F_TABLE  レコード件数:300,000
 G_TABLE  レコード件数:300,000
 H_TABLE  レコード件数:300,000
 I_TABLE  レコード件数:300,000
 J_TABLE  レコード件数:300,000

Optimize Indexes利用手順は以上です。

SQL Optimizerを利用される場合、次のTipsもございます。
 - SQLを自動チューニングできる簡易SQL Optimizer利用手順
 - SQLを自動チューニングできるSQL Optimizer利用手順
 - 複雑なSQL文をシンプルに書き換えるRefactor利用手順

Toadは30日間、フル機能で無償トライアルも可能です。
Toadに少しでも興味を持っていただけたなら、是非次のページもご参照ください。
 ・トライアルのご依頼はこちら
 ・Spotligh概要ページはこちら
 ・Spotlight on Oracle機能紹介ページはこちら
 ・Spotlight on SQL Server機能紹介ページはこちら
 ・Toad概要ページはこちら
 ・Toad for Oracle機能紹介ページはこちら
 ・Toad for SQL Server機能紹介ページはこちら
 ・Toadの製品紹介資料ダウンロードはこちら