问题描述
是否有可能对同一查询有多个SQL配置文件？
专家解答
是的。只要你在不同的类别中创建它们!
这使您可以尝试新的配置文件，同时保留现有的配置文件。您可以通过设置sqltune_category参数来做到这一点:
create table t as
select level x, lpad('x', 100, 'x') y
from dual
connect by level <= 100;
set serveroutput off
select * from t where x = 1;
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +OUTLINE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from t where x = 1
Plan hash value: 2498539100
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T |
----------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('_optimizer_cost_model' 'fixed')
OPT_PARAM('_fix_control' '6670551:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
begin
dbms_sqltune.import_sql_profile (
sql_text => 'select * from t where x = 1',
name => 'ORIG_PROF',
profile => sqlprof_attr(q'| /*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('_optimizer_cost_model' 'fixed')
OPT_PARAM('_fix_control' '6670551:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/ |')
);
end;
/
select * from dba_sql_profiles;
create index i on t(x);
set serveroutput off
select * from t where x = 1;
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +OUTLINE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from t where x = 1
Plan hash value: 3995057492
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T |
| 2 | INDEX RANGE SCAN | I |
----------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('_optimizer_cost_model' 'fixed')
OPT_PARAM('_fix_control' '6670551:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."X"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
begin
dbms_sqltune.import_sql_profile (
sql_text => 'select * from t where x = 1',
name => 'NEW_PROF',
profile => sqlprof_attr(q'| /*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('_optimizer_cost_model' 'fixed')
OPT_PARAM('_fix_control' '6670551:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."X"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/ |')
);
end;
/
ORA-13830: SQL profile or patch with category DEFAULT already exists for this SQL statement
begin
dbms_sqltune.import_sql_profile (
sql_text => 'select * from t where x = 1',
name => 'NEW_PROF',
profile => sqlprof_attr(q'| /*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('_optimizer_cost_model' 'fixed')
OPT_PARAM('_fix_control' '6670551:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."X"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/ |'),
category => 'TEST'
);
end;
/
select name, category, sql_text
from dba_sql_profiles;
NAME CATEGORY SQL_TEXT
NEW_PROF TEST select * from t where x = 1
ORIG_PROF DEFAULT select * from t where x = 1 这使您可以尝试新的配置文件，同时保留现有的配置文件。您可以通过设置sqltune_category参数来做到这一点:
sho parameter sqltune_category
NAME TYPE VALUE
---------------- ------ -------
sqltune_category string DEFAULT
select * from t where x = 1;
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +NOTE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from t where x = 1
Plan hash value: 3995057492
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T |
| 2 | INDEX RANGE SCAN | I |
----------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL profile ORIG_PROF used for this statement
alter session set sqltune_category = 'TEST';
select * from t where x = 1;
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +NOTE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from t where x = 1
Plan hash value: 3995057492
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T |
| 2 | INDEX RANGE SCAN | I |
----------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL profile NEW_PROF used for this statement 「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
