Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Creating a SQL Patch with Many Hints Requires a Hack

7 February 2022 5 Comments Written by Christian Antognini

In the past, when I created a SQL patch, I always specified a small number of hints. Last week, for the first time, I created one with more than 100 of them. Given their number, I didn’t want to specify them manually. Instead, my goal was to create a SQL patch that contained the outline associated with a cursor stored in shared pool. For that reason, I executed the following PL/SQL block:

DECLARE
  l_sql_id VARCHAR2(13) := '2q7d290pp5vmp';
  l_name dba_sql_patches.name%TYPE := 'TEST';
  l_hints CLOB;
BEGIN
  dbms_lob.createtemporary(lob_loc => l_hints, cache => TRUE);
  FOR i IN (SELECT trim(extractValue(value(h),'.')) AS hint
            FROM v$sql_plan p, table(xmlsequence(extract(xmltype(p.other_xml),'/other_xml/outline_data/hint'))) h
            WHERE sql_id = l_sql_id
            AND id = 1)
  LOOP
    dbms_lob.append(dest_lob => l_hints, src_lob => chr(10) || i.hint);
  END LOOP;
  dbms_sqldiag.drop_sql_patch(name => l_name, ignore => TRUE);
  l_name := dbms_sqldiag.create_sql_patch(sql_id => l_sql_id, hint_text => l_hints, name => l_name);
END;

Since the input parameter L_HINTS of the function CREATE_SQL_PATCH is a CLOB, passing many hints shouldn’t be a problem. Unfortunately, what I noticed is that despite the fact that the SQL patch was created, and later on used by the query optimizer, a dozen of hints stored into the table SQLOBJ$DATA were invalid. To check it, I executed the following query:

SELECT trim(extractValue(value(h),'.')) AS hint
FROM sys.sqlobj$data od, sys.sqlobj$ so,
table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
WHERE so.name = 'TEST'
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id

Because of those invalid hints, the SQL patch didn’t work as expected.

While carefully checking the full list of hints stored in the table SYS.SQLOBJ$DATA, I noticed that the invalid hints weren’t stored at random positions. Instead, the number of characters between two invalid hints was always 500. Hence, I tried to pad the hints with some whitespaces to make sure that those “boundaries” weren’t in the middle of a hint. The amended PL/SQL block is the following (notice that the only change I did is that I replaced “i.hint” with “rpad(i.hint, 499, ‘ ‘)”):

DECLARE
  l_sql_id VARCHAR2(13) := '2q7d290pp5vmp';
  l_name dba_sql_patches.name%TYPE := 'LONG_PARSE';
  l_hints CLOB;
BEGIN
  dbms_lob.createtemporary(lob_loc => l_hints, cache => TRUE);
  FOR i IN (SELECT trim(extractValue(value(h),'.')) AS hint
            FROM v$sql_plan p, table(xmlsequence(extract(xmltype(p.other_xml),'/other_xml/outline_data/hint'))) h
            WHERE sql_id = l_sql_id
            AND id = 1)
  LOOP
    dbms_lob.append(dest_lob => l_hints, src_lob => chr(10) || rpad(i.hint, 499, ' '));
  END LOOP;
  dbms_sqldiag.drop_sql_patch(name => l_name, ignore => TRUE);
  l_name := dbms_sqldiag.create_sql_patch(sql_id => l_sql_id, hint_text => l_hints, name => l_name);
END;

The SQL patch created with that PL/SQL block worked fine!

Then, I contacted Nigel Bayliss to know whether the bug was already a known one. Since it wasn’t, he created a new one (Bug 33824792 – I_CREATE_PATCH INCORRECTLY PROCESSES HINT_TEXT CLOB). Thank you, Nigel!

In summary, if you create a SQL patch with many hints, you have to format the list of hints in a way that every 500th character doesn’t fall in the middle of a hint. If you don’t do so, the database engine creates a SQL patch containing invalid hints.

12cR2, 18c, 19c, 21c, Bug, Query Optimizer, SQL Patch
Observations About the Scalability of Data Loads in Snowflake

5 Comments

1 Ping/Trackback

  1. dbms_sqldiag | Oracle Scratchpad dbms_sqldiag | Oracle Scratchpad
    7 February 2022    

    […] with so many little details, I had forgotten about this until Christian Antognini published a blog note about it, including some sample to work around the […]

  2. stefan zehnder stefan zehnder
    7 February 2022    

    is the still not documented dbms_sqltune.import_sql_profile, that also accepts an array as list of hints, no longer working?
    it is still in the spec of the package, this seems to be an alternative hack

    Reply
    • Christian Antognini Christian Antognini
      8 February 2022    

      Hi Stefan

      It is an alternative hack only in case you have the necessary license to use it. Which, for example, is not possible if you are using Standard Edition.

      Best,
      Chris

      Reply
  3. Ric Van Dyke Ric Van Dyke
    7 February 2022    

    Holy mackerel! 100 hints? Dude, I think there is a bigger problem eh? :-) Long time now see, I hope you are well.

    Reply
    • Christian Antognini Christian Antognini
      8 February 2022    

      Hi Ric

      The actual problem is more related to the 20 subqueries to be unnested!

      Best,
      Chris

      Reply
  1. dbms_sqldiag | Oracle Scratchpad on 7 February 2022 at 11:44

Leave a Reply Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.