Skip to main content

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

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel