oracle 11 gにおけるSQL最適化(SQL TUNINNG)新特性のSQL Plan Management(SPM)
1.概要
Oracle Database 11 gR 1には、DBAが任意のSQL文の実行計画を最適化するための新しいツールとして、SQL PlanManagement(SPM)が導入されています.これにより、オプティマイザ統計のリフレッシュ、アプリケーションの変更、データベース・バージョンのアップグレードによる影響が制限されます.この文書では、SPMの原理を基本的に理解し、パフォーマンスの最適化能力について簡単に説明します.
2.SPMの原理とメカニズム
Oracle 11 gは、SQL計画の予期せぬ悪化を解決するSQL Plan Management(SPM)と呼ばれる新しい特徴を、簡単で優雅な方法で実施しています.ユーザーセッションが自動SQL Plan Baselineキャプチャをオンにすると、CBOはSQL Management Base(SMB)にそのセッション内で実行されたSQLを記録し、SQL文テキスト、アウトライン(Outline)、バインド変数、およびそのコンパイル環境などをSQL Plan Baselineとして格納します.
文が初めて実行されるため、Oracle 11 gは当時の実行計画を最適化します.同じSQL文が2回目に実行されたときこそ、SPMの優雅さがより顕著に現れます.文の2回目の実行期間中、CBOは文の実行計画とSMBに格納された計画を比較し、新しい計画がSMBの計画よりも効率的であるかどうかを評価します.
新しい計画が文のパフォーマンスを向上させる場合、SPMは新しい計画を文の最良の計画としてマークします.DBAがOPTIMIZERを修正していない限りUSE_SQL_PLAN_BASELINESパラメータのデフォルト設定(true)では、CBOは現在の文実行に新しい計画を採用します.そうでなければ、新しい計画が文の性能を低下させると、CBOはSMBのすべての許容可能な計画から最もコストの低い計画を選択し、SPMはその新しい計画をSMBに格納します.近い将来、この新しい計画は良い選択になるかもしれません.
2.1.SQL Plan Baseblineの取得
Oracle 11 gでは、SQLPlan BaselineをキャプチャしてSMBに格納するのは簡単です.まず、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESパラメータは、SQL Plan Baselinesの自動キャプチャがオンになっているかどうかを制御します.このパラメータのデフォルトはFALSEに設定されています.これは、SQL Plan Baselinesのデフォルトが自動キャプチャされないことを意味します.しかし、DBAがセッションまたはシステムレベルでTRUEに設定されると、SPMはSQL文の実行を記録し始め、1つのSQL文が1回以上実行されると、SQL Plan Baselinesがキャプチャした候補とみなされる.
次に、Oracle 11 gに新しいパッケージDBMS_があります.SPMは、以下のいくつかのソースから手動で「栽培」計画を行うことで、大量のSQL文を事前にキャプチャし、導入することができます.
ØデータベースLibrary CacheのSQL文の1つ以上を使用して、SQL PlanBaselinesを作成できます.プロセスLOAD_PLANS_FROM_CURSOR_CACHEは、潜在的なSMBの候補として、Library Cache内の任意の文のサブセットをキャプチャするために使用することができる.
ØSQL Tuning SetまたはAWRスナップショットに格納されているSQL文は、プロセスLOAD_PLANS_FROM_SQLSETはSQL Plan Baselinesにキャプチャされ、変換されます.
Ø最後に、SQL Plan Baselinesはインポート・ストレージ・テーブルから使用できます.これは、異なるデータベースから文をキャプチャできることを意味します.
3.SQL Plan Baseline情報の表示
SMBにキャプチャおよび格納されたSQL Plan Baselineメタデータには、計画を制御するためのSPMおよびCBOの属性が含まれます.新しい計画がSMBに入ると、ENABLEDと表示されますが、ACCEPTEDは表示されません.
1)CBOはすでにこの計画を評価し、それが最良の計画であると判断した.
2)計画はACCEPTEDモードに進化した.CBOが計画を採用することを考慮する前に、計画はENABLEDとACCEPTEDとしてマークされなければならない.
これらのメタデータを表示する最も簡単な方法は、DBA_をクエリーすることです.SQL_PLAN_BASELINES辞書ビュー.以下は、実行計画を制御する最も価値のある情報のまとめです.
Table 1.1. SQL Plan Baseline Plan Control Metadata
Attribute
Description
SQL_HANDLE
A unique SQL identifier in string form; it can be used as a search key
PLAN_NAME
A unique SQL plan identifier in string form; it can be used as a search key
SQL_TEXT
The SQL statement’s unnormalized, actual text
ORIGIN
Tells if the SQL Plan was either: AUTO-CAPTURE: Automatically captured MANUAL-LOAD: Manually evolved MANUAL-SQLTUNE: Automatically evolved by SQL Tuning Advisor AUTO-SQLTUNE: Automatically evolved by Automatic SQL Tuning
ENABLED
Indicates that the SQL Plan is enabled (YES) for CBO utilization or not (NO). Disabled plans are ignored by the CBO
ACCEPTED
Indicates that the SQL Plan is validated as a good plan, either because Oracle 11g has: Automatically accepted it, or The DBA has forced its manual acceptance by changing its status to ACCEPTED via procedure DBMS_SPM.ALTER_SQL_PLAN_BASELINE()
FIXED
SQL Plans whose FIXED attribute is set to YES will be considered by the CBO. If multiple plans are marked as FIXED, the CBO will only select the best execution plan from those so marked
OPTIMIZER_COST
The total cost estimated by the CBO to execute the SQL statement using this execution plan
既存のSQL Plan Baselinesで、1つのSQL文に潜在的な影響を与える別の方法はDBMS_XPLANの新プロセスDISPLAY_SQL_PLAN_BASELINE.たとえば、SMBとSQL文のハンドルが一致するすべてのSQLPlan Baselinesを表示するには、この手順を使用します.SQL文の計画名が指定されている場合は、その文の実行計画などを表示することもできます.
4.自動キャプチャの実装とプロセス
次に、SQL Plan Baselinesを自動的にキャプチャするプロセスを分析します.まず、OPTIMIZER_を設定しますCAPTURE_SQL_PLAN_BASELINESパラメータはTRUE(デフォルトはFALSE)であり、SQL Plan Baselines自動キャプチャをオンにします.OPTIMIZERをUSE_SQL_PLAN_BASELINESパラメータはTRUE(デフォルト)に設定されています.このパラメータは、CBOがSQL文の繰り返し実行によって生成された計画が良い計画として評価されるかどうかをチェックするかを制御します.
次に、同じSQLを2回実行します.1回目の実行ではSQL文が記録され、2回目の実行ではSMBに自動的に取り込まれ、その文ACCEPTEDとしてマークされたSQLPlan Baselineが計画されます.
その後、SQL文が再び実行され、異なる新しい計画が作成されると、計画は自動的にSMBにキャプチャされますが、ACCEPTEDとは表示されません.したがって、SPMは最初の計画をENABLEDとACCEPTEDとしてマークするだけです.
5.SQL Plan Baselineの進化
SPBにACCEPTEDと表示されていないSQL Plan Baselinesは、ACCEPTED状態を表示するようにさらに進化する必要があり、今後再実行されるSQL文に採用されることができ、SPBを進化させる方法は主に以下の2つがある.
5.1.手作業の方法
Ødbmsを呼び出す_spmパッケージのevolve_sql_plan_ベースライン関数
SQL> var report clob;
SQL> exec :report := dbms_spm.evolve_sql_plan_baseline();
SQL> print :report
SQL> select sql_text, plan_name, enabled, accepted fromdba_sql_plan_baselines;
ØSQL Tuning Advisorキットの呼び出し
SQL> var tname varchar2(30);
SQL> exec :tname :=dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc');
SQL> execdbms_sqltune.execute_tuning_task(task_name => :tname);
SQL> selectdbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual;
SQL> exec dbms_sqltune.accept_sql_profile(task_name=> :tname);
SQL> select sql_text, plan_name, enabled,accepted from dba_sql_plan_baselines;
5.2.自動メソッド
Ø定期スケジューリングdbms_spmパッケージのevolve_sql_plan_baseline()
ØSQL TUNING ADVISORを自動タスクウィンドウで自動運転させる
6.具体的な操作命令
Ø自動キャプチャとSPMの採用を開始する
ALTER SESSION SET optimizer_capture_sql_plan_baselines=TRUE;
ALTER SESSION SET optimizer_use_sql_plan_baselines=TRUE;
ØSPMメタデータの表示
ØDBMS経由XPLAN.DISPLAY_SQL_PLAN_BASELINEには、特定のテキストが保持されているSQL Plan Baselinesが表示されます.
Oracle Database 11 gR 1には、DBAが任意のSQL文の実行計画を最適化するための新しいツールとして、SQL PlanManagement(SPM)が導入されています.これにより、オプティマイザ統計のリフレッシュ、アプリケーションの変更、データベース・バージョンのアップグレードによる影響が制限されます.この文書では、SPMの原理を基本的に理解し、パフォーマンスの最適化能力について簡単に説明します.
2.SPMの原理とメカニズム
Oracle 11 gは、SQL計画の予期せぬ悪化を解決するSQL Plan Management(SPM)と呼ばれる新しい特徴を、簡単で優雅な方法で実施しています.ユーザーセッションが自動SQL Plan Baselineキャプチャをオンにすると、CBOはSQL Management Base(SMB)にそのセッション内で実行されたSQLを記録し、SQL文テキスト、アウトライン(Outline)、バインド変数、およびそのコンパイル環境などをSQL Plan Baselineとして格納します.
文が初めて実行されるため、Oracle 11 gは当時の実行計画を最適化します.同じSQL文が2回目に実行されたときこそ、SPMの優雅さがより顕著に現れます.文の2回目の実行期間中、CBOは文の実行計画とSMBに格納された計画を比較し、新しい計画がSMBの計画よりも効率的であるかどうかを評価します.
新しい計画が文のパフォーマンスを向上させる場合、SPMは新しい計画を文の最良の計画としてマークします.DBAがOPTIMIZERを修正していない限りUSE_SQL_PLAN_BASELINESパラメータのデフォルト設定(true)では、CBOは現在の文実行に新しい計画を採用します.そうでなければ、新しい計画が文の性能を低下させると、CBOはSMBのすべての許容可能な計画から最もコストの低い計画を選択し、SPMはその新しい計画をSMBに格納します.近い将来、この新しい計画は良い選択になるかもしれません.
2.1.SQL Plan Baseblineの取得
Oracle 11 gでは、SQLPlan BaselineをキャプチャしてSMBに格納するのは簡単です.まず、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESパラメータは、SQL Plan Baselinesの自動キャプチャがオンになっているかどうかを制御します.このパラメータのデフォルトはFALSEに設定されています.これは、SQL Plan Baselinesのデフォルトが自動キャプチャされないことを意味します.しかし、DBAがセッションまたはシステムレベルでTRUEに設定されると、SPMはSQL文の実行を記録し始め、1つのSQL文が1回以上実行されると、SQL Plan Baselinesがキャプチャした候補とみなされる.
次に、Oracle 11 gに新しいパッケージDBMS_があります.SPMは、以下のいくつかのソースから手動で「栽培」計画を行うことで、大量のSQL文を事前にキャプチャし、導入することができます.
ØデータベースLibrary CacheのSQL文の1つ以上を使用して、SQL PlanBaselinesを作成できます.プロセスLOAD_PLANS_FROM_CURSOR_CACHEは、潜在的なSMBの候補として、Library Cache内の任意の文のサブセットをキャプチャするために使用することができる.
ØSQL Tuning SetまたはAWRスナップショットに格納されているSQL文は、プロセスLOAD_PLANS_FROM_SQLSETはSQL Plan Baselinesにキャプチャされ、変換されます.
Ø最後に、SQL Plan Baselinesはインポート・ストレージ・テーブルから使用できます.これは、異なるデータベースから文をキャプチャできることを意味します.
3.SQL Plan Baseline情報の表示
SMBにキャプチャおよび格納されたSQL Plan Baselineメタデータには、計画を制御するためのSPMおよびCBOの属性が含まれます.新しい計画がSMBに入ると、ENABLEDと表示されますが、ACCEPTEDは表示されません.
1)CBOはすでにこの計画を評価し、それが最良の計画であると判断した.
2)計画はACCEPTEDモードに進化した.CBOが計画を採用することを考慮する前に、計画はENABLEDとACCEPTEDとしてマークされなければならない.
これらのメタデータを表示する最も簡単な方法は、DBA_をクエリーすることです.SQL_PLAN_BASELINES辞書ビュー.以下は、実行計画を制御する最も価値のある情報のまとめです.
Table 1.1. SQL Plan Baseline Plan Control Metadata
Attribute
Description
SQL_HANDLE
A unique SQL identifier in string form; it can be used as a search key
PLAN_NAME
A unique SQL plan identifier in string form; it can be used as a search key
SQL_TEXT
The SQL statement’s unnormalized, actual text
ORIGIN
Tells if the SQL Plan was either:
ENABLED
Indicates that the SQL Plan is enabled (YES) for CBO utilization or not (NO). Disabled plans are ignored by the CBO
ACCEPTED
Indicates that the SQL Plan is validated as a good plan, either because Oracle 11g has:
FIXED
SQL Plans whose FIXED attribute is set to YES will be considered by the CBO. If multiple plans are marked as FIXED, the CBO will only select the best execution plan from those so marked
OPTIMIZER_COST
The total cost estimated by the CBO to execute the SQL statement using this execution plan
既存のSQL Plan Baselinesで、1つのSQL文に潜在的な影響を与える別の方法はDBMS_XPLANの新プロセスDISPLAY_SQL_PLAN_BASELINE.たとえば、SMBとSQL文のハンドルが一致するすべてのSQLPlan Baselinesを表示するには、この手順を使用します.SQL文の計画名が指定されている場合は、その文の実行計画などを表示することもできます.
4.自動キャプチャの実装とプロセス
次に、SQL Plan Baselinesを自動的にキャプチャするプロセスを分析します.まず、OPTIMIZER_を設定しますCAPTURE_SQL_PLAN_BASELINESパラメータはTRUE(デフォルトはFALSE)であり、SQL Plan Baselines自動キャプチャをオンにします.OPTIMIZERをUSE_SQL_PLAN_BASELINESパラメータはTRUE(デフォルト)に設定されています.このパラメータは、CBOがSQL文の繰り返し実行によって生成された計画が良い計画として評価されるかどうかをチェックするかを制御します.
次に、同じSQLを2回実行します.1回目の実行ではSQL文が記録され、2回目の実行ではSMBに自動的に取り込まれ、その文ACCEPTEDとしてマークされたSQLPlan Baselineが計画されます.
その後、SQL文が再び実行され、異なる新しい計画が作成されると、計画は自動的にSMBにキャプチャされますが、ACCEPTEDとは表示されません.したがって、SPMは最初の計画をENABLEDとACCEPTEDとしてマークするだけです.
5.SQL Plan Baselineの進化
SPBにACCEPTEDと表示されていないSQL Plan Baselinesは、ACCEPTED状態を表示するようにさらに進化する必要があり、今後再実行されるSQL文に採用されることができ、SPBを進化させる方法は主に以下の2つがある.
5.1.手作業の方法
Ødbmsを呼び出す_spmパッケージのevolve_sql_plan_ベースライン関数
SQL> var report clob;
SQL> exec :report := dbms_spm.evolve_sql_plan_baseline();
SQL> print :report
SQL> select sql_text, plan_name, enabled, accepted fromdba_sql_plan_baselines;
ØSQL Tuning Advisorキットの呼び出し
SQL> var tname varchar2(30);
SQL> exec :tname :=dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc');
SQL> execdbms_sqltune.execute_tuning_task(task_name => :tname);
SQL> selectdbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual;
SQL> exec dbms_sqltune.accept_sql_profile(task_name=> :tname);
SQL> select sql_text, plan_name, enabled,accepted from dba_sql_plan_baselines;
5.2.自動メソッド
Ø定期スケジューリングdbms_spmパッケージのevolve_sql_plan_baseline()
ØSQL TUNING ADVISORを自動タスクウィンドウで自動運転させる
6.具体的な操作命令
Ø自動キャプチャとSPMの採用を開始する
ALTER SESSION SET optimizer_capture_sql_plan_baselines=TRUE;
ALTER SESSION SET optimizer_use_sql_plan_baselines=TRUE;
ØSPMメタデータの表示
COL creator FORMAT A08 HEADING 'Creator'COL hndle FORMAT A08 HEADING 'SQL|Handle'COL plnme FORMAT A08 HEADING 'Plan|Name'COL sql_hdr FORMAT A25 HEADING 'SQL Text' WRAPCOL origin FORMAT A12 HEADING 'Origin'COL optimizer_cost FORMAT 9999999 HEADING 'CBO|Cost'COL enabled FORMAT A04 HEADING 'Ena-|bled'COL accepted FORMAT A04 HEADING 'Acpt'COL fixed FORMAT A04 HEADING 'Fixd'COL autopurge FORMAT A04 HEADING 'Auto|Purg'COL create_dt FORMAT A11 HEADING 'Created|On' WRAPCOL lst_exc_dt FORMAT A11 HEADING 'Last|Executed' WRAPSELECT creator ,SUBSTR(sql_handle, -8, 8) hndle ,SUBSTR(plan_name, -8, 8) plnme ,SUBSTR(sql_text, 1, 75) sql_hdr ,origin ,optimizer_cost ,enabled ,accepted ,fixed ,autopurge ,TO_CHAR(created, 'yyyy-mm-dd hh24:mi:ss') create_dt ,TO_CHAR(last_executed, 'yyyy-mm-dd hh24:mi:ss') lst_exc_dt FROM dba_sql_plan_baselines WHERE (sql_text LIKE '%SPM%') ORDER BY 1,2,3;ØDBMS経由XPLAN.DISPLAY_SQL_PLAN_BASELINEには、特定のテキストが保持されているSQL Plan Baselinesが表示されます.
SET LINESIZE 150SET PAGESIZE 2000SELECT PT.* FROM (SELECT DISTINCT sql_handle FROM dba_sql_plan_baselines WHERE sql_text like '%SPM%') SPB, TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SPB.sql_handle, NULL,'TYPICAL +NOTE')) PT;