How to Create a SQL Patch for a Query from the AWR views

Hey everyone,

Hope you’re doing good!

In most applications, application code is static and cannot be modified by users. If there is a performance issue with application SQL, one potential solution is to modify it adding hints to force the desired access path.

Sometimes, the original code has performance issues and, in a time critical situation, one such alternative is the SQL Patch. The SQL Patch allows a user to insert hints into a query whose text cannot be edited.

In this blog post, I’ll show how to create a SQL Patch for a SQL_ID whose hint is stored in AWR.

First, let’s get the ORIGINAL sql_text:

var c clob
exec SELECT trim(sql_text) INTO :c FROM dba_hist_sqltext WHERE sql_id='1pzcw65khq4tu' AND rownum=1;

PL/SQL procedure successfully completed.

Now, let’s get the sql_text for the query which has the hint, again, from the AWR views:

var h clob
exec SELECT -
listagg(hint,' ') within group(order by rownum) into :h -
FROM -
( -
SELECT -
b.hint -
FROM -
dba_hist_sql_plan m -
,xmltable ( -
'/other_xml/outline_data/hint' passing xmltype (m.OTHER_XML) columns hint clob PATH '/hint' -
) b -
WHERE -
TRIM( OTHER_XML ) IS NOT NULL -
AND sql_id = 'gs6gf69grvbbt' -
AND plan_hash_value = '2839797856' -
);

PL/SQL procedure successfully completed.

And now, let’s create the SQL Patch to add the hint into the query:

var x varchar2(100);
begin
:X:=dbms_sqldiag_internal.I_CREATE_PATCH(SQL_TEXT => :C,HINT_TEXT => :h,CREATOR=>'SYS',NAME => '&SQLPATCH_NAME');
end;
/

PL/SQL procedure successfully completed.

So, from now, every time original query is executed, database will replace by the query with hint.

Hope it helps.

Peace,

Vinicius