SQLプロファイルの使用方法
これらの記事は、『OracleベースSQL最適化』の本から見つけたものです.
私の実験過程を参考にしてください.
SQL Profile
Oracle 10 GのSQLプロファイルは、Oracle 9 iのStored Outlineの進化と言えます.
Stored Outlineが実現できる機能SQL Profileも完全に実現できます.
SQL Profileは、Stored Outlineと比較して、次のような利点を備えています.は、生成、変更、および制御を容易にします. はSQL文のサポートに優れています.つまり、適用範囲が広いということです.
SQL Profileを使用すると、次の2つの目的を簡単に実現できます.ロックまたは安定した実行計画. ターゲットSQLのSQLテキストを変更できない場合は、ターゲットSQL文を使用して指定した実行計画に従って を実行します.
SQL Profileには、次の2つのタイプがあります. Automatic Manual
******************AutomaticタイプのSQL Profile******************************
AutomaticタイプのSQL Profileは、データ辞書に格納されているターゲットSQLの追加の調整情報です.
AutomaticタイプのSQLプロファイルがある場合、Oracleは実行計画を作成する際に、ターゲットSQLに関する統計などの内容に応じて調整するため、誤った実行計画をある程度回避できます.
AutomaticタイプのSQL Profileの正確性を心配する必要はありません.Oracleでは、動的サンプリング技術のような手段を使用して、これらの追加の調整情報が相対的に正確であることを保証します.
簡単な例を挙げると、表Aの元の統計情報に記録されている表Aは100個のデータしかないが、実際のデータ量は確かに100万個である.
この場合、CBOがテーブルAの元の統計情報のみに基づいてターゲットSQLに対して対応する実行計画を生成する場合、得られた実行計画は誤りである可能性が高い.
この時点で表AにAutomaticタイプのSQLプロファイルが使用されている場合、Oracleでは、ターゲットSQLの「SCALE_ROWS=10000」をデータ辞書に記録するだけで、ターゲットSQLの表Aの実際の記録数が元の統計情報の10000倍であることを示します.
CBOがターゲットSQLに対して実行計画を作成すると、テーブルAの記録数は100*10=100万となり、
これにより生じた実行計画は正しい実行計画である可能性が高い.
ここで注意しなければならないのは、AutomaticタイプのSQL Profileは、Stored OutlineのようにターゲットSQLの実行計画をロックしないからです.AutomaticタイプのSQL Profileの本質は、ターゲットSQLに対する追加の調整情報であり、これらの追加の調整情報が変化すると、元のAutomaticタイプのSQL Profileが変わっていなくても、このSQLの実行計画も変更される可能性があります.
この意味では、AutomaticタイプのSQL Profileは、実行計画を調整するために使用できるが、目標SQLの実行計画を完全に安定させる役割を果たすことはできません.
ターゲットSQLのSQLテキストを変更せずにAutomaticタイプのSQL Profileを使用して実行計画を調整する例を見てみましょう
Noteセクションには、「SQL profile」「SYS_SQLPROF_01539 fb 60 e 15 c 000」used for this statement」という内容があります.これは、先ほど受け取ったSQL Profileがすでに機能していることを示しています.このSQL Profileの名前はSYS_SQLPROF_01539fb60e15c000.
実行計画からも分かるように、実行計画は、テーブルT 1の全テーブルスキャンからインデックスIDX_に変更された.T 1のインデックス範囲スキャン.
これは、AutomaticタイプのSQL Profileが、宛先SQLのSQLテキストを変更せずに実行計画を変更できることを示しています.
DBMS_SQLTUNE.ACCEPT_SQL_PROFILEの入力パラメータFORCE_MATCHのデフォルト値はFALSEで、
SQL ProfileはSQLテキストが完全に一致している場合にのみ適用されることを示します.
この場合、ターゲットSQLのSQLテキストが少し変更されると、元のSQL Profileが機能しなくなります.
上記SQLのSQLテキストは、select/*+no_です.index(t1 idx_t1) */* from t1 where n=1,
ここでwhere条件をn=1からn=2に変更して再度実行する.
**********************ManualタイプのSQL Profile**************************
ManualタイプのSQL Profileは本質的にHintのグループであり、この一対のHintのグループは実際には実行計画のOutline DATA部分のHintのグループに由来している.
ManualタイプのSQL Profileは、ターゲットSQLのSQLテキストを変更せずに実行計画を調整することができます.さらに重要なのは、ManualタイプのSQL ProfileがターゲットSQLの実行計画を安定させる役割を果たすことです.これはAutomaticタイプのSQL Profileにはありません.
ターゲットSQLのSQLテキストを変更せずにManualタイプのSQL Profileで実行計画を調整する例を見てみましょう.
ここでは、先ほどのテストテーブルT 1とそのインデックスIDX_を用いてT1.
まず、AutomaticタイプのSQLプロファイルを削除します.
SQLプロファイルの作成:
Plan hash value:1369807930という実行計画のOutline DATA部分を下に貼ります.
sqlprof_に置くattr部分は、貼るときに一重引用符と二重引用符に注意します.
大体の過程は、まずターゲットSQLのテキストを書き換えて、その中で適切なHintを使って、Hintに参加した後のSQLが私たちの望む実行計画から抜け出すことができることを知っています.
次に、適切なHintを追加し、Outline DATAセクションの値を問合せ、ManualタイプのSQL Profileに追加します.
manualタイプにもFORCE_がありますMATCH属性は、ここではtrueを使用していますので、SQLのwhere部分が異なっていても構いません.
上記のNoteセクションでは、「SQL profile“SQLPROFILE_001”used for this statement」が使用されているという記録があります.
添付ファイルのスクリプトを介してSQL Profileを作成するより簡単な方法があります.
添付ファイル:このスクリプトでSQL Profileを生成できます
私の実験過程を参考にしてください.
SQL Profile
Oracle 10 GのSQLプロファイルは、Oracle 9 iのStored Outlineの進化と言えます.
Stored Outlineが実現できる機能SQL Profileも完全に実現できます.
SQL Profileは、Stored Outlineと比較して、次のような利点を備えています.
SQL Profileを使用すると、次の2つの目的を簡単に実現できます.
SQL Profileには、次の2つのタイプがあります.
******************AutomaticタイプのSQL Profile******************************
AutomaticタイプのSQL Profileは、データ辞書に格納されているターゲットSQLの追加の調整情報です.
AutomaticタイプのSQLプロファイルがある場合、Oracleは実行計画を作成する際に、ターゲットSQLに関する統計などの内容に応じて調整するため、誤った実行計画をある程度回避できます.
AutomaticタイプのSQL Profileの正確性を心配する必要はありません.Oracleでは、動的サンプリング技術のような手段を使用して、これらの追加の調整情報が相対的に正確であることを保証します.
簡単な例を挙げると、表Aの元の統計情報に記録されている表Aは100個のデータしかないが、実際のデータ量は確かに100万個である.
この場合、CBOがテーブルAの元の統計情報のみに基づいてターゲットSQLに対して対応する実行計画を生成する場合、得られた実行計画は誤りである可能性が高い.
この時点で表AにAutomaticタイプのSQLプロファイルが使用されている場合、Oracleでは、ターゲットSQLの「SCALE_ROWS=10000」をデータ辞書に記録するだけで、ターゲットSQLの表Aの実際の記録数が元の統計情報の10000倍であることを示します.
CBOがターゲットSQLに対して実行計画を作成すると、テーブルAの記録数は100*10=100万となり、
これにより生じた実行計画は正しい実行計画である可能性が高い.
ここで注意しなければならないのは、AutomaticタイプのSQL Profileは、Stored OutlineのようにターゲットSQLの実行計画をロックしないからです.AutomaticタイプのSQL Profileの本質は、ターゲットSQLに対する追加の調整情報であり、これらの追加の調整情報が変化すると、元のAutomaticタイプのSQL Profileが変わっていなくても、このSQLの実行計画も変更される可能性があります.
この意味では、AutomaticタイプのSQL Profileは、実行計画を調整するために使用できるが、目標SQLの実行計画を完全に安定させる役割を果たすことはできません.
ターゲットSQLのSQLテキストを変更せずにAutomaticタイプのSQL Profileを使用して実行計画を調整する例を見てみましょう
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> grant dba to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> create table t1 (n number);
Table created.
SQL> declare
2 begin
3 for i in 1..10000
4 loop
5 insert into t1 values(i);
6 commit;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select count(*) from t1;
COUNT(*)
----------
10000
SQL> create index idx_t1 on t1(n);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',method_opt=>'for all columns size 1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
N
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1kg76709mx29d, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("N"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
41 rows selected.
SQL Automatic SQL Profile ：
SQL> declare
2 my_task_name varchar2(30);
3 my_sqltext clob;
4 begin
5 my_sqltext :='select /*+ no_index(t1 idx_t1) */ * from t1 where n=1';
6 my_task_name := dbms_sqltune.create_tuning_task(
7 sql_text => my_sqltext,
8 user_name => 'SCOTT',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'my_sql_tuning_task_2',
12 description => 'Task to tune a query on table t1');
13 end;
14 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_sqltune.execute_tuning_task( task_name => 'my_sql_tuning_task_2');
3 end;
4 /
PL/SQL procedure successfully completed.
：
SQL> set long 9000
SQL> set longchunksize 1000
SQL> set lines 800
SQL> select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task_2
Tuning Task Owner : SCOTT
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 07/12/2015 04:34:15
Completed at : 07/12/2015 04:34:16
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 4bh6sn1zvpgq7
SQL Text : select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 83.98%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task_2', replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=1)
2- Using SQL Profile
--------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
-------------------------------------------------------------------------------
， ， SQL Profile：
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE);
PL/SQL procedure successfully completed.
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
N
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID 1kg76709mx29d, child number 1
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
1 - access("N"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
Note
-----
- SQL profile "SYS_SQLPROF_01539fb60e15c000" used for this statement
45 rows selected.Noteセクションには、「SQL profile」「SYS_SQLPROF_01539 fb 60 e 15 c 000」used for this statement」という内容があります.これは、先ほど受け取ったSQL Profileがすでに機能していることを示しています.このSQL Profileの名前はSYS_SQLPROF_01539fb60e15c000.
実行計画からも分かるように、実行計画は、テーブルT 1の全テーブルスキャンからインデックスIDX_に変更された.T 1のインデックス範囲スキャン.
これは、AutomaticタイプのSQL Profileが、宛先SQLのSQLテキストを変更せずに実行計画を変更できることを示しています.
DBMS_SQLTUNE.ACCEPT_SQL_PROFILEの入力パラメータFORCE_MATCHのデフォルト値はFALSEで、
SQL ProfileはSQLテキストが完全に一致している場合にのみ適用されることを示します.
この場合、ターゲットSQLのSQLテキストが少し変更されると、元のSQL Profileが機能しなくなります.
上記SQLのSQLテキストは、select/*+no_です.index(t1 idx_t1) */* from t1 where n=1,
ここでwhere条件をn=1からn=2に変更して再度実行する.
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;
N
----------
2
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID c4j6hxkqudj1s, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=2
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=2)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
41 rows selected.
FORCE_MATCH TRUE DBMS_SQLTUNE.ACCEPT_SQL_PROFILE:
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', task_owner=>'SCOTT', replace => TRUE, force_match=>true);
PL/SQL procedure successfully completed.
SQL>
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;
N
----------
2
SQL> set pages 200
SQL> set lines 200
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID c4j6hxkqudj1s, child number 1
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=2
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=2)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
Note
-----
- SQL profile "SYS_SQLPROF_01539fb946248001" used for this statement
45 rows selected.**********************ManualタイプのSQL Profile**************************
ManualタイプのSQL Profileは本質的にHintのグループであり、この一対のHintのグループは実際には実行計画のOutline DATA部分のHintのグループに由来している.
ManualタイプのSQL Profileは、ターゲットSQLのSQLテキストを変更せずに実行計画を調整することができます.さらに重要なのは、ManualタイプのSQL ProfileがターゲットSQLの実行計画を安定させる役割を果たすことです.これはAutomaticタイプのSQL Profileにはありません.
ターゲットSQLのSQLテキストを変更せずにManualタイプのSQL Profileで実行計画を調整する例を見てみましょう.
ここでは、先ほどのテストテーブルT 1とそのインデックスIDX_を用いてT1.
まず、AutomaticタイプのSQLプロファイルを削除します.
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_01539fb946248001');
PL/SQL procedure successfully completed.
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_01539fb60e15c000');
PL/SQL procedure successfully completed.
SQL:
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=3
2 ;
N
----------
3
：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8czm7qzgrxc1b, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=3
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=3)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
41 rows selected.
， SQL:
SQL> select * from t1 where n=3;
N
----------
3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dvc6rkgpkan8d, child number 0
-------------------------------------
select * from t1 where n=3
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=3)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
41 rows selected.SQLプロファイルの作成:
Plan hash value:1369807930という実行計画のOutline DATA部分を下に貼ります.
sqlprof_に置くattr部分は、貼るときに一重引用符と二重引用符に注意します.
大体の過程は、まずターゲットSQLのテキストを書き換えて、その中で適切なHintを使って、Hintに参加した後のSQLが私たちの望む実行計画から抜け出すことができることを知っています.
次に、適切なHintを追加し、Outline DATAセクションの値を問合せ、ManualタイプのSQL Profileに追加します.
SQL> declare
2 v_hints sys.sqlprof_attr;
3 begin
4 v_hints:=sys.sqlprof_attr(
5 'BEGIN_OUTLINE_DATA',
6 'IGNORE_OPTIM_EMBEDDED_HINTS',
7 'OPTIMIZER_FEATURES_ENABLE("10.2.0.5")',
8 'ALL_ROWS',
9 'OUTLINE_LEAF(@"SEL$1")',
10 'INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))',
11 'END_OUTLINE_DATA'
12 );
13 dbms_sqltune.import_sql_profile(
14 'select /*+ no_index(t1 idx_t1) */ * from t1 where n=3',
15 v_hints,'SQLPROFILE_001',
16 force_match=>true,replace=>false);
17 end;
18 /
PL/SQL procedure successfully completed.
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;
N
----------
2manualタイプにもFORCE_がありますMATCH属性は、ここではtrueを使用していますので、SQLのwhere部分が異なっていても構いません.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c4j6hxkqudj1s, child number 1
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=2
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=2)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
Note
-----
- SQL profile "SQLPROFILE_001" used for this statement
45 rows selected.上記のNoteセクションでは、「SQL profile“SQLPROFILE_001”used for this statement」が使用されているという記録があります.
添付ファイルのスクリプトを介してSQL Profileを作成するより簡単な方法があります.
添付ファイル:このスクリプトでSQL Profileを生成できます