Does level 3 optimization change PL/Scope data? No!

I gave a webinar on April 6, 2017 for the Taste of Kscope17 series for ODTUG (odtug.com) on Change Impact Analysis with PL/Scope. Here are the slides from SlideShare. I will add a link to the video when it is available.



After my presentation, this question came up: if you set the optimization level to 3 (inlining of subprogram code), will that change the PL/Scope data gathered? Interesting question.

Suppose your function body contains an assignment to variable x. Just that one place. But the function is called in ten places. Will PL/Scope find ten assignments to x or just one?

Just one, as you can see in this LiveSQL script. The identifier information is gathered before optimization. Which makes perfect sense. Post-optimized code is no longer PL/SQL code.

Here's the procedure I tested this one:

CREATE OR REPLACE PROCEDURE PLSCOPE_DEMO
IS
   PRAGMA INLINE (f1, 'YES');

   FUNCTION f1 (p NUMBER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN p * 10;
   END;

   FUNCTION f2 (p BOOLEAN)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN CASE WHEN p THEN 10 ELSE 100 END;
   END;

   FUNCTION f3 (p PLS_INTEGER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN p * 10;
   END;
BEGIN
   DBMS_OUTPUT.put_line (f1 (1));
   
   PRAGMA INLINE (f2, 'YES');
   DBMS_OUTPUT.put_line (f2 (TRUE) + f2 (FALSE));

   PRAGMA INLINE (f3, 'NO');
   DBMS_OUTPUT.put_line (f3 (55));
END;

Here's the query I used to get my identifier information back out.

  SELECT i.signature ||'-'|| s.line ||'-'|| s.text text 
    FROM    user_identifiers i 
         JOIN 
            user_source s 
         ON (    s.name = i.object_name 
             AND s.TYPE = i.object_type 
             AND s.line = i.line) 
   WHERE object_name = 'PLSCOPE_DEMO'  
ORDER BY s.line

And the output is the same regardless of the optimization level:

7189BE581AF770C7FA9F660333721E03-1-PROCEDURE PLSCOPE_DEMO
7189BE581AF770C7FA9F660333721E03-1-PROCEDURE PLSCOPE_DEMO
47BFC756469F1D97B6C84EF73A9C5D48-5-   FUNCTION f1 (p NUMBER)
785705602C9312732B24D9A341360ACF-5-   FUNCTION f1 (p NUMBER)
C762ED3C314ABB3D7257031401DD1583-5-   FUNCTION f1 (p NUMBER)
C762ED3C314ABB3D7257031401DD1583-5-   FUNCTION f1 (p NUMBER)
2C17DB6428F739B212C1E11EED057D63-6-      RETURN PLS_INTEGER
785705602C9312732B24D9A341360ACF-9-      RETURN p * 10;
1B5895A65C6952FDD192221BFC45A132-12-   FUNCTION f2 (p BOOLEAN)
C0BA9F319D1E759AD02A3C7138D62232-12-   FUNCTION f2 (p BOOLEAN)
EE1C5F13825B7DF0BF06D82DE633992E-12-   FUNCTION f2 (p BOOLEAN)
1B5895A65C6952FDD192221BFC45A132-12-   FUNCTION f2 (p BOOLEAN)
2C17DB6428F739B212C1E11EED057D63-13-      RETURN PLS_INTEGER
C0BA9F319D1E759AD02A3C7138D62232-16-      RETURN CASE WHEN p THEN 10 ELSE 100 END;
335958BDCA260D5E550A47F8194048DC-19-   FUNCTION f3 (p PLS_INTEGER)
205AC954A1ADD2BC3DC6A112A2081ADA-19-   FUNCTION f3 (p PLS_INTEGER)
2C17DB6428F739B212C1E11EED057D63-19-   FUNCTION f3 (p PLS_INTEGER)
335958BDCA260D5E550A47F8194048DC-19-   FUNCTION f3 (p PLS_INTEGER)
2C17DB6428F739B212C1E11EED057D63-20-      RETURN PLS_INTEGER
205AC954A1ADD2BC3DC6A112A2081ADA-23-      RETURN p * 10;
C762ED3C314ABB3D7257031401DD1583-26-   DBMS_OUTPUT.put_line (f1 (1));
1B5895A65C6952FDD192221BFC45A132-29-   DBMS_OUTPUT.put_line (f2 (TRUE) + f2 (FALSE));
1B5895A65C6952FDD192221BFC45A132-29-   DBMS_OUTPUT.put_line (f2 (TRUE) + f2 (FALSE));
335958BDCA260D5E550A47F8194048DC-32-   DBMS_OUTPUT.put_line (f3 (55));

For more information about PL/Scope:


For more information about Inlining:

Comments

  1. Hello Steven,

    Two small remarks:

    1.
    Though it seems like a very natural and intuitive usage,
    the PRAGMA INLINE positioned before a subprogram definition,
    like for function F1 in the above example will NOT work as
    expected.

    If we compile the above procedure with warnings enabled,
    we get the following:

    Line: 1 PLW-05018: unit PLSCOPE_DEMO omitted optional AUTHID clause;
    default value DEFINER used
    Line: 3 PLW-05011: pragma INLINE for procedure 'F1'
    does not apply to any calls
    Line: 29 PLW-06004: inlining of call of procedure 'F2' requested
    Line: 29 PLW-06004: inlining of call of procedure 'F2' requested
    Line: 32 PLW-06008: call of procedure 'F3' will not be inlined
    Line: 29 PLW-06005: inlining of call of procedure 'F2' was done
    Line: 29 PLW-06005: inlining of call of procedure 'F2' was done
    Line: 12 PLW-06006: uncalled procedure "F2" is removed.


    2.
    While hearing this specific question on today's webinar,
    I suddenly remembered one of our past PL/SQL Challenge quizzes,
    played on Aug 7 2013, which touched exactly the same issue.

    You see, when you make a mistake on a quiz, you will surely
    remember the lesson learned for a very long time :):)

    The author of that quiz was Vyacheslav Stepanov,
    who, by the way, deserves all our kudos for his amazing result
    in the Logic Championship of 2016, just to be announced.


    Cheers & Best Regards,
    Iudith




    ReplyDelete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts