Yes. Provided you create them in different categories!
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 This allows you to try out the new profile while leaving the existing one in place. You can do this by setting the sqltune_category parameter:
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