How to create SQL Patch in Oracle

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, and Sql plan baseline available to tune the application code.

How to create SQL Patch in Oracle

• SQL Patch is created using the 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 also use SQL_ID 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 = 'ghhgdx1cb1z1u' 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');

or

VAR c CLOB
EXEC SELECT sql_fulltext INTO :c FROM gv$sql WHERE sql_id ='ghhgdx1cb1z1u' AND ROWNUM = 1;
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');

How to check the status of SQL Patch

SELECT name, status FROM dba_sql_patches WHERE name = '&sql_patch';

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: check out this post on Sql plan Management in Oracle, how it works out, what are the benefits, and how it helps in performance tuning
Autotrace in oracle
Histograms in Oracle: check out Histograms in EBS, How to check the columns for histograms, How to load new columns for histograms
how to find if the sql is using the SQL Patch : Visit this page to find out if the sql is using the applied sql profile and also how to check past usage of sql profile