• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle database
  • Oracle Ebusiness Suite
  • Oracle weblogic
  • Oracle Performance Tuning
  • Oracle Hyperion
  • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
Home » Oracle » Oracle Database » How to create SQL Patch for sql tuning in Oracle

How to create SQL Patch for sql tuning in Oracle

September 12, 2023 by techgoeasy Leave a Comment

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

Table of Contents

  • How to create SQL Patch in Oracle
  • How to disable the SQL Patch
  • How to enable the SQL Patch
  • How to drop the SQL Patch

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

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to create SQL Patch for sql tuning in Oracle
  • How to Configure Auto Login TDE Wallet
  • how to find if the sql is using the sql profile
  • What is Sequence in oracle
  • How to check temp tablespace in Oracle

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us