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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

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 perspective…