This is very less know feature . Ideally most us are aware of sql profiles to fix plan change how this is very less known which can be used to execution plan by enforcing hints when changing code is not possible
Please note these will be applicable to single sql id , unlike sql profile which is applicable to sql signature, In case of sql is keeps changing like in case on bind variable , we need to add patch by modifying sql profile .
Create Sql patch :
declare
--> hint_text => 'PARALLEL(big_table,10)',
--> hint_text => 'PARALLEL(8)',
Drop Sql Patches :
This is undocumented method to update sqlobj$ which your client will not allow . First create a sql profile with any available
SQL> select name from dba_sql_patches;
SQL> exec dbms_sqldiag.create_stgtab_sqlpatch('SQLPATCH_STAGE','SYSTEM');
SQL> exec dbms_sqldiag.pack_stgtab_sqlpatch(staging_table_name=>'SQLPATCH_STAGE', staging_schema_owner=>'SYSTEM');
export import -->>
SQL> exec dbms_sqldiag.unpack_stgtab_sqlpatch(replace=>true, staging_table_name=>'SQLPATCH_STAGE', staging_schema_owner=>'SYSTEM');
Please note these will be applicable to single sql id , unlike sql profile which is applicable to sql signature, In case of sql is keeps changing like in case on bind variable , we need to add patch by modifying sql profile .
Create Sql patch :
declare
v_sql CLOB;
begin
select sql_text into v_sql from dba_hist_sqltext where sql_id=
'bn7zqwkfgtr38'
;
sys.dbms_sqldiag_internal.i_create_patch(
sql_text => v_sql,
hint_text =>
'DYNAMIC_SAMPLING(4)'
,
name =>
'user_extents_patch'
);
end;
/
Listing commonly used hints in patch :
--> hint_text=>' IGNORE_OPTIM_EMBEDDED_HINTS'),--> hint_text => 'PARALLEL(big_table,10)',
--> hint_text => 'PARALLEL(8)',
--> hint_text => 'OPT_PARAM("optimizer_index_caching" 80)',
--> hint_text => 'OPT_PARAM("optimizer_index_cost_adj" 1)',
Drop Sql Patches :
exec dbms_sqldiag.drop_sql_patch(name => 'my_patch');
PL/SQL procedure successfully completed.
check sql patch :
-->
select
name
, status, created, sql_text
from
dba_sql_patches
where
name
=
'user_extents_patch'
--> expalin plan:
Note
-----
- SQL patch "PATCH_gz85dtvwaj4fw" used for this statemen
How to add hint when sql id changes :
When there are multiple sqlid in case of bind aware we have below 2 options
Option 1)
Modify your sql generated form COE script to add hint or add hint
to create sql profile statement . Optionally you can add hint to explain plan
statement with outline and use same outline to generate sql profile
statement with outline and use same outline to generate sql profile
DECLARE
l_sql
clob;
BEGIN
l_sql := q'!select
su_pk,su_name,su_comment,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk from t_order_or group
by su_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is
not null!';
dbms_sqltune.import_sql_profile( sql_text => l_sql,
name => 'SQLPROFILE_01',
profile => sqlprof_attr
(q'!USE_HASH_AGGREGATION(@"SEL$639F1A6F")!',
q'!FULL(@"SEL$639F1A6F"
"T_ORDER_OR"@"SEL$2")!',
q'!USE_NL(@"SEL$1" "INNER_VIEW"@"SEL$1")!',
q'!LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1"
"INNER_VIEW"@"SEL$1")!',
q'!NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")!',
q'!FULL(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1")!',
q'!OUTLINE(@"SEL$1")!',
q'!OUTLINE(@"SEL$2")!',
q'!OUTLINE_LEAF(@"SEL$1")!',
q'!PUSH_PRED(@"SEL$1"
"INNER_VIEW"@"SEL$1" 1)!',
q'!OUTLINE_LEAF(@"SEL$639F1A6F")!',
q'!ALL_ROWS!',
q'!DB_VERSION('11.2.0.3')!',
q'!OPTIMIZER_FEATURES_ENABLE('11.2.0.3')!',
q'!IGNORE_OPTIM_EMBEDDED_HINTS!')
,
force_match
=> true );
end;
/
Option 2 )
sql plan . Then update sqlobj$ to add int to sql profile
declare
pln_sql_id varchar2(20) :='4sdd3343222';
pln_plan_hash_value number := 2949544139;
orig_sql_id varchar2(20) := '4sdd3343222';
new_prof_name varchar2(20) := 'SQL_PROFILE_2';
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin
select extractvalue(value(d), '/hint') as outline_hints
bulk collect into ar_profile_hints
from xmltable('/*/outline_data/hint'
passing (select xmltype(other_xml) as xmlval from v$sql_plan
where sql_id = pln_sql_id and plan_hash_value = pln_plan_hash_value
and other_xml is not null)) d;
select sql_fulltext into cl_sql_text
from v$sql where sql_id = orig_sql_id;
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
name => new_prof_name,
force_match => true);
end;
/
pln_sql_id varchar2(20) :='4sdd3343222';
pln_plan_hash_value number := 2949544139;
orig_sql_id varchar2(20) := '4sdd3343222';
new_prof_name varchar2(20) := 'SQL_PROFILE_2';
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin
select extractvalue(value(d), '/hint') as outline_hints
bulk collect into ar_profile_hints
from xmltable('/*/outline_data/hint'
passing (select xmltype(other_xml) as xmlval from v$sql_plan
where sql_id = pln_sql_id and plan_hash_value = pln_plan_hash_value
and other_xml is not null)) d;
select sql_fulltext into cl_sql_text
from v$sql where sql_id = orig_sql_id;
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
name => new_prof_name,
force_match => true);
end;
/
select comp_data from sqlobj$data where signature=(select signature from sqlobj$ where name='SQL_PROFILE_2');
- For 10.2, use the following sql to get profile hints :
-- select attr#, attr_val from sqlprof$attr where signature=(select signature from sqlprof$ where sp_name='SQL_PROFILE_2');
-- select attr#, attr_val from sqlprof$attr where signature=(select signature from sqlprof$ where sp_name='SQL_PROFILE_2');
update sqlobj$data set
comp_data='';
1 row updated.
--
-- For 10.2 use the below update statement :
-- update sqlprof$attr set ATTR_VAL='FULL(@"SEL$1" "EMP"@"SEL$1")
NOPARALLEL(@"SEL$1" "EMP"@"SEL$1")' where attr#=5 and
signature='784334333455334';
--
commit;
alter system flush shared_pool;
comp_data='';
1 row updated.
--
-- For 10.2 use the below update statement :
-- update sqlprof$attr set ATTR_VAL='FULL(@"SEL$1" "EMP"@"SEL$1")
NOPARALLEL(@"SEL$1" "EMP"@"SEL$1")' where attr#=5 and
signature='784334333455334';
--
commit;
alter system flush shared_pool;
Export import sql patch :
Reference :
How to Create a SQL Patch to add Hints to Application SQL Statements (Doc ID 1931944.1)