Oracle Scratchpad

August 21, 2019

sql_patch

Filed under: Infrastructure,Oracle,Tuning — Jonathan Lewis @ 4:49 pm BST Aug 21,2019

This note is a short follow-up to a note I wrote some time ago about validating foreign key constraints where I examined the type of SQL Oracle generates internally to do the validation between parent and child tables.  In that article I suggested (before testing) that you could create an SQL patch for the generated SQL to over-ride the plan taken by Oracle – a plan dictated to some extent by hints (including a “deprecated” ordered hint) embedded in the code. I did say that the strategy might not work for SQL optimised by SYS, but it turned out that it did.

Here’s a little script I ran to test a few variations on the theme:


declare
        v1      varchar2(128);
begin
        v1 :=   dbms_sqldiag.create_sql_patch(
                        sql_id  => 'g2z10tbxyz6b0',
                        name    => 'validate_fk',
                        hint_text => 'ignore_optim_embedded_hints'
--                      hint_text => 'parallel(a@sel$1 8)'      -- worked
--                      hint_text => 'parallel(8)'              -- worked
--                      hint_text => q'{opt_param('_fast_full_scan_enabled' 'false')}'  -- worked
                );
        dbms_output.put_line(v1);
end;
/

I’ve tested this on 12.2.0.1 and 19.3.0.0, but for earlier versions of Oracle, and depending what patches you’ve applied, you will need to modify the code.

The SQL_ID represents the query for my specific tables, of course, so you will have to do a test run to find the query and SQL_ID for the validation you want to do. This is what the statement for my parent/child pair looked like (cosmetically adjusted):

select /*+ all_rows ordered dynamic_sampling(2) */ 
        A.rowid, :1, :2, :3
from
        "TEST_USER"."CHILD" A , 
        "TEST_USER"."PARENT" B 
where
        ("A"."OBJECT_ID" is not null) 
and     ("B"."OBJECT_ID"(+) = "A"."OBJECT_ID")
and     ("B"."OBJECT_ID" is null)

The patch that the script creates simply tells Oracle to ignore the embedded hints (in particular I don’t want that ordered hint), but I’ve left a few other options in the text, commenting them out.

Without the patch I got the following plan:.


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  NESTED LOOPS ANTI (cr=399 pr=279 pw=0 time=47801 us starts=1 cost=70 size=22000 card=1000)
    100000     100000     100000   INDEX FAST FULL SCAN CHI_FK_PAR (cr=250 pr=247 pw=0 time=19943 us starts=1 cost=32 size=1700000 card=100000)(object id 73191)
     10000      10000      10000   INDEX UNIQUE SCAN PAR_PK (cr=149 pr=32 pw=0 time=3968 us starts=10000 cost=0 size=49995 card=9999)(object id 73189)
 

Rerunning the validation test with the patch in place I got the following plan – clearly the patch had had an effect.

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  HASH JOIN RIGHT ANTI (cr=246 pr=242 pw=0 time=96212 us starts=1 cost=39 size=22000 card=1000)
     10000      10000      10000   INDEX FAST FULL SCAN PAR_PK (cr=24 pr=23 pw=0 time=1599 us starts=1 cost=4 size=50000 card=10000)(object id 73235)
    100000     100000     100000   INDEX FAST FULL SCAN CHI_FK_PAR (cr=222 pr=219 pw=0 time=27553 us starts=1 cost=32 size=1700000 card=100000)(object id 73237)
 

Don’t worry too much about the fact that in my tiny example, and with a very new, nicely structured, data set the original plan was a little faster. In a production environment creating a hash table from the parent keys and probing it with the child keys may reduce the CPU usage and random I/O quite dramatically.

Bear in mind that the best possible plan may depend on many factors, such as the number of child rows per parent, the degree to which the parent and child keys arrive in sorted (or random) order, and then you have to remember that Oracle gets a little clever with the original anti-join (note that there are only 10,000 probes for 100,000 child rows – there’s an effect similar to the scalar subquery caching going on there), so trying to patch the plan the same way for every parent/child pair may not be the best strategy.

If you want to drop the patch after playing around with this example a call to execute dbms_sqldiag.drop_sql_patch(name=>’validate_fk’) will suffice.

 

8 Comments »

  1. […] solved – provided we can get the hint into the code (by hand, or SQL Patch, etc.) But the question still remains: where did the time go? The trace file was fairly long […]

    Pingback by Adaptive Joins | Oracle Scratchpad — April 13, 2022 @ 1:53 pm BST Apr 13,2022 | Reply

  2. In multitenant environment opt_param(‘container_data’ ‘current_directory’) hint is working when used as hint in the sql but it is not working when used with sql patch or sql profile. I need to optimize the query on all_arguments without changing the code. Any alternate approach to add this hint without changing the code?

    Comment by Anonymous — November 15, 2022 @ 2:03 pm GMT Nov 15,2022 | Reply

    • Thanks for the question.

      I’ve just tried it in 21.3.0.0 with the 10053 (CBO) trace enabled and the Outline Information shows the hint as part of the Outline Information, and reports it in:

        ***************************************
        PARAMETERS IN OPT_PARAM HINT
        ****************************
        container_data                      = 'CURRENT_DICTIONARY'
      
      

      As you say, when written into a query it seems to work, when attached as a patch it doesn’t.
      I’m a little surprised that it shows up as an optimizer parameter at all, but maybe the problem is that by the time it’s attached to the query it’s already too late for the restriction to take place.

      Unless faking it into place as an SQL Plan Profile, Baseline or Directive works I can’t think of a reasonable alternative. (Though for a fixed query you could try using the SQL Translation tool – package dbms_sql_translator.)

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — November 15, 2022 @ 3:36 pm GMT Nov 15,2022 | Reply

      • Sql translator is working after setting parameter at session level, this will be code level change.
        alter session set events = ‘10601 trace name context forever, level 32’;
        alter session set sql_translation_profile = profile_name;

        Comment by Anonymous — November 15, 2022 @ 8:13 pm GMT Nov 15,2022 | Reply

        • Thanks for the update.

          I haven’t done any tests on them yet, but Oracle introduced two hidden parameters in 18c that might have been created to replace the 10601 event.

          _STFForceTranslateOracleSQL     if TRUE translation profile will translate Oracle SQL statements
          _STFTranslateDynamicSQL         if TRUE translation profile will translate dynamic SQL statements
          

          I suspect the first one is the replacement for 10601 if you want to experiment.

          Regards
          Jonathan Lewis

          Comment by Jonathan Lewis — November 16, 2022 @ 7:55 am GMT Nov 16,2022

  3. Thank you Jonathan. Even faking is not working, I tried with all alternatives. I will try with sql translation.

    Comment by Anonymous — November 15, 2022 @ 7:47 pm GMT Nov 15,2022 | Reply

  4. Do you have any notes on WRI$_EMX_FILES. I am seeing a sys lob segment of this table being scanned but this table is not used in the sql, why optimizer is trying to access this segment/table.

    This sql is running fast
    SELECT * FROM (SELECT DISTINCT aa.PACKAGE_NAME, aa.OBJECT_NAME, aa.OVERLOAD, aa.OBJECT_ID, aa.OWNER FROM ALL_ARGUMENTS aa
    WHERE aa.OBJECT_ID IN (select object_id from ALL_OBJECTS where object_id=15199343 ) ORDER BY aa.PACKAGE_NAME, aa.OBJECT_NAME) WHERE ROWNUM < 102;

    This is slow sql and most of the time it is spending on accessing sys lob segment of WRI$_EMX_FILES. Why it is scanning this table and why it is causing an issue when I just added rownum<6 in above sql?
    SELECT * FROM (SELECT DISTINCT aa.PACKAGE_NAME, aa.OBJECT_NAME, aa.OVERLOAD, aa.OBJECT_ID, aa.OWNER FROM ALL_ARGUMENTS aa
    WHERE aa.OBJECT_ID IN (select object_id from ALL_OBJECTS where object_id=15199343 and rownum<6) ORDER BY aa.PACKAGE_NAME, aa.OBJECT_NAME) WHERE ROWNUM < 102

    Comment by Anonymous — November 16, 2022 @ 4:41 pm GMT Nov 16,2022 | Reply

    • Part of the “why” would be that the plan has probably changed, but it’s going to be a messy plan given the views you’re querying, so not one you’d really want to analyze.

      If it’s very slow you could call up the SQL Monitor Report as soon as it’s finished and see which lines of the plan show reads of the lob segment. That might give you a clue.

      You could also query dba_dependencies where referenced_name = ‘WRI$_EMX_FILES’ to see if you get any clues from the names of the users of the table. They seem to be all about “report files” and the notes about it are that the blob column holds reports from Enterprise Manager Express. Can’t think why that would have to be scanned for your query, though.

      I don’t know the answer to the question, though.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — November 16, 2022 @ 5:40 pm GMT Nov 16,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

Website Powered by WordPress.com.

%d bloggers like this: