SQL Developerのチューニング・ユーティリティの使用

概要

    目的

    このチュートリアルでは、SQL Developerでチューニング・ユーティリティを使用する方法について説明します。

    所要時間

    約40分

    はじめに

    このチュートリアルで実施する内容は、次のとおりです。

    • SQL Developerを使用した次の作業
      • SQL問合せに対する実行計画の生成と表示
      •  自動トレース計画の実行
    • 複合索引での列順序の変更による影響の調査
    • SQL計画ベースラインを使用した、提示された実行計画とハードコードされたSQL文の関連付け

    ハードウェアとソフトウェアの要件（オプション）

    ハードウェアとソフトウェアの要件リストは、以下のとおりです。

    • Oracle Database 11g Enterprise Edition（Oracle Tuning Management PackおよびOracle Diagnostic Management Packを使用できること、サンプル・スキーマがインストールされていること）
    • Oracle SQL Developer 3.2

    前提条件

    • Oracle SQL Developer 3.2をこちらからインストールしてください。

    注： このチュートリアルを最適な状態で表示するには、ブラウザとしてFirefoxまたはChromeを使用してください。

データベース接続の作成

    オプティマイザのアクセス・パスを使用するための最初のステップは、データベース接続の作成です。
    次の手順に従って、データベース接続を作成します。

    デスクトップ上のSQL Developer 3.2アイコンをクリックして、SQL Developerを起動します。 Oracle SQL Developer 3.2が起動します。



    接続ナビゲータで、「Connections」を右クリックし、「New Connection」を選択します。
    New / Select Database Connectionダイアログ・ボックスが開きます。 以下の接続情報を入力し、「Save Password」を選択して「Test」をクリックします。

    Connection Name： hr_conn
    Username： hr
    Password： ＜使用するパスワード＞（「Save Password」を選択）
    Hostname： localhost
    SID： ＜使用するSID＞ 


    左下（Helpボタンの上）で接続のステータスを確認します。 Successと表示されているはずです。 「Connect」をクリックします。



 SQL Developerを使用した、SQL問合せに対する実行計画の生成と表示

    SQL文セクションの実行ステップを表示するには、以下の手順を実行します。

    「SQL Worksheet」をクリックし、「hr」ユーザーを選択します。



    新しいSQLワークシートが開きます。



    次の問合せを実行して、表empを作成します。

    CREATE TABLE emp (
       emp_id varchar2(5) CONSTRAINT emp_id_pk PRIMARY KEY,
       emp_fn varchar2(20),
       emp_ln  varchar2(20),
       dept_id varchar2(5),
       hire_date DATE CONSTRAINT "EMP_HIREDATE_NN" NOT NULL ENABLE,
       salary NUMBER(8,2)
    );

    注： emp表がすでに存在する場合は、いったん削除してから再作成します。



    emp表にレコードを挿入します。

    INSERT INTO emp       
    SELECT employee_id, first_name, last_name, department_id, hire_date, salary from employees;
    commit;


    次の文を実行して、emp表の統計情報を収集します。

    exec DBMS_STATS.GATHER_TABLE_STATS ('HR','EMP', METHOD_OPT => 'for all indexed columns', CASCADE => TRUE);


    EXPLAIN PLANコマンドを使用して実行計画を分析します。

    次に示すコードを実行して、SQL問合せの実行計画を分析します。

    EXPLAIN PLAN FOR
    SELECT * FROM hr.emp WHERE emp_id = :empid;
    SELECT * FROM TABLE (dbms_xplan.display); 

    ここではemp_id_pkが使用されていることが分かります。


    自動トレース・オプションを使用して、次のSQL問合せのパフォーマンス特性を確認します。

    SELECT * FROM hr.emp WHERE emp_id = :empid;



複合索引での列順序の変更による影響の調査

    ここでは、いくつかのSQL文を実行し、複合索引における列順序の変更による影響を確認します。

    次の索引を作成します。

    CREATE INDEX emp_fname_sal_idx
    ON emp (emp_fn,salary)
    NOLOGGING
    COMPUTE STATISTICS;

    CREATE INDEX emp_sal_fname_lname_idx
    ON emp (salary,emp_fn,emp_ln)
    NOLOGGING
    COMPUTE STATISTICS;



    次のコードを実行し、EXPLAIN PLAN コマンドを使用してSQL文の実行計画を収集します。  

    EXPLAIN PLAN FOR SELECT emp_fn, salary, hire_date
    FROM emp
    WHERE     emp_fn     like 'S%'
    AND salary > 1000
    /
    SELECT * FROM TABLE (dbms_xplan.display);


    実行計画から次の要素を見つけます。

    • どの索引が使用されたか
    • 総コストはいくらか

    次に、以下のコードを実行し、EXPLAIN PLAN コマンドを使用してSQL文の実行計画を収集します。

    EXPLAIN PLAN FOR SELECT emp_fn, emp_ln, salary
    FROM emp
    WHERE emp_ln like 'S%'
    AND salary > 1000
    /
    SELECT * FROM TABLE (dbms_xplan.display);


    実行計画から次の要素を見つけます。

    • どの索引が使用されたか
    • 総コストはいくらか


SQL計画ベースラインを使用した、提示された実行計画とハードコードされたSQL文の関連付け

    ここでは、SQL計画ベースラインを使用して、提示された実行計画にハードコードされたSQL文を関連付けます。

    接続ナビゲータで、「Connections」を右クリックし、「New Connection」を選択します。
    New / Select Database Connectionダイアログ・ボックスが開きます。 以下の接続情報を入力し、「Test」をクリックします。

    Connection Name： sh_conn
    Username： sh
    Password： ＜使用するパスワード＞（「Save Password」を選択）
    Hostname： localhost
    SID： ＜使用するSID＞ 


    左下（Helpボタンの上）で接続のステータスを確認します。 Successと表示されているはずです。 「Save」をクリックしてから、「Connect」をクリックします。


    「SQL Worksheet」をクリックし、「sh」ユーザーを選択します。



    新しいSQLワークシートが開きます。



    主キー索引と一意キー索引（*_PK, *_UK）を除く以下の索引のすべてをcustomers表から削除します。

    DROP INDEX customers_gender_bix ;
    DROP INDEX customers_yob_bix;
    DROP INDEX cust_cust_credit_limit_idx;

    次の索引を作成します。

    CREATE BITMAP INDEX cust_yob_bix
    ON CUSTOMERS(cust_year_of_birth)
    NOLOGGING
    COMPUTE STATISTICS;

    CREATE BITMAP INDEX cust_gender_bix
    ON CUSTOMERS(cust_gender)
    NOLOGGING
    COMPUTE STATISTICS;




    次に示すコードを実行して、customers表の統計情報を収集します。  

    exec DBMS_STATS.GATHER_TABLE_STATS ('SH','CUSTOMERS');



    次のSQL文を実行します。 

    SELECT /*+ INDEX_COMBINE (customers cust_gender_bix, cust_yob_bix) */ *
    FROM customers
    WHERE cust_year_of_birth < 70
    AND cust_gender = 'M';
    注： このハードコードされたSQL文が特定されたものとします。 このSQL文はチューニングの必要がありますが、パフォーマンスを向上するために利用できるオプションは数種類しかありません。
    この問合せは意図的に値を返していません。 どの計画が使用できるかを確認しているだけです。 最初の問合せでは、元の計画ではなく提示された実行計画を使用します。     

    v$sqlareaを使用して、sql_idとplan_hash_valueをチェックします。
    select sql_id, plan_hash_value, sql_text
    from v$sqlarea
    where sql_text like '%SELECT /*+ INDEX_COMBINE(customers cust_gender_bix, cust_yob_bix)%';


    Note: ステップ7からsql_textの値をコピーします（'%SELECT /*+ INDEX_COMBINE(customers cust_gender_bix, cust_yob_bix)%'）。
    両方のテキストが一致しない場合、期待される結果は得られません。 sql_idの値が異なる可能性があります。



    次のコードを実行し、SQL文の実行計画を確認します。 前のステップで取得したsql_idをコピーします。

    注： 次のテキストのsql_idをコピーします。

    SELECT /*+ INDEX_COMBINE (customers cust_gender_bix, cust_yob_bix) */ * FROM customers where cust_year_of_birth < 70 AND cust_gender = 'M';

    SELECT * FROM table (dbms_xplan.display_cursor ('Please Enter the SQL_ID value'));



    Note: BITMAP MERGE処理が使用されており、 総コストは4になっています。

    SYSTEMユーザーを使用して次の問合せを実行し、DBMS_SPMパッケージへのアクセス権限を付与します。

    GRANT ADMINISTER SQL MANAGEMENT OBJECT TO sh;

    SQL文の実行計画をSQL計画ベースラインにロードします。

    var res number;
    exec :res := dbms_spm.load_plans_from_cursor_cache (sql_id => 'Please enter the original SQL_ID value', plan_hash_value => 'Please enter the &original_plan_hash_value');

    Note: ステップ9で取得したsql_idとplan_hash_valueを入力します。

    次のコードを使用して、SQL計画ベースラインにロードされた計画を確認します。

    select sql_handle, plan_name, sql_text, enabled, accepted
    from dba_sql_plan_baselines
    where sql_text like '%SELECT /*+ INDEX_COMBINE (customers cust_gender_bix, cust_yob_bix)%';



    次の提示されたSQL文を実行し、sql_idとplan_hash_valueを確認します。

    SELECT /*+ INDEX (customers cust_yob_bix)*/ *
    FROM customers
    WHERE cust_year_of_birth < 70
    AND cust_gender = 'M';

    select sql_id, plan_hash_value, sql_text
    from v$sqlarea
    where sql_text like 'SELECT /*+ INDEX (customers cust_yob_bix)%';

    ここでは、より適切な実行計画をSQLベースラインに関連付けています。


    次のコードを実行し、SQL文の実行計画を確認します。 前のステップで取得したsql_idをコピーします。

    注： 次のテキストのsql_id をコピーします。

    SELECT /*+ INDEX_COMBINE (customers cust_yob_bix) */ * FROM customers where cust_year_of_birth < 70 AND cust_gender = 'M';

     SELECT * FROM table (dbms_xplan.display_cursor ('Please Enter the SQL_ID value'));


    注： BITMAP MERGE処理が使用されており、 総コストは2になっています。


まとめ


    このチュートリアルで学習した内容は、次のとおりです。

    • SQL Developerを使用した次の作業
      • SQL問合せに対する実行計画の生成と表示
      •  自動トレース・オプションの使用
    • 複合索引での列順序の変更による影響の調査
    • SQL計画ベースラインを使用した、提示された実行計画とハードコードされたSQL文の関連付け

    著者

    ここに著者を記入

    • カリキュラム開発責任者： Dimpi Sarmah、Sharon Sophia Stephen
    • 共著者： Ashley Chen、Swarnapriya Shridhar、Nancy Greenberg

このOracle by Exampleをナビゲートする際、以下の機能を使用できます。

ヘッダー・ボタンの非表示：
ヘッダー内のボタンを非表示にするには、タイトルをクリックします。 ボタンを再表示するには、もう一度タイトルをクリックします。
トピック一覧ボタン：
すべてのトピックの一覧です。 いずれかのトピックをクリックすると、その項に移動します。
すべてのトピックを開く/閉じる：
すべての項に対する詳細を表示または非表示にします。 デフォルトでは、すべてのトピックが閉じられています。
すべてのイメージを表示/非表示：
すべてのスクリーンショットを表示または非表示にします。 デフォルトでは、すべてのイメージが表示されています。
印刷：
コンテンツを印刷します。 現在表示または非表示にされているコンテンツが印刷されます。

このチュートリアルの特定の項に移動するには、一覧からトピックを選択してください。