SQL patch is a new feature from 12c onwards where we can insert hints into the sql statement using this feature. This is particularly useful when the application code cannot be modified and hints are providing the performance benefits. We have other tools like Sql profile, Sql plan baseline available also to to tune the application code.
How to create SQL Patch in Oracle
- SQL Patchis created using procedure DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(). is 12.1 release and using dbms_sqldiag.create_sql_patch from 12.2 onwards
- We cannot use SQL_ID in the 12.1 release in the procedure but we can use it from 12.2 onwards
- Let’s check the various use cases
Suppose you want to change the “Optimizer feature enable” for the poor-performing query to improve the performance
12.1
SQL> connect / as sysdba
SQL> begin
sys.dbms_sqldiag_internal.i_create_patch(sql_text=>'select * from emp_details', hint_text=>'optimizer_features_enable(''9.2.0'')', name=> 'SQL_Patch_9.2.0');
end;
/
12.2 onwards
SQL> connect / as sysdba
SQL> variable x varchar2(100);
SQL> begin
x:=dbms_sqldiag.create_sql_patch(sql_text=>'select * from emp_details', hint_text=>'optimizer_features_enable(''9.2.0'')', name=> 'SQL_Patch_9.2.0');
end;
/
We can SQL_ID can be used to create the SQL Patch.
SQL> connect / as sysdba
SQL> variable x varchar2(100);
SQL> exec :x:=dbms_sqldiag.create_sql_patch(sql_id=>'ghhgdx1cb1z1u', hint_text=>'optimizer_features_enable(''9.2.0'')', name=> 'SQL_Patch_9.2.0');
Here Name is the sql patch name and hint_text provides the hints to the sql statement
You can avoid giving lengthy sql statements in 12.1 also using the below sql
VAR c CLOB
EXEC SELECT t.sql_text INTO :c FROM dba_hist_sqltext t WHERE t.sql_id = '' AND rownum = t.dbid = (SELECT d.dbid FROM v$database d);
EXEC sys.dbms_sqldiag_internal.i_create_patch(sql_text => :c, hint_text => 'optimizer_features_enable(''9.2.0'')', name => 'SQL_Patch_9.2.0');
Suppose you want to change the parameter for the poor-performing query to improve the performance, then below hint_text can be used
Suppose you want to set _optimizer_squ_bottomup=true,then hint_text will be
hint_text=>'OPT_PARAM(''_optimizer_squ_bottomup'' ''TRUE'')'.
Suppose you want to set a fix_control, then hint_text will be
hint_text=>' OPT_PARAM(''_fix_control'' ''20648883:1'')
How to disable the SQL Patch
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH(name=>'SQL_Patch_9.2.0', attribute_name=>'STATUS', attribute_value=>'DISABLED');
How to enable the SQL Patch
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH(name=>'SQL_Patch_9.2.0', attribute_name=>'STATUS', attribute_value=>'ENABLE');
How to drop the SQL Patch
EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name=>'SQL_Patch_9.2.0');
SQL Patch is a good feature from 12.1 onwards for quickly resolving time-critical issues. I hope you like this article on How to create SQL Patch in Oracle
Related Articles
Sql plan Management in Oracle
Autotrace in oracle
Histograms in Oracle
Leave a Reply