How to Create a SQL Patch for a Query from the AWR views
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.
This site uses Akismet to reduce spam. Learn how your comment data is processed.